Download Relational Algebra: Sample Solutions and more Study notes Algebra in PDF only on Docsity!
Introduction to Databases Fall-Winter 2009/
Exercises Michail Kazimianec Werner Nutt Damiano Somenzi
Relational Algebra: Sample Solutions
Note that the solutions given here are samples, i.e., there may be many more ways to express these queries in relational algebra.
- Write queries in relational algebra
Write the following queries in relational algebra.
- āFind the names of suppliers who supply some red part.ā
Ļsname((Ļcolour=ā²redā²^ (Part) 1 Catalog) 1 Supplier))
Since there is not subscript under the joins, the joins are natural joins, i.e., the common attributes are equated.
- āFind the IDs of suppliers who supply some red or green part.ā
Ļsid(Ļcolour=ā²redā² (^) āØ colour=ā²greenā² (Part) 1 Catalog)
An equivalent formulation uses the union operator
Ļsid(Ļcolour=ā²redā² (Part) 1 Catalog āŖ Ļcolour=ā²greenā² (Part) 1 Catalog).
The latter version can be refined by pushing the projection through the union:
Ļsid(Ļcolour=ā²redā²^ (Part) 1 Catalog) āŖ Ļsid(Ļcolour=ā²greenā²^ (Part) 1 Catalog).
- āFind the IDs of suppliers who supply some red part or are based at 21 George Street.ā
Ļsid(Ļcolour=ā²redā² (Part) 1 Catalog) āŖ Ļsid(Ļaddress=ā²21G.S.ā² (Supplier)).
- āFind the names of suppliers who supply some red part or are based at 21 George Street.ā
Ļsname(Ļcolour=ā²redā²^ (Part) 1 Catalog 1 Supplier) āŖ Ļsname(Ļaddress=ā²21G.S.ā²^ (Supplier)).
Alternatively, we can pull the projection on sname to the top level.
Ļsname(Ļcolour=ā²redā²^ (Part) 1 Catalog 1 Supplier āŖ Ļaddress=ā²21G.S.ā² (Supplier)).
- āFind the IDs of suppliers who supply some red part and some green part.ā
Ļsid(Ļcolour=ā²redā² (Part) 1 Catalog) ā© Ļsid(Ļcolour=ā²greenā² (Part) 1 Catalog)
Alternatively, we can replace the intersection with a join:
Ļsid(Ļcolour=ā²redā²^ (Part) 1 Catalog) 1 Ļsid(Ļcolour=ā²greenā²^ (Part) 1 Catalog).
- āFind pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid.ā First, by creating temporary copies we introduce two versions of Catalog:
Cat1 ā Catalog Cat2 ā Catalog.
Then we join the two versions and take the projection:
ĻCat1.sid,Cat2.sid(Cat1 (^1) Cat1.pid=Cat2.pid ā§ Cat1.cost>Cat2.cost Cat2)
Note that we have to qualify the attributes by the relation name because both relations have attributes with the same names.
- āFind the IDs of suppliers who supply only red parts.ā
Ļsid(Supplier) \ Ļsid(Catalog 1 Ļcolour 6 =ā²redā²^ (Part)).
- āFind the IDs of suppliers who supply every part.ā
First, we observe that the projection of Catalog on the attributes sid and pid,
Ļsid,pid(Catalog)
contains all pairs of suppliers and the parts they supply, expressed by sids and pids. The Cartesian product
Ļsid(Catalog) Ć Ļpid(Part)
contains all possible combinations of (1) the suppliers that supply something with (2) all the products. If we take the set-theoretic difference of the second and the first relation,
Ļsid(Catalog) Ć Ļpid(Part) \ Ļsid,pid(Catalog),
we obtain those combinations of suppliers and parts where the supplier does not deliver the part. Let us store the difference in the temporary relation Temp1:
Temp1 ā Ļsid(Catalog) Ć Ļpid(Part) \ Ļsid,pid(Catalog).
Now, Ļsid(Temp1), the projection of this relation onto the attribute sid, gives us the suppliers that do not supply some part, i.e., some part is missing in their range of products.
If there is a foreign key constraint on Catalog(sid), then the join with Supplier is superfluous. (Some query optimisers find out such redundant joins. For instance, Microsoftās SQL Server eliminates joins that are redundant because of foreign key constraints, and it eliminates parts of the query that are inconsistent.)
- Ļsname(Ļsid,sname(Ļcolour=ā²redā²^ (Part) 1 Ļcost< 100 (Catalog) 1 Supplier) ā©
Ļsid,sname(Ļcolour=ā²greenā²^ (Part) 1 Ļcost< 100 (Catalog) 1 Supplier))
āFind the names of suppliers supplying some red part for less than 100 Quid and some green part for less than 100 Quid.ā This is the query that intuitively makes most sense.
