Model-Library-Database

Project Description
Jungle Library is a library for all ages. Jungle Library seeks to implement a centralized
relational database system to streamline and structure its operational processes. The
proposed database design will incorporate the following core entities: Person, Employee,
Member, Book, Publisher, Author, and Payment, ensuring data consistency, integrity, and
efficient management of library resources and transactions.
A Person entity may represent either an Employee or a Member, with the possibility that
Employee can also be Member. The system shall store the following attributes for each
Person: Person ID, Name (First Name, Middle Name, Last Name), Address, Gender, Date
of Birth, and Phone Number(s), where a Person may have multiple associated phone
numbers. An Employee must be at least 18 years of age. The Person ID attribute must
follow the format “PXXX”, where X denotes a digit between 0 and 9.
Each Member is issued a library card, for which details such as Card ID, Issue Date,
Membership Level (Silver or Gold), and other relevant information are maintained. The
library may also associate Promotions with library cards. Each Promotion is defined by a
unique Promotion code and an accompanying description.
Each Member is categorized as either Silver or Gold. For Gold Members, a Guest Log is
maintained, capturing details such as the Gold Member’s Card ID, Guest ID, Guest Name,
Guest Address, and Guest Contact Information. Temporary IDs are assigned to individuals
visiting as guests of a Gold Member. Each Guest ID is unique only within the scope of the
associated Gold Member and is not globally unique across the entire system.
An Employee may belong to one of three categories: Library Supervisor, Cataloging
Manager, or Receptionist. The system records the Employment Start Date for each
Employee. A Receptionist is required to receive training from a designated Trainer, where a
Trainer may be either a Library Supervisor or a Cataloging Manager. Both Library
Supervisors and Cataloging Managers are permitted to train multiple Receptionists.
Details of Books, including Book ID, Book Title, and other relevant information, are
maintained in the system. Books are classified into three categories: Cate. 1, Cate. 2, and
Cate. 3. Each Cataloging Manager is responsible for cataloging one category per day,
though they may work on different categories on different days. Additionally, a Person may
leave Comments on Books, which include the Comment Time, a Rating Score (ranging
from 1 to 5), and the Comment Content.
A Publisher may publish multiple Books, whereas each Book is assumed to be published
by a single Publisher. The system stores Publisher details, including Publisher ID, Publisher
Name, and other relevant information (additional attributes may be defined based on
requirements). Similarly, Author details, such as Author ID, Author Name, and other
pertinent information, are maintained. A Book may have multiple Authors, and an Author
may contribute to multiple Books.
A Receptionist is responsible for maintaining records of Borrowing details. Each record
includes information about the borrowed Book, Issue Date, Due Date for Return, the
Person borrowing the book, the responsible Receptionist, and associated Payment
Details. Borrowing records are created only when a Person borrows a Book. Payment
Details include Payment ID, Payment Method (e.g., Cash, Debit/Credit Card), Payment
Time, and Amount Paid.
In addition, the Receptionist is responsible for managing Member Inquiries. Each inquiry is
assigned a unique Inquiry ID and records details such as the Inquiry Time and Resolution
Status. To monitor member satisfaction and engagement, Members may provide a Rating
(ranging from 1 to 5) for the inquiries they have submitted.