Library Database Management System Using SQL
Graduation Project / Research Report
Department: __________________________
Institute/University: __________________________
Submitted by: __________________________
Supervised by: __________________________
Academic Year: 2025–2026
Abstract
Libraries handle large volumes of information, including book catalogs, membership records,
and borrowing transactions. Manual or semi-manual methods often lead to duplicated records,
slow searching, and inconsistent updates. This project presents the design and implementation of
a Library Database Management System based on a relational database and SQL. The system
organizes core entities (books, copies, members, staff, loans, reservations, and fines) using well-
defined relationships and constraints. SQL queries, views, and triggers are used to enforce
business rules such as availability checks, due dates, and overdue penalties. The proposed design
improves accuracy, reduces redundancy through normalization, and enables efficient reporting
and decision support.
Keywords: Library Management, Relational Database, SQL, Normalization, Transactions,
Constraints.
Table of Contents
Table of Contents (Right-click to update)
Chapter 1: Introduction
1.1 Background of the Study
Modern libraries provide access to printed and digital resources and must serve many users
efficiently. As collections grow, the effort needed to track inventory, borrowing history, and
member activity increases. A database-driven management system allows librarians to store
records in a structured form and retrieve information quickly.
1.2 Problem Statement
Traditional record-keeping makes it difficult to confirm a book’s availability, identify overdue
loans, and generate accurate reports. Common issues include inconsistent entries, duplicate
records, and delays in updating returns. A reliable SQL-based system is required to ensure data
integrity, reduce manual errors, and support fast searching and reporting.
1.3 Project Objectives
This project aims to:
Design a normalized relational database for a library environment.
Implement tables, keys, and constraints to ensure accuracy and consistency.
Support borrowing/returning workflows with clear business rules.
Provide SQL queries and views for daily operations and reporting.
Apply security principles such as role-based access where appropriate.
1.4 Scope and Limitations
The project focuses on the database layer (schema, constraints, and SQL logic). A user interface
is out of scope, but the design is suitable for integration with web or desktop applications. The
SQL scripts are written in a standards-friendly style and may require minor adjustments for a
specific DBMS (MySQL, PostgreSQL, SQL Server).
Chapter 2: Literature Review
2.1 Relational Database Management Systems
A Relational DBMS stores data in tables (relations) where rows represent records and columns
represent attributes. Relationships between tables are represented using keys. This model
supports strong consistency through constraints and transactions.
2.2 SQL and Data Integrity
SQL is used to define schemas (DDL), manipulate data (DML), and control access (DCL).
Integrity constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK prevent
invalid states and enforce business rules.
2.3 Library Management Databases
Most library systems include entities for bibliographic data, physical copies, members, and
circulation transactions. Best practices recommend separating 'book titles' from 'book copies' to
accurately track inventory and lending history. Normalization reduces redundancy and improves
update correctness.
Chapter 3: System Analysis and Database Design
3.1 Functional Requirements
The system should support the following operations:
Register members and staff.
Add books and manage multiple copies per title.
Borrow and return copies with due dates.
Reserve a book when no copy is available.
Calculate and track overdue fines.
Generate reports (most borrowed books, active members, overdue list).
3.2 Conceptual Design (Entities and Relationships)
Key entities include Book, BookCopy, Member, Staff, Loan, Reservation, and Fine. A Book can
have many BookCopies. A Member can have many Loans and Reservations. Each Loan
references exactly one BookCopy. Fines are linked to Loans.
3.3 Logical Design and Normalization
The schema is designed to satisfy at least Third Normal Form (3NF): each non-key attribute
depends on the key, the whole key, and nothing but the key. This reduces duplication (e.g.,
author and title are stored once in the Books table) and prevents update anomalies.
3.4 Data Dictionary (Tables Summary)
Table Purpose Primary Key Key Notes
Relationships
Books Bibliographic book_id Referenced by Stores ISBN, title,
record for each title BookCopies author, category
BookCopies Physical/digital copy_id FK to Tracks barcode and
copy instances Books(book_id) status
Members Library membership member_id Referenced by Stores contact and
records Loans, Reservations activity status
Staff Librarian/admin staff_id Referenced by Optional for audit
records Loans (issued_by)
Loans Borrow/return loan_id FK to Members, Tracks due date and
transactions Copies, Staff return date
Reservations Hold requests when reservation_id FK to Members, Queue by request
unavailable Books date
Fines Overdue penalty fine_id FK to Calculated after due
records Loans(loan_id) date
Chapter 4: Implementation (SQL Code and Tables)
4.1 Database Schema (CREATE TABLE)
The following SQL script creates the core tables with primary keys, foreign keys, and integrity
constraints. Data types may be adjusted slightly to match the target DBMS.
-- ===============================================
-- Library Database Management System (SQL Schema)
-- Compatible style: PostgreSQL / MySQL (minor edits may be needed)
-- ===============================================
CREATE TABLE Books (
book_id INT PRIMARY KEY,
isbn VARCHAR(20) UNIQUE,
title VARCHAR(200) NOT NULL,
author VARCHAR(150) NOT NULL,
publisher VARCHAR(150),
publish_year INT,
category VARCHAR(80),
language VARCHAR(40) DEFAULT 'English'
);
CREATE TABLE BookCopies (
copy_id INT PRIMARY KEY,
book_id INT NOT NULL,
barcode VARCHAR(50) UNIQUE NOT NULL,
location VARCHAR(80),
status VARCHAR(20) NOT NULL DEFAULT 'AVAILABLE',
CONSTRAINT fk_copies_book
FOREIGN KEY (book_id) REFERENCES Books(book_id),
CONSTRAINT chk_copy_status
CHECK (status IN ('AVAILABLE','LOANED','LOST','MAINTENANCE'))
);
CREATE TABLE Members (
member_id INT PRIMARY KEY,
full_name VARCHAR(150) NOT NULL,
phone VARCHAR(30),
email VARCHAR(120) UNIQUE,
address VARCHAR(250),
join_date DATE NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE Staff (
staff_id INT PRIMARY KEY,
full_name VARCHAR(150) NOT NULL,
role VARCHAR(40) NOT NULL DEFAULT 'LIBRARIAN',
phone VARCHAR(30),
email VARCHAR(120) UNIQUE
);
CREATE TABLE Loans (
loan_id INT PRIMARY KEY,
copy_id INT NOT NULL,
member_id INT NOT NULL,
issued_by INT,
loan_date DATE NOT NULL,
due_date DATE NOT NULL,
return_date DATE,
CONSTRAINT fk_loans_copy
FOREIGN KEY (copy_id) REFERENCES BookCopies(copy_id),
CONSTRAINT fk_loans_member
FOREIGN KEY (member_id) REFERENCES Members(member_id),
CONSTRAINT fk_loans_staff
FOREIGN KEY (issued_by) REFERENCES Staff(staff_id),
CONSTRAINT chk_dates
CHECK (due_date >= loan_date)
);
CREATE TABLE Reservations (
reservation_id INT PRIMARY KEY,
book_id INT NOT NULL,
member_id INT NOT NULL,
request_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
CONSTRAINT fk_res_book
FOREIGN KEY (book_id) REFERENCES Books(book_id),
CONSTRAINT fk_res_member
FOREIGN KEY (member_id) REFERENCES Members(member_id),
CONSTRAINT chk_res_status
CHECK (status IN ('ACTIVE','FULFILLED','CANCELLED'))
);
CREATE TABLE Fines (
fine_id INT PRIMARY KEY,
loan_id INT NOT NULL UNIQUE,
amount DECIMAL(10,2) NOT NULL DEFAULT 0,
is_paid BOOLEAN NOT NULL DEFAULT FALSE,
created_at DATE NOT NULL,
CONSTRAINT fk_fines_loan
FOREIGN KEY (loan_id) REFERENCES Loans(loan_id)
);
4.2 Sample Data (INSERT)
-- Sample seed data
INSERT INTO Books (book_id, isbn, title, author, publisher, publish_year,
category, language) VALUES
(1, '9780131103627', 'The C Programming Language', 'Kernighan & Ritchie',
'Prentice Hall', 1988, 'Programming', 'English'),
(2, '9780132350884', 'Clean Code', 'Robert C. Martin', 'Prentice Hall', 2008,
'Software Engineering', 'English'),
(3, '9781449355739', 'Learning SQL', 'Alan Beaulieu', "O'Reilly Media", 2020,
'Databases', 'English');
INSERT INTO BookCopies (copy_id, book_id, barcode, location, status) VALUES
(101, 1, 'BC-000101', 'Shelf A1', 'AVAILABLE'),
(102, 1, 'BC-000102', 'Shelf A1', 'AVAILABLE'),
(201, 2, 'BC-000201', 'Shelf B3', 'AVAILABLE'),
(301, 3, 'BC-000301', 'Shelf C2', 'AVAILABLE');
INSERT INTO Members (member_id, full_name, phone, email, address, join_date,
is_active) VALUES
(1, 'Ahmed Ali', '0770xxxxxxx', 'ahmed@[Link]', 'Baghdad', '2025-10-01',
TRUE),
(2, 'Zahra Hussein', '0780xxxxxxx', 'zahra@[Link]', 'Erbil', '2025-10-
10', TRUE);
INSERT INTO Staff (staff_id, full_name, role, phone, email) VALUES
(1, 'Sara Hassan', 'ADMIN', '0750xxxxxxx', 'sara@[Link]');
4.3 Common Queries (SELECT)
-- 1) Search books by title/author (keyword)
SELECT book_id, title, author, category
FROM Books
WHERE title LIKE '%SQL%'
OR author LIKE '%Martin%';
-- 2) Show available copies for a specific book
SELECT c.copy_id, [Link], [Link], [Link]
FROM BookCopies c
WHERE c.book_id = 1 AND [Link] = 'AVAILABLE';
-- 3) Current loans with member names and book titles
SELECT l.loan_id, m.full_name AS member_name, [Link], [Link],
l.loan_date, l.due_date, l.return_date
FROM Loans l
JOIN Members m ON m.member_id = l.member_id
JOIN BookCopies c ON c.copy_id = l.copy_id
JOIN Books b ON b.book_id = c.book_id
WHERE l.return_date IS NULL;
-- 4) Overdue loans
SELECT l.loan_id, m.full_name, [Link], l.due_date
FROM Loans l
JOIN Members m ON m.member_id = l.member_id
JOIN BookCopies c ON c.copy_id = l.copy_id
JOIN Books b ON b.book_id = c.book_id
WHERE l.return_date IS NULL
AND l.due_date < CURRENT_DATE
ORDER BY l.due_date;
4.4 Views for Reporting
-- View: Active loans summary
CREATE VIEW vw_active_loans AS
SELECT l.loan_id, m.full_name AS member_name, [Link], [Link],
l.loan_date, l.due_date
FROM Loans l
JOIN Members m ON m.member_id = l.member_id
JOIN BookCopies c ON c.copy_id = l.copy_id
JOIN Books b ON b.book_id = c.book_id
WHERE l.return_date IS NULL;
-- View: Most borrowed books
CREATE VIEW vw_most_borrowed AS
SELECT b.book_id, [Link], [Link], COUNT(*) AS borrow_count
FROM Loans l
JOIN BookCopies c ON c.copy_id = l.copy_id
JOIN Books b ON b.book_id = c.book_id
GROUP BY b.book_id, [Link], [Link]
ORDER BY borrow_count DESC;
4.5 Triggers (Business Rules)
The following triggers illustrate business rules: 1) when a loan is created, the copy status
becomes LOANED; 2) when a return date is recorded, the copy status becomes AVAILABLE;
3) a fine record is created (or updated) when an item becomes overdue. Trigger syntax varies by
DBMS, so examples are provided in a clear, commonly used style.
-- Trigger 1: Set copy status to LOANED when a new loan is inserted (copy must
be AVAILABLE)
-- (PostgreSQL-style example using a function)
CREATE OR REPLACE FUNCTION fn_loan_copy_lock()
RETURNS TRIGGER AS $$
BEGIN
IF (SELECT status FROM BookCopies WHERE copy_id = NEW.copy_id) <>
'AVAILABLE' THEN
RAISE EXCEPTION 'Copy is not available for loan';
END IF;
UPDATE BookCopies
SET status = 'LOANED'
WHERE copy_id = NEW.copy_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_loan_insert
BEFORE INSERT ON Loans
FOR EACH ROW
EXECUTE FUNCTION fn_loan_copy_lock();
-- Trigger 2: Set copy status to AVAILABLE when return_date is updated from
NULL to a date
CREATE OR REPLACE FUNCTION fn_return_copy_release()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.return_date IS NOT NULL AND OLD.return_date IS NULL THEN
UPDATE BookCopies
SET status = 'AVAILABLE'
WHERE copy_id = NEW.copy_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_loan_return
AFTER UPDATE ON Loans
FOR EACH ROW
EXECUTE FUNCTION fn_return_copy_release();
-- Trigger 3: Create/update fine when a loan becomes overdue (simple daily
rate example)
CREATE OR REPLACE FUNCTION fn_update_fine()
RETURNS TRIGGER AS $$
DECLARE
days_overdue INT;
daily_rate DECIMAL(10,2) := 0.50;
BEGIN
IF NEW.return_date IS NULL AND NEW.due_date < CURRENT_DATE THEN
days_overdue := (CURRENT_DATE - NEW.due_date);
INSERT INTO Fines (fine_id, loan_id, amount, is_paid, created_at)
VALUES (NEW.loan_id, NEW.loan_id, (days_overdue * daily_rate), FALSE,
CURRENT_DATE)
ON CONFLICT (loan_id)
DO UPDATE SET amount = [Link];
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_fine_check
AFTER INSERT OR UPDATE ON Loans
FOR EACH ROW
EXECUTE FUNCTION fn_update_fine();
Chapter 5: Testing, Results, and Discussion
5.1 Test Scenarios
Key test cases include:
Loan creation when a copy is AVAILABLE (should succeed).
Loan creation when a copy is already LOANED (should fail).
Return processing updates the copy status to AVAILABLE.
Overdue detection shows correct results in the overdue query/view.
Fine calculation updates consistently for overdue loans.
5.2 Discussion of Results
Testing indicates that the relational design supports accurate tracking of inventory and
transactions. Separating Books and BookCopies prevents confusion between bibliographic data
and physical items. Constraints reduce invalid records, while views provide fast reporting
without repeating complex joins. Triggers enforce key business rules directly at the database
layer, which helps maintain correctness even if multiple applications use the same database.
Chapter 6: Conclusion and Future Work
6.1 Conclusion
This project designed and implemented a Library Database Management System using SQL. The
proposed schema organizes core library entities, enforces integrity through keys and constraints,
and supports daily operations using queries and views. By relying on a structured relational
model, the system improves accuracy, reduces redundancy, and enables reliable reporting.
6.2 Future Work
Possible enhancements include:
Developing a full user interface (web or desktop) connected to the database.
Adding authentication and detailed role-based permissions within the DBMS.
Supporting ISBN-based importing and external catalog integration.
Implementing scheduled jobs for fine calculation and reminders (email/SMS).
Adding analytics dashboards for circulation trends and collection planning.
References
1. Elmasri, R., & Navathe, S. B. Fundamentals of Database Systems. Pearson.
2. Date, C. J. An Introduction to Database Systems. Addison-Wesley.
3. Silberschatz, A., Korth, H. F., & Sudarshan, S. Database System Concepts. McGraw-Hill.
4. PostgreSQL Documentation: SQL Commands and PL/pgSQL Reference.
5. Microsoft SQL Server Documentation: CREATE TABLE, Constraints, and Triggers.