The Books and Noble Online Bookstore needs a database designed to help them to keep track of their book inventory and sales. Following are the requirements for the system (you may need to look at multiple requirements to get both sides of the relationship):

1. For each book, list the ISBN number, title, the year it was published, and number of copies in inventory; also for each author of the book list the author ID and name. List the publisher name and the category (must be biography, fiction, history, science fiction, romance, comedy, other) for each book.

2. For each author list the author ID, author first name, author last name, main phone number.

3. For each publisher maintain the publisher ID, the name, main phone number and for every book that the publisher has list the ISBN and title.

4. For each customer order there needs to be the order number, order date, customer ID, customer first name, customer last name, customer address (broken out into street address, city, state and zip), and total cost of the order. Also, for each book ordered include the ISBN code, book title, book price, and quantity ordered. A specific book can only be on one detail line of the total order but multiple books may be ordered and there the book price (resale price) is 50% higher than the wholesale cost of the book.

5. For each customer there needs to be a customer ID, customer first name, customer last name, main phone number, street address, city, state, zip, and an indicator whether they are a first time customer.

6. For the inventory of the books there needs to be for each book: the ISBN, title, wholesale cost of the book, and the quantity of books on hand. There is only one wholesale cost for a specific book.

b. You must write SQL commands to create the database structure and enter the test data.

4. Revision to the original design: After the original database was implemented the users decided they had some additional data requirements.

a. For each publisher there needs to be a street address, city, state and zip and the name of the contact person for the publisher.

b. For every order there needs to be a shipped date. Also, there is a 20% discount if this is a returning customer (not first time) so the amount of the discount needs to be included.

c. For each book that an author wrote list the amount that the author was paid

d. Revise the original DBDL, diagram and special restrictions.

5. Implement the design revisions from #4 into your database structure in Oracle

a. Use SQL to create any new tables and to revise existing tables.

6. Write a query that will produce a report listing of all orders that have not shipped, include the order number, total cost of the order, the order date, customer ID, first and last name, phone number, the amount of the discount (if applicable for the customer).

7. requirements :

a. Your cumulative DBDL, data structure diagram, special restrictions based upon the first 6 user requirements.

b. Copy of SQL commands to implement the original design listed in 7a