Library Database Design: Student, Book and Staff Details

This report details the design of a database for a library system, encompassing the storage of student, book, staff, and supplier information. The database includes entities such as students, next of kin, sections, staff, suppliers, and a book catalog. Relationships are defined between these entities, with foreign keys linking related tables. The report outlines the structure of each table, including attributes like student numbers, book titles, and staff details, as well as payment and reservation information. The structure of the database includes the ability to track book issuance, returns, and associated late charges. The report also includes the creation of tables, entity relationship diagrams, and data entry to ensure efficient management of library resources. The database also handles the reservation of books, ensuring the efficient management of library resources.

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