GATE 2008 Normalization

Question

I am getting Collection is in BCNF so it is in 3NF but for BOOK relation there is Partial dependency.So how option C is TRUE? 

Answer 1

Just go with the basic defenition of 3NF and 2NF.Now consider the relation book and the dependencies on it.Lets mention the dependencies on it.

All the 3 dependencies given in the question holds for book relation.So we have {author,title} as the key so author and title as the prime attributes and from 2nd dependency,

catalog --> author, title so catalog is also a candidate key and hence a prime attribute

So the remaining attributes are non prime.

Now it is not in BCNF since all determinants are not keys.

For 3NF , if we see the 3rd dependency ,neither {publisher,title,year} is a key nor price is a prime attribute.And this dependency is in the book relation.Hence the book relation is not in 3NF.

But you can see that none of lhs part(determinant) of the 3 FDs in LHS is proper subset of {author,title} .Hence no partial dependency exists and hence the book relation is in 2NF.

Hence C) option is true.

Comments

Book is in 2NF but Collection is in BCNF because:-

Collection(Title,Author,Catalog_No)

The FDs applicable here are:-  1) Title Author->Catalog_No    2) Catalog_No->Title Author 

Here {Author,Title} is given as key. Catalog_No is also a key bcz (Catalog_No)⁺={Catalog_No Title Author}

So key attributes ={Catalog_No Title Author} and there is no non-key attribute

1) Title Author->Catalog_No    2) Catalog_No->Title Author 

The LHS of both of the FDs applicable here are Superkey hence it satisfies the condition of BCNF.