I.C.U Blinds Database Design ERD

I.C.U Blinds Database Design

A customer goes to the store to buy blinds.  This customer is served by one salesperson in a department.  This salesperson can have many customers.  An installer is scheduled to measure the customer’s house for blinds.  The installer can have many customers but each customer is served by only one installer.  When a customer buys a blind, an invoice is created for the transaction.  Installers and sales people report to a manager.  A manager may manage many employees.

Based upon a visit to the store and interviews with several store employees, consider the following:
Customer data must be stored including name, address, email address, and phone number.  Sales person and installer data must be stored including name, address, phone number, hire date, and manager ID.  Manager information is similar but the database must store information about the employees they manage.  Each employee may also have dependents eligible for the company-provided health coverage.  Information to be stored about dependents includes name and date of birth.  Each blind has a SKU, price, and description associated with it.  Each invoice may have multiple blinds on it.  Invoices include date purchased, item quantity, and calculated fields such as item subtotals (qty * price), and grand total.

You have been asked to design a database for “I.C.U Blinds” based upon your expertise in databases.

CHOOSE ONE OF THE FOLLOWING (AND ONLY ONE)

Option #1 (Rock)
Draw the dependency diagrams which step through the normalization process (1NF, 2NF, and 3NF).  Denormalize as needed and show your final dependency diagram.  If you do denormalize, explain the reasoning behind each case.  Please follow the formatting guidelines as per the normalization homework assignment we did.  What you should turn in, is similar to the Chapter 5 assignment.

Option #2 (Hard Place)
Break down the scenario into the component relationships.  For each relationship, write the associated one liner, and the 4 sentences.  Combine these relationships and in Visio, draw the ERD associated with your solution.  Be sure to note all attributes, PKs, FKs, cardinality, optional and mandatory relationships, strong/weak relationships and entities.  What you should turn in, is very similar to the Chapter 4 assignments.

Tips and tricks
Carefully consider each question and do not be afraid to add attributes – some will be needed (e.g. primary and foreign keys), some need to be inferred – (e.g. purchase date for the invoice), and some will be calculated (e.g. totals).   As we have learned in class, remember in your answers that good solutions take into account the trade-offs between number of tables, the amount of data redundancy, and data to be entered for each transaction.