Normalisation & Dependencies

Normalisation & Dependencies

1. First Normal Form

   • The Domain of an attribute must include only atomic values
     • The value of any attribute in a tuple must be a single value
     • Disallows multi-valued & Composite attributes
   • To Solve REMOVE the attribute OR place it in another relation

2. Second Normal Form

   • In second normal form if it is in First normal form AND every non-prime attribute is fully functionally dependent on the primary key
   • In second normal form if it is in First normal form AND IS NOT partially dependent on any KEY
   • Prime Attribute - An attribute of the relation that is a member of some candidate key
   • Non-Prime Attribute - If it is not a prime attribute OR it is not a member of any candidate key

3. Third Normal Form

   • The third step in Normalization is 3NF. A table is in 3NF, only if a relation is in 2NF and it has no Transitive Functional Dependency

4. Functional Dependency

   • Attribute B of relation R is functionally dependent on attribute A of R if, at every instant of time, each value in A has no more than one value in B associated with it under R – This is written as A -> B
   • Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.

5. Full functional dependency

   • E is said to be fully dependent on D in R – That is, the set of attributes E is functionally dependent on the whole of set D, but not on any subset of D – This is written R(D) ->R(E)
   • Stock is dependent on all of { Supplier, Part# } • Stock is not dependent on Supplier • Stock is not dependent on Part# • Therefore, Stock is fully dependent on combination of Supplier, Part#

6. Partial dependency

   • Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key. The 2nd Normal Form (2NF) eliminates the Partial Dependency.

7. Transitive dependency

   • If P -> Q and Q -> R is true, then P-> R is a transitive dependency. To achieve 3NF, eliminate the Transitive Dependency.