Assignment 1
Due date: Wednesday, October 1st, 5:30PM, in class.
NOTES:
- 10% penalty for each day late, up to three days
- This is an individual homework; no groups allowed
- Chapter numbers refer to the textbook's third edition
- Solutions to odd-numbered exercises are availble here
This assignment is meant to give you practice with the fundations of database systems, the relational model (keys and constraints), SQL and Relational Algebra.
Question 1 (2 points)
Exercise 1.2, Page 23. Explain it in in your own words.
Question 2 (3 points)
Exercise 1.4, Page 23. Explain it in in your own words.
Question 3 (5 points)
Assume that attributes with the same name in different relations represent the same domain and that
the primary key attributes in each relation are underlined. Answer the following questions given the
relational schema below:
Rel1(A, B, C, D)
Rel2(B, C, D, E, F, G)
Rel3(D, H, I)
Rel4(J, D, K, H)
- List all potential foreign keys in the four relations. (1 point)
- List all superkeys of Rel2. (1 point)
- List all candidate keys of Rel3. (1 point)
- What is the degree and cardinality of Rel4? (1 point)
- If the cardinality of Rel4 is m, what is the cardinality of π D,H(Rel4)? (1 point)
Question 4 (10 points)
Alice and Brian are two students who took the CS 448 some years ago.
Now, years later, they meet and challenge themselves about their understanding of
the relational model. Alice proposes the following relation schemas.
Rel1(A: string, B : integer, C : string)
Rel2(B : integer, D : string, E : integer, F : string)
Rel1.B is a foreign key to Rel2.B.
F is a UNIQUE KEY for Rel2.
Alice asks Brian to provide an instance for the above relation. Here is the
instance given by Brian:
| Rel1(A, B, C) |
| A | B | C |
| null | 1 | up |
| aa | null | 100 |
| bb | 3 | up |
| cc | 4 | null |
| dd | 4 | down |
|
| Rel2(B, D, E) |
| B | D | E | F |
| 1 | Ted | 1 | one |
| 2 | Fred | di | two |
| 4 | Joe | null | null |
| 5 | null | 1 | four |
| 6 | Jed | ab | five |
|
List ALL constraint violations of Brian's instance with respect to the schema givem by Alice.
Question 5 (80 points)
Assume the following relational schemas
ProductionHouse(pid, name, location, president)
Produce(pid, mid, manHour)
Movies(mid, title, director)
Below are a number of database queries formulated in English. Express these queries using SQL and Relational Algebra.
If a query cannot be expressed in one of these languages, provide an explanation.
- Find the names of Production Houses that produce some movies directed by Ben Affleck.
- Find the pid of Production Houses that produce some movies directed by Ben Affleck or Peter Jackson.
- Find the pid of Production Houses that produce some movies directed by Ben Affleck and some movies directed by Peter Jackson.
- Find the pid of Production Houses that produce some movies directed by Ben Affleck or are located in "Ney York".
- Find the pid of Production Houses that produce all movies directed by Ben Affleck.
- Find the pid of Production Houses that produce only movies directed by Ben Affleck or by Peter Jackson.
- Find the pid of Production Houses that produce at least two movies directed by Ben Affleck.
- Find the pid of Production Houses that produce the largest number of movies.
- Show pid, name and the total manHours for each Production House.
- Show pid, name and the total manHours for each Production House that has produced at least 5 movies.