DO NOT COPY FROM OTHER CHEGG QUESTIONS IT'S WRONG

Assume you are creating a database for a library. The data requirements for this system are as follows:

The system keeps track of many books. Each book has a unique ISBN, a title, keywords, and author names. A book can have multiple authors and multiple keywords. Each book belongs to one category, but a category can have no or multiple books. A category has a unique category name. The categories are hierarchical: besides the top category, each child category has a single parent category, but a parent category can have one or many children categories.

Each book is published by a publisher. A publisher has a unique ID, a unique name, an address and publishes many books. Not all books published by a publisher are kept in the library.

The system also keeps track of readers. Each reader is with a unique patron ID (patronID), a name (composed of first name and last name), a phone number, and an email. A record will keep track of which book is borrowed by which reader, the date when the book was borrowed (issueDate), the due date, and the date when the book has been returned (returnDate). One reader can borrow many books at the same time or borrow the same book multiple times. But during the same period, a book can be borrowed by one reader at most. (Hints: no need to consider multiple copies of a book. Let's assume the library has only one copy of each book.)

1. Draw the ER diagram and state any assumptions you make. But you could do not add extra entity, attribute or relationship beyond the above description. (Note: Use ER diagram that uses squares, diamonds and other shapes not the Crows Foot/Martin/Information Engineering style, Remember to indicate total/ partial participation)

2. For your ER diagram given above, convert it into relational schema using the mapping guidelines. For each relation (table) obtained, specify the name and its attributes, its primary key, as well as foreign key references.