Database Midterm Due: 3:00pm Monday, Nov. 7.

Part I: ER diagram

Consider the Salespeople-Customers-Orders domain described in our sample database used in the earlier labs. Draw an ER diagram to depict the domain with as much details as possible. Your diagram should graphically specify

• the relationships between entity sets,
• the attributes of entity sets and relationships,
• the primary keys of entity sets,
• for each relationship, whether it is a one-to-many, one-to-one, or many-to-many relationship, and
• for each entity set, whether the entities in the entity set have total participation in a relationship.

Part II: SQL queries:

Consider the tables in the Supplier-Parts-Catalogue Domain. (Note that the same part may be supplied by zero, one or more suppliers with the same cost or with different costs.) For each task in the following write a single SQL query to accomplish the task. The first 12 tasks don’t need to use subqueries, while the rest 7 may or may not need to use subqueries.

1. List the names of suppliers whose addresses contains the string “ London”
2. List the pids of parts supplied by suppliers whose addresses contains the string “ London”.
3. List the pids and the pname, and the cost of parts supplied by suppliers whose addresses contains the string “ London”.
4. List the pids of parts which are supplied by suppliers whose addresses contains the string “ London” and whose colors are red.
5. List the pids and the pname, and the cost of parts which are supplied by suppliers whose addresses contains the string “ London” and whose colors are red.
6. With the help of the aggregation function AVG, calculate the average cost (price) of parts charged by suppliers whose addresses contains the string “ London”.
7. With the help of the aggregation function AVG, calculate the average cost (price) of red parts charged by suppliers whose addresses contains the string “ London”.
8. With the help of the order by clause, from the parts table list the parts (their pid, pname, and color) in groups according to their colors. In other words, records of the parts of the same color should be displayed as a contiguous group.
9. With the help of the order by clause, from the catalogue table and for each supplier, list the sid, the pids and the costs of the supplier and various parts supplied by that supplier. In other words, records of the sid, the pids and the costs with the same sid (supplier ID) should be displayed as a contiguous group.
10. With the help of the group by clause and the aggregation function AVG, from the catalogue table and for each supplier, calculate and list the sid of the supplier and the average cost of parts supplied by the supplier.
11. With the help of the order by clause, from the catalogue table and for each part, list the pid, the sids and the costs of the part supplied by various suppliers. In other words, records of the pid, the sids and the costs with the same pid (the part ID) should be displayed as a contiguous group.
12. With the help of the group by clause and the aggregation function AVG, from the catalogue table and for each part, calculate and list the pid of the part and the average cost of the part supplied by various suppliers.

13. The sids of suppliers who supply some parts with cost less than $15.
14. The names of suppliers who never charge more than $15 for the parts they supply. In other words, we want to know the names of the suppliers whose sids never appear (NOT IN) in the list of sids of those who do supply some parts with cost more than $15.
15. The names of suppliers who do supply some parts and who always charge more than $15 for the parts they supply. In other words, we want to know the names of the suppliers (i) whose sids do appear (IN) in the list of sids of those who supply some parts in the catalogue and (ii) whose sids never appear (NOT IN) in the list of sids of those who supply some parts with cost less than or equal to $15.
16. List the name of every supplier who supplies 10 or more different parts.
17. List the name of every part that is supplied by exactly one supplier.
18. For each part that is supplied by at least a supplier, list (i) the pid of the part and (ii) the cheapest cost charged for that part by the suppliers.
19. For each part that is supplied by at least a supplier, list (i) the pid and the name of the part together with (ii) the sid and the name of the supplier(s) who charges the cheapest cost for that part.
20. List the names of the parts that are supplied by nobody.