Project Goal: This project aims to provide hands$-$on experience in designing, implementing, and managing a relational database system for a small library. The system will manage a diverse collection of loanable items, track various types of memberships, enforce borrowing rules, and generate meaningful reports.

Project Phases and Deliverables

Domain modeling and requirements engineering

Brainstorm about the entitles and relationships in a typical library domain. Entities may include

Each Book and Digital Media item should have attributes such as Title, Author$/$Creator$,$ ISBN, Publication Year, Genre, and Availability Status.

Magazines should include Title, Issue Number, Publication Date, and Availability Status.

Clients should have a unique ID$,$ name, contact information, membership type, and account status.

Several categories of members $($e$.$g$.,$ regular, students, senior citizens, $...)$

Membership types will determine borrowing limits and fee structures.

Identify constraints, for example, each client can borrow a maximum number of items at a time, depending on their membership type, fees are incurred for late returns, with different rates based on the membership type, certain items $($e$.$g$.,$ rare books or latest issues of magazines$)$ may have borrowing restrictions, etc.

More $...$

Conceptual Design

Develop a conceptual model using ER diagrams. Make sure all entities, relationships, entity and relationship attributes, key attributes, etc. are clearly identified and illustrated.

Logical Design

Convert the E$-$R model into a set of relations. For each relationship, clearly indicate the relation names, attributes, attribute types, primary key attributes, and foreign key attributes. Normalize the relations to $3$NF or BCNF$.$ Enforce all important referential integrity constraints.

Contemplate check constraints $($for example, to ensure fees are non$-$negative and borrowing limits are not exceeded$).$ Also contemplate triggers, for example, to automatically calculate late fees and update the availability status of items.

Physical Design and Data Population

Use DDL to design your database. Populate the database with a realistic set of data: at least $20$ books, $20$ digital media items, $20$ magazines, and $50$ client records, etc. Include a variety of membership types and associated borrowing records. Randomly generate some of the data to ensure diversity in loans, fees, and reservations.

Ensure that no item is borrowed beyond its due date without generating a late fee. Ensure each client adheres to the borrowing limits set by their membership type.

Objective: Develop a comprehensive Entity$-$Relationship $($ER$)$ diagram that accurately represents the data requirements and relationships for your database project.

Now that you have documented the requirements of your database project, the next step is to prepare a conceptual model. This model will serve as a blueprint for your database design, capturing the essential entities, relationships, and constraints based on the requirements you have gathered.

Tasks $($the introduction section is for continuity; the italic parts are subsections$)$

Introduction. Project Overview: Write a brief paragraph summarizing the purpose and primary functions of your database. This should align with the overall goals and objectives outlined in your requirements document. Scope: Provide a concise paragraph defining the boundaries of your project. Mention what aspects will be covered and what will be excluded, ensuring consistency with your initial project scope. Glossary: Create a list of key terms and acronyms used in your project. Define each term clearly to ensure that all stakeholders have a common understanding of the terminology.

Review Requirements: Use the requirements document you have already prepared. Ensure that all team members understand the initial data entities, their attributes, and the relationships between them.

Get Ready for ER Modeling. Identify Entities: List all the major entities that will be part of your database. This includes the initial entities in the project description, the ones you identified during the requirements engineering, and the additional ones during your team brainstorming. Define Attributes: For each entity, list its attributes and specify the data types or constraints. For example, the Books entity might have attributes such as ISBN, Title, Author, Genre, Price, and Stock Quantity. Establish Relationships: Determine how the entities are related to each other. Define the cardinality $($one$-$to$-$one, one$-$to$-$many, many$-$to$-$many$)$ and any constraints. For example, a Book can be written by one or more Authors, and a Sale can include multiple Books.

Create the ER Diagram: Use a diagramming tool $($such as draw.io$,$ Lucidchart, or any other ER diagram tool$)$ to create your ER diagram. Ensure that your diagram includes:

All identified entities and their attributes

Primary keys for each entity

Relationships between entities with appropriate cardinality in min..max format