Functional Dependencies and Normalization - PowerPoint PPT Presentation

About This Presentation
Title:

Functional Dependencies and Normalization

Description:

Functional Dependencies and Normalization Normalization Normalization is a formalized procedure to eliminating redundancy from data by the progressive use of non ... – PowerPoint PPT presentation

Number of Views:180
Avg rating:3.0/5.0
Slides: 31
Provided by: Trave61
Category:

less

Transcript and Presenter's Notes

Title: Functional Dependencies and Normalization


1
Functional Dependencies and Normalization
2
Normalization
  • Normalization is a formalized procedure to
    eliminating redundancy from data by the
    progressive use of non-lose decomposition,
    which involves splitting records without losing
    information.
  • In reducing the data model to the state where
    each bit of information is only held in one
    place, the update process is much simpler, more
    efficient and inconsistencies in the database are
    impossible.

3
Normalization (cont.)
Redundancy
Redundancy
Redundancy
4
Normalization (cont.)
  • Normalization is based on the idea that an
    attribute may depend on another attribute in some
    way.
  • There are 2 different kinds of dependencies
    involved up to 5 NF
  • Functional dependency
  • Multivalued dependence

5
Functional Dependence
S CITY P QTY
S1 Khon Kaen P1 100
S1 Khon Kaen P2 100
S2 Saraburii P1 200
S2 Saraburii P2 200
S3 Saraburii P2 300
S4 Bangkok P2 400
S4 Bangkok P4 400
S4 Bangkok P5 400
QTY is functionally dependent on SandP
S and P are the determinant of QTY
6
Functional Dependence
  • In a relation including attribute A and B, B is
    functional dependent on A if, for every valid
    occurrence, the value A determines the value B
  • An occurrence can not be used to show that a
    dependency is true, only that it is false
  • A and B can be composite
  • If B is Functional Dependent on A, then A is
    the determinant of B
  • All fields are functionally dependent on the
    primary key or indeed any candidate key be
    definition.

7
First Normal Form
  • A relation is in First Normal form if, and only
    if, it contains no multi-value or no repeating
    groups.

8
First NF (cont.)
NO Name Province PayDate1 Amount1 PayDate2 Amount2
E001 Somchai Khon Kaen 15/04/2004 5,000.00 30/04/2004 5,000.00
E002 Sompong Sarakham 15/04/2004 4,500.00 30/04/2004 4,500.00
E003 Somchay Ubon 15/04/2004 5,200.00 30/04/2004 5,200.00
9
Problem
Staff
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P04, P05 Voice ordering, Speech Coding
E004 SomSiri D02 Computer Science P04, P06 Voice ordering, Speech Synthesis
  • Problem
  • Difficult to manipulate data
  • Redundancy
  • UPDATE ANOMALIES

10
Insert
Staff
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P04, P05 Voice ordering, Speech Coding
E004 SomSiri D02 Computer Science P04, P06 Voice ordering, Speech Synthesis
We can not insert new project if the project has
not assigned to any employee yet.
11
UPDATE ANOMALIES
Staff
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P04, P05 Voice ordering, Speech Coding
E004 SomSiri D02 Computer Science P04, P06 Voice ordering, Speech Synthesis
Change ProjName from Voice Ordering to Speech
Ordering need to change all in Database
12
DELETE Problem
Staff
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P04, P05 Voice ordering, Speech Coding
E004 SomSiri D02 Computer Science P04, P06 Voice ordering, Speech Synthesis
Delete Employee E003 Somchay Project P03 Medical
Image Processing was deleted also
13
Solution
  • Remove the repeating group
  • In case of multi-valued
  • Create new relation
  • Columns Key multi-valued
  • Take its determinant with it

14
Repeating group
Employee
ENO Name Province PayDate1 Amount1 PayDate2 Amount2
E001 Somchai Khon Kaen 15/04/2004 5,000.00 30/04/2004 5,000.00
E002 Sompong Sarakham 15/04/2004 4,500.00 30/04/2004 4,500.00
E003 Somchay Ubon 15/04/2004 5,200.00 30/04/2004 5,200.00
ENO PayDate Amount
E001 15/04/2004 5,000.00
E001 30/04/2004 5,000.00
E002 15/04/2004 4,500.00
E002 30/04/2004 4,500.00
E003 15/04/2004 5,200.00
E003 30/04/2004 5,200.00
ENO Name Province
E001 Somchai Khon Kaen
E002 Sompong Sarakham
E003 Somchay Ubon
15
Multi-Valued
Staff
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P04, P05 Voice ordering, Speech Coding
E004 SomSiri D02 Computer Science P04, P06 Voice ordering, Speech Synthesis
16
Multi-Valued
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01 NMR
E001 Somchai D01 Physic P02 Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P05 Voice ordering
E003 Somchay D02 Computer Science P04 Speech Coding
E004 SomSiri D02 Computer Science P04 Voice ordering
E004 SomSiri D02 Computer Science P06 Speech Synthesis
Insert Project still has problem
17
Second Normal Form(2NF)
  • A relation is in first normal form if and only if
  • It is in 1NF
  • Every non-key attribute is dependent on all parts
    of the primary key.

18
2NF ?
Staff
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01 NMR
E001 Somchai D01 Physic P02 Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P05 Voice ordering
E003 Somchay D02 Computer Science P04 Speech Coding
E004 SomSiri D02 Computer Science P04 Voice ordering
E004 SomSiri D02 Computer Science P06 Speech Synthesis
Answer is No. Because ProjNo is dependent on
ProjNo. (not all part of Key)
19
Problem
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01 NMR
E001 Somchai D01 Physic P02 Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P05 Voice ordering
E003 Somchay D02 Computer Science P04 Speech Coding
E004 SomSiri D02 Computer Science P04 Voice ordering
E004 SomSiri D02 Computer Science P06 Speech Synthesis
We can not insert Project if have not yet
assigned project to any employee
20
Solution
  • Remove the attribute involved
  • Take its determinant with it

21
Normalize
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01 NMR
E001 Somchai D01 Physic P02 Laser
E002 Sompong D01 Physic P03 Medical Image processing
E003 Somchay D02 Computer Science P05 Voice ordering
E003 Somchay D02 Computer Science P04 Speech Coding
E004 SomSiri D02 Computer Science P04 Voice ordering
E004 SomSiri D02 Computer Science P06 Speech Synthesis
22
Result
PERSON_Proj
Project
PERSON
ENO ProjNo
E001 P01
E001 P02
E002 P03
E003 P04
E004 P05
E004 P06
ProjNo ProjName
P01 NMR
P02 Laser
P03 Medical Image processing
P04 Speech Coding
P05 Voice ordering
P06 Speech Synthesis
ENO Name Dno DeptName
E001 Somchai D01 Physic
E003 Somchay D02 Computer Science
E004 SomSiri D02 Computer Science
PERSON(ENO,NAME,Dno,DeptName) PROJECT(ProjNo,ProjN
ame) PERSON_PROJ(ENO,ProjNo)
23
Third Normal Form
  • A relation is in 3NF if, and only if
  • It is in 2NF
  • Every non-key attribute is functionally dependent
    upon the key. (No non-key attribute is functional
    dependent on another non-key attribute)
  • Or non-key attribute no transitive dependent on
    key

24
Transitive dependent
  • R(A,B,C,D) A is Key, others are non- key
  • If A ? B and B ? C
  • can say
  • A ? B ? C (C transitive dependent on A)

25
3NF?
PERSON_Proj
Project
PERSON
ENO ProjNo
E001 P01
E001 P02
E002 P03
E003 P04
E004 P05
E004 P06
ProjNo ProjName
P01 NMR
P02 Laser
P03 Medical Image processing
P04 Speech Coding
P05 Voice ordering
P06 Speech Synthesis
ENO Name Dno DeptName
E001 Somchai D01 Physic
E003 Somchay D02 Computer Science
E004 SomSiri D02 Computer Science
Answer is No Because DeptName is dependent on Dno
(has transitive dependent on key)
26
Solution
  • Remove the offending attributes
  • Take the determinant along

27
Result
PERSON
ENO Name Dno
E001 Somchai D01
E003 Somchay D02
E004 SomSiri D02
PERSON_Proj
Project
ENO ProjNo
E001 P01
E001 P02
E002 P03
E003 P04
E004 P05
E004 P06
ProjNo ProjName
P01 NMR
P02 Laser
P03 Medical Image processing
P04 Speech Coding
P05 Voice ordering
P06 Speech Synthesis
Department
Dno DeptName
D01 Physic
D02 Computer Science
D02 Computer Science
28
Note
  • The third normal form is often reached in
    practice by inspection, in a single step. Its
    meaning seems intuitively clear it represents a
    formalization of designers common sense.
  • This level of normalization is widely accepted as
    the initial target for a design which eliminates
    redundancy.
  • However, there are higher normal forms which,
    although less frequently invoked, highlight
    further redundancy problems which may affect the
    designer

29
Boyce-Codd Normal Form (BCNF)
  • A relation is in BCNF if, and only if, every
    determinant is a candidate key.
  • BCNF is a refinement to third normal form, and
    tightens its duration.

30
Multivalued Dependence
  • In a relation including attribute A, B and C, B
    is multivalued dependent on A if the set of B
    values matching a given AC value pair, depends
    only on the A value.
Write a Comment
User Comments (0)
About PowerShow.com