Course Project

You have been tasked with turning a denormalized set of data into a functioning MySQL database. You'll be working with 400,000 rows of order data for an ecommerce website called...Fetsy.

Requirements

Tables

1. Break the denormalized table into Third Normal Form.
2. Only use the new normalized tables going forward.
   • Using the denormalized table for anything other importing data into the new tables will yield an automatic zero grade on the project.
3. You must use appropriate
   • datatypes
   • constraints
   • foreign keys
   • etc.
4. Define the cardinality between entities!
   • In order to correctly normalize the database, you need to know if the relationships between your entities are 1:1, 1:many or many:many.
   • This means you need to run some queries on the denormalized table to figure out the cardinality before creating the new tables.
   • e.g. Do you always see the same buyer with the same address? Or can the same buyer have multiple addresses?
5. Don't change any column names from the denormalized table.

Indexes

Add indexes where appropriate. Justify by running EXPLAIN on the queries below before and after adding your indexes. If there's no index that will help with a query, write a comment explaining why (hint: there's a pattern).

Queries

• Create a stored procedure called top_ten_for_country which accepts

  • country

  and returns

  • buyer_id
  • first_name
  • last_name
  • total_amount_spent

  for the top ten buyers (in terms of total amount of money spent) for that country.

• Create a view called top_rated_products which displays

  • product_id
  • product_name
  • product_price
  • avg_rating
  • rating_count

  for the top ten products according to their average rating (minimum of 20 ratings).

• Create a stored procedure called buyer_for_date which accepts

  • first_name
  • last_name
  • order_date

  and returns

  • order_id
  • order_quantity
  • product_name
  • order_date

  for orders made by buyers with that name on that date.

• Create a view called top_five_buyer_cities which displays

  • city
  • total_amount_spent

  for the top five cities in terms buyer spending, descending order. Display the sum of spending (per city).

• Create a stored procedure called sales_for_month which accepts

  • a specific month (i.e. month and year)

  and returns

  • month_and_year (in one column)
  • total_sales sum of all sales in that month

• Create a view called seller_sales_tiers which displays

  • seller_id
  • seller_name
  • total_sales sum of all sales
  • sales_tier based on the total sales for a seller. Apply the corresponding label:
    • $100,000.00 <= High
    • $10,000.00 <= Medium < $100,000.00
    • $10,000.00 > Low

• Create a stored procedure called top_products_for_seller which accepts

  • a seller's name

  and returns

  • seller_id
  • product_id
  • product_name
  • total_sales

  for all the products sold by that seller, sorted by most total sales (in terms of money) in descending order.

• Create a stored procedure called seller_running_totals which accepts

  • seller_name

  and returns

  • seller_id
  • order_id
  • order_date
  • order_total
  • running_total a running sales total for that seller over all time (hint: windowing functions) (another hint: The running total should increase with each sale made).

Important Notes:

• All view/procedure names must EXACTLY match what's specified above
• All column names must EXACTLY match what's specified above
• When displaying monetary amounts, format the INTs as proper currency (e.g., display $100.00, not 10000)

Getting Started

Watch the video

How to Submit

1. You'll be graded only on whatever your final commit is. You can create as many as you want. None have to work except the final one.
2. Everytime you push a commit, my tests will run against your project.sql file. To do this, they build a copy of the environment. If the build fails, you have an issue that will preclude my tests from running, so you must fix the issue to get anything above a 0. If the build is successful, then you'll receive a grade.
3. Add a PDF to your repo with screenshots of you running all your queries. It must be a PDF. There must be only 1 PDF in the repo.
   • Write a 1-2 sentences explaining why you did what you did for each table. I don't care if you state the obvious, just tell me in your own words. Be concise.
4. Any deviations from above will cause your grade to drop, as I've automated this entire process.