Functional Dependencies

The following table is un-normalized.

The original data set is in the Normalization Lab Data.xls file in your lab folder. For each question that requires you to re-write tables, re-write them in the Excel workbook and copy/paste your new table(s) in the appropriate sections below. Tables must include all data (not merely column headings). To make your results more readable, paste your tables as bitmaps or screenshots. To receive credit for these questions, your tables must be easily readable. You may refer to the Normalization_Example.xls file as an example on how to approach this task. This file located in your lab folder.

Question #1:

In the space below, re-write the table above so that it is in first normal form.

Question #2:

What is the primary key of the 1NF table you just constructed?

Question #3:

Identify the functional dependencies in the 1NF table you constructed.

Question #4:

Describe any insertion anomalies that can occur in your 1NF table.

Question #5:

Describe any deletion anomalies that can occur in your 1NF table.

Question #6:

Describe any modification anomalies that can occur in your 1NF table.

Question #7:

Based on your 1NF table, in the space below, re-write the table so that it is in second normal form. You may add ID fields to any new tables you create and use those IDs as foreign keys in other tables as appropriate. (10 points)

Question #8:

What is the primary key of each of the 2NF tables you just constructed?

Question #9:

Identify the functional dependencies in the 2NF table(s) you constructed.

Question #10:

In the space below, re-write the 2NF table(s) so that they are in 3NF.

Question #11:

What is the primary key of each of the 3NF tables you just constructed?

Question #12:

Identify the functional dependencies in the 3NF table(s) you constructed.

Question #13:

Are all of your tables in BCNF? Why or why not?