Title: Functional Dependencies and Normalization
1Functional Dependencies and Normalization
2Normalization
- 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.
3Normalization (cont.)
Redundancy
Redundancy
Redundancy
4Normalization (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
5Functional 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
6Functional 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.
7First Normal Form
- A relation is in First Normal form if, and only
if, it contains no multi-value or no repeating
groups.
8First 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
9Problem
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
10Insert
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.
11UPDATE 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
12DELETE 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
13Solution
- Remove the repeating group
- In case of multi-valued
- Create new relation
- Columns Key multi-valued
- Take its determinant with it
14Repeating 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
15Multi-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
16Multi-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
17Second 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.
182NF ?
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)
19Problem
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
20Solution
- Remove the attribute involved
- Take its determinant with it
21Normalize
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
22Result
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)
23Third 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
24Transitive 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)
253NF?
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)
26Solution
- Remove the offending attributes
- Take the determinant along
27Result
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
28Note
- 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
29Boyce-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.
30Multivalued 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.