The Online Library Management System is my capstone project in my MS SQL Masterclass. I'm assigned to create an online library and perform DML (Data Manipulation Language), DDL (Data Definition Language), DQL (Data Query Language), and DCL (Data Control Language).
Task 1: Define the Database Schema
Description: Create the database schema for the library management system, defining tables
and relationships.
-- Creating Library Database CREATE DATABASE Library -- Creating the Books Table CREATE TABLE Books( BookID INT NOT NULL, Title NVARCHAR(150) NOT NULL, Author NVARCHAR(100), Language NVARCHAR(50), DatePublished DATE, GenreID INT ) -- Creating the Genre Table CREATE TABLE Genre( GenreID INT NOT NULL, Genre NVARCHAR(150) ) -- Creating the Borrowers Table CREATE TABLE Borrowers ( StudentID INT NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BookID INT NOT NULL ) -- Creating the Students Info Table CREATE TABLE StudentsInfo ( StudentID INT NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, Gender NVARCHAR(6) NOT NULL, DateOfBirth NVARCHAR(12) NOT NULL, ContactInformation NVARCHAR(20) NOT NULL )
Task 2: Specify Keys and Constraints
Description: Define primary keys, foreign keys, constraints, and relationships between tables.
-- Defining Primary Key for books table ALTER TABLE Books ADD CONSTRAINT PK_Books PRIMARY KEY(BookID) -- Defining Primary Key for Genre table ALTER TABLE Genre ADD CONSTRAINT PK_GenreID PRIMARY KEY (GenreID) -- Defining Primary Key for Students Info table ALTER TABLE StudentsInfo ADD CONSTRAINT PK_StudentID PRIMARY KEY (StudentID)
Task 3: Populate the Database with Sample Data
Description: Insert sample data into the database, including books, user information, and
transactions.
--INSERTING sample book records INSERT INTO Books( BookID, Title, Author, Language, DatePublished, GenreID ) VALUES (1, 'A Tale of Two Cities','Charles Dickens','English','1859',28), (2, 'The Little Prince (Le Petit Prince)','Antoine de Saint-Exupéry','French','1943',41), (3, 'Harry Potter and the Philosopher''s Stone','J. K. Rowling','English','1997',23), (4, 'And Then There Were None','Agatha Christie','English','1939',36), (5, 'Dream of the Red Chamber (紅樓夢)','Cao Xueqin','Chinese','1791',22), (6, 'The Hobbit','J. R. R. Tolkien','English','1937',23), (7, 'The Lion, the Witch and the Wardrobe','C. S. Lewis','English','1950',23), (8, 'She: A History of Adventure','H. Rider Haggard','English','1887',1), (9, 'Vardi Wala Gunda (वर्दी वाला ग ुंडा)','Ved Prakash Sharma','Hindi','1992',17), (10, 'The Da Vinci Code','Dan Brown','English','2003',37), (11, 'Harry Potter and the Chamber of Secrets','J. K. Rowling','English','1998',23), (12, 'Harry Potter and the Prisoner of Azkaban','J. K. Rowling','English','1999',23), (13, 'Harry Potter and the Goblet of Fire','J. K. Rowling','English','2000',23), (14, 'Harry Potter and the Order of the Phoenix','J. K. Rowling','English','2003',23), (15, 'Harry Potter and the Half-Blood Prince','J. K. Rowling','English','2005',23), (16, 'Harry Potter and the Deathly Hallows','J. K. Rowling','English','2007',23), (17, 'The Alchemist (O Alquimista)','Paulo Coelho','Portuguese','1988',23), (18, 'The Catcher in the Rye','J. D. Salinger','English','1951',13), (19, 'The Bridges of Madison County','Robert James Waller','English','1992',46), (20, 'Ben-Hur: A Tale of the Christ','Lew Wallace','English','1880',28), (21, 'You Can Heal Your Life','Louise Hay','English','1984',53), (22, 'One Hundred Years of Solitude (Cien años de soledad)','Gabriel García Márquez','Spanish','1967',33), (23, 'Lolita','Vladimir Nabokov','English','1955',40),(24, 'Heidi','Johanna Spyri','German','1880',7), (25, 'The Common Sense Book of Baby and Child Care','Benjamin Spock','English','1946',34), ... Continuation : GitHub Link
-- INSERTING sample Genre records INSERT INTO Genre( GenreID, Genre ) VALUES (1, 'Adventure'), (2, 'Autobiographical novel'), (3, 'Autobiography'), (4, 'Bildungsroman'), (5, 'Biographical novel'), (6, 'Children''s fantasy novel'), (7, 'Children''s fiction'), (8, 'Children''s literature'), (9, 'Children''s novel'), (10, 'Children''s picture book'), (11, 'Christian literature'), (12, 'Classic regency novel'), (13, 'Coming-of-age'), (14, 'Coming-of-age Murder mystery'), (15, 'Crime novel'),(16, 'Crime thriller novel'), (17, 'Detective'), (18, 'Dystopian fiction'), (19, 'Dystopian'),(20, 'Erotica'), (21, 'Essay/Literature'), (22, 'Family saga'), (23, 'Fantasy'), (24, 'Feminist novel'), (25, 'Fiction'), ... Continuation : GitHub Link
-- Inserting Students Info INSERT INTO StudentsInfo( StudentID, FirstName, LastName, Gender, DateOfBirth, ContactInformation ) VALUES (1,'Emilio','Sutton','Male','2010-03-24','4279136052'), (2,'Marie','Terrell','Female','2011-04-12','9292275268'), (3,'Marci','Crawford','Female','2011-08-23','2706509721'), (4,'Sean','Hooper','Male','2011-01-29','7236067595'), (5,'Annmarie','House','Female','2010-01-17','3054312373'), (6,'Adrian','Petersen','Male','2011-01-24','8497793876'), (7,'Jermaine','Sherman','Female','2011-05-31','9245478519'), (8,'Isabelle','Ingram','Female','2011-08-28','262346399'), (9,'Lionel','Hughes','Male','2011-07-28','5061167473'), (10,'Rayford','Villa','Male','2010-04-22','6524638243'), (11,'Jesus','Osborn','Male','2010-09-23','8764583622'), (12,'Clair','Bender','Female','2011-07-22','5013277127'), (13,'Nadine','Mcguire','Female','2010-06-06','4646150553'), (14,'Adriana','Coffey','Female','2011-12-28','7910311108'), (15,'Angie','Lloyd','Female','2010-11-21','6861024275'), (16,'Alexander','Stanton','Male','2011-06-18','2416086703'), (17,'Roy','Simpson','Male','2010-02-23','8802004982'), (18,'Connie','Dunlap','Female','2011-12-30','7653620966'), (19,'Edith','Keith','Female','2011-08-12','1468126034'), (20,'Donovan','Edwards','Female','2011-07-15','3112403183'), (21,'Ignacio','Reeves','Male','2011-06-12','1714817033'), (22,'Charley','Daugherty','Female','2011-05-02','8069617923'), (23,'Sheri','Hardin','Female','2010-08-21','6048254722'), (24,'Ezequiel','Hall','Male','2011-06-09','4125152639'), (25,'Bethany','Conrad','Female','2011-04-05','2770487472'), ... Continuation : GitHub Link
-- Inserting into borrowers INSERT INTO Borrowers ( StudentID, FirstName, LastName, BookID ) VALUES (1,'Emilio','Sutton',56), (26,'Tameka','Bright',18), (20,'Donovan','Edwards',95), (6,'Adrian','Petersen',63), (24,'Ezequiel','Hall',32), (26,'Tameka','Bright',90), (14,'Adriana','Coffey',75), (40,'Loretta','Peterson',44), (39,'Patricia','Garrison',21), (39,'Patricia','Garrison',24), (50,'Chandra','Hodge',105), (33,'Emmanuel','Marks',105), (1,'Emilio','Sutton',15), (22,'Charley','Daugherty',88), (7,'Jermaine','Sherman',4), (27,'Manuela','Morrow',73), (30,'Williams','Berg',60), (43,'Aisha','Sampson',33), (3,'Marci','Crawford',33), (24,'Ezequiel','Hall',42), (27,'Manuela','Morrow',59), (9,'Lionel','Hughes',16), (29,'Tammie','Proctor',55), (7,'Jermaine','Sherman',16), (18,'Connie','Dunlap',109), (39,'Patricia','Garrison',61), ... Continuation : GitHub Link
Task 4: Implement Data Modification
Description: Create SQL scripts for updating, deleting, and modifying data as needed.
-- Updating the books by removing excess data UPDATE Books SET Title = 'Dream of the Red Chamber' WHERE BookID = 5 UPDATE Books SET Title = 'Vardi Wala Gunda' WHERE BookID = 9 UPDATE Books SET Title = 'How the Steel Was Tempered' WHERE BookID = 44 UPDATE Books SET Title = 'War and Peace' WHERE BookID = 45 UPDATE Books SET Title = 'The Young Guard' WHERE BookID = 64 UPDATE Books SET Title = 'Virgin Soil Upturned' WHERE BookID = 71 UPDATE Books SET Title = 'Uncle Styopa' WHERE BookID = 76 UPDATE Books SET Title = 'Kitchen' WHERE BookID = 81 UPDATE Books SET Title = 'Andromeda Nebula' WHERE BookID = 82 UPDATE Books SET Title = 'Autobiography of a Yogi' WHERE BookID = 83 UPDATE Books SET Title = 'Wolf Totem' WHERE BookID = 88 UPDATE Books SET Title = 'Wolf Totem' WHERE BookID = 89 UPDATE Books SET Title = 'Totto-chan, the Little Girl at the Window' WHERE BookID = 107 UPDATE Books SET Title = 'Totto-chan, the Little Girl at the Window' WHERE BookID = 108
Task 5: Create SQL Queries for Common Tasks
Description: Develop SQL queries to perform common library-related tasks, such as searching for books by title, author, or genre.
-- Display the title and author of the book SELECT Title, Author FROM Books
-- Find the details of books that BookID = 100 SELECT * FROM Books WHERE BookID = 100
-- Find the books that are published year 2000 onwards SELECT * FROM Books WHERE DatePublished >= '2000'
-- Find the books that are published before year 2000 SELECT * FROM Books WHERE DatePublished <= '2000'
-- Find the total count of books SELECT COUNT(Title) AS [Total Books] FROM Books
-- Rank the students based on their borrowed books SELECT distinct(StudentID), FirstName, LastName, COUNT(*) AS [Total Borrowed Books] FROM Borrowers GROUP BY StudentID, FirstName, LastName ORDER BY [Total Borrowed Books] DESC
-- Find the books that the language is Chinese and Portuguese SELECT * FROM Books WHERE Language = 'Chinese' OR Language = 'Portuguese'
-- Find the book where the author is J. K. Rowling that published year 2000 onwards SELECT * FROM Books WHERE Author LIKE '%owling' and DatePublished >= '2000'
Task 6: Implement Advanced Queries
Description: Construct more advanced SQL queries for generating reports on borrowed books, overdue books, and popular book genres.
-- Find The Genre Of each title SELECT B.Title, G.Genre FROM Books AS B JOIN Genre AS G ON B.GenreID = G.GenreID
-- Find the top 50 Students that borrowed books Together with the genre of the books SELECT TOP 50 BW.StudentID, BW.FirstName, BW.LastName, B.Title, G.Genre FROM Books AS B JOIN Genre AS G ON B.GenreID = G.GenreID JOIN Borrowers AS BW ON B.BookID = BW.BookID
-- What is the TOP 10 most borrowed books based on their genre? SELECT TOP 10 G.Genre, COUNT(BW.StudentID) AS Most_Borrowed_Books_By_Genre FROM Borrowers AS BW LEFT JOIN Books AS BK ON BW.BookID = BK.BookID LEFT JOIN Genre AS G ON BK.GenreID = G.GenreID GROUP BY G.Genre ORDER BY COUNT(BW.StudentID) DESC
-- count the students based on their age SELECT DATEDIFF(year,DateOfBirth, GETDATE()) AS Age , COUNT(*) AS Students FROM StudentsInfo GROUP BY DATEDIFF(year,DateOfBirth, GETDATE())
-- What are the most popular books that borrowed by the students SELECT Title, Author, COUNT(BR.BookID) AS Total_Borrowed_Books FROM Borrowers AS BR JOIN Books AS BK ON BR.BookID = BK.BookID GROUP BY Title, Author, BR.BookID ORDER BY COUNT(BR.BookID) DESC
Task 7: Define User Roles and Permissions
Description: Define user roles (e.g., librarian, member) and set appropriate permissions.
-- Description: Define user roles (e.g., librarian, member) and set appropriate permissions. USE Library CREATE ROLE librarian; CREATE ROLE Adviser;
Task 8: Implement Access Control Statements
Description: Implement DCL statements to control access to the database based on user roles
and permissions.
GRANT SELECT ON Books TO librarian GRANT UPDATE ON StudentsInfo TO Adviser
Task 9: Simulate User Interactions
Description: Simulate user interactions with the system, demonstrating how access control
works based on user roles.
USE master CREATE LOGIN UserLibrarian WITH PASSWORD = 'UserLibrarian' USE Library CREATE USER Librarian1 FOR LOGIN UserLibrarian EXEC sp_addrolemember 'Librarian','Librarian1' USE MASTER CREATE LOGIN UserAdviser WITH PASSWORD = 'UserAdviser' USE Library CREATE USER Adviser1 FOR LOGIN UserAdviser EXEC sp_addrolemember Adviser,Adviser1