Assignment 1

Total Scores: 60 (6% of total grade).

Due Tues. Oct. 6th during class time

1. (12 points) Construct an ER diagram for a prison system. Define the tables (schemas) corresponding to your ER diagram, and indicate the primary key of each table.

• Make sure your design includes at least the following: policemen, criminals, judges, sentences, police departments, prisons, guardians.
• For each policeman-criminal pair a date of capture is recorded.
• For each criminal-judge pair a date of sentence is recorded
• Each policeman is assigned to only one police department

N.B.There are many "correct" answers for this question, depending on the assumptions made. State your assumptions clearly. You should try to explore more E-R features such as weak entity sets, generalization and aggregation. For full points, your design and assumptions should match.

2. (24 points) Consider the following relational database:

pilot(pname,city-of-residence,rank).

flight(flight#,city-of-origin,city-of-destination).

times(pname,flight#,day-of-the-week).

Express each of the following queries in:

- the relational algebra

- the tuple relational calculus

- the domain relational calculus

a) Flight number of a flight that departs on Wednesdays from Aleph and is piloted by Amelia Earheart.

b) Names of all pilots who live in the same city as the city of origin of a flight they are piloting.

c) Flight number and city of destination for all flights that depart later in the week than all the flights that leave from Vancouver.

d) Names of all pilots that fly from every city from which the pilot Aladin flies.

3. (12 points) Let R = (A, B, C) and S = (D, E, F). Express each following expression in two other equivalent forms (relational algebra, tuple relational calculus, or domain relational calculus).

a)

b)

c)

4. (12 points) Consider the relational database defined by the schema in your solution of question 1. Give a relational-algebra expression for each of the following queries:

a. Find the prison with the most criminals.

b. Find the average sentence at the prison named Chateau-d'If.

c. Find the smallest police department (number of policemen).

d. Find the names of guardians of the prison with the highest average sentence.

N.B. It is not necessary to read The Aleph, or The Count of Monte Cristo where the Chateau d'If is depicted- but it is fun, I advise it for your holidays :-)