Homework: Microsoft SQL
Create a database for a small e-commerce web site. As a minimum, it should include the following entities:
1) Customer - name, address, city, state, zip, etc.
2) Inventory - description, category, quantity on hand, unit price, etc.
3) Order - date, payment info, etc.
4) OrderDetail - typical shopping cart line items
5) Supplier - vendor info to include MainPhone, SalesPhone, ShippingPhone, and BillingPhone
6) StateFees - list of 50 states and the sales tax and shipping costs
Notes:
1) Inventory and Supplier start out as a N:M, so you'll need to fix that
2) The fee info is: 1) Charge 6.25% tax in Texas 2) Charge 8.25% tax in California, 3) All other states have 0% tax.... and 4) Charge $4 to ship to TX, LA, OK, NM, 5) Charge $8 to ship to AK and HI, and 6) Charge $6 shipping to all other states.
3) Real databases do not always allow you delete records, so please account for this in your table design
4) Use the database diagram to ensure that there are no "orphan" tables
As a minimum, You must create Views, Table Functions, or Scalar Functions for the following:
1) query to produce the extended price (a single value) for a given Order and a given Line Item
2) query to show all order activity details for a given Customer
3) query to show the total cost (cost of items + tax + shipping) for a given order
4) query to show the total sales (in $) for each month
5) query to show the total sales (in $) for each category of product
6) create a view to demonstrate the use of each of the functions above
7) You need to have enough sample data in your database to be able to demonstrate that each query works
Format your homework according to the following formatting requirements:
(1) The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
(2) The response also includes a cover page containing the title of the homework, the student's name, the course title, and the date. The cover page is not included in the required page length.
(3) Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.