Spring 2009 CS 146 Homework 4: Due Feb.27^th, 10am. No extensions.

Ques.1: Consider the following collection of relations and dependencies. For each of the schemas (parts a through e), do the following:

(i) determine the candidate keys, (ii) determine if the relation schema is in Third Normal Form, and (iii) determine if a relation is in BCNF and if it is not then decompose it into a collection of BCNF relations. Show all of your work and explain which dependency violation you are correcting by your decompositions. Note that schema in (c) contains two relations.

(a) R1(A,C,B,D,E), Dependencies: A ® B, C® D

(b) R2(A,B,F), Dependencies: AB® F, B® F

(c) R3(A,B), R4(C,D,E,F) Dependencies: A® B, C ® D, D ® EF

(d) R5(A,B,C,D,E) with functional dependencies D → B, CE → A.

(e) R6(A,B,C,D,E) with functional dependencies A→ E, BC→ A, DE→ B.

Ques.2: Consider a database of ship voyages with the following attributes: S (ship name), T (type of ship), V (voyage identifier), C (cargo carried by one ship on one voyage), P (port) and D (day). We assume that a voyage consists of a sequence of events where one ship picks up a single cargo, and delivers it to a sequence of ports. A ship can visit only one port in a single day.

This description implies the following functional dependencies:

S → T

V → SC

SD→ PV

Determine the key(s) for this relation (assuming all attributes are in one table to begin with).

Give a lossless join decomposition into BCNF. Does this preserve all dependencies?

Give a lossless join, dependency preserving decomposition into 3NF.