IT 330 Database Design and Management

  1. 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

find the cost of your paper