Library Database Design Explained

Library Database Tables Explained

Designing a Library Database: SQL Table Structure Explained

When creating a library management system, organizing data efficiently is key. Let’s explore the four main tables that form the backbone of a typical library database:

1. Authors Table

This table stores information about the authors whose books the library holds. Each author has:

author_id: A unique number to identify each author.
name: The full name of the author.
birthdate: The author’s date of birth.

2. Books Table

This table holds details about every book in the library:

book_id: Unique ID for each book.
title: The title of the book.
author_id: Links each book to its author (foreign key).
publication_year: The year the book was published.
genre: The category or genre of the book.

The author_id column references the Authors table, ensuring that every book is tied to a valid author.

3. Members Table

This table manages the library members:

member_id: Unique ID assigned to each member.
name: Full name of the member.
membership_date: The date the member joined the library.
address: Contact address of the member.

4. Loans Table

This table tracks which books are loaned to which members, and when:

loan_id: Unique ID for each loan transaction.
book_id: The borrowed book’s ID (foreign key referencing Books).
member_id: The borrowing member’s ID (foreign key referencing Members).
loan_date: The date the book was checked out.
return_date: The date the book was returned (can be empty if not yet returned).

By linking the book_id and member_id columns to their respective tables, the Loans table ensures data integrity and makes it easy to track borrowing history.

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birthdate DATE
);

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    publication_year INT,
    genre VARCHAR(50),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Members (
    member_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    membership_date DATE,
    address VARCHAR(255)
);

CREATE TABLE Loans (
    loan_id INT PRIMARY KEY,
    book_id INT,
    member_id INT,
    loan_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (member_id) REFERENCES Members(member_id)
);

With this database design, managing authors, books, members, and loans becomes organized and efficient—perfect for running a smooth library system!

Performing Database Operations on the Library Schema

Based on the library database schema, we will perform key operations like inserting data, querying, updating, and deleting records. These operations help manage the data efficiently and maintain data integrity.

1. Insert Sample Data into Tables

To test the database and simulate real-world scenarios, we insert sample data:

-- Insert at least 2 authors
INSERT INTO Authors (author_id, name, birthdate) VALUES
(1, 'Selina Hossain', '1947-10-31'),
(2, 'Humayun Ahmed', '1948-11-13');

-- Insert at least 3 books written by these authors
INSERT INTO Books (book_id, title, author_id, publication_year, genre) VALUES
(1, 'Megher Gaan', 1, 1998, 'Upanyas'),
(2, 'Shankhnil Karagar', 2, 1978, 'Upanyas'),
(3, 'Debdas', 2, 1985, 'Natok');

-- Insert at least 3 members
INSERT INTO Members (member_id, name, membership_date, address, email) VALUES
(1, 'Ali Ahsan', '2023-01-15', 'Dhaka, Bangladesh', 'ali@example.com'),
(2, 'Rahim Uddin', '2023-03-22', 'Chattogram, Bangladesh', 'rahim@example.com'),
(3, 'Sumaiya Khan', '2023-04-05', 'Rajshahi, Bangladesh', 'sumaiya@example.com');

-- Insert at least 1 loan record indicating book borrowing
INSERT INTO Loans (loan_id, book_id, member_id, loan_date, return_date) VALUES
(1, 1, 1, '2024-05-01', '2024-05-15');

2. Retrieve Books by a Specific Author

To find all books written by the author with author_id = 2 (Humayun Ahmed), we write:

SELECT title
FROM Books
WHERE author_id = 2;

3. Update Return Date of a Loan

To update the return date for the loan with loan_id = 1, use:

UPDATE Loans
SET return_date = '2024-05-30'
WHERE loan_id = 1;

4. Delete Members Who Have Not Borrowed Books

To remove members without any loans, execute:

DELETE FROM Members
WHERE member_id NOT IN (
    SELECT DISTINCT member_id FROM Loans
);

5. Retrieve Members and Their Current Loans

To display members with books currently on loan:

SELECT m.name AS member_name, b.title AS book_title
FROM Members m
JOIN Loans l ON m.member_id = l.member_id
JOIN Books b ON l.book_id = b.book_id
WHERE l.return_date IS NULL OR l.return_date > CURRENT_DATE;

Summary

Inserted sample data for authors, books, members, and loans with Bangla names in English characters.
Selected books by a specific author using WHERE clause.
Updated loan return dates to extend borrowing periods.
Deleted inactive members who never borrowed books.
Used JOINs to retrieve members with their current active loans.