Project ForgeConsider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with functional dependencies: I. TitleAuthor → Catalog_no, II. Catalog_no → TitleAuthorPublisherYear, III. PublisherTitleYear → Price. Assume {Author,Title} is the key for both schemes. Which of the following statements is true?
- ABoth Book and Collection are in BCNF
- BBoth Book and Collection are in 3NF only
- Book is in 2NF and Collection is in 3NF
- DBoth Book and Collection are in 2NF only
Explanation
Collection(Title,Author,Catalog_no): FD II restricted to these attributes gives Catalog_no→TitleAuthor, making Catalog_no a SECOND candidate key alongside {Title,Author} — every determinant (TitleAuthor, Catalog_no) is a superkey, so Collection is in BCNF (hence also 3NF). Book additionally has FD III (PublisherTitleYear→Price); {Publisher,Title,Year} is not shown to be a superkey (doesn't determine Author or Catalog_no), and Price is a non-prime attribute — this violates both BCNF and 3NF. No partial dependency on part of the {Title,Author} key is evident, so Book still satisfies 2NF. Overall: Book is in 2NF, Collection is in (at least) 3NF.
Source: GATE CS 2008 (2008)
This question is part of a 15-day revision cycle — sign in and Project Forge will bring it back the day before you'd forget it.