You are given a table with sample data Your task is to norma

You are given a table with sample data. Your task is to normalize the tables. Your solution should not show tables with data. Instead, represent each table in this format:

TABLENAME (column, column, column … , column)

Underline the primary key for each table you create. If the table has a foreign key, it should be italicized.

Salesperson Number

Salesperson Lname

Dept ID

Dept Manager LName

Order Number

Order Date

Order Total

134

Katz

003

Davis

0112

10/15/11

$403.00

137

Smith

004

Johnson

0117

10/16/11

$135.00

134

Katz

003

Davis

0121

10/16/11

$338.50

1. Normalize the table to third normal form (3NF). Use appropriate table names.

2. Specify the referential integrity constraints that must be maintained by the database now that you have broken the original table into smaller tables.

Salesperson Number	Salesperson Lname	Dept ID	Dept Manager LName	Order Number	Order Date	Order Total
134	Katz	003	Davis	0112	10/15/11	$403.00
137	Smith	004	Johnson	0117	10/16/11	$135.00
134	Katz	003	Davis	0121	10/16/11	$338.50

Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a given table

1. Salesperson Lname is dependes on Salesperson Number.

2.Dept Manager LName is depends on Dept ID

3.Order Date,Order Total are depends on Order ID

All above points violates the 3NF ,because evey non prime attribute is depends on another non prime attribute and every non-prime attribute of table must not dependent on primary key.

Table 1:

Dept ID

(primary)

Dept Manager LName

(Non-prime)

Salesperson Number

(Non-prime)

003

Davis

134

004

Johnson

137

Table 2:

Salesperson Number

(primary)

Salesperson Lname

(Non-prime)

134

Katz

137

Smith

Table 3:

Order Number

(primary)

Order Date

(Non-prime)

Order Total

(Non-prime)

Salesperson Number

(Non-prime)

0112

10/15/11

$403.00

134

0117

10/16/11

$135.00

137

0121

10/16/11

$338.50

134

Note : In the above 3 tables each non-prime attribute is depends on prime attribute of that table only.

There is no transitive dependency also (i.e A depends on B, B depends on C ,C depends on A).But in the given input table there is transitive dependency(Ex: Sales person name depends on sales id,sales id depends on department id i. sales person name depends on department id).

Dept ID

(primary)

Dept Manager LName

(Non-prime)

Salesperson Number

(Non-prime)

003

Davis

134

004

Johnson

137