Question

Instructions: Draw an ER diagram for the following description. Identify the keys and give the cardinality of all relationships (e.g. 1:1, N:1, N: M). Make sure you note your assumptions.

Assume you are creating a database for a library system with the following properties:

1. The library contains one or several copies of the same book.

2. Every copy of a book has a copy number and is located at a specific location on a shelf.

3. A copy is identified by the copy number and the ISBN number of the book.

4. Every book has a unique ISBN, a publication year, a title, an author, and a number of

   pages.

5. Books are published by publishers.

6. A publisher has a name as well as a location.

7. Within the library system, books are assigned to one or several genres.

8. A category has a name and no further properties.

9. Each reader needs to provide his/her family name, his/her first name, his/her city, and

   his/her date of birth to register at the library.

10. Each reader gets a unique reader number. Readers borrow copies of books. Upon

    borrowing the return date is stored.

A good strategy is to start by identifying the entities and their associated attributes, then identity the relationships present in this description.

Answer 1

The entities and attributes for the given data are:-

Book entity:- It has author, ISBN number, publication year, no.of pages, title. ISBN is the Primary Key and No. of pages is the weak entity for Book Entity.

Duplicate book:- It has ISBN number, and copy number. Where copy no. is the Primary Key for duplicate book.

Genre:- It has Category as attribute and a name as derived attribute.

Publishers:- It has Location, Name as attributes.

Reader:- It has First name, city, reader_no., family name, DOB, Return date. Where the Reader_no. is the primary key and family name is the weak entity for reader.

** 0..*, 1..*,1..1 represents the cardinality of the relations.