Normalisation : Normalisation By
E. Siva Sankari, M.E.,
SL/IT,NEC.
Normalisation : Normalisation Process of converting a relation to a std form
Process of analyzing the given relation schemas based on their functional dependencies & primary keys to achieve the desirable properties of
Minimizing redundancy
Minimizing the insertion, deletion & update anomalies
Normalisation : Normalisation a design technique by which relational database tables are structured in such a way as to make them invulnerable to certain types of logical inconsistencies and anomalies.
Normal form
Rules for structuring relations that eliminate anomalies
Anomalies : Anomalies Update anomaly
Data inconsistency resulting from data redundancy and partial update
Deletion anomaly
Unintended loss of data due to deletion of other data
Insertion anomaly
Inability to add data to the database due to absence of other data
First normal form(1NF) : First normal form(1NF) A relation schema R is in first normal form(1NF)
if the domains of all attributes of R are atomic.
(A domain is atomic if elements of the domain are considered to be indivisible units.)
(Atomicity: Each attribute must contain a single value, not a set of values.)
or
there must be no repeating groups, i.e. no attributes which occur a different number of times on different records.
Second normal form(2NF) : Second normal form(2NF) A relation schema R is in second normal form(2NF)
R must be in 1NF
if each attribute A in R meets one of the following criteria :
It appears in a candidate key
It is not partially dependent on a candidate key
(A functional dependency α → β is called a partial dependency if there is a proper subset γ of α such that
γ → β. We say that β is partially dependent on α )
No partial dependency (non-key attributes partially dependent on a key attribute)
A relation is in second normal form (2NF) if it is in first normal form and all the non-key attributes are fully functionally dependent on the key.
Boyce Codd normal form : Boyce Codd normal form A relation schema R is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form α → β , where α R and β R, at least one of the following holds:
α → β is a trivial functional dependency (that is β α )
α is a super key for schema R
Not every BCNF decomposition is dependency preserving.
Dependency of a non-key attribute on another non-key attribute
Boyce Codd normal form : Boyce Codd normal form We cannot always satisfy all three design goals
BCNF
Lossless Join
Dependency Preservation
In those cases where we cannot meet all three design criteria, we abandon BCNF and accept a weaker normal form called third normal form(3NF)
Third Normal Form(3NF) : Third Normal Form(3NF) A relation schema R is in 3NF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form α → β , where α R and β R, at least one of the following holds:
α → β is a trivial functional dependency (that is β α )
α is a super key for schema R
Each attribute A in β-α is contained in a candidate key for R (transitive dependency)
Definition of 3NF allows transitive functional dependencies that are not allowed in BCNF.
requires that data stored in a table be dependent only on the primary key, and not on any other field in the table. To be 3NF, relation must be 2NF and have no transitive dependencies
Comparison of BCNF & 3NF : Comparison of BCNF & 3NF 3NF
Adv- Possible to obtain a 3NF design without sacrificing a lossless join or dependency preservation
Disadv – allowing transitive dependencies
force to use null values to represent some of the possible meaningful relationships among data items
there is the problem of repetition of information
Comparison of BCNF & 3NF : Comparison of BCNF & 3NF If we are forced to choose between BCNF and dependency preservation with 3NF, it is generally preferable to opt for 3NF.
If we cannot test for dependency preservation efficiently, we either pay a high penalty in system performance or risk the integrity of the data in our database.
Limited amount of redundancy imposed by transitive dependencies allowed under 3NF is the lesser evil.
Comparison of BCNF & 3NF : Comparison of BCNF & 3NF Three design goals for a relational database design
BCNF
Lossless join
Dependency preservation
If we cannot achieve all three, we accept
3NF
Lossless join
Dependency preservation