The Library Management System (LMS) is a relational database application designed to
manage the core aspects of a library, such as the storage, tracking, and retrieval of books,
managing author details, keeping track of borrowers, and monitoring the status of borrowed
books. This system organizes and efficiently handles data about books, authors, borrowers, and
borrowing transactions.
Entities and Their Attributes:
1. Books:
o BookID (Primary Key)
o Title
o Genre
o PublishedYear
o AuthorID (Foreign Key)
2. Authors:
o AuthorID (Primary Key)
o Name
o Email
3. Borrowers:
o BorrowerID (Primary Key)
o FullName
o Email
o Phone
4. BorrowedBooks:
o BorrowID (Primary Key)
o BookID (Foreign Key)
o BorrowerID (Foreign Key)
o BorrowDate
o ReturnDate
Relationships:
Authors → Books: One author can write many books (One-to-Many).
Books → BorrowedBooks: One book can be borrowed many times (One-to-Many).
Borrowers → BorrowedBooks: One borrower can borrow many books (One-to-Many).
Step 1: Setting Up the Database
-- Step 1.1: Create a new database
CREATE DATABASE LibraryManagement;
GO
-- Step 1.2: Use the created database
USE LibraryManagement;
GO
Step 2: Creating Tables
Tables:
1. Books: Contains details about books in the library.
2. Authors: Contains information about book authors.
3. Borrowers: Contains information about people borrowing books.
4. BorrowedBooks: Tracks which borrower has borrowed which book.
Table Structure:
-- Step 2.1: Create Authors table
CREATE TABLE Authors (
AuthorID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Email NVARCHAR(100) UNIQUE
);
-- Step 2.2: Create Books table
CREATE TABLE Books (
BookID INT IDENTITY(1,1) PRIMARY KEY,
Title NVARCHAR(200) NOT NULL,
AuthorID INT NOT NULL,
PublishedYear INT,
Genre NVARCHAR(50),
-- FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);
-- Step 2.3: Create Borrowers table
CREATE TABLE Borrowers (
BorrowerID INT IDENTITY(1,1) PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
Email NVARCHAR(100) UNIQUE,
Phone NVARCHAR(15)
);
-- Step 2.4: Create BorrowedBooks table
CREATE TABLE BorrowedBooks (
BorrowID INT IDENTITY(1,1) PRIMARY KEY,
BookID INT NOT NULL,
BorrowerID INT NOT NULL,
BorrowDate DATE NOT NULL,
ReturnDate DATE,
-- FOREIGN KEY (BookID) REFERENCES Books(BookID),
-- FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID)
);
Step 3: Adding Relationships Using ALTER Commands
-- Step 3.1: Add a primary key constraint to Authors (already defined as part
of the table creation)
-- Step 3.2: Add a foreign key to Books referencing Authors
ALTER TABLE Books
ADD CONSTRAINT FK_Books_Authors
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID);
-- Step 3.3: Add a foreign key to BorrowedBooks referencing Books
ALTER TABLE BorrowedBooks
ADD CONSTRAINT FK_BorrowedBooks_Books
FOREIGN KEY (BookID) REFERENCES Books(BookID);
-- Step 3.4: Add a foreign key to BorrowedBooks referencing Borrowers
ALTER TABLE BorrowedBooks
ADD CONSTRAINT FK_BorrowedBooks_Borrowers
FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID);
Step 4: Inserting Sample Data
-- Step 4.1: Insert sample authors
INSERT INTO Authors (Name, Email)
VALUES
('J.K. Rowling', 'jk.rowling@example.com'),
('George R.R. Martin', 'grrm@example.com'),
('J.R.R. Tolkien', 'jrr.tolkien@example.com');
-- Step 4.2: Insert sample books
INSERT INTO Books (Title, AuthorID, PublishedYear, Genre)
VALUES
('Harry Potter and the Philosopher''s Stone', 1, 1997, 'Fantasy'),
('Harry Potter and the Chamber of Secrets', 1, 1998, 'Fantasy'),
('A Game of Thrones', 2, 1996, 'Fantasy'),
('The Hobbit', 3, 1937, 'Adventure');
-- Step 4.3: Insert sample borrowers
INSERT INTO Borrowers (FullName, Email, Phone)
VALUES
('Alice Johnson', 'alice.johnson@example.com', '123-456-7890'),
('Bob Smith', 'bob.smith@example.com', '987-654-3210');
-- Step 4.4: Insert sample borrowed books
INSERT INTO BorrowedBooks (BookID, BorrowerID, BorrowDate, ReturnDate)
VALUES
(1, 1, '2024-11-01', NULL), -- Alice borrowed "Harry Potter 1"
(3, 2, '2024-11-05', '2024-11-20'); -- Bob borrowed "A Game of Thrones"
Step 5: Querying the Data
Basic Queries
1. View all authors:
SELECT * FROM Authors;
2. View all borrowers:
SELECT * FROM Borrowers;
3. View all borrowed books:
SELECT * FROM BorrowedBooks;
4. View all books:
SELECT * FROM Books;
Using Aliases (AS)
1. Get all book titles along with their genre using aliases for columns:
SELECT Title AS BookTitle, Genre AS BookGenre FROM Books;
Expression
1. Calculate the age of the book based on PublishedYear (assume the current year is
2024):
SELECT Title, PublishedYear, (2024 - PublishedYear) AS BookAge
FROM Books;
2. List the names of the books and the borrower who had borrowed that book, in the
format “book borrowed by borrower”:
SELECT Title + ' borrowed by ' + FullName AS BookAndBorrower
FROM Books, Borrowers
3. Convert book titles to uppercase:
SELECT Title, UPPER(Title) AS UpperCaseTitle
FROM Books;
4. Calculate the number of days between BorrowDate and ReturnDate in the
BorrowedBooks table:
SELECT BookID, BorrowerID, BorrowDate, ReturnDate,
DATEDIFF(DAY, BorrowDate, ReturnDate) AS DaysBorrowed
FROM BorrowedBooks
WHERE ReturnDate IS NOT NULL;
Condition-Based Queries
1. View books published after the year 1990:
SELECT Title, PublishedYear FROM Books WHERE PublishedYear > 1990;
2. Find books published before the year 2000:
SELECT Title, PublishedYear
FROM Books
WHERE PublishedYear < 2000;
3. View borrowers who have not returned books:
SELECT BorrowerID, BookID, BorrowDate FROM BorrowedBooks WHERE
ReturnDate IS NULL;
4. View all fantasy books:
SELECT Title FROM Books WHERE Genre = 'Fantasy';
5. Get the details of borrowers whose names start with 'A':
SELECT FullName, Email
FROM Borrowers
WHERE FullName LIKE 'A%';
6. Retrieve books written by a specific author (e.g., J.K. Rowling):
SELECT Title
FROM Books
WHERE AuthorID = 1; -- Assuming J.K. Rowling has AuthorID 1
7. Display all fantasy books authored by a specific author (e.g., AuthorID 1):
SELECT Title, Genre
FROM Books
WHERE Genre = 'Fantasy' AND AuthorID = 1;
Sorting and Filtering
1. Sort books by their published year in descending order:
SELECT Title, PublishedYear FROM Books ORDER BY PublishedYear DESC;
2. Get all authors' names in alphabetical order:
SELECT Name FROM Authors ORDER BY Name ASC;
