For this exam, consider the following schema and instances of the relations. Feel free to remove this page from the exam.
Our database is very simple. It is composed of three relations: Parts, Suppliers and Catalog.
The Catalog table contains the parts that are being offered by a given supplier at a given price (a part is missing a price if this field is NULL). Every pid in Catalog exists in Parts, and every sid in Catalog exists in Suppliers.
Parts(pid: integer, pname character(40), color character(20));
Primary key: pid.
pid pname color
6 Anti-Gravity Turbine Generator Cyan
7 Anti-Gravity Turbine Generator Magenta
8 Fire Hydrant Cap Red
9 7 Segment Display Green
10 SQL queries Green
Suppliers(sid: character(10), sname: character(40), address: char(50));
Primary key: sid.
sid sname address
amazon Amazon Canada 1 Grub St., Potemkin Village, IL 61801
walmart Walmart Inc 4 My Way, Bermuda Shorts, OR 90305
rim Research in Motion 99999 Short Pier, Terra Del Fuego, TX 41299
google Google Inc. 2 Groom Lake, Rachel, NV 51902
Catalog(sid: character(10), pid: integer, price: real);
Primary key: (sid,pid).
sid pid cost
amazon 8 11.7
walmart 8 7.95
rim 8 12.5
rim 9 1
amazon 10 10.5
CSC 370 PAGE 2
1. Writing queries in Relational Algebra and SQL
Give both relational algebra and SQL queries to answer the following questions. Your relational algebra should match your SQL queries.
1.1)  For every supplier, lists its sname and the pid of each of the parts they offer. Result should contain two attributes.
1.2)  List the pname of parts that are being offered at $10 or more. Result should contain only one attribute.
1.3)  For every pid in relation Parts, list the number of suppliers that offer it, and the minimal price at which it is offered. Result should contain three attributes.
1.4)  How many parts in table Parts are not being offered by any supplier? Result should contain only one attribute.
CSC 370 PAGE 3
1.5)  List the pid and sid of parts that offered by such supplier and are missing a price. Result should contain two attributes.
1.6)  For every supplier, list its sid and the average price of the parts they offer. Result should contain two attributes.
1.7)  List the pid and the pname of parts that are offered by exactly 3 suppliers. Result should contain two attributes.
1.8)  List the pid of the parts that are being offered by both suppliers: Amazon and Walmart (these are their sid). Result should contain one attribute.
CSC 370 PAGE 4
1.9)  Compute the difference between the average price of parts with pid l2 and 32. In other words, compute (the average price of partid l2) minus (the average price of pid 32). The result should contain one tuple with one attribute.
1.10)  For every pid in the relation Catalog, list the sname of the supplier who offers it a the lowest
price, and such price. Result should contain three attributes.
1.11)  List the pid of parts that are being offered by at least two suppliers at exactly the same price. Your result should contain two columns: the pid of the two parts, and their price.
CSC 370 PAGE 5
2. Relational Model
2.1)  Given the relation R(A;B;C;D) and the set of functional dependencies A ! BC, BC ! A, and B ! D. Find all the candidate keys of this relation. Show all your work.
For this you have to compute the closure of each combination of attributes ABCD, ABC, ABD, … A, B, C, D (15 in total). The candidate keys are only A and BC. End of examination