4bb07functional dependencies
TRANSCRIPT
-
8/8/2019 4bb07functional Dependencies
1/33
Functional Dependencies and
Normalization
-
8/8/2019 4bb07functional Dependencies
2/33
E-R model gives table structure of the database.
Table structure given by E-R model may be or may not
be good. i.e. tables created by E-R model may have
redundancies and various anomalies.
So, how do we recognize a bad database
design or good database design?
-
8/8/2019 4bb07functional Dependencies
3/33
Normalization
Thus, Normalization is the process of refining the data
structure built by E-R model.
-
8/8/2019 4bb07functional Dependencies
4/33
Normalization takes a relation schema through a series of
tests to certify whether it satisfy a certain normal form.
1 NF
2NF
3NF
BOYEE and CODD
4NF
5NF
-
8/8/2019 4bb07functional Dependencies
5/33
Minimizing the redundancy
Removing the insertion, deletion and update anomalies.
Normalization is a formalized procedure to eliminatingredundancy from data by the progressive use of non-
lose decomposition, which involves splitting records
without losing information
Normalization
-
8/8/2019 4bb07functional Dependencies
6/33
(cont.)
Normalization is based on the idea that an attributemay depend on another attribute in some way.
There are 2 different kinds of dependencies involvedup to 5NF
Functional dependency
Multivalued dependence
-
8/8/2019 4bb07functional Dependencies
7/33
Functional Dependence
In a relation including attribute A and B,B is functional dependent on A if,
for every valid occurrence, the value A determinesthe value B
A and B can be composite
If B is Functional Dependent on A, then A is thedeterminant of B
All fields are functionally dependent on the primary key or indeed any candidate key.
-
8/8/2019 4bb07functional Dependencies
8/33
Let X, Y be two subsets of relation schema R then if the given value of X,
there is only one value ofY , then Y is said to Functionally dependent on
X, or
X functionally determines Y.
X Y
i.e. functional dependencies is a constraints between two subsets of
attributes of relation schema R.
If X Y hold in R, we can not say that whetherY X holds in R or
not.
-
8/8/2019 4bb07functional Dependencies
9/33
Interference Rule forFunctional Dependency
Let F be the given set ofFunctional Dependencies specified on
Schema R.
1. Augmentation Rule:
if ZY holds in R and X is a set of attributes from R then XZXY
holds.
2. Transitive Rule:
if XY andY Z holds in R, then X Z also holds.
3. Union or Additive Rule:
if X Y and X Z hold in R , then X YZ holds in R.
4. Decomposition or Projective Rule:
if X YZ hold in R , then X Y or X Z holds in R.
-
8/8/2019 4bb07functional Dependencies
10/33
5. Pseudotransitive Rule:
if X Y and ZY W holds in R then ZX W holds in R.
-
8/8/2019 4bb07functional Dependencies
11/33
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
S#,P# QTY QTY is functionally dependent on S#andP#
S# and P# are the determinant of QTY
-
8/8/2019 4bb07functional Dependencies
12/33
First Normal Form
A relation is in First Normal form if, and onlyif, it contains no multi-value or no repeating
groups.
-
8/8/2019 4bb07functional Dependencies
13/33
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
Repeat
-
8/8/2019 4bb07functional Dependencies
14/33
Problem
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Imageprocessing
E003 Somchay D02 Computer
Science
P04, P05 Voice ordering, Speech
Coding
E004 SomSiri D02 Computer
Science
P04, P06 Voice ordering, Speech
Synthesis
Multi-value
Problem1. Difficult to manipulate data2. Redundancy
UPDATE ANOMALIES
Staff
-
8/8/2019 4bb07functional Dependencies
15/33
Insert
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Imageprocessing
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 toany employee yet.
P06 Speech Corpus
Staff
-
8/8/2019 4bb07functional Dependencies
16/33
UPDATE ANOMALIES
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Imageprocessing
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 Orderingneed to change all in Database
Staff
-
8/8/2019 4bb07functional Dependencies
17/33
DELETE Problem
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01, P02 NMR, Laser
E002 Sompong D01 Physic P03 Medical Imageprocessing
E003 Somchay D02 Computer
Science
P04, P05 Voice ordering, Speech
Coding
E004 SomSiri D02 Computer
Science
P04, P06 Voice ordering, Speech
Synthesis
Delete Employee E002 SompongProject P03 Medical Image Processing was deleted also
Staff
-
8/8/2019 4bb07functional Dependencies
18/33
Solution
Remove the repeating group
In case of multi-valued Create new relation
Columns = Key + multi-valued
-
8/8/2019 4bb07functional Dependencies
19/33
Repeating group
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 Name Province
E001 Somchai Khon Kaen
E002 Sompon
g
Sarakham
E003 Somcha
y
Ubon
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
Employee
Employee
PayCheck
-
8/8/2019 4bb07functional Dependencies
20/33
Multi-Valued
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
Staff
-
8/8/2019 4bb07functional Dependencies
21/33
Multi-Valued
ENO Name Dno DeptName ProjNo ProjName
E001 Somchai D01 Physic P01 NMR
E001 SomchaiD01
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
-
8/8/2019 4bb07functional Dependencies
22/33
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.
-
8/8/2019 4bb07functional Dependencies
23/33
-
8/8/2019 4bb07functional Dependencies
24/33
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 yetassigned project to any employee
-
8/8/2019 4bb07functional Dependencies
25/33
Solution
Remove the attribute involved
Take its determinant with it
-
8/8/2019 4bb07functional Dependencies
26/33
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
-
8/8/2019 4bb07functional Dependencies
27/33
Result
ENO Name Dno DeptNa
me
E001 Somchai D01 Physic
E003 Somchay D02 Computer Science
E004 SomSiri D02 Computer Science
Proj
No
ProjName
P01 NMR
P02 Laser
P03 Medical Image
processing
P04 Speech Coding
P05Voice ordering
P06 Speech Synthesis
ProjectPERSON
ENO ProjN
o
E001 P01
E001 P02
E002 P03
E003 P04
E004 P05
E004 P06
PERSON_Proj
PERSON(ENO,NAME,Dno,DeptName)
PROJECT(ProjNo,ProjName)
PERSON_PROJ(ENO
,P
rojN
o)
-
8/8/2019 4bb07functional Dependencies
28/33
Third Normal Form
A relation is in 3NF if, and only if:
It is in 2NF
Every non-key attribute is functionallydependent upon the key. (No non-keyattribute is functional dependent on another
non-key attribute)
Or non-key attribute no transitive dependenton key
-
8/8/2019 4bb07functional Dependencies
29/33
-
8/8/2019 4bb07functional Dependencies
30/33
3NF?
ENO Name Dno DeptNa
me
E001 Somchai D01 Physic
E003 Somchay D02 Computer Science
E004 SomSiri D02 Computer Science
Proj
No
ProjName
P01 NMR
P02 Laser
P03 Medical Image
processing
P04 Speech Coding
P05Voice ordering
P06 Speech Synthesis
ProjectPERSON
ENO ProjN
o
E001 P01
E001 P02
E002 P03
E003 P04
E004 P05
E004 P06
PERSON_Proj
Answer is NoBecause DeptName is dependenton Dno (has transitive dependent
on key)
-
8/8/2019 4bb07functional Dependencies
31/33
Solution
Remove the offending attributes
Take the determinant along
-
8/8/2019 4bb07functional Dependencies
32/33
Result
ENO Name Dno
E001 Somchai D01E003 Somchay D02
E004 SomSiri D02
ProjNo
ProjName
P01 NMR
P02 Laser
P03 Medical Image
processing
P04 Speech Coding
P05 Voice ordering
P06 Speech Synthesis
Project
PERSON
ENO ProjNo
E001 P01
E001 P02
E002 P03
E003 P04E004 P05
E004 P06
PERSON_Proj
Dno DeptName
D01 Physic
D02 Computer
Science
D02 Computer
Science
Department
-
8/8/2019 4bb07functional Dependencies
33/33
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.