Page 1 of 2
COMP 5120/6120 Database Systems I
Homework #2
1. What is a foreign key constraint? Why are such constraints important? What is
referential integrity? (10 pts)
2. Explain the difference between external, internal, and conceptual schemas. How
are these different schema layers related to the concepts of logical and physical
data independence? (10 pts)
3. Consider the following schema:
Suppliers (sid: integer, sname: string, address: string)
Parts (pid: integer, pname: string, color: string)
Catalog (sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by suppliers. Write the
following queries in SQL (40 pts):
a.) Find the pnames of parts for which there is some supplier.
b.) For each part, find the sname of the supplier who charges the most for that
part.
c.) Find the sids of suppliers who supply only red parts.
d.) Find the snames of suppliers who supply every part.
4. Consider the following schema:
Employee (person-name, street, city)
Works (person-name, company-name, salary)
Company (company-name, city)
Manages (person-name, manager-name)
Write the following queries in relational algebra (40 pts):
a.) Find the names of all employees who work for Auburn Bank.
Page 2 of 2
b.) Find the names and cities of residence of all employees who work for Auburn
Bank.
c.) Find the names, street address, and cities of residence of all employees who
work for Auburn Bank and earn more than $50,000 per year.
d.) Find the names of all employees in this database who live in the same city as
the company for which they work.
