Database Design Exercise

Good News Grocers

Moodle Link

Study the outputs below from an existing system. We will use them to create a new database design for the grocery store. Essentually we are figuring out an appropriate database design for a desired set of outputs. Use LucidChart to create diagrams the necessary tables.

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.

User View 1 - Price Update List

Department Product Code Aisle Number Price Unit of Measure
Produce 4081 1 0.35 lb
Produce 4027 1 0.90 ea
Produce 4108 1 1.99 lb
Produce 4851 1 0.54 lb
Butcher 331100 4 1.50 lb
Butcher 331105 4 2.40 lb
Butcher 332110 4 5.00 lb
Freezer 411100 6 1.00 ea
Freezer 521101 6 1.00 ea
Freezer 866503 6 5.00 ea
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.

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.

Activity - Design Database

Your goal if try to figure out from the user view information above what the underlying database should look like. You should make a set of tables with redundancy minimized.

  1. Use the data output examples above to design a complete database that will allow us to output this information, and will minimize redunancy.
  2. Show ER diagram. using Lucidchart, and show the tables with keys.
Solution
Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r2 - 2018-02-12 - JimSkon
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback