DIRE DAWA UNIVERSIT

College of Computing

Department of software Engineering

Course : Database System

Title : Library management database system of dire dawa youth center Name
Id Number

1.Teshager Habtie 1303976

2.Kaleab solomon 1409657

3.Samuel Ayele 1402911

4.Anwar Dawud 1401371

5.Murtessa Shemsedin 1409235

6.Mohammed Kedir 1402651

Submitted on:

Submitted to:
Introduction
The dire dawa Center library currently relies on an outdated and inefficient paper-based or stand-
alone system for managing their data. Recognizing the need for improvement in data collection
and information sharing, our project aims to introduce a modern database management system to
replace the existing system. By implementing this new system, the organization will experience
enhanced efficiency and effectiveness in managing their data, ultimately benefiting both
librarians and library users. This project paper will outline the various phases involved in
developing a Library Management System specifically tailored for the dire dawa Center in a
university around sabian

DIRE DAWA UNIVERSITY LIBRARY

• Customers and users in the selected organization

The company we have selected provides library services on weekdays. As a service
provider, the library chosen handles a large volume of customers (users) within a specific
period of time. The number of users in the selected organization is approximately 1500.
This includes both dire dawa center library members and librarians.
• Challenges that exist in data handling of the organization and
limitations of the current system
The challenges of the existing data handling system include:
• Limited storage capacity: The current paper-based system has limited space (shelf or
cabinet) to store all the necessary information and recordings, leading to a lack of
comprehensive records.
• Inefficient organization: The paper-based system makes it difficult to locate and retrieve
specific information quickly such as transaction history, resulting in wasted time and
effort.
• Lack of information sharing: The stand-alone system does not allow for easy sharing of
data between librarians who are on different time shifts, leading to a lack of collaboration
and potential duplication of efforts.
• Data security risks: Paper-based records are susceptible to damage or loss, which can
result in the permanent loss of important data.
• How the data is managed (stored, organized, searched, retrieved,
 deleted and generate report)
The data is currently managed through a combination of physical files and manual record-
keeping. This process is time-consuming and prone to errors. The information is stored in
filing cabinets and organized alphabetically using the library member’s name and generalized
assertion of books on shelves. To search for specific data, librarians need to manually go
through the files on the cabinet. Retrieval also involves physically pulling out the relevant
file which is quite tiring for librarians. Data deletion is done by physically removing the
paper record from the file by the librarians.
• Benefit of replacing an existing system by using a
database system
The benefits of replacing the existing system with a database management system can be
measured in several ways:
• Time efficiency: The database system will allow for quick and easy retrieval of
information, saving time for librarians.
• Improved data accuracy: With a database system, there will be fewer chances for errors
or omissions in the data especially concerning transactions, reservations and due-date of
borrowed books, leading to more accurate records.
• Enhanced information sharing: The database system will enable seamless sharing of data
between the librarians particularly for those who aren't on the same shift, promoting
collaboration and avoiding duplication of efforts.
• Increased data security: A database system can provide better protection against data loss
or damage, ensuring the integrity and availability of important information.
• List of business requirements and expectations of the database
project
Business requirements and expectations for the database project include:
• Input: The system should allow for the recording of library member information,
membership status, number of book copies, transaction process.
• Processing: The system should be able to process data quickly and efficiently, allowing
for easy retrieval and manipulation of information.
• Storage/Organization: The system should provide a centralized database that can store
and organize all relevant data in a structured manner such as book allocation and book
reservation.
• Frequency of transactions: The system should support daily transactions, including new
registrations, book checkouts, returns, and updates to librarians and library members.
• Reports/Outputs: The system should be able to generate reports on various aspects such
as book availability. These reports should be easily accessible to different departments or
staff members.
• The collection of related tables/records managed, stored,
maintained, and frequently manipulated in the organization
The collection of related tables/records managed, stored, maintained, and frequently manipulated
in the organization would include:
• Library member table: This table would store all customer information, including
personal details, membership status, borrowing history, and contact information.
• Librarian table: This table would record staff information, including their roles,
responsibilities, and contact details.
• Book table: This table would contain information about the available books in the library,
including their titles, authors, genres, and availability status.
• Transaction table: This table would record all the transactions related to book checkouts,
returns, and renewals. It would include details such as the customer ID, book ID,
transaction date, and due date.
• Copy table: This table would contain information books found in large numbers,
 availability, book conditions.

Conceptual model
Entities and their attributes

Book: The Book entity represents a specific book title or work. The Book entity serves as a
central reference for all copies of a particular book. Its attributes are :

•
• Book_id (primary key)
• Title
• Authors
• ISBN/ISSN
• Publication_year
• Publisher
• Edition
• Genre

Copy: The Copy entity represents a physical instance or unit of a book. Its attributes are :

•
• Copy_id (primary key)
• Book_id (foreign key)
• Location
• Condition
• Availability_status

Library Member: The Library Member entity represents an individual who has registered as a
member of the library.Library members can borrow books, interact with librarians, and engage in
transactions within the library system. Its attributes are :

•
• Member_id (primary key)
• Name
• Address
• Contact_number
• Memeber_email

Librarian: The Librarian entity represents an individual who works at the library, responsible
for managing library operations, assisting library members, and facilitating transactions. Its
attributes are :
 •
• Librarian_id (primary key)
• Librarian_name
• Contact_number
• Librarian_email

Transaction: The Transaction entity represents a specific interaction or activity that occurs
between a Library Member and the library. It captures details about the borrowing, return,
renewal, or other operations related to a Copy of a Book. Its attributes are :

•
• Transaction_id (primary key)
• Copy_id (foreign key)
• Librarian_id (foreign key)
• Member_id (foreign key)
• Transaction_type
• Timestamp
• Due_date
• Fees

Relationships:
• A Book has multiple Copies.
• A Copy belongs to a Book.
• A Copy is associated with a Library Member through a Transaction.
• A Library Member can have multiple Transactions.
• A Transaction is performed by a Librarian Id

Assumptions:
• Each book can have multiple copies, but each copy is associated with only one book.
• Each transaction involves one copy, one librarian, and one library member.
• A library member can have multiple transactions, but a transaction is associated with only
one member.
• A librarian can perform multiple transactions, but a transaction is associated with only
one librarian

ER-Diagram

Logical Design
Cardinalities
 • Book to Copy: 1-to-N (1:N)
• Copy to Book: N-to-1 (N:1)
• Transaction to Copy: 1-to-1 (1:1)
• Transaction to Librarian: 1-to-1 (1:1)
• Transaction to Library Member: 1-to-1 (1:1)
• Library Member to Transaction: 1-to-N (1:N)
• Librarian to Transaction: 1-to-N (1:N)

Specializations and Generalization

Transaction entity specializations refer to the concept of representing different types or
categories of transactions in a database. It allows for capturing and managing specific attributes
and behaviors associated with each specialized transaction type.
The Transaction entity represents a general transaction that could involve various actions within
a library system, such as borrowing, returning, or renewing a book. However, these transaction
types may have distinct attributes and behaviors that differentiate them from one another.
To handle transaction entity specializations, one approach is Single Table with Discriminator
Column (Single Table Inheritance):

• In this approach, all transaction types are stored in a single table.

• A discriminator column is added to the table to identify the type of each transaction.
• Additional columns are included in the table to capture the specialized attributes of each
transaction type.
• The discriminator column allows for distinguishing between different transaction types.
• This approach simplifies the data model but may lead to a larger table and potential data
redundancy.

Transaction Table:

• Transaction_id (Primary Key)

• Copy_id (Foreign Key referencing Copy.copy_id)
• Librarian_id (Foreign Key referencing Librarian.librarian_id)
• Member_id (Foreign Key referencing LibraryMember.member_id)
• Transaction_type (Discriminator Column)
• Timestamp
• Due_date
Database
schema

Table Name: Book

Field name Data type Field Constraint Validation

 Size

book_id Integer - Autogenerated, Primary Key Not Null

title Varchar 100 - Not Null

ISBN/ISSN Varchar 13 Unique Not Null

edition Integer Not Null

publication_yea Integer Not Null

r

publisher_name Varchar 100 Not Null

Table Name: BookAuthor

Field Name Data Type Field Size Constraint Validation

book_id Integer Not Null

first_name Varchar 50 Not Null

 last_name Varchar 50 Not Null

Table Definition: BookGenre

Field Name Data Type Field Size Constraint Validation

book_id Integer , Foreign Key (Books) Not Null

genre_name Varchar 50 Not Null

Table Name: Copy

Field Name Data Type Field Size Constraint Validation

copy_id Integer Primary Key Not Null

book_id Integer , Foreign Key (Books) Not Null

location Varchar 100 Not Null

condition Varchar 50 Not Null

availability_statu ENUM Not Null

s

Table Name: LibraryMember

Field Data Type Field Size Constraint Validation

Name

member_id Integer Primary Key, Not Null

first_name Varchar 50 Not Null

last_name Varchar 50 Not Null

Table Name: LibraryMemberAddress

Field Name Data Type Field Constraint Validation

Size

member_id Integer , Foreign Key (LibraryMember) Not Null

town Varchar 50 Not Null

Kebele Varchar 50 Not Null

House_numbe Varchar 10 Not Null

r

Table Name: LibraryMemberEmail

Field Name Data Type Field Size Constraint Validation

member_id Integer , Foreign Key (LibraryMember) Not Null

email Varchar 100 Not Null

Table Name: LibraryMemberContact

Field Name Data Type Field Size Constraint Validation

member_id Integer Foreign Key (LibraryMember) Not Null

contact_number (PK) Varchar 13 Primary Key Not Null

Table Name: Transaction

 Field Name Data Type Field Size Constraint Validation

transaction_id Integer Primary Key, Not Null

(PK)

copy_id Integer Foreign Key (Copy) Not Null

member_id Integer Foreign Key (LibraryMember) Not Null

librarian_id Integer Foreign Key (Librarian) Not Null

transaction_type Varchar 50 Not Null

date Date Not Null

duedate Date Not Null

fees Decimal Default: 0.00

Table Name: Librarian

Field Name Data Type Field Constraint Validation

Size

librarian_id Integer Primary Key Not Null

first_name Varchar 50 Not Null

last_name Varchar 50 Not Null

Table Name: LibrarianContact

Field Name Data Type Field Size Constraint Validation

librarian_id Integer Foreign Key (Librarian) Not Null

 contact_numbe Varchar 20 Not Null
r

Table Name: LibrarianEmail

Field Name Data Type Field Constraint Validation

Size

librarian_id Integer Foreign Key (Librarian) Not Null

email Varchar 100 Primary Key Not Null

My SQL Code:
Tables
CREATE DATABASE LIBRARYs;

DROP DATABASE LIBRARYs;

USE LIBRARYs;

CREATE TABLE Book (

book_id INTEGER PRIMARY KEY,

title VARCHAR(100) NOT NULL,

isbn_issn VARCHAR(19) NOT NULL,

edition INTEGER NOT NULL,

publication_year INTEGER,
 publisher_name VARCHAR(100)

);

CREATE TABLE BookAuthor (

book_id INTEGER,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

FOREIGN KEY (book_id) REFERENCES Book (book_id)

);

CREATE TABLE BookGenre (

book_id INTEGER,

genre_name VARCHAR(50) NOT NULL,

FOREIGN KEY (book_id) REFERENCES Book (book_id)

);

CREATE TABLE Copy (

copy_id INTEGER PRIMARY KEY,

book_id INTEGER,

location VARCHAR(100) NOT NULL,

conditionn VARCHAR(50) NOT NULL,

 availability_status VARCHAR(50) NOT NULL,

FOREIGN KEY (book_id) REFERENCES Book (book_id)

);

CREATE TABLE LibraryMember (

member_id INTEGER PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL

);

CREATE TABLE LibraryMemberAddress (

member_id INTEGER,

town VARCHAR(50) NOT NULL,

kebele VARCHAR(50) NOT NULL,

house_number VARCHAR(10),

FOREIGN KEY (member_id) REFERENCES LibraryMember (member_id)

);

CREATE TABLE LibraryMemberEmail (

member_id INTEGER,

email VARCHAR(100) PRIMARY KEY NOT NULL,

 FOREIGN KEY (member_id) REFERENCES LibraryMember (member_id)

);

CREATE TABLE LibraryMemberContact (

member_id INTEGER,

contact_number VARCHAR(13) NOT NULL,

FOREIGN KEY (member_id) REFERENCES LibraryMember (member_id)

);

CREATE TABLE Librarian (

librarian_id INTEGER PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL

);

CREATE TABLE Transactions (

transaction_id INTEGER PRIMARY KEY,

copy_id INTEGER,

member_id INTEGER,

librarian_id INTEGER,

transaction_type VARCHAR(50) NOT NULL,

 fees DECIMAL,

date DATE NOT NULL,

duedate DATE NOT NULL,

FOREIGN KEY (copy_id) REFERENCES Copy (copy_id),

FOREIGN KEY (member_id) REFERENCES LibraryMember (member_id),

FOREIGN KEY (librarian_id) REFERENCES Librarian (librarian_id)

);

CREATE TABLE LibrarianContact (

librarian_id INTEGER,

contact_number VARCHAR(20) NOT NULL,

FOREIGN KEY (librarian_id) REFERENCES Librarian (librarian_id)

);

CREATE TABLE LibrarianEmail (

librarian_id INTEGER,

email VARCHAR(100) PRIMARY KEY NOT NULL,

FOREIGN KEY (librarian_id) REFERENCES Librarian (librarian_id)

);

-- Inserting records into the Book table

INSERT INTO Book (book_id, title, isbn_issn, edition, publication_year, publisher_name)

VALUES

(1, 'Book 1', 'ISBN123', 1, 2022, 'Publisher 1'),

(2, 'Book 2', 'ISBN456', 2, 2021, 'Publisher 2');

-- Inserting records into the BookAuthor table

INSERT INTO BookAuthor (book_id, first_name, last_name)

VALUES

(1, 'Author 1', 'Lastname 1'),

(2, 'Author 2', 'Lastname 2');

-- Inserting records into the BookGenre table

INSERT INTO BookGenre (book_id, genre_name)

VALUES

(1, 'Genre 1'),

(2, 'Genre 2');

-- Inserting records into the Copy table

INSERT INTO Copy (copy_id, book_id, location, conditionn, availability_status)

VALUES
 (1, 1, 'Location 1', 'Good', 'Available'),

(2, 2, 'Location 2', 'Fair', 'On Loan');

-- Inserting records into the LibraryMember table

INSERT INTO LibraryMember (member_id, first_name, last_name)

VALUES

(1, 'Tura', 'Rahel'),

(2, 'Hagos', 'Abel');

-- Inserting records into the LibraryMemberAddress table

INSERT INTO LibraryMemberAddress (member_id, town, kebele, house_number)

VALUES

(1, 'Adama', 'Kebele 1', '123'),

(2, 'Bishoftu', 'Kebele 2', '456');

-- Inserting records into the LibraryMemberEmail table

INSERT INTO LibraryMemberEmail (member_id, email)

VALUES

(1, 'tura@example.com'),

(2, 'hagos@example.com');

-- Inserting records into the LibraryMemberContact table

INSERT INTO LibraryMemberContact (member_id, contact_number)

VALUES

(1, '02'),

(2, '03');

-- Inserting records into the Librarian table

INSERT INTO Librarian (librarian_id, first_name, last_name)

VALUES

(1, 'Librarian 1', 'Lastname 1'),

(2, 'Librarian 2', 'Lastname 2');

INSERT INTO Transactions (transaction_id, copy_id, member_id, librarian_id, transaction_type, fees, date, duedate)

VALUES

(1, 1, 1, 1, 'Borrow', 0.00, '2022-01-01', '2022-01-15'),

(2, 2, 2, 2, 'Return', 0.00, '2022-02-01', '2022-02-15');

INSERT INTO LibrarianContact (librarian_id, contact_number)

VALUES

(1, '1111111111'),

(2, '2222222222');

-- Inserting records into the LibrarianEmail table

INSERT INTO LibrarianEmail (librarian_id, email)

VALUES

(1, 'librarian1@example.com'),

(2, 'librarian2@example.com');

SELECT * FROM Book;

SELECT * FROM BookAuthor;

SELECT * FROM BookGenre;

SELECT * FROM Copy;

SELECT * FROM LibraryMember;

SELECT * FROM LibraryMemberAddress;

SELECT * FROM LibraryMemberEmail;

SELECT * FROM LibraryMemberContact;

SELECT * FROM Librarian;

SELECT * FROM Transactions;

SELECT * FROM LibrarianContact;

SELECT * FROM LibrarianEmail;

Normalization
# process of identifying the logical associations between data items and
designing a database that will represent such associations
Chack if it satisifys the 3 normalizations
 • First Normal Form
• • Disallow multivalued attributes, composite attributes, and their combinations.
• • Domain of attribute must include only atomic and single value attribute
• • The only attribute values permitted by 1NF are single atomic (or indivisible) values
• Second Normal Form
➢ Is based on the concept of full functional dependency.
.➢ A relation R is in 2NF if every nonprime attribute A in R is fully functionally dependent on
the primary key.
Third Normal Form
• Is based on the concept of transitive dependency.
• Relation should not have a non-key attribute functionally determined by another non-key
Attribute.
Plan for backup and recovery
Based on the volume of data being processed and the criticality of the data, it is better that
the database needs to be backed up daily.The backups will stored in a secure offsite location
using cloud storage to prevent data loss due to physical damage or theft.The backup and
recovery process should be tested every month to ensure that data can be restored in the event
of a disaster. This includes testing the backup files, restoring them to a test environment,
verifying the restored data, resuming normal operations. And also includes contact
information for key personnel and vendors to be contacted in case of a disaster. For data
integrity it should be performed every week to ensure that the data stored in the database is
accurate and consistent. Finally it stores backups in multiple locations, including on-site and
off-site storage which ensures that in case of any physical damage or disaster at the primary
location, the data can be recovered from the offsite or on-site storage.
Access level and privileges
Each user's access level and privileges would be determined by their role in the library and
the tasks they need to perform within the system. This helps ensure that sensitive data is only
accessible to authorized users and that each user can perform their job duties efficiently and
effectively. It could include:
1.Administrator: This user would have full access to all features and functions of the system,
including the ability to add, manage, modify, and delete user accounts, manage the database,
configure security settings, and perform system maintenance tasks.
2.Librarian: This user would have access to all library-related functions, such as updating book
information, adding, modifying, and deleting books and other materials, managing loans and
returns, and generating reports.
3.Volunteer: This user would have limited access to the system, such as being able to check in
and check out books, but would not have access to sensitive data or administrative functions.
4.Patron: This user would have access to the online catalog, be able to place holds on materials,
and view their own account information, such as loans and fines.
5.Guest: This user would have limited access to the system, such as being able to search the
catalog, but would not be able to check out materials or view account information.
Security level enforcement
Security level enforcement could require all users to have a unique username and password
to access the system. Additionally, the system could be set up to automatically log users out
after a certain period of inactivity, requiring them to enter their login credentials again to
continue using the system.
Furthermore, the system keeps on up-to-date with the latest security patches and updates for
all software components, including the operating system, database management system, and
any third-party libraries or frameworks used.
The system could also be configured to restrict access to certain functions or data based on
the user's role, with administrators having the highest level of access and guests having the
lowest. Finally, the system could be monitored regularly for any unusual activity or attempts
to access sensitive data, with alerts sent to administrators if any suspicious behavior is
detected.
Future Enhancements
Improved search capabilities, an online reservation system, user recommendations and
reviews, integration with e-book platforms like Kindle or OverDrive, improved reporting and
analytics, a mobile application, integration with online databases, and improved security
measures are just a few ways to improve a library's database system. By enabling users to
look for books by genre, author, publication date, or availability status, these enhancements
can improve search capability and increase catalog accessibility for the library. Additionally,
the system can improve research capabilities and streamline interlibrary loan services by
interacting with online resources, such as scholarly article repositories or other libraries. The
privacy of user information can also be ensured by enhancing security measures, such as
two-factor authentication, encryption for sensitive data, and routine software updates.