Linda works as a Database Designer for Lion Inc. She has been given an assignment to design the
database of a publishing company. The database has a table named Author, which has a composite
key, AuthorID and TitleID. Royalty and LiteraryAgent are the other attributes. The functional
dependencies are such that AuthorID + TitleID-> Royalty (i.e. Royalty is functionally dependent on
AuthorID and TitleID) and AuthorID-> LiteraryAgent (i.e. LiteraryAgent is functionally dependent on
AuthorID). Which of the following is true about this table?
A.
It violates 4NF.
B.
It violates 2NF.
C.
It violates 1NF.
D.
There is no violation.
Explanation:
It violates the 2NF. According to 2NF, all the attributes that are not part of the primary key should be
functionally dependent on the entire key attributes (composite key). Here, Literary Agent is
functionally dependent on one of the composite key attributes, i.e. AuthorID. To make this in 2NF,
the Author table should be decomposed into two tables such that the first table contains AuthorID,
TitleID, and Royalty as its attributes, and the second table contains AuthorID and LiteraryAgent as its
attributes. This will make the two tables in 2NF.