Develop ERD and the relational schema Define entities and relationships. Provide the business rules of the ERD. Include the final ERD. Convert the ERD to a relational schema. Normalize all tables to third normal form. Include the relational schema. (This is not DDL.)

Project Work Plan

The project will focus on developing a Library database to keep track of the book Inventory and to keep various library records of patrons by staff.

Purpose and Objective:

This database supports the Library Transaction application and keeps track of items, their prices, manufacturer, name, and the available number of copies of every item at each library branch. The purpose of the database is to support the patrons with borrowing books at the library branch location or online. Library database will allow patrons (users) to find Books which can also be articles in Journals/Magazines/Newspapers & other documents.

The Library information includes the patron, book, staff, transaction, and author. The library information includes the library name, street address, zip code, and staffs name. For each author, the database keeps track of first name, last name, and published date. Also, patron information includes first name, last name, phone number, date of birth, street address, and zip code. The transaction tracks the books borrowed, time due for return and the number of available books in the library.

For this database design application, each library item must have an author. If there is more than one author, the database stores only the first name of the author. Each author could have produced zero or more book (reading) items. A library item can be available at zero or many library branch, and each branch could lend zero or more book.

The Diagram Tool for this project is SQL Developer Data Modeler and SQL Developer. The Database will include Oracle 11g. To design the library database, SQL (Structured Query Language) will be used for the DDL and DML. For the DDL scripts, the Group will use SQL to translate the information captured in SQL developer data modeler into database table creation scripts.