Databases Question (decomposition) Consider the following universal relation tha

Question

Databases Question (decomposition)

Consider the following universal relation that holds information about the inventory of books in a bookstore: Assume: The is bn 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 national headquarters. Titles are not unique. total Copl esOrdered 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, and so on. 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. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation. What are the candidate keys for this relation? Identify the primary key. Is the relation or resulting set of relations in Boyce-Codd Normal Form? If not, find a lossless join decomposition that is in BCNF. Identify any functional dependencies that are not preserved.

Explanation / Answer

a.)

The non-trivial functional dependencies of the given question are:

b.)

Candidate keys for this relation is given below:

1. isbn

2. {title, author}

3. {title, author, publisherName}

isbn will be the primary key although it does not identify each copy of book. Because there are various benefits of using isbn as primary key. Lets assume you want to knew how many copies of a particular book gets sold, then in this case one can do it easily using isbn. All the attributes given in the database can be easily accessed using isbn only.

c.) The relation or result set is in boyce-codd normal form.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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