Step 1: Select the data source and convert into an unnormalised table (UNF)
- Create column headings (ignoring any calculated fields)
- Enter sample data into table
- Identify a key for table (and underline it)
- Remove duplicate data
Step 2: Transform a table of unnormalised data into first normal form (1NF)
Rule: Remove any repeating attributes to a new table
- Identify repeating attributes
- Remove repeating attributes to a new table together with a copy of the key from the UNF table
- Assign a key to the new table (and underline it). The key from the unnormalised table alway becomes part of the key of the new table. A compound key is created. The value for this key must be unique for each entity occurrence.
Step 3: Transform data in first normal form (1NF) into second normal form (2NF)
Rule: Remove any non-key attributes that only depend on part of the table key to a new table
Ignore tables with a) a simple key or b) with no non-key attributes (these go straight to 2NF with no conversion)
- Take each non-key attribute in turn and ask the question – is this attribute dependent on one part of the key?
- If yes, remove attribute to new table with a copy of the par of the key it is dependent upon. The key it is dependent upon becomes the key in the new table. Underline the key in this new table.
- If no, check against other part of the key and repeat above process.
- If still no, ie not dependent on either part of key, keep attribute in current table.
Step 4: Transform data in second normal form (2NF) into third normal form (3NF)
Rule: Remove to a new table any non-key attributes that are more dependent on other non-key attributes than the table key
Ignore tables with zero or only one non-key attribute (these go straight to 3NF with no conversion)
- If a non-key attribute is more dependent on another non-key attribute than the table key
- Move the dependent attribute, together with a copy of the non-key attribute upon which it is dependent, to a new table
- Make the non-key attribute, upon which it is dependent, the key in the new table. Underline the key in this new table.
- Leave the non-key attribute, upon which it is dependent, in the original table and mark it a foreign key (*).