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 addressthe 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.