Normal Forms (1 to 3)

Overview:

                The normal forms defined in relational database theory represent a set of guidelines for record design.

1^st Normal Form:

                This is the basic and simplest normal form, the aim of this is to eliminate duplicate columns from the table and create separate tables for each group of related data and identify each row with a unique column (the primary key).

Employee (Not in 1NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name	Skills
1	Ramesh	201	IT	C,C#,SQL
2	Suresh	225	Systems	32Bit, 63 Bit
3	Ganesh	225	Systems	32Bit

Employee (In 1NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name	Skills
1	Ramesh	201	IT	C
1	Ramesh	201	IT	C#
1	Ramesh	201	IT	SQL
2	Suresh	225	Systems	32Bit
2	Suresh	225	Systems	64bit
3	Ganesh	225	Systems	32Bit

2^nd Normal Form:

Each attribute must be functionally dependent on the primary key. Functional dependence - the property of one or more attributes that uniquely determines the value of other attributes. Any non-dependent attributes are moved into a smaller (subset) table. Prevents update, insert, and delete anomalies

Note: Skills is not functionally dependent on EMP_ID since it is not unique to each EMP_ID.

Employee (In 1NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name	Skills
1	Ramesh	201	IT	C
1	Ramesh	201	IT	C#
1	Ramesh	201	IT	SQL
2	Suresh	225	Systems	32Bit
2	Suresh	225	Systems	64bit
3	Ganesh	225	Systems	32Bit

Employee (In 2NF)					Skills (In 2NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name		EMP_ID	Skills
1	Ramesh	201	IT		1	C
2	Suresh	225	Systems		1	C#
3	Ganesh	225	Systems		1	SQL
					2	32Bit
					2	64bit
					3	32Bit

3^rd Normal Form:

Remove transitive dependencies. Transitive dependence - two separate entities exist within one table. Any transitive dependencies are moved into a smaller (subset) table. Prevents update, insert, and delete anomalies.

Employee (In 2NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name
1	Ramesh	201	IT
2	Suresh	225	Systems
3	Ganesh	225	Systems

Employee (In 3NF)				Department (in 3NF)
EMP_ID	EMP_Name	Dept_ID		Dept_ID	Dept_Name
1	Ramesh	201		201	IT
2	Suresh	225		225	Systems
3	Ganesh	225