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

Question

Consider the following relation for published books:

BOOK (BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher) (Bolded are supposed to be underlined*)

Assume that each book has a single author. AuthorAffil refers to the affiliation of the author.

Assume that {BookTitle, AuthorName} is the primary key. Suppose the following dependencies exist:

BookTitle -> Publisher, BookType BookType -> ListPrice AuthorName -> AuthorAffil

Is this relation in 1NF? Why or why not?

Is this relation in 2NF? Why or why not?

Is this relation in 3NF? Why or why not?

Is the relation in BCNF? Why or why not?

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

BOOK (BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher)

{BookTitle, AuthorName} is the primary key

dependencies are:

BookTitle -> Publisher, BookType

BookType -> ListPrice

AuthorName -> AuthorAffil

Yes , this in first normal form (1NF) , because we can find everything with help of candidate key {BookTitle, AuthorName} alone , which is our primary key

No , this relation is not in second normal form (2NF) because there are some partial depandencies {BookTitle, AuthorName} is our primary key so everything must be depand on the complete primary key not partially because if one of the attribute will null than there will be problem.

partial depandancies

BookTitle -> Publisher, BookType

AuthorName -> AuthorAffil

BookType -> ListPrice

here Publisher and BookType are only depands on BookTitle not on complete {BookTitle, AuthorName} and AuthorAffil only depends on AuthorName not on {BookTitle, AuthorName} and  ListPrice only depends on BookType which is depands on only BookTitle not on {BookTitle, AuthorName}.

so we have to break the reletion in parts to remove partial dependancy.

BOOK(BookTitle, AuthorName)

R1(BookTitle, BookType, ListPrice, Publisher)

R2(AuthorName, AuthorAffil)

now it is in (2NF) where BookTitle is primary key in R1 and AuthorName is primary key in R2, but it is NOT in third Normal form (3NF). because there is transitive dependancy exist so we have to remove transitive depandancy.

what is transitive depandancy , when a non-prime attribute depands on non-prime attribue and that non-prime attribute depends on prime attribute.

here it is in R1

BookTitle -> Publisher, BookType

BookType -> ListPrice

BookTitle is prime attribute and all other are non-prime but here ListPrice depends on BookType not on BookTitle so we have to remove that transitive dependancy so we have to break R1 further

BOOK(BookTitle, AuthorName)

R1(BookTitle, BookType, Publisher)

R2(AuthorName, AuthorAffil)

R3(BookType, ListPrice)

now it is in (3NF). and also in BCNF(Boyce–Codd normal form) because there is not any dependancy like that any prime attribute is depending on non-prime attribue .

all BOLD attributes are subject to primary key.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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