.
PALESTINE POLYTECHNIC UNIVERSITY
College of Information Technology
Course name : Database Programming
Academic Year: 2023 \ 2024
STUDENTS NAME: Hamza Said
Fouad Al-Mashni
ACTIVITY TITLE: Book Rent System Project
Instructor: Professor Mahmoud Al-Saheb
1-Use Case Diagram :
You can access the full photo via this link : https://ibb.co/bWwBN9s
Actors
● Customer: This actor represents any user of the system who wishes to rent
books. Customers have access to various functionalities like registering, logging
in, searching for books, viewing book details, renting and returning books, viewing
their cart and total rent, and logging out.
● Admin: This actor represents users with administrative privileges. Admins can
log in, add new books, update book details, remove books from the system, view
registered users, remove users, and access various reports.
Use Cases
● Register: Customers can create a new account on the system.
● Login: Both customers and admins need to log in to access their respective
functionalities.
● Search Book: Customers can search for books using different criteria.
● View Book Details: Customers can view detailed information about a book,
including its cover page.
● Rent Book: Customers can rent a book, with the rent being calculated based on
the book's market price and the rental period.
● Return Book: Allows customers to return rented books.
● View Cart: Customers can view the books they have added to their cart.
● View Total Rent: Customers can see the total rent for the books they intend to
rent or have rented.
● Logout: Customers and admins can log out of the system.
● Add Book/Update Book Details/Remove Book: Admins can manage the book
inventory by adding new books, updating existing book details, and removing
books from the system.
● View Registered Users/Remove User: Admins can view all registered users and
have the authority to remove users if necessary.
● View Reports: Admins can access various reports such as a list of customers
and their rent costs, previous rents for a particular book, book details, a list of
inactive customers, and a search for books by their main subject. These reports
help in managing the system and making informed decisions.
Relationships
● Association: The lines connecting actors to use cases indicate that the actor is
involved in the use case.
● Extend: Some use cases like "View Reports" have extended use cases connected
with dotted lines. This shows that the base use case can be extended or
enhanced by the additional use case under certain conditions.
2-Book Rental System Database Conceptual Data Model
Entities:
Customer: Represents users of the system who can rent books.
● Attributes: CustomerID, Name, Address, Email, TelephoneNumbers (1 to
3).
Book: Represents books available for rent.
● Attributes: BookID, Title, LanguageCode, NumberOfPages, MajorTopic,
PublisherID.
BookCopy: Represents individual copies of a book, each with its own unique
status.
● Attributes: CopyID, BookID, Status (new, old, disposal).
Author: Represents authors who have written the books.
● Attributes: AuthorID, Name.
Publisher: Represents publishers who publish the books.
● Attributes: PublisherID, Name, Address.
Rent: Represents the rental transactions.
● Attributes: RentID, CustomerID, CopyID, StartDate, EndDate, TotalRent.
AudioBook: A specialized type of book with audio format.
● Attributes: BookID (inherited from Book), Duration, AudioFormat (WAV,
AIFF, AU, PCM).
Conceptual model (ER-Model using UML):
Logical Model (using mapping):
● Customers to Rents:
● One-to-Many relationship from Customer to Rent (a customer can have
multiple rental records).
● Books to BookCopies:
● One-to-Many relationship from Book to BookCopy (each book can have
multiple copies).
● Books to Publishers:
● Many-to-One relationship from Book to Publisher (each book is published
by one publisher).
● Books to Authors:
● Many-to-Many relationship handled by the Book_Author join table.
● Books to AudioBooks:
● One-to-One Inheritance relationship where AudioBook is a specialized type
of Book.
Check the entities to be in the 3NF normal form.
To perform normalization on the entities , we would need to first identify the functional
dependencies between the attributes of each entity. Based on these dependencies, we can then
apply normalization rules to ensure that the database is in a normalized form.
Check the entities to be in the 3NF normal form.
- First Normal Form: No repeated group or multi value
- Second Normal Form: There's a single attribute primary key (No partial dependence)
- Third Normal Form: No transitive dependency
1. Customer
● Attributes: CustomerID, Name, Address, Email, TelephoneNumbers
● Primary Key: CustomerID
● Functional Dependencies:
● CustomerID → Name, Address, Email, TelephoneNumbers
Normalization:
● 1NF: If TelephoneNumbers is stored as a serialized list or array, it violates 1NF. It
should be stored in separate fields or a separate table. Assuming correction, it's
in 1NF.
● 2NF: Since all non-key attributes are fully functionally dependent on the whole of
the primary key, it's in 2NF.
● 3NF: There are no transitive dependencies; all non-key attributes depend only on
CustomerID. It's in 3NF.
2. Book
● Attributes: BookID, Title, LanguageCode, NumberOfPages, MajorTopic,
PublisherID
● Primary Key: BookID
● Functional Dependencies:
● BookID → Title, LanguageCode, NumberOfPages, MajorTopic, PublisherID
Normalization:
● 1NF: Each attribute is atomic and unique per book.
● 2NF: All attributes are fully dependent on the primary key.
● 3NF: There are no transitive dependencies among non-key attributes. It's in 3NF.
3. Publisher
● Attributes: PublisherID, Name, Address
● Primary Key: PublisherID
● Functional Dependencies:
● PublisherID → Name, Address
Normalization:
● 1NF: Each attribute is atomic.
● 2NF: As there's only one key attribute, there is no partial dependency.
● 3NF: No transitive dependencies are present. It's in 3NF.
4. Author
● Attributes: AuthorID, Name
● Primary Key: AuthorID
● Functional Dependencies:
● AuthorID → Name
Normalization:
● 1NF: Each attribute is atomic.
● 2NF: All attributes are fully dependent on the primary key.
● 3NF: No transitive dependencies exist. It's in 3NF.
5. Book_Author (Join Table)
● Attributes: BookID, AuthorID
● Primary Key: (BookID, AuthorID)
● Functional Dependencies:
● (BookID, AuthorID) → None (no non-key attributes)
Normalization:
● 1NF: Both attributes are atomic.
● 2NF: This table is a join table to resolve many-to-many relationships, and each
attribute is part of the primary key.
● 3NF: No non-key attributes means no transitive dependencies. It's in 3NF.
6. BookCopy
● Attributes: CopyID, BookID, Status
● Primary Key: CopyID
● Functional Dependencies:
● CopyID → BookID, Status
Normalization:
● 1NF: Each attribute is atomic.
● 2NF: All attributes are fully dependent on the primary key.
● 3NF: There are no transitive dependencies. It's in 3NF.
7. Rent
● Attributes: RentID, CustomerID, CopyID, StartDate, EndDate, TotalRent
● Primary Key: RentID
● Functional Dependencies:
● RentID → CustomerID, CopyID, StartDate, EndDate, TotalRent
Normalization:
● 1NF: Each attribute is atomic.
● 2NF: All attributes are fully dependent on the primary key.
● 3NF: There are no transitive dependencies among non-key attributes. It's in 3NF.
8. AudioBook
● Attributes: BookID, Duration, AudioFormat
● Primary Key: BookID (also a Foreign Key)
● Functional Dependencies:
● BookID → Duration, AudioFormat
Normalization:
● 1NF: Each attribute is atomic.
● 2NF: All attributes are fully dependent on the primary key.
● 3NF: No transitive dependencies. It's in 3NF.
Data dictionary for each table and Physical Design
Admin
Column Name Data Type Description
AdminID INT Unique identifier for the admin
Name VARCHAR(100) Name of the admin
Email VARCHAR(100) Email address of the admin
Role VARCHAR(50) Role or position of the admin
CREATE TABLE Admin (
AdminID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Role VARCHAR(50)
);
CustomerPhones
Field Data Type Description
PhoneID INT Uniquely identifies a phone record.
Links to the Customer table to
CustomerID INT
identify the Customer.
PhoneNumber VARCHAR(20) The Customer phone number.
CREATE TABLE CustomerPhones (
PhoneID INT AUTO_INCREMENT PRIMARY KEY,
CustomerID INT NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL UNIQUE,
FOREIGN KEY (CustomerID) REFERENCES
Customer(CustomerID)
);
Column Name Data Type Description
CustomerID INT Unique identifier for the customer
Name VARCHAR(100) Name of the customer
Address VARCHAR(255) Address of the customer
Email VARCHAR(100) Email address of the customer
CREATE TABLE Customer (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Address VARCHAR(255),
Email VARCHAR(100),
);
Publisher
Column Name Data Type Description
PublisherID INT Unique identifier for the publisher
Name VARCHAR(100) Name of the publisher
Address VARCHAR(255) Address of the publisher
CREATE TABLE Publisher (
PublisherID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Address VARCHAR(255)
);
Book:
Column Name Data Type Description
BookID INT Unique identifier for the book
Title VARCHAR(255) Title of the book
LanguageCode CHAR(2) Language code of the book
NumberOfPages INT Number of pages in the book
MajorTopic VARCHAR(100) Major topic or subject of the book
Foreign key referencing
PublisherID INT
Publisher(PublisherID)
CREATE TABLE Book (
BookID INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(255),
LanguageCode CHAR(2),
NumberOfPages INT,
MajorTopic VARCHAR(100),
PublisherID INT,
FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);
AudioBook
Column Name Data Type Description
Primary key and Foreign key referencing
BookID INT
Book(BookID)
Duration TIME Duration of the audio book
ENUM('WAV', 'MP3', 'AAC',
AudioFormat Format of the audio book
'FLAC')
Database Definitions (DDL)
-- Assuming 'enum' for AudioFormat consists of 'WAV', 'MP3', etc.
CREATE TABLE AudioBook (
BookID INT PRIMARY KEY,
Duration TIME,
AudioFormat ENUM('WAV', 'MP3', 'AAC', 'FLAC'),
FOREIGN KEY (BookID) REFERENCES Book(BookID)
);
Author
Column Name Data Type Description
AuthorID INT Unique identifier for the author
Name VARCHAR(100) Name of the author
CREATE TABLE Author (
AuthorID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100)
);
BookAuthor
Column Name Data Type Description
BookID INT Foreign key referencing Book(BookID)
AuthorID INT Foreign key referencing Author(AuthorID)
Composite primary key consisting of BookID and
Primary Key (BookID, AuthorID)
AuthorID
CREATE TABLE BookAuthor (
BookID INT,
AuthorID INT,
PRIMARY KEY (BookID, AuthorID),
FOREIGN KEY (BookID) REFERENCES Book(BookID),
FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);
BookCopy
Column Name Data Type Description
CopyID INT Unique identifier for the book copy
Status VARCHAR(50) Status of the book copy (e.g., available, rented)
BookID INT Foreign key referencing Book(BookID)
CREATE TABLE BookCopy (
CopyID INT AUTO_INCREMENT PRIMARY KEY,
Status VARCHAR(50),
BookID INT,
FOREIGN KEY (BookID) REFERENCES Book(BookID)
);
Rent
Column Name Data Type Description
RentID INT Unique identifier for the rent
StartDate DATETIME Start date of the rental
EndDate DATETIME End date of the rental
TotalRent DECIMAL(10,2) Total rent amount
CustomerID INT Foreign key referencing Customer(CustomerID)
CopyID INT Foreign key referencing BookCopy(CopyID)
CREATE TABLE Rent (
RentID INT AUTO_INCREMENT PRIMARY KEY,
StartDate DATETIME,
EndDate DATETIME,
TotalRent DECIMAL(10,2),
CustomerID INT,
CopyID INT,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (CopyID) REFERENCES BookCopy(CopyID)
);
Physical Design
Views
CREATE VIEW CustomerRentView AS
SELECT c.Name, r.StartDate, r.EndDate, r.TotalRent
FROM Customer c
JOIN Rent r ON c.CustomerID = r.CustomerID;
Indexes
CREATE INDEX idx_book_title ON Book(Title);
CREATE INDEX idx_author_name ON Author(Name);
CREATE INDEX idx_customer_email ON Customer(Email);
CREATE INDEX idx_rent_dates ON Rent(StartDate, EndDate);
Queries for Reports
-- List of Customers and Rent Costs
SELECT c.CustomerID, c.Name, SUM(r.TotalRent) AS TotalRent
FROM Customer c
JOIN Rent r ON c.CustomerID = r.CustomerID
GROUP BY c.CustomerID;
-- List of all Previous Rents for one Book
SELECT r.RentID, r.StartDate, r.EndDate, r.TotalRent, c.Name AS CustomerName
FROM Rent r
JOIN Customer c ON r.CustomerID = c.CustomerID
JOIN BookCopy bc ON r.CopyID = bc.CopyID
WHERE bc.BookID = ?; -- Replace ? with actual BookID
-- Book Details by BookID
SELECT b.BookID, b.Title, b.NumberOfPages, b.MajorTopic, p.Name AS PublisherName
FROM Book b
JOIN Publisher p ON b.PublisherID = p.PublisherID
WHERE b.BookID = ?; -- Replace ? with actual BookID
-- List of all Inactive Customers
SELECT c.CustomerID, c.Name, c.Email
FROM Customer c
WHERE NOT EXISTS (
SELECT 1 FROM Rent r WHERE r.CustomerID = c.CustomerID AND r.EndDate >
CURRENT_DATE - INTERVAL 1 YEAR
);
-- Search for a Book by its Main Subject
SELECT b.BookID, b.Title
FROM Book b
WHERE b.MajorTopic = ?; -- Replace ? with actual MajorTopic
