Lab 8

Good News Grocers

Study the tables below. Use them to create a normalized database for the grocery store.

Assumptions
  1. There is always exactly one isle per department
  2. Fields that can be calculated should NOT show up in the final database tables
  3. You will use views to replicate the tables below from the underlying tables.

Step 1 - User View 1 - Price Update List

Department Product Code Aisle Number Price Unit of Measure
1 - Produce 4081 1 0.35 lb
1 - Produce 4027 1 0.90 ea
1 - Produce 4108 1 1.99 lb
1 - Produce 4851 1 0.54 lb
4 - Butcher 331100 5 1.50 lb
4 - Butcher 331105 5 2.40 lb
4 - Butcher 332110 5 5.00 lb
6 - Freezer 411100 6 1.00 ea
6 - Freezer 521101 6 1.00 ea
6 - Freezer 866503 6 5.00 ea
6 - Freezer 866504 6 5.00 ea

This report is used by the department managers to update the prices that are displayed in the grocery store for these products. Find:

  1. UNF
  2. 1NF
  3. 2NF
  4. 3NF

Step 2 - User View 2: Product Cost Report

Supplier Product Cost Markup Price Dept Code
21 – Very Veggie 4108 – tomatoes, plum 1.89 5% 1.99 PR
32 – Fab Fruits 4081 – bananas 0.20 75% 0.35 PR
32 – Fab Fruits 4027 – grapefruit 0.45 100% 0.90 PR
32 – Fab Fruits 4851 – celery 1.00 100% 2.00 PR
08 – Meats R Us 331100 – chicken wings 0.50 300% 1.50 BU
08 – Meats R Us 331105 – lean ground beef 0.60 400% 2.40 BU
08 – Meats R Us 332110 – boneless chicken breasts 2.50 100% 5.00 BU
10 – Jerry’s Juice 411100 – orange juice 0.25 400% 1.00 FR
10 – Jerry’s Juice 521101 – apple juice 0.25 400% 1.00 FR
45 – Icey Creams 866503 – vanilla ice cream 2.50 100% 5.00 FR
45 – Icey Creams 866504 – chocolate ice cream 2.50 100% 5.00 FR

This report is used by the grocery store manager to determine the final selling price of his products. Find:

  1. UNF
  2. 1NF
  3. 2NF
  4. 3NF

Step 3 - Merged Database

  1. Merge the two steps from above into a complete database.

Step 4 - MySQL

  1. Create MySQL Database
  2. Populate with Data above
  3. Show each table with data.
  4. Create views for the two tables above
  5. Show the views
Solution
Topic revision: r3 - 2013-11-27 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback