SQL Database w/queries and ERD diagram

Table PRODUCT

Table CUSTOMER

Table ORDERS

Table SUPPLIER

Data Normalization into 3NF

CUSTOMER table: The CustomerCity, CustomerState and CustomerZip are dependent on the address thus a table is based on the address.

Table C_ADDRESS

PRODUCT table: In the table PRODUCT the PublishDate is dependent on the Publisher so we need a table based on the publisher

Table: PUBLISHER

ORDERS table: In the table ORDERS CustomerDetails_CustomerNo and ItemsOrdered are dependent on CustomerNo so we need a table based on CUSTOMERORDERDETAILS

Table: CUSTOMERORDERDETAILS

Table: PRODUCT

ProductId Publisher, Author, Title, Description, PublishDate, Category

Table: CUSTOMER

CustomerNo CustomerAddress, CustomerCity, CustomerName, CustomerState, CustomerZip, CustomerEmail

Table: ORDER

OrderNo, CustomerNo OrderDate, ItemsOrdered, CustomerDetails_CustomerNo, Orders_CustomerDetails

Table: SUPPLIER

SupplierNo Name, SupplierCity, SupplierStreet, SupplierState, SupplierZip, Title, NumberSupplied

Table: C_ADDRESS

CustomerAddress CustomerCity, CustomerZip

Table: PUBLISHER

PublisherID PublishName, Title, PublishDate,DateSold

Table: CUSTOMERORDERDETAILS

CustomerNo ItemsOrdered, CustomerDetails_CustomerNo

· How many books are sold each month by the publisher?

This is important because quantity discount are available from the publisher

SELECT Title(count) As BooksSold from PUBLISHER

where DateSold = between "#1/1/2016" AND "#31/1/2016";

· Which authors are the biggest sellers of books in our stores?

This is important because the publisher offers discounts for certain authors each month.

SELECT Author(COUNT) AS BiggestSellers , Title From PRODUCT

Where DateSold.PUBLISHER = between "#1/1/2016" AND "#31/1/2016" and

BiggestSellers=MAX;

· What books are associated with each publisher?

Select Title, Author From PRODUCT

WHERE Publisher.PRODUCT = PublisherName.PUBLISHER;

· What are the most popular products besides books that are sold in each store?

In addition to books, the stores sell magazines, café-specific products like coffee and pastries, and various gift products.

SELECT Category(Count) AS Popular FROM PRODUCT

WHERE Category NOT = “books” AND Popular = MAX;

· From what region(s) (by ZIP code) do customers visit our stores?

This is important because it will assist with future marketing efforts.

SELECT CustomerNo, CustomerName, CustomerCity From CUSTOMER

WHERE CustomerZip=1;

· What customer data must be stored for the e-commerce portion of the website?

SELECT CustomerAddress, CustomerCity, CustomerName, CustomerZip, CustomerEmail, OrderDate.ORDERS AS DateOfOrder, OrderedItems.Orders As ItemsOrdered FROM CUSTOMER;

Milestone 2

Bret – Thank you for submitting your homework. Your homework needed to include the following points to receive full credit.

1. ERD – Your ERD needs to model the business narrative that was included in lab one and outlined in the Final Document Project. You should have the correct crow's feet notation to correctly model all the relationships (one to many, one to one and many to many).  I want each table to include a PK. I want the FKs to correctly reference the parent PK. Finally I want all attributes (columns) decomposed to the atomic level. Your ERD should include the following tables: stores, employees, books. Products, authors, publisher, sales and customers.

2. I want all your tables to be in 3rd normal form.

3. I want you to provide SELECT statements to answer the six questions outlined in Final Project Document. The following provides some hints (clues) for each question:

1. Will require an inner join between publisher and books it will also require a group by and aggregate function.

2. This could require you to join three tables (author, book and sales) it will also require an order by statement.

3. This will require an inner join between books and publisher.

4. This will require an inner join between products and sales it will also require an order by statement.

5. This will require you to join the customer and sales tables it will also require the distinct clause.

6. Based on how you have designed your tables this may be as simple as a select against the customer table.

If you have any questions on this feedback or the material please let me know. 

CUSTOMER

PKCustomerNo

CustomerName

CustomerAddress

CustomerCity

CustomerZip

CustomerState

CustomerEmail

PRODUCT

PKProduct_ID

Publisher

Author

Title

Description

PublishDate

Category

C_ADDRESS

PKCustomerAddress

CustomerCity

CustomerState

CustomerZip

ORDERS

PKOrderNo

CustomerNo

OrderDate

ItemsOrdered

CustomerDetails_CustomerNo

Orders_CustomerDetais

PUBLISHER

PKPublisherID

PublisherName

Title

PublishDate

SUPPLIER

PKSupplierNo

Name

SupplierAddress

SupplierCity

SupplierState

SupplierZip

Title

NumberSupplied

CUSTOMERORDERDETAILS

PKCustomerNo

ItemsOrdered

CustomerDetails_CustomerNo

�

Table