GATE CSE 2008 | Question: 69

Question

Consider the following relational schemes for a library database:

Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection(Title, Author, Catalog_no)

with the following functional dependencies:

1. $\text{Title Author }\rightarrow\text{ Catalog_no}$

2. $\text{Catalog_no }\rightarrow\text{ Title Author Publisher Year}$

3. $\text{Publisher Title Year}\rightarrow\text{ Price}$

Assume $\left\{\text{ Author, Title }\right\}$ is the key for both schemes. Which of the following statements is $\text{true}$?

• A. Both Book and Collection are in $\text{BCNF}$

• B. Both Book and Collection are in $\text{3NF}$ only

• C. Book is in $\text{2NF}$ and Collection in $\text{3NF}$

• D. Both Book and Collection are in $\text{2NF}$ only

Comments

If titleauthor is a candidate key then in third one it is partial dependency bcz title is the proper subset of ck and price is the none key attribute. Then how how it is in 2NF.plz help

---

Even I have this doubt!

---

2NF violation

$ C_k \space \underrightarrow { Partial } \space $ $ Non \space Prime \space attribute$.
This type of Functional Dependency shouldn’t be present.
We have 2 candidate keys $\{ Catalog \_no\} $ and $\{Author, Title\}$

The 3^rd functional dependency is clearly not a proper subset of above 2 candidate keys.
Hence the relation is in 2NF.

 

Answer 1

(C)
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.