Functional Dependencies FDs and

1
Lecture 25 (11/14/05)

• Functional Dependencies (FDs) and
• Normalization

2
1NF First Normal Form

• When is a relation is 1NF?
• Definition of 1NF?
• How to make in 1 NF?

3
2NF Second Normal Form

• Full functional dependency (VS partial
  dependency) - a Full FD or FFD Y ? Z is an FD
  where removal of any attribute from Y means the
  FD does not hold any more
• SSN, PNUMBER ? HOURS
• SSN, PNUMBER ? ENAME
• (Defn.) A relation schema R is in second normal
  form (2NF)
• If it in 1NF and
• If every non-prime attribute A in R is fully
  functionally dependent on every key of R
• (i.e. No partial dependencies allowed for
  nonprime attributes)
• R can be decomposed into 2NF relations via the
  process of 2NF normalization

4
Non-prime attributes are only associated with the
part of the key on which they are fully
functional dependent
5
Assume COUNTRY_NAME, LOT is another candidate
key
6
3NF Third Normal Form

• Transitive functional dependency
• A FD X?Z that can be derived from two FDs X?Y
  and Y?Z
• SSN ? DMGRSSN
• is a transitive FD since
• SSN ? DNUMBER
• DNUMBER ? DMGRSSN
• SSN ? ENAME
• is non-transitive
• no set of attributes X where SSN?X and X?ENAME

7
3NF Third Normal Form

• (Defn.) A relation schema R is in third normal
  form (3NF) if
• it is in 2NF and
• no non-prime attribute A in R is transitively
  dependent on a candidate key
• no non-prime attribute A in R is dependent on
  anything other than candidate key
• If whenever a FD X ? A holds in R, then either
• X is a superkey of R, or
• A is a prime attribute of R
• R can be decomposed into 3NF relations via the
  process of 3NF normalization

8
Non-prime attribute cant be transitively
functionally dependent on the key (i.e. cant be
functionally dependent on anything other than the
key) Is (b) in 2NF?
9
(No Transcript)
10
Recap

• These are not the actual definitions
• 1NF "every (non-prime) attribute is functionally
  dependent on the key"
• 2NF "every non-prime attribute is fully
  functionally dependent on the key
• 3NF every non-prime attribute is fully
  functionally dependent on nothing but key"
• Memory Trigger "every non-prime attribute is
  functionally dependent upon
• the key (1NF)
• the whole key (2NF)
• And nothing but the key (3NF)
• So help you Codd !!!
• Witnesses are sworn in courts

11
Problems

• Give examples of all 3 update anomalies in the
  following?

12
Problems

• Consider the EMP_DEPT relation
• SSN ? EName, BDate, Address, DNumber
• DNumber ? DName, DMGRSSN
• Find SSN and DNumber
• What is a key?

13
Practice Exercise (1)

• Given a R(A,B,C,D,E,F,G,H,I,J) and F
• A,B?C,
• A?D,E,
• B?F,
• F?G,H,
• D?I,J
• Find a key
• Normalize to 1NF, 2NF and 3NF
• R(A,B,C,D,E,F,G,H,I,J)

14
Practice Exercise (1)

• Consider the universal relation R A, B, C, D,
  E, F, G, H, I, J and the set of functional
  dependencies
• F A, B?C, A?D, E, B?F, F?G,
  H, D?I, J
• What is a candidate key for R?
• A minimal set of attributes whose closure
  includes all the attributes in R is a key
• Try singletons, then doubletons then triple-tons,
  etc
• Since the closure of A, B, A, B R, one key
  of R is A, B
• (in this case, it is the only key)

15
Practice Exercise (1)

• What is the normal form of R?
• 1 NF
• Decompose R into 2NF?
• First, identify partial dependencies that violate
  2NF.
• These are attributes that are functionally
  dependent on either parts of the key, A or B,
  alone
• We can calculate the closures A and B to
  determine partially dependent attributes
• A A, D, E, I, J. Hence A?D, E, I, J
• B B, F, G, H, hence B? F, G, H
• Remove the attributes that are functionally
  dependent on parts of the key (A or B) from R and
  place them in separate relations R1 and R2, along
  with the part of the key they depend on (A or B),
  which are copied into each of these relations but
  also remain in the original relation, which we
  call R3 below
• R1 A, D, E, I, J, R2 B, F, G, H, R3 A,
  B, C

16
Practice Exercise (1)

• Decompose R into 3NF?
• Next, we look for transitive dependencies in R1,
  R2, R3.
• The relation R1 has the transitive dependency
  A?D?I, J, so
• remove the transitively dependent attributes I,
  J from R1 into a relation R11
• copy the attribute D they are dependent on into
  R11.
• The remaining attributes are kept in a relation
  R12.
• Hence, R1 is decomposed into R11 and R12 as
  follows
• R11 D, I, J, R12 A, D, E
• The relation R2 is similarly decomposed into R21
  and R22 based on the transitive dependency
  B?F?G, H
• R21 F, G, H, R22 B, F
• The final set of relations in 3NF are R11, R12,
  R21, R22, R3

17
Practice Exercise (2)

• Assume now F
• B?A,
• B?C,
• A?D, E,
• B?F,
• F?G, H,
• D, E?I, J,
• I?D
• What is a candidate key for R?
• B A, B, C, D, E, F, G, H,I, J
• B !

18
Practice Exercise (2)

• What is the normal form of R?
• 2 NF
• Decompose R into 3NF?
• Transitive dependencies
• B?F?G, H
• B?A?D, E?I, J
• R1(B, A, C, F) R2(A, D, E, I, J)R3(F, G, H)

19
Practice Exercise (2)

• Still we have problems in R2 because of
• A ? D,E?I, J and
• R1(B, A, C, F) R21(A, D, ER22(D, E, I,
  J)R3(F, G, H)

20
BCNF Boyce-Codd Normal Form

• RECALL that R is in 3NF if whenever a FD X ? A
  holds in R, then either
• X is a superkey of R, or
• A is a prime attribute of R
• A relation schema R is in Boyce-Codd Normal Form
  (BCNF) if it is in 3NF and whenever an FD X ? A
  holds in R, then X is a superkey of R
• Like 3NF but removes 2nd possibility of 3NF
• All attributes are dependant on the key
• Stricter form of 3NF
• A determinant is any attribute on which some
  other attribute is functionally dependent
• All determinants should be superkeys

21
BCNF Boyce-Codd Normal Form

• Each normal form is strictly stronger than the
  previous one
• Every 2NF relation is in 1NF
• Every 3NF relation is in 2NF
• Every BCNF relation is in 3NF
• There exist relations that are in 3NF but not in
  BCNF
• The goal is to have each relation in BCNF (or 3NF)

22
The Normalization Process

• The overall normalization process so far
• Project off composite and multi-valued
  attributes) - produce 1NF
• Eliminate non-full FDs - produce 2NF
• Eliminate transitive FDs - produce 3NF
• Eliminate remaining FDs where determinant is not
  candidate key - produce BCNF
• Normalization has become an art form
• We now have 7NF, 8NF and even more
• Just research ?

23
Properties of Relational Decompositions

• Relation decomposition and normal forms
• Universal relation schema a relation schema
  RA1, A2, , An that includes all the
  attributes of the database
• Universal relation assumption every attribute
  name is unique (we are starting with one
  relation)
• Decomposition The process of decomposing the
  universal relation schema R into a set of
  relation schemas D R1,R2, , Rm that will
  become the relational database schema by using
  the functional dependencies

24
Properties of Relational Decompositions

• Attribute preservation condition (a must)
• Dependency preservation property of a
  decomposition (desired)
• Lossless (Non-additive) join property of a
  decomposition (a must)
• Note The word loss in lossless refers to loss of
  information, not to loss of tuples.
• In fact, for loss of information a better term
  is addition of spurious information
• Non-additive