1. BOOK(Book_title, Author_name, Book_type, List_price, Author_affiliation, Publ
ID: 3866745 • Letter: 1
Question
1.
BOOK(Book_title, Author_name, Book_type, List_price, Author_affiliation, Publisher_name)
Primary Key: (Book_title, Author_name)
FD1: (Book_title, Author_name) -> (Book_type, List_price, Author_affiliation, Publisher_name)
The following additional dependencies exist:
FD2: Book_title -> Publisher_name, Book_type
FD3: Book_type -> List_price
FD4: Author_name -> Author_affiliation
Is the relation BOOK in first normal form? Why?
(b) Is the relation BOOK in second normal form? Why?
(c) Is the relation BOOK in third normal form? Why?
(d) Improve the relation by breaking it down to separate relations, so that the whole design is in third normal form. For each relation in this improved design, please clearly indicate the relation name, the attributes, and have the primary key attribute(s) underlined. You CANNOT add new attributes or change the names of the existing attributes.
Explanation / Answer
a) The relation BOOK is not in the first normal form as there can be more than one values for Author_name for one Book_title.
b) The relation BOOK is not in the second normal form as all non key attributes are not fully functionally dependent on the primary key eg
FD2: Book_title -> Publisher_name, Book_type
Publisher_name and Book_type is functionally dependent on Book_title but not functionally dependent on Author_name so there is partial dependency on the composite primary key.
FD4: Author_name -> Author_affiliation
Author _affiliation has partial dependency on composite primary key.
c)The relation BOOK is not in third normal form as there exists following transitive dependencies.
FD3: Book_type -> List_price
List_price is functionally dependent on Book_Type and Book_Type is functionally dependent on (Book_title, Author_name)
d) Normalized Relations(3NF)
BOOK(Book_title ,Publisher_name, Book_type)
BOOK_PRICE(Book_type ,List_price)
AUTHOR_AFFILIATION(Author_name ,Author_affiliation)
underlined are primary keys
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.