IT 330 Database Design and Management

Lab 3: Normalization
Objective:
•    Normalize table to third normal form (3NF)

Submission requirements:
•    For all text and image submission(s), use MS Word, which is available to you within the Virtual Desktop Infrastructure (VDI).
•    For all SQL code submission(s), use MS Word, which is available to you within VDI.
•    For all diagram(s) submissions, use MS Visio, which is available to you within VDI.
o    Note: If you need assistance on how to get started with this tool, go to the references section at the end of this document.
•    If the submission is more than one file:
1.    Name each item appropriately
a.    For example: LAB2-ERD-yourName.vsd, LAB2-Questions-yourName.docx
2.    Save each item in a single folder
3.    This folder should also be named appropriately
a.    For example: LAB2-yourName
4.    Compress the folder
5.    Submit the compressed file in Blackboard

LAB:
Normalize ORDERS at 3NF. Make sure to identify all the entities and their attributes, especially if you make any new items (such as entity or attributes).

List of attributes:

ORDERS
OrderNo
CustomerNo
CustomerName
CustomerAddress
CustomerCity
CustomerState
CustomerZip
OrderDate
ItemsOrdered
Total

The following table is a sample, before ORDERS was normalized:
ORDER NO    ORDER DATE    CUSTOMER NO    CUSTOMER NAME    CUSTOMER ADDRESS    ITEMS ORDERED

100    1/1/99    54545    ACME Co    1234 1st St.    5-1A4536, Flange, 7lbs, $75;4-OR2400, Injector, .5lbs, $108;4-OR2403, Injector, .5lbs, $116;1-4I5436, Head, 63lbs, $750
101    1/1/99    12000    Sneed Corp.    555 Main Ave.    1-3X9567, Pump, 5lbs, $62.50
102    1/1/99    66651    ZZZ & Co.    4242 SW 2nd    7-8G9200; Fan, 3lbs, $84;1-8G5437, Fan, 3lbs, $15;1-3H6250, Control, 5lbs, $32
103    1/2/99    54545    ACME Co    1234 1st St.    40-8G9200, Fan, 3lbs, $480;1-2P5523, Housing, 1lb, $165;1-3X9567, Pump, 5lbs, $42

References:
The following is referenced from Microsoft:
Create a Database Model (also known as Entity Relationship diagram)
How to create a Crow’s Foot ER Diagram
1.    Open Microsoft Visio
2.    Click the File menu, select New, then Database, and then Database Model Diagram
3.    On the menu bar, click Database, then Options, and finally Document
4.    In the new window, select the Relationship tab
5.    Check the Crow’s Feet box under Show and click OK
6.    Drag and drop Shapes onto Drawing to create Diagram
7.    Double-click Entities to adjust properties such as Name, Column, and Primary Key
8.    Double-click Relationships to adjust properties such as Name