Question $1$

Entity$-$Relationship $($ER$)$ and Enhanced ER $($EER$)$ Modeling $(40$ Marks$)$

Task $1$: Complex ER Diagram

Create an ER diagram based on the following scenario:

A Library Management System needs to store information about books, members, and staff. The library stores:

Books: Each book has a unique ISBN, title, author$($s$),$ publisher, and category. Multiple copies of each book are available, each identified by a unique copy number. A book can belong to multiple categories $($e$.$g$.,$ Fiction, Science$).$

Members: Members have a member ID$,$ name, date of birth, address, phone number, and membership type $($standard or premium$).$ Premium members have borrowing privileges that allow them to borrow more books.

Borrowing Records: For each transaction, the system keeps track of the member, book copy, issue date, return date, and the staff member responsible for the transaction.

Staff: Each staff member has an employee ID$,$ name, role $($e$.$g$.,$ librarian, assistant$),$ and department.

Requirements:

Identify all entities, relationships, cardinalities, and attributes.

Identify weak entities, if any.

Clearly mark primary keys, foreign keys, and relationships.

Include ISA hierarchies $($e$.$g$.,$ for membership types, or staff roles$)$ if applicable.

Task $2$: Extended EER Concepts

Using the ER diagram from Task $1,$ extend it to include specialization$/$generalization and aggregation where appropriate. Explain how these concepts help improve the database design.

Specialization: Consider any subclass for members $($e$.$g$.,$ premium vs$.$ standard$)$ or staff roles $($e$.$g$.,$ librarian vs$.$ assistant$).$

Aggregation: If a borrowing transaction involves a set of entities that can be considered together $($e$.$g$.,$ a book, a member, and a staff member$).$