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 Ban