Library Loan Tracking System

In this course, you will design, develop and implement a relational database for the case study assigned to you

The required database should be developed according to the following stages:

1. Develop a conceptual data model reflecting the following requirements:

• Identification of the relations (entity types).
• Identification of relationship types and their participation and cardinality constraints.
• Identification of attributes and association of attributes with entity or relationship types.
• Determination of candidate and primary key attributes of entity types.
• Determination of specialization/generalization and categorization relationships, whenever it is appropriate.
• Enhanced Entity-Relationship (EER) diagram to reflect the requirements.

2. Develop a global logical data model based on the following requirements:

• Refinement of the conceptual model - including a refined Enhanced Entity-Relationship (EER) diagram.
• Derivation of relations from the refined conceptual model.
• Validation of logical model using normalization to BCNF.
• Validation of logical model against corresponding user transactions.
• Definition of integrity constraints including:
  • Primary key constraints.
  • Referential Integrity (foreign key) constraints.
  • Entity integrity (NULL and default value) constraints.
  • Alternate key constraints.
  • General constraints if any.

3. Translate the global logical data model for the ORACLE/MYSQL/MSSQL Enterprise DBMS Platforms.

• Development of SQL code to create the entire database schema and reflect its constraints.
• Development of the case study application to interact with the database and enable the transaction requirements (Use any programming language Java, C++, Python, etc..)

4. Interact with the database and enable the transaction requirements. You need to create several sample tuples for each relation in your database.

Report: The report should include a detailed typed documentation of the project's stages, results at each stage, test data, sample output, and conclusion. For example, you should include the EER diagrams for the conceptual data model and the logical data model.

Case Study: Library Loan Record Tracking System

Develop a library loan record tracking system to track loan records in a library. Library patrons may borrow books, magazines, movies, compact discs, and audio tapes. Each copy of a library item must be managed. For example, a library may have fi e copies of the book "The Grapes of Wrath". Your system must track borrowers, their addresses, and phone number. Each borrower is assigned a library card.

Each category of library item has a standard checkout period and number of renewals. For example, children's books may be checked out for a month, while adult books may be checked out for only two weeks. Ordinary books can be renewed once; books with a pending request may not be renewed. Your system must record the actual return date and any fines that was paid. Your system must maintain any records of damage and loss and associated payments. Your system must also enable searches, such as

• Total fines owed by a patron.
• Fine revenue to the library for an interval of time.
• The copies of library items that are grossly overdue. "Grossly" is the number of days specified by the librarian.

Sample Queries

1. List the number of copies of a particular library item.

2. List the details of the patrons who have at least an overdue library item today.

3. Identify the total fines owed by a patron (by his/her library card number) currently in the system.

4. List the details (e.g., damage, loss, amount, etc.) of the payment made by a patron.

5. List the copies of library items that are grossly overdue.

6. List the details of the current pending requests in the system.

7. Identify the total fines revenue to the library between April 1, 2014 to October 1st, 2014.

8. List the details of the checkout periods and the numbers of renewals for all the categories of library items.

9. List the total number and the details of library items that are checked out and renewed by a patron.

10. Insert a new pending request made by a patron.