Library Management System Database

Overview

This project implements a relational database for a Library Management System using MySQL. It is designed to efficiently store and manage information about library members, books, authors, borrowing records, and book categories.

The database demonstrates good database design principles, including normalization, relationships, and constraints.

---

Features

• Member Management: Store member details such as full name, email, phone number, and join date.
• Author Management: Keep track of authors and their birth year.
• Book Management: Store books with information like title, author, ISBN, published year, and available copies.
• Borrow Records: Track which member borrowed which book, with borrow and return dates.
• Book Categories: Categorize books using a many-to-many relationship.

---

Database Design

Tables and Relationships

1. Members

   • MemberID (PK)
   • FullName, Email, PhoneNumber, JoinDate

2. Authors

   • AuthorID (PK)
   • FullName, BirthYear

3. Books

   • BookID (PK)
   • Title, AuthorID (FK), ISBN, PublishedYear, CopiesAvailable

4. BorrowRecords

   • RecordID (PK)
   • MemberID (FK), BookID (FK), BorrowDate, ReturnDate

5. Categories

   • CategoryID (PK)
   • CategoryName

6. BookCategories (Many-to-Many join table)

   • BookID (FK), CategoryID (FK)
   • Composite primary key (BookID, CategoryID)

Relationships

• One-to-Many:

  • Members → BorrowRecords
  • Authors → Books
  • Books → BorrowRecords

• Many-to-Many:

  • Books ↔ Categories (via BookCategories table)

---

Constraints

• PRIMARY KEY ensures unique identification of each row.
• FOREIGN KEY maintains referential integrity between tables.
• NOT NULL ensures required fields are always filled.
• UNIQUE prevents duplicate entries for fields like email or ISBN.

---

Usage

1. Open MySQL Workbench or any SQL client.
2. Run the library_management_system.sql file to create the database and tables.
3. Optionally, add sample data using INSERT statements.
4. Query the database to manage members, books, borrow records, and categories.

---

Example Queries

-- List all books with their authors
SELECT Books.Title, Authors.FullName
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

-- List all books borrowed by a member
SELECT Members.FullName, Books.Title, BorrowRecords.BorrowDate
FROM BorrowRecords
JOIN Members ON BorrowRecords.MemberID = Members.MemberID
JOIN Books ON BorrowRecords.BookID = Books.BookID
WHERE Members.MemberID = 1;