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
No comments:
Post a Comment