University of Sulaimani
College of Commerce
Information Technology Department
2nd Stage – 1st Semester
Subject: Library Management Database
Submitted By
Mihraban Faxir Braim
Supervised by
Mr. Bilal Rashid
Submission Date: 2 Dec 2024
2024-2025
Diagram:
2
First to create a Database, we write this code in SQL and RUN:
Next create the Tables in the Database:
Books Table code:
Books structure:
3
Members table code:
Members Structure:
Bonus Task:
Add librarians table:
CREAT TABLE librarians(
LibraryID INT AUTO-INCREMENT PRIMARY KEY,
NAME VARCHAR(225) NOT NULL
Phone VARCHAR(20) UNIQUE
);
4
Borrowing Table code:
CREATE TABLE Borrowing ( BorrowID INT AUTO_INCREMENT PRIMARY KEY,
BookID INT NOT NULL,
MemberID INT NOT NULL,
LibrarianID INT NOT NULL,
BorrowDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ReturnDate DATE CHECK (ReturnDate > BorrowDate),
FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN KEY (LibrarianID) REFERENCES Librarians(LibrarianID) );
ADD NEW COLUMN:
ALTER TABLE Members ADD COLUMN PhoneNumber VARCHAR(15);
RENAME THE COLUMN:
ALTER TABLE Members RENAME PhoneNumber TO ContactNumber;
CHANGE DATATYPE:
ALTER TABLE Members CHANGE PhoneNumber ContactNumber VARCHAR(20);
Or ALTER TABLE Members MODIFY COLUMN ContactNumber VARCHAR(20) NOT NULL;
Note:
• MODIFY: Changes the datatype of the column but keeps its name.
• CHANGE: To rename the column and change its datatype at the same time.
ADD NOT NULL:
ALTER TABLE Members MODIFY COLUMN ContactNumber VARCHAR(20) NOT NULL;
REMOVE COLUMN:
ALTER TABLE Members DROP COLUMN ContactNumber;
5
Add 5 books with different genres and prices :
INSERT INTO Books (Title, Genre, Price) VALUES (‘CHILL GUY’,’Non-fiction’,’7,89’),
(‘flowers’,’fiction’,’5,00’),(‘friends’,’Non-fiction’,’5,55),(‘VIKINGS’,’fiction’,’9,09),
(‘ Crime and Punishment’,’fiction’,’10,00’);
Add 5 members with unique emailes:
INSERT INTO Members (FirstName, LastName, Gender, DateOfBirth, Email)
VALUES ('Lana', 'Ahmed', 'Female', '2005-01-10', 'lanaahmed1@***.com'),
('Sewa', 'Shad', 'Female', '1999-02-20', ''sewajan@***.com '),
('Hastyar', 'Hamd', 'Other', '2003-03-05', 'hastyard@***.com’),
('Sana', 'Hadi', 'Female', '2000-11-17',’ 'sanahadi@***.com’),
('Muhamad', 'Saman', 'Male', '2000-07-05', 'muhamada@***.com’);
Add 3 borrowing records:
INSERT INTO Borrowing (BookID, MemberID, ReturnDate)
VALUES (2, 2, '2024-11-10'),
(3, 3, '2024-12-09'),
(4, 4, '2024-03-20');
Update:
- UPDATE Books
SET Price = 6,13
WHERE BookID = 1;
SELECT * FROM Books WHERE BookID = 1;
- UPDATE Members
SET Email = ' sewashad@***.com’
WHERE MemberID = 2;
SELECT * FROM Members WHERE MemberID = 2;
6
- UPDATE Borrowing
SET ReturnDate = '2024-12-25'
WHERE BorrowID = 1;
SELECT * FROM Borrowing WHERE BorrowID = 1;
- DELETE FROM Members
WHERE MemberID = 2;
SELECT * FROM Members WHERE MemberID = 2;
SELECT * FROM Borrowing WHERE MemberID = 2;
Errors:
➢ Insert a book with Null title :
The code is
INSERT INTO Books (BookID, Title, Author, Price)
VALUES (105, NULL, 'Author Name', 25.00);
But we can not add this as Null because we previously assigned it as Not Null, and now we can
not enter it as Null.
➢ Insert a member with duplicate email:
We can not add two emails because it has been set up in a way only accept unique .
➢ Insert a borrowing record where ReturnDate is earlier than BorrowData:
It must be ReturnDate later than BorrowData
Bonus Task:
TRUNCATE TABLE Borrowing;
TRUNCATE TABLE Librarians;
TRUNCATE TABLE Members;
TRUNCATE TABLE Books;
