Consider the following relational schemes for a library database: Book (Title,…

2008

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?

  1. A.

    Both Book and Collection are in BCNF

  2. B.

    Both Book and Collection are in 3NF only

  3. C.

    Book is in 2NF and Collection is in 3NF

  4. D.

    Both Book and Collection are in 2NF only

Attempted by 22 students.

Show answer & explanation

Correct answer: C

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

Reasoning:

Given key:

  • {Title, Author} is given as the key for both relations.

Analysis of Book (Title, Author, Catalog_no, Publisher, Year, Price):

  • 2NF check: There are no partial dependencies of non-prime attributes on a proper subset of the composite key (no FD with Title -> ... or Author -> ...). Hence Book is in 2NF.

  • 3NF check: There is a transitive dependency chain: (Title, Author) -> Catalog_no and Catalog_no -> Publisher, Year. This makes Publisher and Year depend transitively on the key, which violates 3NF.

  • Also, the FD Publisher, Title, Year -> Price has a left-hand side that is not a superkey, and Price is not a prime attribute, so this FD also violates the 3NF/BCNF requirement.

  • Conclusion for Book: it satisfies 2NF but violates 3NF (and therefore is not BCNF).

Analysis of Collection (Title, Author, Catalog_no):

  • Relevant dependencies restrict to these attributes: (Title, Author) -> Catalog_no and (given) Catalog_no -> Title, Author. Each determinant in these dependencies is a key for the relation (either the composite key or Catalog_no), so non-key attributes are not determined by non-keys.

  • Therefore Collection meets the conditions for 3NF. (With the given FDs it in fact satisfies BCNF as well, since every non-trivial FD has a superkey on the left-hand side.)

  • Conclusion for Collection: it satisfies 3NF (and under the provided dependencies it is actually BCNF).

Final conclusion: Book is in 2NF only, while Collection is in 3NF (and under the given FDs, BCNF). Thus the correct statement is: Book is in 2NF and Collection is in 3NF.

A video solution is available for this question — log in and enroll to watch it.

Explore the full course: Gate Guidance By Sanchit Sir