Question

Consider the following relation for published books: BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher) Author_affil refers to the affiliation of the author. Suppose the following dependencies exist: Book_title -> Publisher, Book_type Book_type -> Listprice Author_name -> Author-affil (a) What normal form is the relation in? Explain your answer. (b) Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

Answer 1

The key for this relation is Book_title,Authorname. This relation is in 1NF and not in 2NF as no attributes are FFD on the key. It is also not in 3NF

Explanation:

lGiven the relation

lBook(Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)

land the FDs

lBook_title . Publisher, Book_type

lBook_type . Listprice

lAuthorname .Author_affil

(a) The key for this relation is Book_title,Authorname. This relation is in 1NF and not in 2NF as no attributes are FFD on the key. It is also not in 3NF

(b) 2NF decomposition:

lBook0(Book_title, Authorname)

lBook1(Book_title, Publisher, Book_type, Listprice)

lBook2(Authorname, Author_affil)

This decomposition eliminates the partial dependencies.

3NF decomposition:

lBook0(Book_title, Authorname)

lBook1-1(Book_title, Publisher, Book_type)

lBook1-2(Book_type, Listprice)

lBook2(Authorname, Author_affil)

lThis decomposition eliminates the transitive dependency of Listprice