Normalize table to 3rd normal form

Question

This questions is obviously a homework question. I can't understand my professor and have no idea what he said during the election. I need to make step by step instructions to normalize the following table first into 1NF, then 2NF, then 3NF.

I appreciate any help and instruction.

Answer 1

Okay, I hope I remember all of them correctly, let's start...

Rules

To make them very short (and not very precise, just to give you a first idea of what it's all about):

• NF1: A table cell must not contain more than one value.
• NF2: NF1, plus all non-primary-key columns must depend on all primary key columns.
• NF3: NF2, plus non-primary key columns may not depend on each other.

Instructions

• NF1: find table cells containing more than one value, put those into separate columns.
• NF2: find columns depending on less then all primary key columns, put them into another table which has only those primary key columns they really depend on.
• NF3: find columns which depend on other non-primary-key columns, in addition to depending on the primary key. Put the dependent columns into another table.

Examples

NF1

a column "state" has values like "WA, Washington". NF1 is violated, because that's two values, abbreviation and name.

Solution: To fulfill NF1, create two columns, STATE_ABBREVIATION and STATE_NAME.

NF2

Imagine you've got a table with these 4 columns, expressing international names of car models:

• COUNTRY_ID (numeric, primary key)
• CAR_MODEL_ID (numeric, primary key)
• COUNTRY_NAME (varchar)
• CAR_MODEL_NAME (varchar)

The table may have these two data rows:

• Row 1: COUNTRY_ID=1, CAR_MODEL_ID=5, COUNTRY_NAME=USA, CAR_MODEL_NAME=Fox
• Row 2: COUNTRY_ID=2, CAR_MODEL_ID=5, COUNTRY_NAME=Germany, CAR_MODEL_NAME=Polo

That says, model "Fox" is called "Fox" in USA, but the same car model is called "Polo" in Germany (don't remember if that's actually true).

NF2 is violated, because the country name does not depend on both car model ID and country ID, but only on the country ID.

Solution: To fulfill NF2, move COUNTRY_NAME into a separate table "COUNTRY" with columns COUNTRY_ID (primary key) and COUNTRY_NAME. To get a result set including the country name, you'll need to connect the two tables using a JOIN.

NF3

Say you've got a table with these columns, expressing climatic conditions of states:

• STATE_ID (varchar, primary key)
• CLIME_ID (foreign key, ID of a climate zone like "desert", "rainforest", etc.)
• IS_MOSTLY_DRY (bool)

NF3 is violated, because IS_MOSTLY_DRY only depends on the CLIME_ID (let's at least assume that), but not on the STATE_ID (primary key).

Solution: to fulfill NF3, put the column MOSTLY_DRY into the climate zone table.

---

Here are some thoughts regarding the actual table given in the exercise:

I apply the above mentioned NF rules without to challenge the primary key columns. But they actually don't make sense, as we will see later.

• NF1 isn't violated, each cell holds just one value.
• NF2 is violated by EMP_NM and all the phone numbers, because all of these columns don't depend on the full primary key. They all depend on EMP_ID (PK), but not on DEPT_CD (PK). I assume that phone numbers stay the same when an employee moves to another department.
• NF2 is also violated by DEPT_NM, because DEPT_NM does not depend on the full primary key. It depends on DEPT_CD, but not on EMP_ID.
• NF2 is also violated by all the skill columns, because they are not department- but only employee-specific.
• NF3 is violated by SKILL_NM, because the skill name only depends on the skill code, which is not even part of the composite primary key.
• SKILL_YRS violates NF3, because it depends on a primary key member (EMP_ID) and a non-primary key member (SKILL_CD). So it is partly dependent on a non-primary-key attribute.

So if you remove all columns which violate NF2 or NF3, only the primary key remains (EMP_ID and DEPT_CD). That remaining part violates the given business rules: this structure would allow an employee to work in multiple departments at the same time.

Let's review it from a distance. Your data model is about employees, departments, skills and the relationships between these entities. If you normalize that, you'll end up with one table for the employees (containing DEPT_CD as a foreign key), one for the departments, one for the skills, and another one for the relationship between employees and skills, holding the "skill years" for each tuple of EMP_ID and SKILL_CD (my teacher would have called the latter an "associative entity").