1] Consider the following relation:

CAR_SALE(Car#, Date_sold, Salesman#, Commission%, Discount_amt)

The primary key is {Car#,Salesman#} because a car can be sold by multiple salesmen. The Discount_amt can be uniquely determined by the Date_sold and the Commission% can be uniquely determined by the Salesman#. Normalize into BCNF.

[2] Consider the following relation for published books:

BOOK (book-title, author-name, book-type, list-price, author-affiliation, publisher)

Assume that the book-title uniquely identifies the publisher and the book-type, the book-type uniquely identifies the list-price, and the author-name uniquely identifies her affiliation. Normalize the relation to BCNF

[3] Consider a relation R with attributes ABCDE. Let the following FDs be given:

A-> BC, BC->E, and E -> DA.

Find a key for R.

Is R in BCNF? Why? Normalize it to BCNF if not

[4] Suppose a relation has attributes A, B, C, D, E, F, G, H with the following functional dependencies:

AFC BEG CDGH BCAF DEHAF DEG

a)Extract an example of a transitive functional dependency

b)Find the closure of {ABF}, that is {ABF}⁺

c)Does the functional dependency ABF follow?

d)Does the functional dependency BCGF follow?

e)Come up with your own functional dependency (not a trivial one!) that follows from the functional dependencies above.

[5] We have an (over simplified) university database, with attributes CLASS, SECTION, STUDENT, MAJOR, INSTRUCTOR, RANK, SALARY, and TEXT. Intuitively a given CLASS (such as IS702) is divided into SECTIONs (such as Section 2) each of which has one INSTRUCTOR and various STUDENTS. Each CLASS has a set of TEXT BOOKS, which are used by all SECTIONS of the CLASS. Each STUDENT has one MAJOR. Each INSTRUCTOR has one RANK and one SALARY.

Starting with one relation:

R(CLASS, SECTION, STUDENT, MAJOR, INSTRUCTOR, RANK, SALARY, TEXT)

7-Find all non-trivial functional and multi-valued dependencies

8-Decompose into 4NF (and hence BCNF)