You are in charge of designing and creating a database for a library.  Follow the guidelines for completing this task below.

Database Preparation and Design:

For this project, you will need to be concerned with the following types of data:

·         Books

·         Magazines

·         Publishers

·         Patrons

·         Fines

You can utilize the following assumptions in your design:

·         Books can have one or many authors.

·         Books need to be classified by category.

·         Magazines can have different types of issuing policies, including weekly, monthly, etc.

·         Different fine rates will apply to a book versus a magazine.  These should be stored in a separate table.

·         You must include the date of any transaction that occurs.

·         In order to check out a book, a patron must be registered in the system.

·         A patron can take out as many items as they wish.

The first step of the project is to construct an E-R diagram to show the database model.  In this step, it is important to show all entities, relationships, maximum cardinality, minimum cardinality, keys, and attributes in their proper form.  Remember that each entity (or table) must be normalized.  Otherwise the subsequent components of this project may not work as expected.

Based on the E-R diagram, create a data dictionary in Microsoft Excel that lists the table name, column name, column data type, as well as indicators for primary key, foreign key, and null status.  If the column name is not descriptive, include a Description column as well.

Database Creation:

Once you are complete with the diagram, you will write the SQL needed to create the database tables and relationships.  You will also write the SQL statements necessary for inserting at least five unique rows of data into each table within a script file.

Once you are complete with the above step, the next step will be to work with the data contained in your database.  You need to write at least one SQL statement for each of the following items:

·         SELECT statement involving a minimum of three columns

·         SELECT statement using a built-in function

·         SELECT statement utilizing LIKE.

·         Create a view

·         Query that executes at least one table join

·         One UPDATE statement

·         One DELETE statement

·         One PL/SQL procedure that contains at least one variable