Showing posts with label 1NF. Show all posts
Showing posts with label 1NF. Show all posts

Comparison of Normal Forms


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



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

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




Wikipedia

Search results