University of the People
Programming Assignment: Unit 4
Library Management System Using SQL
Instructor: Abdulhadi Sanusi
a. Database Schema Creation
You are required to create a database schema for managing a library system with the following
entities and attributes:
1. Books with attributes:: ISBN (Primary Key), Title, Author, Genre, Quantity
CREATE TABLE Books (
ISBN VARCHAR(13) PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(255) NOT NULL,
Genre VARCHAR(100),
Quantity INT DEFAULT 0
);
2. Members with attributes: MemberID (Primary Key), Name, Email, Phone
CREATE TABLE Members (
MemberID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
Phone VARCHAR(15)
);
3. Loans with attributes:: LoanID (Primary Key), MemberID (Foreign Key), ISBN (Foreign
Key), LoanDate, ReturnDate
CREATE TABLE Loans (
LoanID INT PRIMARY KEY AUTO_INCREMENT,
MemberID INT,
ISBN VARCHAR(13),
LoanDate DATE NOT NULL,
ReturnDate DATE,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);
b. SQL Query Implementation
Once the database schema is designed, implement the following SQL queries. Provide
screenshots of the executed queries and the resulting records from the tables.
1. Insert Records
Write SQL INSERT statements to add new records into each of the following tables:
- Books
- Members
- Loans
Inserting a record into the Books table:
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity)
VALUES ('978-3-16-148410-0', 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 5);
Inserting a record into the Members table:
INSERT INTO Members (Name, Email, Phone) VALUES ('John Doe', 'john@example.com',
'1234567890');
Inserting a record into the Loans table:
INSERT INTO Loans (MemberID, ISBN, LoanDate, ReturnDate)
VALUES (1, '978-3-16-148410-0', '2024-10-03', '2024-10-17');
2. Retrieve Borrowed Books
SELECT Members.Name, Books.Title, Loans.LoanDate, Loans.ReturnDate
FROM Members
JOIN Loans ON Members.MemberID = Loans.MemberID
JOIN Books ON Loans.ISBN = Books.ISBN
WHERE Members.MemberID = '1';
3. Update Book Quantity
UPDATE Books
SET Quantity = Quantity - 1
WHERE ISBN = '978-3-16-148410-0';
4. Delete a Member Record
DELETE FROM Members
WHERE MemberID = '3';
c. Documentation and Comments
-- Create the Books table to store information about books in the library
CREATE TABLE Books (
ISBN VARCHAR(13) PRIMARY KEY, -- Unique identifier for each book (ISBN)
Title VARCHAR(255) NOT NULL, -- Title of the book
Author VARCHAR(255) NOT NULL, -- Author of the book
Genre VARCHAR(100), -- Genre of the book (optional)
Quantity INT DEFAULT 0 -- Number of copies available (default is 0)
);
-- Create the Members table to store information about library members
CREATE TABLE Members (
MemberID INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for each
member (automatically increments)
Name VARCHAR(255) NOT NULL, -- Name of the member
Email VARCHAR(255) UNIQUE NOT NULL, -- Email address of the member (must be
unique)
Phone VARCHAR(15) -- Phone number of the member (optional)
);
-- Create the Loans table to track the loan transactions of books
CREATE TABLE Loans (
LoanID INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for each loan
transaction (automatically increments)
MemberID INT, -- Foreign key linking to the Members table
ISBN VARCHAR(13), -- Foreign key linking to the Books table
LoanDate DATE NOT NULL, -- Date when the book was loaned out
ReturnDate DATE, -- Date when the book was returned (optional)
FOREIGN KEY (MemberID) REFERENCES Members(MemberID), -- Establishes a
relationship with the Members table
FOREIGN KEY (ISBN) REFERENCES Books(ISBN) -- Establishes a relationship with the
Books table
);
Summary of the Script
The Books table holds data about each book, including its ISBN, title, author, genre, and
quantity available.
The Members table contains details of library members, such as their IDs, names,
emails, and phone numbers.
The Loans table records the transactions of books being loaned out, including which
member borrowed which book, when it was borrowed, and when it was returned.
References
Vidhya, V., Jeyaram, G., & Ishwarya, K. (2016). Database management systems. Alpha Science
International.
Peterson, R. (2023, December 9). Functional dependency in DBMS: What is, types and
examples. Guru99. https://www.guru99.com/dbms-functional-dependency.html
Peterson, R. (2023, December 26). What is normalization in DBMS (SQL)? 1NF, 2NF, 3NF
example. Guru99. https://www.guru99.com/database-normalization.html
