Database Design: Consider the relation: Book_Booktitle, Author_name, Book_type,

Question

Database Design:

Consider the relation: Book_Booktitle, Author_name, Book_type, List_price, Author_affiliation, Publisher_name) Primary Key: (Book_title, Author_name) FD1: (Book_title, Author_name) rightarrow (Book_type, Listjprice, Author_affiliation, Publisher_name) The following additional dependencies exist: FD2: Book_title rightarrow Publisher_name, Book_type FD3: Book_type rightarrow Listjprice FD4: Author_name rightarrow Author_affiliation (a) 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

Hi,

Below is the answer-

Ans 1 - Yes the relation is in First normal form. In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.

Ans 2 - No the relation is not in second normal form.This is because there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. In this case Author_afiliation depends on Author_name itself and does not depend on Book_title. Hence, it has the partial dependency on the candidate key.

Ans 3 - This relation is not in third normal form because it has a transitive dependency i.e. a non- prime attribute is dependent on another non- prime attribute. Below is the sample of transitive dependency-
Book_type-> List_price

Ans 4 -We need tp first convert this relation into second normal form and then remove the transitive dependency from BOOK.

a) Convert into second normal form-
Book(Book_title,Author_name,Book_type,List_Price,Publisher_name)
Author(Author_name,Author_affiliation)

b) Removing the transitive dependency-
Book(Book_title,Author_name,Book_type,Publisher_name)
Author(Author_name,Author_affiliation)
Book_price(Book_type,List_Price)

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.