Database Management Practical

1. For given scenario:

Scenario:

We have a library system where:

- There are multiple books.

- Each book can be borrowed by multiple members.

- Each member can borrow multiple books.

- We also want to track the borrowing date and return date for each book borrowed by a member.

Entities:

Book
Member
Borrow

ER Diagram:

ER-Diagram

				  
	ER Diagram:
	The ER diagram will have the following entities and relationships:
		
			Book: Attributes: BookID (Primary Key), Title, Author, ISBN.
			Member: Attributes: MemberID (Primary Key), Name, Email.
			Borrow: Attributes: BorrowID (Primary Key), BookID (Foreign Key), MemberID (Foreign Key), BorrowDate, ReturnDate.
		


	
	
	
  +--------------+       +------------------+         +------------------+
  |      Book    |       |      Borrow      |         |     Member       |
  +--------------+       +------------------+         +------------------+
  | BookID (PK)  | 1    M| BorrowID (PK)    |M       1| MemberID (PK)    |
  | Title        |-------| BookID (FK)      |---------| Name             |
  | Author       |       | MemberID (FK)    |         | Email            |
  | ISBN         |       | BorrowDate       |         |                  |
  +--------------+       | ReturnDate       |         +------------------+
                         +------------------+

Creating the Database and Tables.

SQL

-- Create the database
CREATE DATABASE LibraryDB;

-- Use the database
USE LibraryDB;

-- Create the Book table
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255),
    Author VARCHAR(255),
    ISBN VARCHAR(13),
    PRIMARY KEY (BookID)
);

-- Create the Member table
CREATE TABLE Member (
    MemberID INT AUTO_INCREMENT,
    Name VARCHAR(255),
    Email VARCHAR(255),
    PRIMARY KEY (MemberID)
);

-- Create the Borrow table to handle the Many-to-Many relationship
CREATE TABLE Borrow (
    BorrowID INT AUTO_INCREMENT,
    BookID INT,
    MemberID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    PRIMARY KEY (BorrowID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

2. Write SQL query for given problem statement:

Problem Statement:

- Creating a Database.

- Viewing all Tables in a Database.

- Viewing all databases.

Creating and Viewing Tables & Database.

SQL

	
	
	
		
			- Creating a Database.
			- Viewing all Tables in a Database.
			- Viewing all databases.
		
	
	
	
	
		
			
				This command creates a new database named LibraryDB.
			
		
	


			  
                
                  Creating a Database
                  
                
                
                  CREATE DATABASE LibraryDB;
                
              

			  
			  
			  
	
		
			This command lists all tables within the current database.
		
	

		  
			  
                
                  Viewing all Tables in a Database
                  
                
                
                  SHOW TABLES;
                
              

			  
			  

		
			These commands create three tables (Book, Member, and Borrow) in the LibraryDB database.
		
	

	
			  
                
                  Creating Tables
                  
                
                
                  -- Use the database where you want to create tables
USE LibraryDB;

-- Create the Book table
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255),
    Author VARCHAR(255),
    ISBN VARCHAR(13),
    PRIMARY KEY (BookID)
);

-- Create the Member table
CREATE TABLE Member (
    MemberID INT AUTO_INCREMENT,
    Name VARCHAR(255),
    Email VARCHAR(255),
    PRIMARY KEY (MemberID)
);

-- Create the Borrow table to handle the Many-to-Many relationship
CREATE TABLE Borrow (
    BorrowID INT AUTO_INCREMENT,
    BookID INT,
    MemberID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    PRIMARY KEY (BorrowID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

Creating Database & Tables

SQL

-- Creating a Database
CREATE DATABASE LibraryDB;

-- Viewing all Databases
SHOW DATABASES;

-- Use the database to create tables
USE LibraryDB;

-- Create the Book table
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255),
    Author VARCHAR(255),
    ISBN VARCHAR(13),
    PRIMARY KEY (BookID)
);

-- Create the Member table
CREATE TABLE Member (
    MemberID INT AUTO_INCREMENT,
    Name VARCHAR(255),
    Email VARCHAR(255),
    PRIMARY KEY (MemberID)
);

-- Create the Borrow table
CREATE TABLE Borrow (
    BorrowID INT AUTO_INCREMENT,
    BookID INT,
    MemberID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    PRIMARY KEY (BorrowID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

-- Viewing all Tables in a Database
SHOW TABLES;

3. Perform the following Operations:

Problem Statement:

- Creating Tables (With and Without Constraints).

- Inserting/ Updating/ Deleting Records in a Table.

- Saving(Commit) and Undoing(rollback).

Creating Tables (With Constraints).

SQL

	
	
		
			Creates the Book table with constraints: Title and Author cannot be null, ISBN must be unique.
		
	


			  
                
                  Creating Tables (With Constraints)
                  
                
                
                  -- Use the database
USE LibraryDB;

-- Create the Book table with constraints
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    ISBN VARCHAR(13) UNIQUE,
    PRIMARY KEY (BookID)
);
				  
				  
				  
                
              

			  
			  
			  
	
		
			Creates a SimpleBook table without any constraints.
		
	

		  
			  
                
                  Creating Tables (Without Constraints)
                  
                
                
                  -- Create a simple Book table without constraints
CREATE TABLE SimpleBook (
    BookID INT,
    Title VARCHAR(255),
    Author VARCHAR(255),
    ISBN VARCHAR(13)
);

Create Read Update Delete CRUD

SQL

	
		
			Creates the Book table with constraints: Title and Author cannot be null, ISBN must be unique.
		
	


			  
                
                  Creating Tables (With Constraints)
                  
                
                
                  -- Use the database
USE LibraryDB;

-- Create the Book table with constraints
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    ISBN VARCHAR(13) UNIQUE,
    PRIMARY KEY (BookID)
);
				  
				  
				  
                
              

			  
			  
	
		
			Inserts a new book record with the given title, author, and ISBN into the Book table.
		
	


			  
                
                  Inserting Records into a Table
                  
                
                
                  -- Insert a record into the Book table
INSERT INTO Book (Title, Author, ISBN) VALUES ('1984', 'George Orwell', '1234567890123');

                
              

			  
			  
			  
	
		
			Updates the title of the book with BookID 1 to 'Animal Farm'.
		
	

		  
			  
                
                  Updating Records in a Table
                  
                
                
                  -- Update a record in the Book table
UPDATE Book SET Title = 'Animal Farm' WHERE BookID = 1;

                
              


	
		
			Deletes the book record with BookID 1 from the Book table.
		
	


			  
                
                  Deleting Records from a Table
                  
                
                
                  -- Delete a record from the Book table
DELETE FROM Book WHERE BookID = 1;

                
              

			  
			  
			  
	
		
			Starts a transaction, inserts a new book, and commits the transaction to save changes.
		
	

		  
			  
                
                  Saving (Commit) and Undoing (Rollback) Transactions
                  
                
                
                  -- Start a transaction
START TRANSACTION;

-- Insert a new book
INSERT INTO Book (Title, Author, ISBN) VALUES ('Brave New World', 'Aldous Huxley', '9876543210987');

-- Commit the transaction
COMMIT;

CRUD & Saving Transaction

SQL

-- Use the database
USE LibraryDB;

-- Create the Book table with constraints
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    ISBN VARCHAR(13) UNIQUE,
    PRIMARY KEY (BookID)
);

-- Create a simple Book table without constraints
CREATE TABLE SimpleBook (
    BookID INT,
    Title VARCHAR(255),
    Author VARCHAR(255),
    ISBN VARCHAR(13)
);

-- Insert a record into the Book table
INSERT INTO Book (Title, Author, ISBN) VALUES ('1984', 'George Orwell', '1234567890123');

-- Update a record in the Book table
UPDATE Book SET Title = 'Animal Farm' WHERE BookID = 1;

-- Delete a record from the Book table
DELETE FROM Book WHERE BookID = 1;

-- Start a transaction
START TRANSACTION;

-- Insert a new book
INSERT INTO Book (Title, Author, ISBN) VALUES ('Brave New World', 'Aldous Huxley', '9876543210987');

-- Commit the transaction
COMMIT;

-- Start another transaction
START TRANSACTION;

-- Insert a new book
INSERT INTO Book (Title, Author, ISBN) VALUES ('The Catcher in the Rye', 'J.D. Salinger', '5678901234567');

-- Rollback the transaction
ROLLBACK;

4. Perform the following Operations:

Problem Statement:

- Altering a Table.

- Dropping/ Truncating/ Renaming Tables.

- Backing up/ Restoring a Database.

Altering a Table.

SQL

	
		
			Altering a Book Table.
		
	


			  
                
                  Book Table
                  
                
                
                  -- Create the Book table with constraints
CREATE TABLE Book (
    BookID INT AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    ISBN VARCHAR(13) UNIQUE,
    PRIMARY KEY (BookID)
);				  
				  
				  
                
              

			  
			  
			  
                
                  Alter Book Table
                  
                
                
                  -- Add a new column to the Book table
ALTER TABLE Book ADD COLUMN PublicationYear INT;
				  
				  
                
              

			  
			  
		
			
				Adds a new column PublicationYear to the Book table.

Dropping/ Truncating/ Renaming Tables.

SQL

			  
                
                  Drop SimpleBook Table
                  
                
                
                  -- Drop the SimpleBook table
DROP TABLE SimpleBook;
				  
				  
                
              

			  
			  
		
			
				Delete the  SimpleBook table completely from the database.
			
		

	
		
		
                
                  Renaming a Table
                  
                
                
                  -- T-- Rename the Book table to LibraryBook
RENAME TABLE Book TO LibraryBook;
				  
				  
                
              

			  
			  
		
			
				Renames the Book table to LibraryBook.
			
		

	
		
		
		
                
                  Truncating a Table
                  
                
                
                  -- Truncate the Book table
TRUNCATE TABLE Book;
				  
				  
                
              

			  
			  
		
			
				Removes all records from the Book table but keeps the table structure intact.

Backing up/ Restoring a Database.

SQL

		
                
                  Backing up a Database
                  
                
                
                  -- Backup command (to be run in the command line, not in MySQL prompt)
mysqldump -u [username] -p LibraryDB > LibraryDB_backup.sql
				  
				  
                
              

			  
			  
		
			
				Creates a backup of the LibraryDB database and saves it to a file named LibraryDB_backup.sql
			
		

	
		
		
		
                
                  Restoring a Database
                  
                
                
                  -- Restore command (to be run in the command line, not in MySQL prompt)
mysql -u [username] -p LibraryDB < LibraryDB_backup.sql
				  
				  
                
              

			  
			  
		
			
				Restores the LibraryDB  database from the backup file LibraryDB_backup.sql

5. Perform the following Operations:

Problem Statement:

- Simple Queries with Where Operators.

- Where with Keywords and Logical Operators.

- Simple Queries with Aggregate functions.

- Queries with Aggregate functions (group by and having clause).

Simple Queries with WHERE Operators

SQL

-- Select all books by George Orwell
SELECT * FROM LibraryBook WHERE Author = 'George Orwell';

WHERE with Keywords and Logical Operators

SQL

-- Select all books published after 1950 and authored by George Orwell
SELECT * FROM LibraryBook 
WHERE Author = 'George Orwell' AND PublicationYear > 1950;

Simple Queries with Aggregate Functions

SQL

-- Count the number of books in the LibraryBook table
SELECT COUNT(*) AS TotalBooks FROM LibraryBook;

Queries with Aggregate Functions (GROUP BY and HAVING Clause)

SQL

-- Get the number of books by each author and filter authors with more than one book
SELECT Author, COUNT(*) AS BookCount 
FROM LibraryBook 
GROUP BY Author 
HAVING COUNT(*) > 1;

6. Perform Queries involving:

Problem Statement:

- Date Functions.

- String Functions.

- Math Functions.

Date Functions

SQL

-- Select books borrowed in the last 30 days
SELECT Title, BorrowDate
FROM LibraryBook lb
INNER JOIN Borrow b ON lb.BookID = b.BookID
WHERE BorrowDate >= CURDATE() - INTERVAL 30 DAY;

String Functions

SQL

-- Select books with the author's name in uppercase
SELECT Title, UPPER(Author) AS AuthorUpper
FROM LibraryBook;

Math Functions

SQL

-- Calculate the average year of publication of all books
SELECT AVG(PublicationYear) AS AveragePublicationYear
FROM LibraryBook;

7. Retrieving Data from Multiple Table:

Problem Statement:

- Joining Tables(Inner Joins, Outer-Joins).

- Aliases for Table Names.

Joins in SQL.

SQL


		
			Fetches book titles, authors, borrow dates, and return dates for all records where there is a matching BookID in both LibraryBook and Borrow.
		
	


			  
                
                  Joining Tables (Inner Join)
                  
                
                
                  -- Retrieve data from LibraryBook and Borrow tables using Inner Join
SELECT lb.Title, lb.Author, b.BorrowDate, b.ReturnDate
FROM LibraryBook lb
INNER JOIN Borrow b ON lb.BookID = b.BookID;

				  
				  
                
              

			  
			  

		
			
			Fetches all book titles and authors from LibraryBook, including borrow dates and return dates if they exist, and shows null for books that haven't been borrowed.
			
		
	


			  
                
                  Joining Tables (Left Outer Join)
                  
                
                
                  -- Retrieve data from LibraryBook and Borrow tables using Left Outer Join
SELECT lb.Title, lb.Author, b.BorrowDate, b.ReturnDate
FROM LibraryBook lb
LEFT JOIN Borrow b ON lb.BookID = b.BookID;

				  
				  
                
              




		
			Fetches all borrow records, including book titles and authors if they exist, and shows null for borrow records without matching books.
		
	


			  
                
                  Joining Tables (Right Outer Join)
                  
                
                
                  -- Retrieve data from LibraryBook and Borrow tables using Right Outer Join
SELECT lb.Title, lb.Author, b.BorrowDate, b.ReturnDate
FROM LibraryBook lb
RIGHT JOIN Borrow b ON lb.BookID = b.BookID;

Aliases for Table Names.

SQL


		
			Fetches all borrow records, including book titles and authors if they exist, and shows null for borrow records without matching books.
		
	


			  
                
                  Aliases for Table Names
                  
                
                
                  -- Using aliases for tables
SELECT lb.Title AS BookTitle, m.Name AS MemberName, b.BorrowDate, b.ReturnDate
FROM LibraryBook lb
INNER JOIN Borrow b ON lb.BookID = b.BookID
INNER JOIN Member m ON b.MemberID = m.MemberID;

8. Sub queries:

Problem Statement:

- With IN clause.

- With EXISTS clause.

- Handling NULL.

Subquery with IN Clause.

SQL


		
			
			Fetches book titles and authors for books that have been borrowed, using a subquery to get BookID's from the Borrow table.
			
		
	


			  
                
                  Subquery with IN Clause
                  
                
                
                  -- Select all books borrowed by members
SELECT Title, Author
FROM LibraryBook
WHERE BookID IN (SELECT BookID FROM Borrow);

Subquery with EXISTS Clause

SQL


		
			
			Fetches names and emails of members who have borrowed books, using a subquery with the EXISTS clause to check for borrowing records.
			
		
	


			  
                
                  Subquery with EXISTS Clause
                  
                
                
                  -- Select all members who have borrowed books
SELECT Name, Email
FROM Member m
WHERE EXISTS (SELECT 1 FROM Borrow b WHERE b.MemberID = m.MemberID);

Handling NULL

SQL


		
			
			Fetches titles and authors of books that have not been borrowed, handling NULL by using NOT IN with a subquery to get BookIDs from the Borrow table.
			
		
	


			  
                
                  Handling NULL
                  
                
                
                  -- Select all books that have not been borrowed
SELECT Title, Author
FROM LibraryBook
WHERE BookID NOT IN (SELECT BookID FROM Borrow);

9. Views:

Problem Statement:

- Creating Views.

- Dropping Views.

- Selecting from view.

Creating Views

SQL


		
			
			Creates a view named BorrowedBooks that shows titles and authors of borrowed books along with member names and borrow/return dates.
			
		
	


			  
                
                  Creating Views
                  
                
                
                  -- Create a view to show borrowed books with member details
CREATE VIEW BorrowedBooks AS
SELECT lb.Title, lb.Author, m.Name AS MemberName, b.BorrowDate, b.ReturnDate
FROM LibraryBook lb
INNER JOIN Borrow b ON lb.BookID = b.BookID
INNER JOIN Member m ON b.MemberID = m.MemberID;

Dropping Views.

SQL


		
			
			Deletes the BorrowedBooks view if it exists.
			
		
	


			  
                
                  Creating Views
                  
                
                
                  -- Drop the BorrowedBooks view
DROP VIEW IF EXISTS BorrowedBooks;

Persisting JSON.

SQL


		
			
			Fetches all records from the BorrowedBooks view.
			
		
	


			  
                
                  Selecting from View
                  
                
                
                  -- Select all records from the BorrowedBooks view
SELECT * FROM BorrowedBooks;

10. DCL statements:

Problem Statement:

- Granting and revoking permissions.

Granting Permissions.

SQL


		
			
			Grants SELECT and INSERT permissions on the LibraryBook table in the LibraryDB database to the user user1 connecting from localhost.
			
		
	


			  
                
                  Granting Permissions
                  
                
                
                  -- Grant SELECT and INSERT permissions on LibraryBook table to user 'user1'
GRANT SELECT, INSERT ON LibraryDB.LibraryBook TO 'user1'@'localhost';

Revoking Permissions.

SQL


		
			
			Revokes the INSERT permission on the LibraryBook table in the LibraryDB database from the user user1 connecting from localhost.
			
		
	


			  
                
                  Revoking Permissions
                  
                
                
                  -- Revoke INSERT permission on LibraryBook table from user 'user1'
REVOKE INSERT ON LibraryDB.LibraryBook FROM 'user1'@'localhost';

Granting and revoking permissions.

SQL


		
			
			GRANT SELECT, INSERT ON LibraryDB.LibraryBook TO 'user1'@'localhost';: Grants SELECT and INSERT permissions on the LibraryBook table to user1.

			REVOKE INSERT ON LibraryDB.LibraryBook FROM 'user1'@'localhost';: Revokes the INSERT permission on the LibraryBook table from user1.
			
		
	


			  
                
                  Granting and revoking permissions
                  
                
                
                  -- Use the database
USE LibraryDB;

-- Granting Permissions
-- Grant SELECT and INSERT permissions on LibraryBook table to user 'user1'
GRANT SELECT, INSERT ON LibraryDB.LibraryBook TO 'user1'@'localhost';

-- Revoking Permissions
-- Revoke INSERT permission on LibraryBook table from user 'user1'
REVOKE INSERT ON LibraryDB.LibraryBook FROM 'user1'@'localhost';