CS4700/6700 Homework 1
(A) Write the following four queries, Q1--Q4, in relational algebra, concerning the following relations:
SUPP(SNAME,STREET, CITY)
SP(SNAME,PNAME,PRICE)
PART(PNAME,COLOR)
The underlined attributes form the primary keys of the corresponding relations. SUPP stores the names, streets and cities of suppliers; PART stores the names and colors of parts; a tuple (s,p,c) in SP says that supplier s can supply part p at price c. SNAME of SUPP is fk referencing SP, and PNAME of PART is fk referencing SP.
Q1. Find the names of suppliers who supply both 'red' parts and 'green' parts.
Q2. Find the names of suppliers who supply some 'red' parts but do not supply any 'green' parts.
Q3. Find the names of suppliers who supply all of the 'blue' parts. Solutions without "division" are preferred.
Q4. For each supplier, list the name of the supplier and the number of 'blue' parts the supplier supplies.
In Q2, "a supplier does not supply any 'green' parts" means that there are no green parts supplied by the supplier.
(B) Show the results of relational algebra queries Q1 and Q6 on Slide 6.57 (L4RACh08.pptx) when applied to the database state on Slide 6.7 (L4RACh08.pptx). You need to "execute" the multi-step relational algebra queries. You should also show the results of the temporary relations in those multi-step queries. You can do this on more queries to help you learn the material.
(C) Answer the following questions:
C.1. What is the difference between a database schema and a database state?
C.2. Describe the three-schema architecture. Why do we need mappings between schema levels? How do different schema definition languages support this architecture?
C.3 What is the difference between a key and a superkey?
There are many more questions like C.1, C.2 and C.3 in the book. You should work out as many exercise questions as possible to help you learn and prepare for the exams. ^(************)