Scribd
Upload
2 views

create a Databasee

Uploaded by

mihraban833
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views

create a Databasee

Uploaded by

mihraban833
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

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;

You might also like