FINALLY DATABASE DESIGN PROJECT BY ABAYOMI ABDULAFEEZ AT PLP ACADEMY

Library Management System Database

A comprehensive MySQL database system designed for managing library operations including books, members, staff, and borrowing records.

Database Structure

Tables

1. Categories

   • Stores book categories/genres
   • Primary Key: category_id
   • Unique constraint on category_name

2. Books

   • Stores book information
   • Primary Key: book_id
   • Foreign Key: category_id references Categories
   • Unique constraint on ISBN
   • Tracks total and available copies

3. Members

   • Stores library member information
   • Primary Key: member_id
   • Unique constraint on email
   • Tracks membership status and date

4. Staff

   • Stores library staff information
   • Primary Key: staff_id
   • Unique constraint on email
   • Different roles: Admin, Librarian, Assistant

5. Borrowing Records

   • Tracks book borrowing transactions
   • Primary Key: record_id
   • Foreign Keys: book_id, member_id, staff_id
   • Tracks due dates and fines

Features

Constraints

• Primary and Foreign key constraints
• Unique constraints on important fields
• Check constraints for data integrity
• NOT NULL constraints where appropriate

Relationships

• One-to-Many: Categories to Books
• One-to-Many: Members to Borrowing Records
• One-to-Many: Books to Borrowing Records
• One-to-Many: Staff to Borrowing Records

Automated Features

• Triggers for updating book availability
• Stored procedures for borrowing and returning books
• Automatic fine calculation for overdue books

Performance Optimization

• Indexes on frequently searched columns
• Optimized table relationships

Usage

1. Create the database:

   source library_management_system.sql

2. Load sample data:

   source sample_data.sql

3. Use stored procedures:

   • Borrow a book:

     CALL borrow_book(book_id, member_id, staff_id, due_date);

   • Return a book:

     CALL return_book(record_id);

Features

• Automatic book availability tracking
• Fine calculation system (#10 per day for overdue books)
• Member status tracking
• Staff role management
• Comprehensive borrowing history