Case Study 1: Library Management System 

A library needs a database system to manage its books, members, and borrow records. The database should store information about books, library members, and the borrowing details of books by members. Additionally, the library needs to generate reports and maintain data integrity using views, cursors, joins, and triggers.

Database Schema:

1. Books:

   • BookID (Primary Key)

   • Title

   • Author

   • Publisher

   • YearPublished

2. Members:

   • MemberID (Primary Key)

   • FirstName

   • LastName

   • JoinDate

   • Email

3. BorrowRecords:

   • BorrowID (Primary Key)

   • BookID (Foreign Key)

   • MemberID (Foreign Key)

   • BorrowDate

   • ReturnDate

2. Data Manipulation Language (DML): 

-- Insert data into Books table

INSERT INTO Books (BookID, Title, Author, Publisher, YearPublished)

VALUES (1, 'The Catcher in the Rye', 'J.D. Salinger', 'Little, Brown and Company', 1951),

       (2, 'To Kill a Mockingbird', 'Harper Lee', 'J.B. Lippincott & Co.', 1960);

-- Insert data into Members table

INSERT INTO Members (MemberID, FirstName, LastName, JoinDate, Email)

VALUES (1, 'Alice', 'Johnson', '2021-01-15', 'alice.johnson@example.com'),

       (2, 'Bob', 'Smith', '2022-03-22', 'bob.smith@example.com');

-- Insert data into BorrowRecords table

INSERT INTO BorrowRecords (BorrowID, BookID, MemberID, BorrowDate, ReturnDate)

VALUES (1001, 1, 1, '2023-09-01', NULL),

       (1002, 2, 2, '2023-09-01', '2023-09-10');

Output: Data is inserted into the Books, Members, and BorrowRecords tables.

Data Control Language (DCL):

-- Granting SELECT permission on Books table to a user

GRANT SELECT ON Books TO 'username';

-- Revoking SELECT permission on Books table from a user

REVOKE SELECT ON Books FROM 'username';

Output: Permissions granted and revoked successfully. 

Select Data to Verify Insertion:

SELECT * FROM Books;

SELECT * FROM Members; 

SELECT * FROM BorrowRecords; 

4. Views: 

-- Create a view to list all books borrowed by a member

CREATE VIEW BooksBorrowedByMember AS

SELECT m.MemberID, m.FirstName, m.LastName, b.Title, br.BorrowDate, br.ReturnDate

FROM Members m

JOIN BorrowRecords br ON m.MemberID = br.MemberID

JOIN Books b ON br.BookID = b.BookID;

Output: View BooksBorrowedByMember created successfully. 

Select Data from View: 

SELECT * FROM BooksBorrowedByMember;

5. Cursors: 

-- Cursor to retrieve book titles borrowed by a specific member

DELIMITER $$

CREATE PROCEDURE GetBooksBorrowedByMember(IN memberID INT)

BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE bookTitle VARCHAR(100);

    DECLARE cur CURSOR FOR 

        SELECT b.Title

        FROM Books b

        JOIN BorrowRecords br ON b.BookID = br.BookID

        WHERE br.MemberID = memberID;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP

        FETCH cur INTO bookTitle;

        IF done THEN

            LEAVE read_loop;

        END IF;

        SELECT bookTitle;

    END LOOP;

    CLOSE cur;

END$$

DELIMITER ;

Output: Stored procedure GetBooksBorrowedByMember created successfully.

Call Procedure to Get Books Borrowed by Member with ID 1:

CALL GetBooksBorrowedByMember(1);

Output:

bookTitle

The Catcher in the Rye

6. Joins: 

-- Join to retrieve member and book details for borrow records

SELECT m.MemberID, m.FirstName, m.LastName, b.BookID, b.Title, br.BorrowDate, br.ReturnDate

FROM Members m

JOIN BorrowRecords br ON m.MemberID = br.MemberID

JOIN Books b ON br.BookID = b.BookID;

7. Triggers: 

-- Trigger to automatically update a book's last borrowed date when a borrow record is inserted

ALTER TABLE Books ADD COLUMN LastBorrowedDate DATE;

CREATE TRIGGER AfterBorrowInsert

AFTER INSERT ON BorrowRecords

FOR EACH ROW

BEGIN

    UPDATE Books

    SET LastBorrowedDate = NEW.BorrowDate

    WHERE BookID = NEW.BookID;

END;

Output: Trigger AfterBorrowInsert created successfully.

Insert a Borrow Record to Trigger the Trigger:

INSERT INTO BorrowRecords (BorrowID, BookID, MemberID, BorrowDate, ReturnDate)

VALUES (1003, 1, 2, '2024-08-06', NULL);

Output: The LastBorrowedDate column of the book record with BookID 1 is updated with the current date.

Verify the Trigger:

SELECT * FROM Books;