Question One

Library Management System

Scenario: You are tasked with designing a database for a library management system. The system needs to track books, authors, members, and book loans. The requirements are as follows:

$$ Each book has a unique ISBN, title, publication year, and genre. A book can have multiple authors.

$$ Each author has a unique author ID$,$ name, and nationality.

$$ Each member has a unique member ID$,$ name, address, and membership date.

$$ Each loan records the loan of a book to a member and includes the loan date and return date.

$$ Authors can write multiple books, and a book can have multiple authors.

$$ Members can borrow multiple books, but a book can only be borrowed by one member at a time.

Given the scenario above, design the relational schema for the library management system. Identify the tables, their attributes, primary keys, and foreign keys. Explain the constraints you would apply to ensure data integrity.

Question Two

Based on the library management system scenario, create an Entity$-$Relationship $($ER$)$ diagram. Include entities, relationships, attributes, and keys. Explain the design choices for the relationships and any constraints applied.

Question Three

Extend the ER diagram created in Module $4$ to include subclass and superclass relationships. Specifically, include subclasses for different types of books $($e$.$g$.,$ Fiction and Non$-$Fiction$)$ and different types of members $($e$.$g$.,$ Student and Faculty$).$ Explain the rationale behind your design choices.