1] Consider the following relation: CAR_SALE(Car#, Date_sold, Salesman#, Commiss

Question

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: AFàC BàEG CDàGH BCàAF DEHàAF DEàG 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)

Explanation / Answer

The relation is in 1NF because all attribute values are single atomic

values.

• The relation is not in 2NF because:

• Car# DateSold

• Car# DiscountAmount

Salesman# Commission%

Thus, these attributes are not fully functionally dependent on the primary key.

2NF decomposition:

CAR_SALE1(Car#, DateSold, DiscountAmount)

CAR_SALE2(Car#, Salesman#)

CAR_SALE3(Salesman#, Commission%)

The relations will not be in 3NF because:

Car# DateSold DiscountAmount

DateSold is neither a key itself nor a subset of a key and Discount amount is not a prime attribute.

3NF decomposition:

CAR_SALES1A(Car#, DateSold)

CAR_SALES1B(DateSold, DiscountAmount)

CAR_SALE2(Car#, Salesman#)

CAR_SALE3(Salesman#, Commission%)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.