Consider the following relation that holds information about books in a bookstor

Question

Consider the following relation that holds information about books in a bookstore.

Books(title, isbn, author, publisherName, publisherAdd, totalCopiesOrdered, copiesInStock, publicationDate, category, sellingPrice, cost)

Assume:

·       The isbn uniquely identifies a book. (It does not identify each copy of the book, however.)

·       A book may have more than one author.

·       An author may have more than one book.

·       Each publisher name is unique. Each publisher has one unique address—the address of the firm's headquarters.

·       Titles are not unique.

·       totalCopiesOrdered is the number of copies of a particular book that the bookstore has ever ordered, while copiesInStock is the number still unsold in the bookstore.

·       Each book has only one publication date. A revision of a book is given a new ISBN.

·       The category may be biography, science fiction, poetry, etc. The title alone is not sufficient to determine the category. The sellingPrice, which is the amount the bookstore charges for a book, is always 20 percent above the cost, which is the amount the bookstore pays the publisher or distributor for the book.

a) Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation.

b) What are candidate keys for this relation? Identify the primary key.

c) Is the relation in third normal form? If not, find a 3NF lossless join decomposition of Books that preserves dependencies. Indicate what are primary keys

d) is the relation in Boyce-Codd normal BCNF form? If not, find a lossless join decomposition that is in BCNF. Identify any functional dependencies that are not preserved.

Explanation / Answer

Ans a) Below are the non trivial functional dependencies-

isbn-->title
isbn-->author
isbn-->publisherName
isbn-->publisherAdd
isbn-->totalCopiesOrdered
isbn-->CopiesIn Stock
isbn-->PublicationDate
isbn-->SellingPrice
isbn-->category
isbn-->cost
publisherName-->>publisherAdd
{title,isbn}-->publicationDate
{title,isbn,author,publisherName}--> totalCopiesOrdered
{title,isbn,author,publisherName}--> copiesInStock
{title,isbn}--> totalCopiesOrdered
{title,isbn}--> copiesInStock
{isbn,author}--> totalCopiesOrdered
{isbn,author}--> copiesInStock
{isbn,publisherName}--> totalCopiesOrdered
{isbn,publisherName}--> copiesInStock

Ans b)

Primary key is - "isbn"

Candidate Keys-

1. {title,isbn}
2. {title,isbn,author}
3. {title,isbn,author,publisherName}
4. {isbn,author}
5. {isbn,publisherName}

Ans c) The relation is not in third normal form as we can see that the non prime attributes are depending on another non prime attributes. Example- A non-prime atribute PublisherAdd depends on PublisherName which is another non-prime attribute. This leads to transitive dependency which is against third normal form. We can resolve thos problem by breaking this table into 2 tables like below-

Books(title, isbn, author, totalCopiesOrdered,publisherName, copiesInStock, publicationDate, category, sellingPrice, cost)

Author( publisherName, publisherAdd)

Further, copiesInStock depends on totalCopiesOrdered and both of these attributes are non-prime. This again leads to transitive dependency-

Hence Book table needs to be broken further into below tables-

Books(title, isbn, author,publisherName, publicationDate, category, sellingPrice, cost)

Book_Sale(isbn, totalCopiesOrdered,copiesInStock)

Ans d) The relation is not in BCNF

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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