Relational Normalization Theory

1
Relational Normalization Theory

• Functional Dependency and Decomposition

2
Schema Refinement

• ERM is a subjective process
• Alternative designs
• Schema integration
• Not every integrity constraint can be expressed
  in ERD
• ERM does not provide
• Criteria for schema evaluation
• Algorithms for schema refinement

3
Data Redundancy (example)
Table Instructor (Instance 1)
EID Name Address Dep. School Office Faculty
12 Smith Atkinson Econ. SASIT 512 Atkinson
13 Green Atkinson Itec. SASIT 602 Atkinson
14 Fox Ross Itec. M. Stat 406 Arts
15 Brown Ross Math. M. Stat 638 Arts
16 Grey Atkinson STS SASIT 510 Atkinson
4
Problems related to Data Redundancy

• Inefficient use of secondary storage
• Update anomalies
• Insertion anomalies
• Deletion anomalies
• Possible solutions
• Decomposition

5
Decomposition (example)
EID Name Dep. School Office
12 Smith Econ. SASIT 512
13 Green Itec. SASIT 602
14 Fox ITec. M. Stat 406
15 Brown Math M. Stat 638
16 Grey STS SASIT 510
School Faculty Address
SASIT Atkinson Atkinson
M. Stat Arts Ross
6
Decomposition

• Decomposition of a relation schema R is replacing
  by 2 or more relation schemas with attributes
  from R which include all attributes from R
• Based on functional dependency
• The necessity for decomposition of a relation
• Normal forms
• Disadvantages of decomposition

7
Functional Dependency (FD)

• Definition
• Let t.A be a projection of a tuple t onto
  attributes from set A and t.B be a projection of
  the tuple t onto attributes from set B
• A ? B
• If t1.A t2.A, then t1.B t2.B

8
Functional Dependency (Example)
Functional dependency AB?D,E is not violated by
the given instance
The new tuple violates proposed FD
9
Properties of FD

• Attributes within a table relate to one another
• Can be formulated only based on business rules
• Property of relational schema, not relation
  instance
• Part of Integrity Constraints
• Legal instance of a relation must satisfy all IC

10
Table Instructor (Instance 2)
EID Name Address Dep. School Office Faculty
12 Smith Atkinson Econ. SASIT 512 Atkinson
17 Wolf CCB Itec. SASIT 150 Atkinson
14 Fox Ross Itec. M. Stat 406 Arts
15 Brown Ross Math. M. Stat 638 Arts
16 Grey Atkinson STS SASIT 510 Atkinson
11
FD Applications

• Primary key constraint special case of
  functional dependency
• Determine alternative keys
• Logical schema refinement which helps to avoid
  problems related to data redundancy

12
Dependency Diagram
13
New terms

• A determinant is any attribute(s) whose value
  determines other values in a tuple
• A prime attribute (key attribute) is any
  attribute that is at least a part of a candidate
  key
• A Nonprime attribute (non-key attribute) is any
  attribute, which is not a part of a candidate key

14
Armstrongs Axioms

• Reflexivity If B is a subset of A, then A?B
  (trivial dependency)
• Augmentation If A ? B, then
• AC ? B,C
• Transitivity If A ? B, and B ? C, then A ? C.

15
Rules to infer FDs

• Self-determination A ? A
• Decomposition If A ? B,C,
• then A?B and A?C
• Union If A?B and A?C,
• then A ? B,C

16
Attribute Closure

• Set A of attributes that are functionally
  dependent on the set A based on the set F of FDs
• Algorithm
• A A
• If there is a FD U?V such that U ? A, then the
  A A? V.
• Repeat step 2 until no more attributes can be
  added

17
Example

• Consider a relation with attributes
• A, B, C, D, E, and F.
• The set S of functional dependencies is AB?C
  BC?A,D D?E, CF?B
• What is the attribute closure of A,B?
• What is the attribute closure of A,B,F?

18
Attribute Closures and Keys

• The closure A will hold all attributes from the
  relation R if and only if A is a superkey of the
  relation.
• Algorithm
• Find subsets of attributes which closure is the
  set of all attributes of the relation
• Check the subsets if they are irreducible
• Subsets with irreducible lists of attributes are
  candidate keys

19
Normalization

• Relation analysis based on keys and FDs
• Set of requirements determines the degree of
  normalization
• Normal forms
• Decomposition

20
Normal Forms

• First normal form every field contains atomic
  values and no repeating group is allowed
• Full functional dependency A?B removal of an
  attribute from A destroys the dependency
• Partial FD A?B an attribute can be removed from
  A and FD still holds.
• Second normal form
• Every non-prime attribute fully functionally
  depends on any candidate key

21
Boyce-Codd Normal Form

• Let R be an arbitrary relation, X is a subset of
  attributes of R, A is an attribute of R,
• For every X?A either
• A?X (trivial)
• Or
• X is a candidate key

22
Example

• Let R is a relation with attributes (A, B, C, D).
  
• Set of FDs is determined on R as
• AB?C,D AC?B,D.
• Is R in the BCNF?

23
Transitive Dependency

• Is a dependency of one nonprime attribute on
  another nonprime attribute
• Example Let R is a relation with (A,B,C,D)
  attributes and the following set of FDs AB
  ?C,D D?B
• Is the dependency D ?B transitive?

24
Third Normal Form

• Let R be an arbitrary relation, X is a subset of
  attributes of R, A is an attribute of R.
• For every X?A one of the following is true
• A?X
• X is a candidate key
• A?Y, where Y is a key for R

25
Third Normal Form (cont)

• A relation is in 3NF if it is in 2NF and it
  contains no transitive dependencies
• Example Let R be a relation with (A,B,C,D)
  attributes and the following set of FDs AB
  ?C,D D?B
• Is the relation R in 3NF?

26
Lossless-join Decomposition

• A decomposition of R into two relation schemas is
  said to be a lossless-join, if for every legal
  instance of R we can recover the original
  relation from the decomposition based on natural
  join.
• R R1 ?? R2

27
Lossless-join Decomposition (Example 1)
R
R2
R1
Compute R1 ?? R2
28
Algorithm for Lossless-Join Decomposition

• R arbitrary relation with FD X?Y.
• If X?Y is empty,
• the decomposition of R into
• R1 R-Y and R2 X Y is lossless-join
• R1 and R2 can be decomposed into R11, R12 and
  R21, R22 and so on.

29
Lossless-Join Decomposition (Example 2)

• Consider the relation
• HasAccount (cid, oid, acN)
• With set of FDs
• cidoid ? acN acN ? oid
• Is the relation in 3NF?
• Find lossless-join decomposition into BCNF

30
Multivalued Dependency

• Example

Branch Agent Owner
B001 Smith Carol
B001 Smith Tina
B001 Grey Carol
B001 Grey John
B001 Green Tina
B002 Grey Nick
31
Multivalued Dependency

• Let R be a relation and X and Y be subsets of
  attributes of R
• Multivalued dependency X ? ?Y holds over R if in
  every legal instance of R each X value is
  associated with a set of Y values and this set is
  independent of the values in the other attributes

32
Denormalization

• What level of normalization is appropriate?
• Normalization purity can be difficult to sustain
  due to conflict in
• Design efficiency
• Information requirements
• Processing