Working with multiple tables - Library database

Database schema for the library management system include  several tables to capture the required entities and relationships:

  1. Books: To store information about books.
  2. Members: To store information about library members.
  3. BookIssues: To track the issuance of books to members.
  4. BookReturns: To track the return of books and any fines collected.

1. Books Table

This table will store details about the books available in the library.

CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publisher VARCHAR(255),
    year_of_publication INT,
    isbn VARCHAR(13),
    category VARCHAR(100)
);

INSERT INTO Books (title, author, publisher, year_of_publication, isbn, category) VALUES ('Book 1', 'Author 1', 'Publisher A', 2000, '1234567890123', 'Fiction'), ('Book 2', 'Author 2', 'Publisher B', 2005, '2345678901234', 'Non-fiction'), ('Book 3', 'Author 3', 'Publisher A', 2010, '3456789012345', 'Science'), ('Book 4', 'Author 1', 'Publisher C', 2015, '4567890123456', 'Fiction'), ('Book 5', 'Author 2', 'Publisher B', 2020, '5678901234567', 'History');

2. Members Table

This table will store details about the library members.
CREATE TABLE Members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15),
    address VARCHAR(255),
    membership_date DATE NOT NULL
);
INSERT INTO Members (first_name, last_name, email, phone_number, address, membership_date) VALUES
('John', 'Doe', 'john.doe@example.com', '1234567890', '123 Main St', '2024-07-07'),
('Jane', 'Smith', 'jane.smith@example.com', '0987654321', '456 Elm St', '2024-07-07');

3. BookIssues Table

This table will store information about the books issued to members.
CREATE TABLE BookIssues (
    issue_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    member_id INT,
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (member_id) REFERENCES Members(member_id)
);

INSERT INTO BookIssues (book_id, member_id, issue_date, due_date) VALUES
(1, 1, '2024-07-01', '2024-07-14'),
(2, 2, '2024-07-01', '2024-07-14');

4. BookReturns Table

This table will store information about the return of books and any fines collected.
CREATE TABLE BookReturns (
    return_id INT AUTO_INCREMENT PRIMARY KEY,
    issue_id INT,
    return_date DATE NOT NULL,
    fine_collected DECIMAL(10, 2),
    FOREIGN KEY (issue_id) REFERENCES BookIssues(issue_id)
);

Let's assume John Doe returns a book on '2024-07-16' (2 days late).
INSERT INTO BookReturns (issue_id, return_date, fine_collected) VALUES
(1, '2024-07-16', 20.00);  -- 2 days late, Rs 10 per day


Query Examples
1.List of Books Issued to a Members

SELECT b.title, bi.issue_date, bi.due_date
FROM Books b
JOIN BookIssues bi ON b.book_id = bi.book_id
WHERE bi.member_id = 1;

2.Get the number of books written by a given author
SELECT COUNT(*) AS number_of_books
FROM Books
WHERE author = 'J.D. Salinger';

3. Get the list of publishers and the number of books published by each publisher
SELECT publisher, COUNT(*) AS number_of_books_published
FROM Books
GROUP BY publisher;

4.Get the list of books that are issued but not returned
SELECT b.title, b.author, b.publisher, bi.issue_date, bi.due_date
FROM BookIssues bi
JOIN Books b ON bi.book_id = b.book_id
LEFT JOIN BookReturns br ON bi.issue_id = br.issue_id
WHERE br.issue_id IS NULL;

5.Get the total fine collected for the current month and current quarter
SELECT SUM(fine_collected) AS total_fine_current_month
FROM BookReturns
WHERE YEAR(return_date) = YEAR(CURDATE()) AND MONTH(return_date) = MONTH(CURDATE());

SELECT SUM(fine_collected) AS total_fine_current_quarter
FROM BookReturns
WHERE YEAR(return_date) = YEAR(CURDATE()) AND QUARTER(return_date) = QUARTER(CURDATE());

6.Get the list of students who have overdue (not returned the books even on due date)
SELECT m.first_name, m.last_name, b.title, bi.due_date
FROM Members m
JOIN BookIssues bi ON m.member_id = bi.member_id
JOIN Books b ON bi.book_id = b.book_id
LEFT JOIN BookReturns br ON bi.issue_id = br.issue_id
WHERE bi.due_date < CURDATE() AND br.issue_id IS NULL;

7.Members who joined after Jan 1 2021 but has not taken any books
SELECT m.first_name, m.last_name, m.membership_date
FROM Members m
LEFT JOIN BookIssues bi ON m.member_id = bi.member_id
WHERE m.membership_date > '2021-01-01'
    AND bi.issue_id IS NULL;

8.Get details of all book issues, including member details:
SELECT bi.issue_id, b.title AS book_title, m.first_name AS member_first_name, m.last_name AS member_last_name, bi.issue_date, bi.due_date FROM BookIssues bi JOIN Books b ON bi.book_id = b.book_id JOIN Members m ON bi.member_id = m.member_id;


Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more