Consider the following relation for published books: BOOK (BookTitle, AuthorName

Question

Consider the following relation for published books: BOOK (BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher) AuthorAffil refers to the affiliation of the author. Assume that [BookTitle, AuthorName is the primary key. Suppose the following dependencies exist: BookTitle rightarrow Publisher, Type BookType rightarrow ListPrice Author Name rightarrow AuthorAffil (1) Is this relation in 1NF? Why or why not? (2) Is this relation in 2NF? Why or why not? (3) Is this relation in 3NF? Why or why not? (4) Is the relation in BCNF? Why or why not? (5) Please successively normalize this relation up to BCNF (include the detailed normalization steps for 1NF, 2NF, 3NF, and BCNF, respectively). For each new relation obtained in the normalization/decomposition process, please underline the primary key of the relation.

Explanation / Answer

1.Relation in 1NF.

As the atrributes are single valued/aomic

2.Not in 2NF as the primary keys are: Book_title,Authorname and there is partial function dependency in the given FDs

BookTitle ->BookType, Publisher and AuthorName ->AuthorAffiliation.

2NF decomposition

Book1(BookTitle, AuthorName)

Book2(BookTitle, BookType, ListPrice, Publisher)

Book3(AuthorName, AuthorAffiliation)

3.Notin 3NF

sincec ther is transitive dependency between key and non-key attributes through another non-key attributes.

here

BookTitle ->BookType and BookType->ListPrice

so BookTitle ->BookType ->ListPrice transitive dependecny non key attributesBookType ,ListPrice

3NF decomposition: reobin the transtitive dependecny by splitting the FD

• Book1(BookTitle, AuthorName)

Book2_1(BookTitle, BookType, Publisher)

Book2_2(BookType, ListPrice)

Book3(AuthorName, AuthorAffiliation)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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