edited by
27,735 views
60 votes
60 votes

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}$?

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

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

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

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

edited by

5 Answers

Best answer
56 votes
56 votes

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.

edited by
15 votes
15 votes
(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
5 votes
5 votes
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.
5 votes
5 votes
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:

Related questions

50 votes
50 votes
6 answers
3
Kathleen asked Sep 12, 2014
21,480 views
A B-tree of order $4$ is built from scratch by $10$ successive insertions. What is the maximum number of node splitting operations that may take place?$3$$4$$5$$6$