Consider the following relation for published books, where { BookTitle, AuthorNa
ID: 3810265 • Letter: C
Question
Consider the following relation for published books, where { BookTitle, AuthorName } is the primary key.
BOOK(BookTitle, AuthorName, BookType, ListPrice, AuthorAffiliation, Publisher)
Suppose that the following dependencies exists:
BookTitle > Publisher, BookType
BookType > ListPrice
AuthorName > AuthorAffiliation
Is the relation in 1NF, 2NF, 3NF, BCNF? Answer yes/no and why/why not for each form. In the process, successively normalize the relation up to BCNF. For each new relation, put the primary key of the relation in bold.
Explanation / Answer
a) 1NF: yes ,assuming domain of each attribute is atomic values
2NF:no ,existence of partial dependency (explained below)
3NF:no, not in 2NF therefore cannot achieve 3NF
BCNF:no, not in 3NF
b) normalization process
step 1 FIRST NORMAL FORM
Domain of each attribute of table Book is atomic values and satisfy First Normal Form
step2 SECOND NORMAL FORM
No partial dependency is allowed in 2-nd Normal Form.ie every non-prime attribute should be fully dependent on prime key attributes. But in the table Book publisher,BookType can be identified by BookTitle alone and AuthorAffiliation can be identified by Author name alone. so we have to split to two table to satisfy 2-nd normal form..
BOOK(BookTitle,BookType,ListPrice,Publisher,AuthorName)
AUTHOR(AuthorName,AuthorAffiliation)
step3 THIRD NORMAL FORM
No Transitive Functional Dependency is allowed in 3rd Normal Form.In the Table Book, BookTitle determines BookType and BookType determines ListPrice.Therefore BookTitle determines ListPrice via BookType and we have a transitive functional dependency. To normalise split table Book.
BOOK(BookTitle,BookType,Publisher,AuthorName)
BOOK_PRICE(BookType,ListPrice)
AUTHOR(AuthorName,AuthorAffiliation)
step4 BCNF
In BCNF every determinant is a candidate key.But in table BOOK, AuthorName though a determinant is not a
candidate key. Therefore we have to split table Book again
BOOK(BookTitle,BookType,Publisher)
BOOK_AUTHOR(BookTitle,AuthorName)
BOOK_PRICE(BookType,ListPrice)
AUTHOR(AuthorName,AuthorAffiliation)