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 thatAuthorID
+ 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 tableshould
be decomposed into two tables such that the first table contains AuthorID, TitleID, and Royalty as itsattributes,
and the second table contains AuthorID and LiteraryAgent as its attributes. This will make the two tables in
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 ONLY one of the composite key attributes, i.e. AuthorID.