Assignment: Create a database for a small e-commerce web site. As a minimum, it should include...

Transcribed Image Text:

Assignment: Create a database for a small e-commerce web site. As a minimum, it should include the following entities as a starting point: Customer - name, address, city, state, zip, etc. Inventory - description, category, quantity on hand, unit price, etc. Order - date, payment info, etc. Order Detail - typical shopping cart line items Supplier - vendor info (name, address, etc.) plus MainPhone, SalesPhone, ShippingPhone, and BillingPhone StateFees - list of 50 states and the sales tax and shipping costs Notes: Inventory and Supplier start out as a N:M, so you'll need to fix that There is a multi-column issue that needs to be fixed 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. Real databases do not always allow you delete records, so please account for this in your table design Use the database diagram to ensure that there are no "orphan" tables Use roles and permissions to avoid unauthorized access or modification of data As a minimum, you must create Views, Table Functions, or Scalar Functions for the following: query to calculate the extended price (a single value) for a given Order and a given Line Item query to show all order activity details for a given Customer query to calculate the line-item subtotal (sum of the extended prices as a single value) for a given Order query to show the customer's name and the total cost (cost of items + tax + shipping) for a given order query to show the total sales (in $) for each month query to show the total sales (in $) for each category of product create a to dem strate the use of the above You need to have enough sample data in your database to be able to demonstrate that each query works Here are some hints about the Term Project The tables and columns shown are just a starting point... you must add/delete/rename as you see fit What are the rules/recommendations for picking/adding a column for a PK? Inventory and Supplier start out as a N:M. So, how do you fix that? Does that mean that you'll need another table? Is there a multi-column issue in Supplier? So, how many tables will there be after solving N:M and multi-column? I would use one table for state fees that contain both sales tax and shipping (not two separate tables) The phrase "given a" implies that you need to pass a parameter. So, what does that mean for a choice between View and Function? Can I pass more than one parameter? For example an order number and a line number What does it mean if a query returns a single value (and not rows of data)? How can you calculate the sum of the costs of all line items in a given order The phrase "for each" implies that you want to group the answers. So, what does that mean for a choice of keywords in the SQL statement? You'll need at least 2 months of sales data and 2 categories of products in order to demonstrate some of the queries A single order should be capable of having more than just 1 item You should consider using the Month() function to extract the month from a date How any scalar functions will there be?... How many table functions?... How many Views? What do you have to do to be able to "retire" a row instead of deleting a row? Please use the database diagram to make sure that you don't have any orphan tables Assignment: Create a database for a small e-commerce web site. As a minimum, it should include the following entities as a starting point: Customer - name, address, city, state, zip, etc. Inventory - description, category, quantity on hand, unit price, etc. Order - date, payment info, etc. Order Detail - typical shopping cart line items Supplier - vendor info (name, address, etc.) plus MainPhone, SalesPhone, ShippingPhone, and BillingPhone StateFees - list of 50 states and the sales tax and shipping costs Notes: Inventory and Supplier start out as a N:M, so you'll need to fix that There is a multi-column issue that needs to be fixed 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. Real databases do not always allow you delete records, so please account for this in your table design Use the database diagram to ensure that there are no "orphan" tables Use roles and permissions to avoid unauthorized access or modification of data As a minimum, you must create Views, Table Functions, or Scalar Functions for the following: query to calculate the extended price (a single value) for a given Order and a given Line Item query to show all order activity details for a given Customer query to calculate the line-item subtotal (sum of the extended prices as a single value) for a given Order query to show the customer's name and the total cost (cost of items + tax + shipping) for a given order query to show the total sales (in $) for each month query to show the total sales (in $) for each category of product create a to dem strate the use of the above You need to have enough sample data in your database to be able to demonstrate that each query works Here are some hints about the Term Project The tables and columns shown are just a starting point... you must add/delete/rename as you see fit What are the rules/recommendations for picking/adding a column for a PK? Inventory and Supplier start out as a N:M. So, how do you fix that? Does that mean that you'll need another table? Is there a multi-column issue in Supplier? So, how many tables will there be after solving N:M and multi-column? I would use one table for state fees that contain both sales tax and shipping (not two separate tables) The phrase "given a" implies that you need to pass a parameter. So, what does that mean for a choice between View and Function? Can I pass more than one parameter? For example an order number and a line number What does it mean if a query returns a single value (and not rows of data)? How can you calculate the sum of the costs of all line items in a given order The phrase "for each" implies that you want to group the answers. So, what does that mean for a choice of keywords in the SQL statement? You'll need at least 2 months of sales data and 2 categories of products in order to demonstrate some of the queries A single order should be capable of having more than just 1 item You should consider using the Month() function to extract the month from a date How any scalar functions will there be?... How many table functions?... How many Views? What do you have to do to be able to "retire" a row instead of deleting a row? Please use the database diagram to make sure that you don't have any orphan tables