PROJECT

BookWise: Online Library

Management Database Project

Database System
Project Title:
BookWise: Online Library Management Database Project

Mission Statement: Our mission is to create a strong and accessible database system fitted for
the BookWise Online Library Management platform. BookWise will serve as the foundation for
efficient organization, retrieval, and management of library resources in the digital space, ensuring a
seamless and enriching experience for users.

Mission Objectives:
1.Centralizing & Optimizing: Design and implement a database schema optimized for online library
operations, covering entities such as books, authors, users, transactions, and administrative
functions.

2.User-Friendly Features: Develop data models that support essential features like online catalog
search, user account management, digital lending, and event scheduling.

3.Speedy Searches: Employ indexing and caching techniques to enhance search performance and
reduce latency, facilitating quick access to library resources.

4.Integrate security: Security measures like user authentication, authorization, and data encryption
to protect sensitive information and ensure user privacy.

5.Ready for Anything: Enable scalability and flexibility in database architecture to accommodate the
growing volume of digital assets(books etc), users, and transactions over time.

Overall Scope: The BookWise Online Library Management Database Project is all about creating
a super-smooth database system designed for online library. This includes designing database tables,
defining relationships, implementing data access methods, and ensuring data integrity and security.

Resources:
• Human Resources: Database administrators, data architects, SQL developers, project
managers to make this happen.
• Technological Resources: We’ll be using software “MySQL” for our database along with
database modeling tools, SQL scripting tools.
• Financial Resources: Budget allocation for software licenses, cloud space, and personnel
expenses.
• Data Resources: Digital library assets (books, e-books, audiobooks), user profiles, transaction
logs, event data, metadata.
Roles:
• Database Administrator: Responsible for database design, configuration, optimization, and
maintenance.
• Data Architect: They designs the database schema, tables, relationships, and indexing
strategies based on system requirements so everything fit together just right.
• SQL Developer: Setting up ways for the system to find information, perform tasks, and
respond to events using the database.
• Project Manager: Oversees project planning, resource allocation, progress tracking, and
keeping everything in the loop.

Data Collection for making Database:

Data collection for the online library system means bringing in information from library catalogs, user
lists, and records of transactions. It also involves gathering new data from things like user actions,
adding new content, and administrative tasks done on the online platform.

There are some questions, advice & queries etc., that helps to take a view how the database should
be created:

• "What details should we keep about each book?"

Ans: We should keep track of things like the title, author, ISBN number, genre, when it was
published, and a short description of what it's about.

• "How would you like to structure your online library management database?"
Ans: We want a database that can store details about books, authors, users, borrowing, and
admin tasks. It should be easy to search, secure, and let users log in securely. Also, it should grow
with us and connect with other systems.

• “What information is required to create user account?”

Ans: It should include name, username, password, email address and contact information.

• “What should be roles and permissions in the database(library)?”

Ans: Yes, there should be different roles (admin, regular user, patron etc.) and permissions like
limit access for each user.

• “What data needs to be tracked for each user borrowing transactions?”

Ans: It should include user id, book id, due date, return date etc.

• “How will be the copyright of the data will be managed on this digital platform?”
Ans: Copyright of data in the online library management system is managed through licensing
agreements, access controls, and agreement monitoring to ensure lawful usage, supported by
DRM (Digital rights management) technologies and legal guidance.
Limitations
The limitations of an online library management database include organizing and connecting
information about books, authors, users, and borrowing records. It's about making sure data is
accurate, secure, and easy to find. This involves setting up rules for how data is stored, accessed, and
protected. It also involves limit user access to our data of database.

Requirements
The requirements for online library management include features like user authentication,
cataloging books, managing user accounts, handling borrowing and returning processes, ensuring
data security, and providing user support. Additionally, it involves maintaining the digital
infrastructure and communication channels for feedback.

ERD (Entity-Relationship Diagram): The Entity-Relationship Diagram visually represents

the structure of the online library management database, illustrating the relationships between
entities such as books, authors, users, transactions, and administrative functions. It serves as a
blueprint for database design, facilitating communication among developers, administrators, and
patrons involved in the project.
E-R Diagram:

ERD 1: Made on "erdplus.com"

Relational Database Schema:

User:
user_id, username, email, password, full name.

Authentication:
auth_id, expiry, OTP, user_id (fk), password, email.

Admin:
admin_id , user_id (fk).

Patron:
patron_id, expiry_date, membership_type, user_id (fk).

Book:
book_id, published_year, genre, title, ISBN, publisher, edition, category, availability.

Book_Author:
book_id(fk), author_id(fk).

Author:
author_id, author_name.
 Report:
report_id, category, date_created, description, book_id (fk).

Publishing Event :
event_id , duration, event_title, date, book_published, publisher, book_id(fk).

Borrower:
borrower_id , contact_info, address, date_of_birth, user_id(fk), book_id(fk) .

Attribute domain table:

“User”
Attributes Domain Name Meaning Definition
user_id User Identification Unique id of user

user_name User Name Name of user

email Email Email of user

password Password Password of user

full_name Full Name Full name of user

“Authentication”
Attributes Domain Name Meaning Definition
auth_id Authentication User’s unique
Identification authentication ID

expiry Expiry Expiry date of OTP

OTP One-Time-Password Security

user_id (fk) User Identificatiom User’s unique ID

password Password Password of user

email Email Email of user

 “Admin”
Attributes Domain Name Meaning Definition
admin_id User Identification Unique ID of user

user_id (fk) Admin Identification Unique ID of admin

”Patron”
Attributes Domain Name Meaning Definition
patron_id Patron Identification To maintain the
integrity of patron
related data

expiry_date Expiry Date Expiry date of book

membership

membership_type Membership Type Defines what member

entitled to no. of book
that borrowed, other
privileges.

Uuer_id (fk) User Identification Unique user ID

“Book”
Attributes Domain Name Meaning Definition
Book_id Book Identification Unique ID of book

Published_year Published Year Year in which book

published

Genre Genre Classification of books

based on their content

Title Title Title of book

ISBN International Standard Unique numeric

Book Number identifier for books

Publisher Publisher Name Name of publisher

Edition Edition release of book

Category Category Used to group books &
material by shared
characterized

Availability Availability No. of books accessible

for borrowing

“Book_Author”
Attributes Domain Name Meaning Definition
book_id (fk) Book Identification Unique book ID

author_id (fk) Author Identification Unique author ID

“Author”
Attributes Domain Name Meaning Definition
author_id Author Identification Unique Author ID

author_name Author Name Name of the Author of

the Book

“Report”
Attributes Domain Name Meaning Definition
Report_id Report Identification Identifier

Category Category Used to group books

and material

Date_created Date Created Timestamp of the

report

Description Description Providing details about

books contents

Book_id (fk) Book Identification Unique book ID

 “PublishingEvent”
Attributes Domain Name Meaning Definition
event_ID Event Identification Unique event ID

duration Duration Time that particular

event lasts

event_title Event Title Name assigned to

library event

date Date Date when event

organized

book_publish Book Published Publication of book

author_name Author Name Name of author

Publisher Publishers Name of Publishers

“Borrower”
Attributes Domain Name Meaning Definition
borrower_ID User Identification Unique user ID

contact_info Contact Info Details like phone no,

email

date_of_birth Date of Birth Part of patron

personal info age
verification

user_id (fk) User Identification Unique User ID

Book_id(fk) Book Identification Unique Book ID

 Normalized relational model:

• Relation Name: User

User_id Full-name username email password

1NF:
The table is already in 1st normal form.

2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Admin

User_ID (fk) Admin_ID (u) Auth_ID (fk)

1NF:
The table is already in 1st normal form.

2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Publishing Event

publisher Duration Date Event_ID Authors(s) Book_Published Event_Title

1NF:
The table is already in 1st normal form.
 2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Borrower

Borrower_ID (fk) Address Contact_Info Date_of_birth

1NF:
The table is already in 1st normal form.

2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Report

Report_ID Category Description Book_ID (fk) Date_created

1NF:
The table is already in 1st normal form.

2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Authentication

Auth_ID Password User_ID Email Expiry OTP
 1NF:
The table is already in 1st normal form.

2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Patron

User_ID (fk) Patron_ID Expiry_Date Membership_Type

1NF:
The table is already in 1st normal form.

2NF:
The table is already in 2nd normal form.

3NF:
The table is already in 3rd normal form.

• Relation Name: Book

Book_Id Published_year genre title ISBN Publisher edition category availabilty

1NF:
Now, the table is already in 1st normal form.

2NF:
Now, the table is already in 2nd normal form.

3NF:
Now, the table is already in 3rd normal form.
 • Relation Name: Book_Author
book_ID(fk) author_ID(fk)

1NF:
Now, the table is already in 1st normal form.

2NF:
Now, the table is already in 2nd normal form.

3NF:
Now, the table is already in 3rd normal form.

• Relation Name: Author

author_ID author_name

1NF:
Now, the table is already in 1st normal form.

2NF:
Now, the table is already in 2nd normal form.

3NF:
Now, the table is already in 3rd normal form.
 DDL(Data Definition Language) CODE

• Schema Definition: This includes the creation of database objects such as tables,
specifying the columns, data types, constraints, and relationships

-- Create the BookWise database

CREATE DATABASE BookWise;

-- Use the BookWise database

USE BookWise;

-- Table: User
CREATE TABLE User (
user_id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(255),
username VARCHAR(255) UNIQUE,
email VARCHAR(255) UNIQUE,
password VARCHAR(255)
);

-- Table: Patron
CREATE TABLE Patron (
patron_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
membership_type VARCHAR(50),
expiry_date DATE,
FOREIGN KEY (user_id) REFERENCES User(user_id)
);

-- Table: Admin
CREATE TABLE Admin (
admin_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES User(user_id)
);

-- Table: Author
CREATE TABLE Author (
author_id INT AUTO_INCREMENT PRIMARY KEY,
author_name VARCHAR(255)
);

-- Table: Book
CREATE TABLE Book (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
ISBN VARCHAR(20) UNIQUE,
category VARCHAR(50),
edition VARCHAR(50),
publisher VARCHAR(255),
published_year YEAR,
genre VARCHAR(50),
availability BOOLEAN
);

-- Junction Table: Book_Author

CREATE TABLE Book_Author (
book_id INT,
author_id INT,
PRIMARY KEY (book_id, author_id),
FOREIGN KEY (book_id) REFERENCES Book(book_id),
FOREIGN KEY (author_id) REFERENCES Author(author_id)
);

-- Table: Borrower
CREATE TABLE Borrower (
borrower_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
book_id INT,
date_of_birth DATE,
address VARCHAR(255),
contact_info VARCHAR(255),
borrow_date DATE,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES Book(book_id),
FOREIGN KEY (user_id) REFERENCES User(user_id)
);

-- Table: Report
CREATE TABLE Report (
report_id INT AUTO_INCREMENT PRIMARY KEY,
 book_id INT,
description TEXT,
category VARCHAR(50),
date_created DATE,
FOREIGN KEY (book_id) REFERENCES Book(book_id)
);

-- Table: PublishingEvent
CREATE TABLE PublishingEvent (
event_id INT AUTO_INCREMENT PRIMARY KEY,
event_title VARCHAR(255),
date DATE,
duration TIME,
publisher VARCHAR(255),
book_published VARCHAR(255),
book_id INT,
FOREIGN KEY (book_id) REFERENCES Book(book_id)
);

-- Table: Authentication
CREATE TABLE Authentication (
auth_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
email VARCHAR(255) UNIQUE,
password VARCHAR(255),
OTP VARCHAR(50),
expiry DATETIME,
FOREIGN KEY (user_id) REFERENCES User(user_id)
);

• Data Insertion: Although strictly speaking, data insertion falls under DML (Data
Manipulation Language) rather than DDL, it is common to include some initial data
insertion commands alongside schema definitions for demonstration or testing purposes.

-- Sample data for User table

INSERT INTO User (full_name, username, email, password)
VALUES
('Muzammil Ali', 'muzammil123', 'muzammil@gmail.com', 'password123'),
('Fatima Ali', 'fatimaali456', 'fatima@gmail.com', 'qwerty456'),
('Mohammad Hassan', 'mohammadhassan789', 'mohammad@gmail.com', 'pass123'),
('Aisha Khan', 'aishakhan101', 'aisha@gmail.com', 'test123');
-- Sample data for Admin table
INSERT INTO Admin (user_id)
VALUES
(1),
(3);

-- Sample data for Author table

INSERT INTO Author (author_name)
VALUES
('J.K. Rowling'),
('Stephen King'),
('Agatha Christie'),
('Héctor García'),
('Francesc Miralles');

-- Sample data for Book table

INSERT INTO Book (title, ISBN, category, edition, publisher, published_year, genre,
availability)
VALUES
('Harry Potter and the Sorcerer''s Stone', '9780590353427', 'Fantasy', 'First', 'Scholastic',
1997, 'Young Adult', TRUE),
('The Shining', '9780385121675', 'Horror', 'First', 'Doubleday', 1977, 'Horror', TRUE),
('Murder on the Orient Express', '9780007119318', 'Mystery', 'First', 'Collins Crime Club',
1934, 'Mystery', TRUE),
('Ikegai', '9780141982326', 'Self-Help', 'First', 'Penguin Books Ltd', 2017, 'Personal
Development', TRUE);

-- Sample data for Book_Author junction table

INSERT INTO Book_Author (book_id, author_id)
VALUES
(1, 1), -- Harry Potter and the Sorcerer's Stone by J.K. Rowling
(2, 2), -- The Shining by Stephen King
(3, 3), -- Murder on the Orient Express by Agatha Christie
(4, 4),
(4, 5);
-- Sample data for Borrower table
INSERT INTO Borrower (user_id, book_id, date_of_birth, address, contact_info,
borrow_date, return_date)
VALUES
(2, 1, '1990-05-15', 'Gujranwala', '555-1234', '2024-06-08', '2024-07-08'),
(4, 3, '1985-09-20', 'Gujrat', '555-5678', '2024-06-08', '2024-07-08');
-- Sample data for Report table
INSERT INTO Report (book_id, description, category, date_created)
VALUES
(2, 'Some blank pages in the book ', 'Blank Page Problem', '2024-06-08'),
(3, 'Missing pages.', 'Page Deficieny', '2024-06-08');

-- Sample data for PublishingEvent table

INSERT INTO PublishingEvent (event_title, date, duration, publisher, book_published)
VALUES
('Book Launch Event', '2024-06-15', '3:00:00', 'Scholastic', 'Harry Potter and the
Sorcerer''s Stone'),
('The Shining Book Launch', '2024-07-20', '2:00:00', 'Doubleday', 'The Shining'),
('Murder on the Orient Express Book Launch', '2024-08-10', '1:30:00', 'Collins Crime
Club', 'Murder on the Orient Express');

-- Sample data for Patron table

INSERT INTO Patron (user_id, membership_type, expiry_date) VALUES
(1, 'Standard', '2024-12-31'),
(2, 'Premium', '2024-12-31'),
(3, 'Standard', '2024-12-31'),
(4, 'Premium', '2024-12-31');

-- Sample data for Authentication table

INSERT INTO Authentication (user_id, email, password, OTP, expiry) VALUES
(1, 'muzammil@gmail.com', 'password123', '789012', '2024-02-01 00:00:00'),
(2, 'fatima@gmail.com', 'qwerty456', '321987', '2024-02-01 00:00:00'),
(3, 'mohammad@gmail.com', 'pass123', '654098', '2024-03-01 00:00:00'),
(4, 'aisha@gmail.com', 'test123', '987654', '2024-03-01 00:00:00');

---------------------------------------------------THE END---------------------------------------------------------