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

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more