168360 Database Systems Semester I/2547 Page 1/1
Exercise 4
Given: July 5, 2004, Submitted: July 14, 2004
Maximum number of points possible: 30. This exercise counts for 1% of your overall grade.
1. (2 points) Explain the statement that relational algebra operators can be composed. Why is the ability to compose operators important?
2. (24 points) Consider the following schema:
Supplies(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color:string)
Catalog(sid: integer, pid: integer, cost:real)
The key fields are underlined, and the domain of each field is listed after the filed name. Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged fro parts by Suppliers. Write the following queries in relational algebra (2 points) and SQL (1 point):
2.1 Find the names of suppliers who supply some red part (3 points)
2.2 Find the sids of suppliers who supply some red or green part (3 points)
2.3 Find the sids of suppliers who supply some red part or are at 123 Mitrapap Rd. (3 points)
2.4 Find the sids of suppliers who supply some red part and some green part. (3 points)
2.5 Find the sids of suppliers who supply every part. (3 points)
2.6 Find the sids of suppliers who supply every red part. (3 points)
2.7 Find the pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid. (3 points)
2.8 Find the pids of parts supplied by at least two different suppliers (3 points)
3. (4 points) Consider the Supplier-Parts-Catalog schema from the previous question. State what the following queries compute:
3.1 psname(psid((scolor=’red’Parts) (scost < 100 Catalog)) Suppliers) (1 point)
3.2 psname(psid((scolor=’red’Parts) (scost < 100 Catalog) Suppliers)) (1 point)
3.3 (psname((scolor=’red’Parts) (scost < 100 Catalog) Suppliers)) intersect
(psname((scolor=’green’Parts) (scost < 100 Catalog) Suppliers)) (1 point)
3.4 psname((psid,sname((scolor=’red’Parts) (scost < 100 Catalog) Suppliers))intersect
(psid,sname ((scolor=’green’Parts) (scost < 100 Catalog) Suppliers))) (1 point)