Unit 04 Database Design & Development: Library Management System

This report presents a comprehensive design for a relational database system intended for managing a library. It begins by outlining the system requirements, including a real-world scenario at FPT University, data storage needs, and entity relationships. The core of the report details the design process, starting with an ER diagram that models the library's entities and their relationships. This ER diagram is then converted into a relational diagram, which forms the basis for the relational database system. The report includes SQL code for creating the necessary tables (LIBRARYBRANCH, LIBRARIAN, CATEGORY, BOOK, STUDENT, and BILL). Furthermore, the report presents system interface designs for key functionalities like login, student registration, book registration, librarian registration, issuing books, returning books, and searching for books. The report concludes by acknowledging the limitations of the implemented system, such as data storage capacity, and suggests areas for future improvement. Desklib offers a range of resources, including past papers and solved assignments, to support students in their learning journey.

ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Submission date Date Received 1st submission
Re-submission Date Date Received 2nd submission
Student Name Student ID
Class Assessor name
Student declaration
I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that
making a false declaration is a form of malpractice.
Student’s signature
Grading grid
P1 M1 D1
1

2

❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:
3

4

Contents
I. INTRODUCE....................................................................................................................................................................................... 5
II. SYSRTEM REQUIREMENTS................................................................................................................................................................. 6
1. Real world scenario........................................................................................................................................................................ 6
2. Data requirements for storage...................................................................................................................................................... 6
3. Relationship between entities....................................................................................................................................................... 7
III. DESIGN RELATIONAL DATABASE SYSTEM FOR A SUBSTANTIAL PROBLEM (P1)...........................................................................10
1. ER Diagram................................................................................................................................................................................... 10
2. Convert ER Diagram to Relation Diagram.................................................................................................................................... 12
3. Relational database system......................................................................................................................................................... 12
IV. SYSTEM INTEERFACE DESIGN....................................................................................................................................................... 19
V. CONCLUSION................................................................................................................................................................................... 25
VI. REFERENCE LIST........................................................................................................................................................................... 26
I. INTRODUCE
Databases and database systems are an essential component of life in modern society: most of us encounter several activities every
day that involve some interaction with a database. The objective of this report is to design a relational database system using
appropriate design tools and techniques, with clear statements of user and system requirements. In this assignment, I will design a
relational database system for the library management system, with clear statements of user and system requirements.
5

II. SYSRTEM REQUIREMENTS
1. Real world scenario
FPT University is expanding due to the growth in the number of students. However, FPT is currently facing difficulties in dealing with
managing the university. And I will design a relational system for the library management system of BTEC FPT International College.
Library management system is to reduce and solve the cumbersome manual management, so that the library can rise to a perfect
electronic management. This is a useful information and information system to meet the research and learning needs of students and
lecturers in the school.
The library of BTEC FPT International College currently has many books updated daily to serve the needs of students in borrowing
books. System objectives are: Librarian, it provides all the details of the borrower, as well as the details of the library inventory; for
students or ordinary users, it has two functions includes borrowing and returning books; the administrator can also record new books,
delete old books and so on. Visitors can also enter the system without a registering user, but there are certain functional limitations.
Library activities can be described as follows:
- Once a book is imported, it will be saved by the librarian to the system including information: book ID, title, publisher, author,
quantity, price, inventory date, type of book and book placement.
- A student who wants to borrow a book must have a student card that includes the student ID, student name, and major. Each
student has only one student ID card. Students need to borrow books to know the loan date and return date. Besides, students
need to provide additional email, phone number to contact in case of need.
- Library books are divided into categories such as: economics, foreign languages, information technology, etc.
2. Data requirements for storage
Entity Description of Entity Attribute Description of Attribute
Book All of books in library BookID It is a unique identifier for
each book in the library
BookName Name of Book
6

BookDescription Description of Book
Publisher Publisher of Book
Author Author of Book
Student All of students in the school StudentID It is a unique identifier for
every student in the school
StudentAddress Address of Student
StudentName Name of Student
StudentEmail Email of Student
Class Class of Student
Librarian All of the librarians in library LibrarianID It is a unique identifier for
each librarian in the library
LibrarianName Name of Librarian
LibrarianEmail Email of Librarian
LibrarianAddress Address of Librarian
Library All branches in the school's
library management system
LibraryID It is a unique identifier for
each librarian branch of the
school
LibraryName Name of librarian branch
Bill BillID It is a unique identifier for
each bill in the library
All invoices in the library BillName Name of Bill
Day Date on the invoice
Category All categories in library CategoryID It is a unique identifier for
each category of the book
CategoryName Name of Category
3. Relationship between entities
7

Each category may include multiple books. Each book only belongs to one category.
Each book can include multiple invoices. Each invoice belongs to only one book.
Each student may have multiple invoices. Each invoice belongs to only one student.
8

Each library branch may have multiple books. Each book belongs to only one library.
Each library branch may have more than one library manager. Each librarian only works in one library.
A single librarian can manage multiple invoices, but an invoice is managed by only one.
9

III. DESIGN RELATIONAL DATABASE SYSTEM FOR A
SUBSTANTIAL PROBLEM (P1)
1. ER Diagram
10

11

ER Diagram
2. Convert ER Diagram to Relation Diagram
CATEGORY (CategoryID, CategoryName)
BOOK (BookID, BookName, BookDescription, Author, Publisher, CategoryID, LibraryID)
STUDENT (StudentID, StudentName, Class, StudentEmail)
BILL (BillID, BillName, BookID, LibrarianID, StudentID)
LIBRARY (LibraryID, LibraryName, LibrarianAddress)
LIBRARIAN (LibrarianID, LibrarianName, LibrarianID)
3. Relational database system
 Create table LIBRARYBRANCH:
12

 Create table LIBRARIAN:
13

 Create table CATEGORY:
 Create table BOOK:
14

 Create table STUDENT:
15

 Create table BILL:
16

 Relational database system:
17

18

IV. SYSTEM INTEERFACE DESIGN
 Login Form
 Student Register
19

 Book Register
20

 Librarian Register
21

 Issue a book
22

 Return Book
23

 Search Book
24

V. CONCLUSION
With the development of network technology, the existing library management system is more and more perfect, and the system
function becomes more and more comprehensive. In this assignment, I designed a relational database system for the library
management system, with clear statements of user and system requirements. From the angle of system development, the system
has completed some functions such as add, delete, correct, inquiry of the library management system, as well as the user
independent query borrowing function. But there are also some shortcomings. Coupled with limited personal time, the system
appears some errors and hidden bugs, such as data storage capacity is limited. When the number of student users and books
increase, the system cannot be used, and the changes that the system made to the data can’t be saved for a long time.
25