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.