ADVANCED NORMALIZATION

1
ADVANCED NORMALIZATION

• Fourth Normal Form
• Multi-valued dependencies

2
Forth Normal Form

• A relation is in the 4th NF if
• It is in Third Normal form
• It does not have multi-valued dependencies
• a data structure should not contain two or more
  independent multi-valued facts
• Multi-valued dependency
• when there are at least three attributes (in a
  PK)
• when two of them are multi-valued AND
• their values, while independent of one another
  are dependent on the 3rd attribute

3
EXAMPLE

• STUDENT id MAJOR code HOBBY code
• 5555 CIS srf
• 5555 CIS rd
• 5555 ACC srf
• 5555 ACC rd
• RULE If a student has a hobby, he(she)
  displays it for every major he is in
• OBSERVATIONS
• Major code and Hobby code are independent of one
  another
• Student id determines Major code and Hobby code
• PROBLEMS
• redundancy - the fact that student is a double
  major is recorded 4 times
• update difficulties - for every new hobby, a
  record must be added for each major the student
  is in

4
From 3NF to 4NF

• Create two tables
• Primary key of table 1
• studentId, majorCode
• Primary key of table 2
• studentId, hobbyCode

5
4NF Example

• Record data about the trade of financial
  instruments
• The instruments traded (e.g., bonds, stocks,
  futures, etc.)
• The dealers who trade in the instruments
• The locations in which the instruments are traded

6
4NF Example

• Business rules
• each instrument can be traded only at a specified
  set of locations AND
• each instrument is to be traded by a specified
  set of dealers

7
4NF Example

• Observations
• dealer and location are independent of one
  another
• dealer and location are multi-valued
• instrument determines location and dealer
• if an instrument is traded in a particular
  location, it is traded by the dealer who is
  allowed to trade in it

8
4NF Example

• Problems
• Redundancy -- info about trader 1111 trading
  Government Bonds is recorded three times
• Update restrictions
• cannot record the fact that an instrument is
  allowed to be traded at a particular location
  unless....
• at least one dealer can trade in that instrument
• cannot record the fact that a dealer can trade in
  a particular instrument unless...
• an instrument can be traded at a min of 1 location

9
4NF Example

• Normalizing to 4NF to avoid multi-valued
  dependencies
• Instrument Dealer
• instrumentId, dealerId
• InstrumentLocation
• instrumentId, locationId

10
Forth Normal Form

• Removes independent multi-valued facts about an
  entity type
• Reduces data redundancies and data
  inconsistencies (for inserts, deletions and
  updates)

11
ADVANCED NORMALIZATION

• Fifth Normal Form Join Dependency
• A Special Case of
• Multi-valued dependency

12
From 4NF to 5NF

• Financial instruments trading example
• Additional business rule
• each dealer can only operate at a specified set
  of locations
• Dealer and location elements are now
  inter-related.
• Therefore, if an instrument is traded at a
  specific location, it is traded by a specific
  dealer only if the dealer can operate at that
  location

13
From 4NF to 5NF

• Splitting up a data structure with dependent
  (cyclic) multi-valued facts into two structures
  is not feasible
• Loss of data in reconstructing the original
  structure
• Applying the rules of 5th NF
• DealerLocation (dealerId, locationId)
• DealerInstrument (dealerId, instrumentId)
• LocationInstrument (locationId, instrumentId)

14
Fifth Normal Form

• Satisfying 5NF requires that data structures with
  two or more dependent multi-valued facts are not
  resolved with one data structure
• While 5NF requires more data structures, there
  may be fewer total record occurrences
• Size of normalized structures increases in
  additive way
• Size of un-normalized structures increases in
  multiplicative way
• E.g. adding a new dealer that trades in X
  instruments and in Y locations
• Add XY records in normalized design
• Add XY in the un-normalized design

15
NORMALIZATION THEORY

• A formal process of
• reducing data redundancy which can cause storage
  and data maintenance problems
• Normal form rules must be applied in sequence
• A data structure is typically considered
  normalized when the first three normal forms are
  applied

16
NORMALIZATION THEORY

• Un-normalized data structure
• Contains repeating attributes (or a group of
  attributes)
• 1st Normal Form
• Repeating attributes removed
• 2nd Normal Form
• Partial functional dependencies are removed

17
NORMALIZATION THEORY

• 3rd Normal Form
• Transitive dependencies are removed
• 4th Normal Form
• Multi-valued dependencies are removed
• 5th Normal Form
• Join dependencies are removed