Database normalization 2nf, 3nf

database-designnormalizationoracle

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps

here is the initial table:

|--------------------------------------------------------------------------------------------------------|
|Pick_up | E# | E_name |Pick_up_cost |  Rate_per_project| Hours worked|project_code|Project location     |
|--------------------------------------------------------------------------------------------------------|
|        |    |        |             |      363         |   10        | 1          |Luton                |
|        |    |        |    200      |      248         |   20        | 12         |Edinburgh            |
|London  | 76 | Ali    |             |      322         |   18        | 11         |Glasgow              |
|--------------------------------------------------------------------------------------------------------|
|        |    |        |    300      |      363         |   1         | 1          |Luton                |
|        |    |        |             |      568         |   2         | 14         |Newcastle            |
|        |    |        |             |      568         |   1         | 14         |Newcastle            |
|Oxford  | 142| Ronald |    300      |      248         |   5         | 12         |Edinburgh            |
|--------------------------------------------------------------------------------------------------------|
|        |    |        |             |      363         |   11        | 1          |Luton                |
|Oslo    | 76 | Shawn  |    500      |      322         |   15        | 11         |Glasgow              |
----------------------------------------------------------------------------------------------------------

after populating it is:

|--------------------------------------------------------------------------------------------------------|
|Pick_up | E# | E_name |Pick_up_cost |  Rate_per_project| Hours worked|project_code|Project location     |
|--------------------------------------------------------------------------------------------------------|
|London  | 76 | Ali    |    200      |      363         |   10        | 1          |Luton                |
|London  | 76 | Ali    |    200      |      248         |   20        | 12         |Edinburgh            |
|London  | 76 | Ali    |    200      |      322         |   18        | 11         |Glasgow              |
|Oxford  | 142| Ronald |    300      |      363         |   1         | 1          |Luton                |
|Oxford  | 142| Ronald |    300      |      568         |   2         | 14         |Newcastle            |
|Oxford  | 142| Ronald |    300      |      568         |   1         | 14         |Newcastle            |
|Oxford  | 142| Ronald |    300      |      248         |   5         | 12         |Edinburgh            |
|Oslo    | 76 | Shawn  |    500      |      363         |   11        | 1          |Luton                |
|Oslo    | 76 | Shawn  |    500      |      322         |   15        | 11         |Glasgow              |
----------------------------------------------------------------------------------------------------------

1NF

Pick_up, emp_no, emp_name, pick_up_cost, rate_per_project, hours_worked, project_code, project_location

Partial dependencies:

Employee_no->emp_name, pick_up, pick_up_cost

Project_code->project_location, rate_per_project, hours_worked

2NF

Emp_no, emp_name, pick_up, pick_up_cost
Project_code, project_location, rate_per_project, hours_worked

Transitive dependencies:
Pick_up->pick_upcost

My question is the transitive dependency correct in 2nf form? and what will be in 3nf form?

Best Answer

A simple mnemonic:

  • The key (1NF - no data items repeated in more than one place within the row)
  • The whole key (2NF - nothing relates to just a part of a composite key)
  • Nothing but the key (3NF - nothing is related to a non-key attribute)

. . . So help me Codd.