Description:

The database is used in tracking and managing the activities for a library system. The system should allow a librarian to manage books in stock in his or her own library branch and other branches in the system. Books can be checked out if there is a copy of the book available in any individual branch in the system.

1. Libraries. This is logically any individual library (or a branch) within the Library System.
2. Books. Specifically this is a title or publication logically identified by a unique combination of ISBN, author, title, publication date, etc. The Book pool (i.e. all available titles) is global for the Library System. However different branches carry only a subset of the book pool.
3. Book Copy. The Book Copy is an instance of a particular Book in a specific library. A Book Copy record with Total Quantity and Quantity on Hand per Book. Any user transaction (check-out/return) will increment/decrement the Quantity on Hand. A library's Quantity on Hand should never exceed Total Quantity for any Book Copy.
4. Library users. They are individuals with privileges to checkout and return books. Users should be associated with a particular library branch in the system. They are able to see a list of all available books in the system. A user cannot checkout new books if he/she has any overdue books. Track overdue books for a particular user.
5. Check-out/return history for any particular users. A branch must track of all Customer transactions (e.g. check-out date, return date, Book) and not just increment/decrement Quantity on Hand.
6. Check-out/return history for any particular book.

Design and implementation:

This project requires you to build up a relational database based on the project requirement given above. Your focus is to design relational database including tables and constraints. You are not required to develop any application interface to use the database. However, it is necessary to consider how a particular data might be used in application or what data application generated need to be stored. The database should include

1. A single tablespace created to store the tables and data for the database.
2. At least one user created who owns the database. The user should have the ability to perform database administration tasks, including create other users.
3. The database should be built on its own schema;
4. A set of tables and other necessary database objects;
5. The proper relationship between the tables established by using proper constraints.
6. The database must allow users to add and modify the data in the tables.
7. There must be basic data populated in these tables.
8. Additional database user who have full access to the schema you have created for the database.

Submission:

1. A script contains SQL code to create tablespace, database users.
2. A script contains all SQL code to create the database of the library system.
3. A script contains SQL queries to collect your database information, including tables and table name, table owner. Constraint names and types and associated table names. Number of the records in each table.
4. Spooled output of the execution results of the above three scripts.
5. I should be able to run your scripts to create the database and populate it with the data you provided in my Oracle database instance.
6. Total score of this part of project makes up 20% of your final grade.