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

It is also given that the {Auther, Title} are the candidate keys, along with the {Catalog_no} and composite {Author, Title} so in the 3rd dependency we have given : Publisher Title Year -> Price and this is the fully functional dependency , since Title is also the Candidate key and not the proper subset of the Candidate Key.

Therefore it follows the condition for the 2NF.
 

---

That's the accurate point that {title} is also a candidate key; hence, the third functional dependency comes under 2NF form without violation.

---

@Aadhyaa Gupttaa, @niharika rathore

For the Book table, there are 2 candidate keys: {Catalog_no} , {Title, Author}. Book table is in 2NF but Not in 3NF.

For the Collection table, there are 2 candidate keys: {Catalog_no} , {Title, Author}. Collection table is in BCNF.

In any table, {Title} alone is Not a candidate key.

Complete & Correct Analysis: https://youtu.be/ldl47NMWciE?feature=shared

Answer 1

Answer: C

It is given that $\{\text{Author},\text{Title}\}$ is the key for both schemas.

The given dependencies are : 

• $\{\text{Title}, \text{Author}\}\to  \text{Catalog_no}$
• $\text{Catalog_no} \to \{\text{Title},\text{Author}, \text{Publisher}, \text{Year}\}$
• $\{\text{Publisher}, \text{Title}, \text{Year}\} \to \{\text{Price}\}$

First, let's take schema Collection (Title, Author, Catalog_no) :

• $\{\text{Title}, \text{Author}\} \to \text{Catalog_no}$

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

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

• $\{\text{Title}, \text{Author}\}^+ \to \{\text{Title}, \text{Author}, \text{Catalog_no}, \text{Publisher}, \text{Year}, \text{Price}\}$
• $\{\text{Catalog_no}\}^+ \to \{\text{Title}, \text{Author}, \text{Publisher}, \text{Year}, \text{Price}, \text{Catalog_no}\}$

So candidate keys are : $\text{Catalog_no}, \{\text{Title}, \text{Author}\}$ 

But in the given set of dependencies we have $\{\text{Publisher}, \text{Title}, \text{Year}\} \to \text{Price},$ which has a Transitive Dependency. So, Book is not in 3NF but is in 2NF.

Comments

TA->C and

C->TAPY this is transitive dependency

---

you need not to think in terms of transitive dependency. instead think like for 3nf you should have either a super key in lhs or a prime attribute in rhs.

take these fd's: catalog_no -> publisher, year. here a non prime attribute is determining 2 other non prime attribute. so its violating 3nf rule. so book is not in 3nf.

---

@Kesavan_Nivesh here catalogue_no is prime attribute

Answer 2

(c)

in collection all the non prime attributes depend directly on candidate key - so BCNF and hence 3NF (actually collection has only prime attributes so it should, by default be at least in 3NF)

in book the non prime attr (price), depends indirectly on the candidate key (catalog_no) thus forming transitive dependency and hence not in 3NF. There is no partial dependency so - 2nf

Comments

Primary key -> any thing(prime, non prime, key) will be in BCNF.

---

sir collection has some other functional dependencies which u can derive if u take closure of title or author they give only title->title and author ->author

hence the rhs is not a super key but lhs is is a prime attribute hence the collection is only in 3NF but not in BCNF

---

@Arjun sir Cant we think like this that since key has only one attribute. so no part of key, no partial dependency . So book is in 2NF. We can easily see transitive dependency , so it is not 3nf.

Answer 3

Answer: (C)

 

Answer 4

Ans. C

In Relation Book there is a transitive dependency and hence not in 3NF. There is no partial dependency so in 2NF

In Relation Collection all the non prime attributes depend directly on candidate key. In fact, collection has only prime attributes. So BCNF and hence 3NF.

Comments

@ Raushank2

catalog_no->Title Author Publisher year 

According to 3nf if X->Y

then 1) X is a super key or 2) y is a prime attribute 

in case of title ,it is prime attribute so by this it must be in 3 nf ?

https://gateoverflow.in/1260/gate2007-62-ugcnet-june2014-ii-47 see this

Answer 5

one point worth noting is that even though the key is mentioned but that simply refers to the primay key which is one of the key among candidate key ,so you have to find all  the candidate key first to determine the normal forms of the schema.

for collection schema:
fd's are:

catalog_no->title author

title author->catalog_no

also catalog_no and (title author) are candidate key for this schema.

so this is in bcnf.

for book schema:

fd's are:

Title Author → Catalog_no

Catalog_no → Title Author Publisher Year

Publisher Title Year→ Price 

the candidate key's are catalog_no and (titlle,author) 

clearly 3rd is a transitive dependency hence 2nf and not 3nf or above.

Answer 6

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