2005 Database Final Exam Due: Dec. 21, 2005.

No collaboration allowed. Send your questions to me.

Check the csci.biola.edu website for the most recent updates.

 

1. Relation R has four attributes A, B, C, D  (i.e. a table with four columns A, B, C, D) and the following are the dependencies we have among the attributes of R:

C==>D                        C==>A                        B==>C

 

In other words, (i) the value of attribute C completely determines what should be the value of attribute D, (ii) the value of attribute C completely determines what should be the value of  attribute A,  and (iii) the value of attribute B completely determines what should be the value of  attribute C.

 

Question 1.a: Show all the candidate key(s) of relation R.

Question 1.b: Is R in 2NF? Is R in 3NF? Is R in BCNF? Explain why.

Question 1.c: If R is not in 3NF, show how you would decompose R further into a number of relations in 3NF?

 

2. Questions 2.a, 2.b, and 2.c: Like in 1.a, 1.b, and 1.c, but the following are the dependencies we have among the attributes of R:

B==>C                        D==>A           

 

 

3. Questions 3.a, 3.b, and 3.c: Like in 1.a, 1.b, and 1.c, but the following are the dependencies we have among the attributes of R:

ABC==>D                   D==>A           

 

4. Questions 4.a, 4.b, and 2.c: Like in 1.a, 1.b, and 1.c, but the following are the dependencies we have among the attributes of R:

A==>B                        BC==>D         A==> C

 

5. Questions 5.a, 5.b, and 5.c: Like in 1.a, 1.b, and 1.c, but the following are the dependencies we have among the attributes of R:

AB==>C         AB==>D         C==>A                        D==> B

Part II: SQL queries: Consider the following tables in the same Supplier-Parts-Catalogue Domain you had for the midterm.

The Suppliers table

sid

The supplier's ID as a number. It is the primary key.

sname

The supplier's name as  a string

address

The supplier's address as  a string

 

The Parts table

pid

The part's ID as a number. It is the primary key.

pname

The part's name as  a string

color

The part's color as  a string

 

The Catalog table

sid

The ID of a supplier supplying a part. sid together with pid form the primary key.

pid

The ID of the supplied part. sid together with pid form the primary key.

cost

The cost (as a number) charged by the

 

 

Note that the same part may be supplied by zero, one or more suppliers with the same cost or with different costs. For each task in the following write a SQL query to accomplish the task.

1.      List the names of all the parts of the red color.

2.      List the sids of all suppliers who do supply one or more  parts of the red color.

3.      With the help of the group by clause and the aggregation function AVG, for each possible part color, calculate and list the color and the average cost of parts of that color.

4.      With the help of the group by clause and the aggregation function AVG, from the catalogue table and for each supplier X who does supply at least one part, calculate and list the sid of the supplier X and the average cost of parts supplied by X.

5.      List the sid of every supplier X and the pid of every part Y where X does supply Y and charge a cost less than or equal to the average cost of X charged by all the suppliers of X.

6.      List the pids of parts which are never supplied with cost less than $15 by any supplier.

7.      List the names and the pids of parts which are never charged more than $15 by any supplier.

8.      List the name and the pid of every part that is supplied by no more than 2 suppliers.

9.      For each supplier X that does supply at least one part, list the cost and the pid of the most expensive part supplied by the supplier X together with the sid of X.

10.  List the sids and the names of the suppliers who supply more than 30% of the parts.