Scribd
Upload
15 views3 pages

Dbmsprac

The document outlines the creation of a Library database with tables for Members, Books, and BorrowRecords, including constraints and relationships. It details operations such as inserting, updating, deleting data, and performing queries with joins and aggregate functions. Additionally, it covers Data Control Language (DCL) permissions, Transaction Control Language (TCL) for managing transactions, and the creation of a view and trigger for managing book availability.

Uploaded by

Advay Joshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
15 views3 pages

Dbmsprac

The document outlines the creation of a Library database with tables for Members, Books, and BorrowRecords, including constraints and relationships. It details operations such as inserting, updating, deleting data, and performing queries with joins and aggregate functions. Additionally, it covers Data Control Language (DCL) permissions, Transaction Control Language (TCL) for managing transactions, and the creation of a view and trigger for managing book availability.

Uploaded by

Advay Joshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

-- Create Database

CREATE DATABASE LibraryDB;


USE LibraryDB;

-- Create Table with Constraints


CREATE TABLE Members (
MemberID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
JoinDate DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Books (


BookID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Author VARCHAR(100),
PublishedYear INT,
CopiesAvailable INT CHECK (CopiesAvailable >= 0)
);

CREATE TABLE BorrowRecords (


RecordID INT PRIMARY KEY,
MemberID INT,
BookID INT,
BorrowDate DATE DEFAULT CURRENT_DATE,
ReturnDate DATE,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

-- Insert Data
INSERT INTO Members VALUES (1, 'Alice', 'alice@example.com', '2025-01-01');
INSERT INTO Books VALUES (101, 'SQL Basics', 'John Doe', 2020, 5);

-- Update Data
UPDATE Books SET CopiesAvailable = CopiesAvailable - 1 WHERE BookID = 101;

-- Delete Data
DELETE FROM BorrowRecords WHERE RecordID = 5;

-- Simple Query
SELECT * FROM Books;
-- String Manipulation
SELECT UPPER(Name) AS UpperName FROM Members;

-- Aggregate Function
SELECT COUNT(*) AS TotalBooks, SUM(CopiesAvailable) AS TotalCopies FROM Books;

-- Inner Join
SELECT M.Name, B.Title
FROM Members M
JOIN BorrowRecords BR ON M.MemberID = BR.MemberID
JOIN Books B ON BR.BookID = B.BookID;

-- Left Join
SELECT M.Name, B.Title
FROM Members M
LEFT JOIN BorrowRecords BR ON M.MemberID = BR.MemberID
LEFT JOIN Books B ON BR.BookID = B.BookID;

-- DCL (Data Control Language)


GRANT SELECT, INSERT ON Books TO 'username';
REVOKE INSERT ON Books FROM 'username';

-- TCL (Transaction Control Language)


START TRANSACTION;
UPDATE Books SET CopiesAvailable = CopiesAvailable - 1 WHERE BookID = 101;
INSERT INTO BorrowRecords VALUES (1, 1, 101, CURDATE(), NULL);
COMMIT;

-- If there's an error
-- ROLLBACK;

-- View
CREATE VIEW AvailableBooks AS
SELECT Title, Author FROM Books WHERE CopiesAvailable > 0;

-- Trigger
DELIMITER //
CREATE TRIGGER DecreaseCopies AFTER INSERT ON BorrowRecords
FOR EACH ROW
BEGIN
UPDATE Books SET CopiesAvailable = CopiesAvailable - 1 WHERE BookID =
NEW.BookID;
END;
//
DELIMITER ;

You might also like