UGC NET CSE | June 2014 | Part 3 | Question: 22

Question

Consider the following relational schemas for a library database :

Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection(Title, Author, Catalog_no) with the following functional dependencies :

I. Title, Author $\rightarrow$ Catalog_no

II. Catalog_no $\rightarrow$ Title, Author, Publisher, Year

III. Publisher, Title, Year $\rightarrow$ Price Assume (Author, Title) is the key for both schemas. Which one of the following is true ?

• A. Both Book and Collection are in BCNF.
• B. Both Book and Collection are in $3$NF. 
• C. Book is in $2$NF and Collection in $3$NF.
• D. Both Book and Collection are in $2$NF. 

Answer 1

Answer : Book is in 2NF and Collection in 3NF.

Title, Author --> Catalog_no

Catalog_no --> Title , Author , Publisher , Year
Publisher , Title , Year} --> Price

Title , Author --> Catalog_no

Keys : Author , Title

Now , let's see Book Title , Author , Catalog_no , Publisher , Year , Price 

 [Title , Author]+ --> Title , Author , Catalog_no , Publisher , Year , Price

[Catalog_no]+--> Title , Author , Publisher , Year , Price , Catalog_no

So candidate keys are : Catalog_no , {Title , Author} 

This dependencies , {Publisher , Title , Year} --> Price  has Transitive Dependency.

Book  is in 2NF.

​If we consider collection

There is no partial dependency Here. it is in 2NF 

There is no Transitive dependency Here. it is in 3NF as well as in BCNF.

Answer 2

In Book Table There is Transitive dependency, Title, Publisher, year are dependent on catalog_no and Price dependent on all the three. so it is in 2NF, But in Collection table no transitive Dependency exists so it is in 3NF.

option is C