Please explain why book is not in 3NF


#1

Consider the following relational schemes for a library database:
Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)

with in the following functional dependencies:

I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price
Assume {Author, Title} is the key for both schemes. Which of the following statements is true?
(A) Both Book and Collection are in BCNF
(B) Both Book and Collection are in 3NF only
© Book is in 2NF and Collection is in 3NF
(D) Both Book and Collection are in 2NF only


#2

It is given that {Author , Title} is the key for both schemes.
The given dependencies are :

{Title, Author} --> {Catalog_no}
Catalog_no --> {Title , Author , Publisher , Year }
{Publisher , Title , Year} --> {Price}
First , let’s take scheme Collection ( Title , Author , Catalog_no ) :

{Title , Author} --> Catalog_no

{Title, Author} is a candidate key and hence super key also and by definition of BCNF; this is in BCNF.

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}

But in the given dependencies , {Publisher , Title , Year} --> Price , which has Transitive Dependency. So , Book is in 2NF, not in 3NF.


#3

Table Collection is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and {Author, Title} is key for collection. Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title Author Publisher Year”. Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Author]. Book is in 2NF because every non-prime attribute of the table is either dependent on the key [Title, Author], or on another non prime attribute.