Comparison of Normal Forms

Normal Forms in DBMS - A Comparison

Compare Normal Forms / 1NF vs 2NF vs 3NF vs BCNF / Differentiate between normal forms

	1 NF	2 NF	3 NF	BCNF
Properties to hold	All the attributes of the relation are atomic (indivisible into meaningful sub parts), Every attribute contains single value (per record).	At the first place the table is in 1NF, All the non-key attributes of the table are fully functionally dependent on the Primary key of the table.	The table is in 2NF, There is no Functional Dependency such that both Left Hand Side and Right Hand Side attributes of the FD are non-key attributes. In other words, no transitive dependency is allowed	For all the Functional Dependencies (FDs) hold in the relation R, if the FD is non-trivial then the determinant (LHS of FD) of that FD should be a Super key
Achievability	Always achievable	Always achievable	Always achievable	Not always
Lossless Join Decomposition	Always achievable	Always achievable	Always achievable	Sometimes not achievable
Dependency Preserving Decomposition	N/A		Possible	Either lossless join or dependency preserving decomposition is possible. Not both.
Anomalies	May allow some anomalies	May allow some anomalies	May allow some anomalies	Always eliminates anomalies
What is eliminated?	Eliminate repeating groups	Eliminate redundant data	Eliminate columns not dependent on key	Eliminate multiple candidate keys
Identification of Functional Dependencies	Not necessary	Must	Must	Must
Attribute Domain	Should be atomic	Should be atomic	Should be atomic	Should be atomic
Handling of Update Anomalies	Does not handle.	Handles	Handles	Handles
Composite Primary Key	Allowed	Allowed (if no partial dependency exists)	Allowed	Not allowed
Partial key dependencies (if AB → C, and if C can be fully determined by either A or B, then this dependency is partial key dependencies)	Permitted	Not permitted	Not permitted	Not permitted
Transitive dependencies (if A → B, and B → C then A → C)	Can be permitted	Can be permitted	Cannot be permitted	Cannot be permitted
Overview	It is about shape of a record type	It is about the relationship between key and non-key fields	It is about the relationship between key and non-key fields	It is about determinant should be a superkey.

*Please give your valuable input

*********

Related Links

• Go to Normal Form - Home page

• Go to Normalization - Home page

• Go to Normalization Solved Exercises - Home page

• Go to Comparison of 1NF and 2NF page

• Go to Comparison of 3NF and BCNF page

Differentiate between all normal forms in DBMS normalization process

1NF and 2NF Comparison

Difference between 1NF and 2NF / Comparison of 1NF and 2NF / Compare 1NF and 2NF / 1NF and 2NF Comparison / 1NF and 2NF difference

Properties to be satisfied for 1NF and 2NF (recall)

1NF – All attribute values are atomic values, i.e, no repeating group or no composite attributes present.

2NF – Table should be in 1NF and no partial functional dependency presents.

Properties	1NF	2NF
Attribute Domain	Should be Atomic	Should be Atomic
Functional Dependencies Identification	Not necessary	Must
Handling of Update Anomalies	Does not handle. It deals with the basic structure of a relation.	Handle update anomalies.
Composite Key as Primary Key	Primary Key can be Composite key	Primary Key cannot be Composite key always. It may violate 2NF if partial key dependency exists. That is, no partial functional dependency is permitted
Goal	Eliminate Redundant Data	Ensure Data Dependencies

*********

Related Links

• Go to Normal Form - Home page

• Go to Normalization - Home page

• Go to Normalization Solved Exercises - Home page

• Go to Comparison of All Normal Forms page

Compare first normal form 1nf with second normal form 2nf in dbms