Library Catalog Database Design

Library Catalog Database Design is a group project completed as part of the CS202 Database Systems course. The goal was to design, normalize, and implement a relational database system for a university library catalog.

Project Scope

The database model handles the following requirements:

• Cataloging books with multiple authors, publishers, and categories.
• Tracking student accounts, borrowing history, and due dates.
• Managing library staff roles and inventory logistics.

Schema Design & Normalization

To ensure data integrity, the schema was normalized to the 3rd Normal Form (3NF) to eliminate anomalies.

Entities & Relationships

• Book (ISBN, Title, PublisherID, Year)
• Author (AuthorID, Name)
• Book_Author (ISBN, AuthorID) - Resolves many-to-many relationship
• Student (StudentID, Name, Email, Department)
• Loan (LoanID, StudentID, ISBN, BorrowDate, DueDate, ReturnDate)

Query Implementation

We implemented various SQL queries to handle typical library operations. Below is a sample query used to retrieve overdue books and the corresponding student details:

1
SELECT s.StudentID, s.Name, b.Title, l.DueDate
2
FROM Loan l
3
JOIN Student s ON l.StudentID = s.StudentID
4
JOIN Book b ON l.ISBN = b.ISBN
5
WHERE l.ReturnDate IS NULL AND l.DueDate < CURRENT_DATE;

This coursework project helped reinforce our theoretical understanding of relational constraints and transaction management.