Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Oracle RMAN For Absolute Beginners

Authors: Darl Kuhn

1st Edition

1484207637, 9781484207635

More Books

Students also viewed these Databases questions

Question

(LO 3-4) What is the difference between a target and a class?

Answered: 1 week ago