Date Assigned: See Syllabus Date Due: See Syllabus
This lab consists of two exercises that will demonstrate your ability to normalize data using the first three normal forms.
OBJECTIVES After completing this lab, you will be able to:
• Discuss why a table is not in the First Normal Form • Convert an unnormalized table into the First Normal Form • Take a table that is in the First Normal Form and convert it to the Second Normal
Form. • Take a table that is in the Second Normal Form and convert it to the Third Normal
Form. • Identify table keys.
ASSIGNMENT Below are a series of two exercises along with a list of requirements for completing each. In the first activity, you will be …show more content…
ORDER_ DATE ID
CUST_ID CUST_ STATE NAME
A004 George VA
ITEM ITEM_ NAME DESCRIP
6531 T ape 7890 T able 7956 Pens
5 100.00 1 400.00 1 2.00
1002 7/8 A999 Ben PA 3456 Stove 1 300.00
4234 Paper 7956 Pens
15 234.00 10 20.00
STEP 2 First Normal Form prohibits repeating groups, such as, ITEM_NUM, ITEM_DESCRIP, and PRICE. To convert the table to First Normal Form:
1. Remove the repeating groups from the base table.
2. Create a new table with the Primary Key of the base table and the repeating group.
Convert the unnormalized table shown in Step 1 to the First Normal Form.
Note: We completed the ORDER table as an example of how you should proceed. All you need to do is create the other table and provide the Table name, column names, and appropriate key types.
Table Name: ORDER
Column ORDER_ DATE CUST_ID Name ID
Key Primary Type
CUST_ STATE NAME
Sample 1001 7/7 A004 George VA Data 1002 7/8 A999 Ben PA
STEP 3 Second Normal Form removes non-key columns that are not dependent upon the table’s entire primary key. The steps to accomplish this are:
1. Determine which non-key columns are not dependent upon the table’s entire primary key.
2. Remove those columns from the base table.
3. Create a second table with those columns and the