Database Design for Library System

The assignment is about designing a database for a university library system. The database needs to store information about students, books, employees, and suppliers. It also includes entities for sections, catalogue, book issued, book reserved, and payment. The ER diagram shows the relationships between these entities.

Database Design 1
5915 Database Design COLT2, 2017
Fahad AlSuwaidi
U3161044
Professor Name
Date

Database Design 2
Introduction
As per the given scenario, the database for a library system needs to be designed. The main
purpose of this library database will be to save the entire information of student, books,
employees, student’s next of kin details etc. A student number is issued to each student when he
or she gets registered in the university. There are six sections defined for a library and that
information needs to be saved in the database. There is a database for the books catalogue where
all the details about book such as book title, author name, publisher name etc are saved in the
database. When a student issue a book, a return date get saved automatically on the basis of
policy of library. And when student returns the book to the library after the return date some
penalty amount get saved for each book. This penalty amount is same for each book. That
basically depends on the number of days by which after which book is returned to the library.
The supplier’s details is also saved in the database from which books are purchased. There is the
facility to reserve the book on first come first serve basis. Students can reserve the book online
before issuing that.
Entities
1. Student  StudentNo, FirstName, LastName, DateOfBirth, Gender, HomeStreet,
Homesuburb, Homecity, Homestate, HomePCode, contact_no.
2. NextofKin  NoKID, FirstName, LastName, DateOfBirth, Gender, HomeStreet,
Homesuburb, Homecity, Homestate, HomePCode, StudentNo, Relationship.
Foreign key  StudentNo references student.
3. Section  SectionID, Section_name, location, phone_no, no_of_staff

Database Design 3
4. Staff  StaffNo, Fname, Lname, position, DateOfBirth, Gender, contact_no, office_no,
SectionID
Foreign key  SectionID references section.
5. Supplier  SupplierID, Supplier_name, Supplier_address, supplier_city, supplier_phone.
6. Catalogue  CatalogueNo, book_title, author_name, publisher_name, publisher_Address,
book_type, SupplierID.
Foreign key  SupplierID references supplier.
7. Book_issued  StudentNo, CatalogueNo, Return_date, late_charge, StaffNo
Foreign key  StudentNo references student, CatalogueNo references catalogue, StaffNo
references Staff
8. Book_reserved  ReservedId, CatalogueNo, StudentNo, book_title.
Foreign key  StudentNo references student, CatalogueNo references catalogue
9. Payment  ReceiptNo, StudentNo, Amount, date
Foreign key  StudentNo references student.
Entity Relationship Diagram

Database Design 4

Database Design 5
Table Creation
Relationship

Database Design 6
Data Entry
Student
NextOfKin

Database Design 7
Reports
Penalty
Books Issued in Last 3 Months

Database Design 8