Data analysis | Database

Normalisation - Third normal form

3NF

Applicable when: Non-key field has related fields.(Look for repeating data inside the table).
Method:
1. Identify transitive dependencies. Transitive dependency occurs when a non-key item is the identifier for some other item.
2. Remove transitive dependencies and create new entities (tables).
Third normal form is when each attribute is dependent on the key, and nothing but the key.

Example:

Step 1:Identify transitive dependencies

EmpId	EmpName	Salary	ProjectId	ProjDsn	CompletionDate
561245	Paddy Field	45,000	123	Motorway	12/06/20xx
591156	C. Shaw	60,000	189	Rural Rd 1	31/11/1997
603345	Ann Tartic	75,000	123	Motorway	12/06/20xx
671122	Authur Ritis	80,000	168	Intersection 6	15/02/1998
720091	Tresa Green	30,000	123	Motorway	12/06/20xx
791006	Amos Quito	15,000	140	HN Bridge	30/09/1999

Notes:
- Project Description (ProjDsn) and Completion Date and dependent on ProjectId

Step 2: Split into separate tables (projection)

EmpId	EmpName	Salary	ProjectId
561245	Paddy Field	45,000	123
591156	C. Shaw	60,000	189
603345	Ann Tartic	75,000	123
671122	Authur Ritis	80,000	168
720091	Tresa Green	30,000	123
791006	Amos Quito	15,000	140
:	:	:	:

ProjectId	ProjDsn	CompletionDate
123	Motorway	12/06/20xx
189	Rural Rd 1	31/11/1997
168	Intersection 6	15/02/1998
140	HN Bridge	30/09/1999
:	:	:

[Rev 16/05/99] 13/6/97 © 1997-99 V/2-Com (Verhaart), P O Box 8415, Havelock North, New Zealand.