Assignment 1, MODEL SOLUTIONS
Q 1. Draw the ER diagram for the following description of a database.
Make sure your ER diagram has all specifications, including (a) Key attributes
for each entity; (b) Cardinality ratios for each binary relationship
type, (c) Participation constraints for each relationship, (d) multi-valued
attributes, (e) derived attributes, and (f) composite attributes, if any.
STATE ANY ASSUMPTIONS you need to make.
We wish to make a database for our department library. The library has many books. Each book has a title, a unique ISBN number, and one or more authors. We may have several copies of some books - each copy has a unique copy-number. We record the publication information for each book: publication year, publication city, publisher name, publisher address, and publisher's phone and fax numbers. We also record the purchase date and purchase price of each copy. Staff in the department may borrow any book. For each staff, we record the Name, Staff ID, Email and Phone number. Nobody is allowed to borrow more than 10 books at any given time. The borrowing date and actual return date of each book is recorded.
NOTES:
(i) We would like to keep records of previously borrowed (and returned) books,
therefore the relationship 'Borrowed by' is M:N. Each copy of a book may have
been borrowed by several staff at different times.
(ii) Some data was added to the ER diagram, but not specified in the description;
e.g. Author ID, Publisher ID. Since we design the DB, we are free to define
some such data.
Q 2. In each of the following ER diagrams, the database currently has 3 unique entities of Entity type A, and 3 unique entities of Entity type B. Complete the picture of the entity sets such that your picture is consistent with the constraints given:
(a) 
(b)[some other solutions are feasible]
(c) [some other solutions are feasible]
