Exercises 6.1 Consider the following universal relation that holds info about th

Question

Exercises 6.1 Consider the following universal relation that holds info about the inventory of books in a bookstore: Books (title, isbn, author, publisherName, publisherAdd, totalCopiesOrdered, copiesInStock, publication Date, 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 . Titles are not unique. . totalCopiesOrdered is the number of copies of a particular address-the address of the firm's national headquarters. book that the bookstore has ever ordered, while copiesInSto is the number still unsold in the bookstore.

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 E
• Subjects

---

---

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