database normalization 2nf, 3nf

Question

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?

Answer 1

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.

Answer 2

Im a student to, and as far as I know,

A table attends to the 1NF if:

1.The table have a primary key

2.Do not have repetition groups(itens that repeat in the same row)

3.Each of it's fields is atomic (no need to decompose the value)

For each repeatition group, move it to a new table and includes the key of the origin table on it.

The table attends to the 2NF if:

1.It is on the 1NF

2.All the table fields depends of the entirely primary key

The following table CustomerCard(CustomerId , CardId, CardBrand) the CardBrand field depends only of the CardId field, this way it should be moved to another table. Card(CardId, CardName)

To attend the 3NF:

1.It is on the 2NF

2.None of its columns have transitive dependencies

3.None of its columns have calculated fields

The following table Order(OrderId, CustomerId, CustomerName, CustomerCityId, CustomerCityName) Orbserve the following transitive dependencies:

• OrderId -> CustomerId -> CustomerName
• OrderId -> CustomerId -> CustomerCityId
• OrderId -> CustomerId -> CustomerCityId -> CustomerCityName

The simplest dependencies should be resolved first. The CustomerId code will stay in the Order table, but columns that depends on the CustomerId are moved to a new table where the CustomerId is the primary key, resulting:

Order(OrderId, CustomerId)

Customer(CustomerId, CustomerName, CustomerCityId, CustomerCityName)

The transitives dependencies of the Order table are removed, but we still need to analyze the table Customer. The column CustomerCityName depends only on the CustomerCityId. Then again we need to create a new table City(CityId, CityName), and the CustomerCityId still present in the Customer table.

The final result using all 3NF:

• Order(OrderId, CustomerId)
• Customer(CustomerId, CustomerName, CustomerCityId)
• City(CityId, CityName)