ics321-20091112-fdnf -...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Schema
Refinement
&
Normal
Forms
 Asst.
Prof.

Lipyeow
Lim
 InformaCon
&
Computer
Science
Department
 University
of
Hawaii
at
Manoa
 11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 1
 ICS
321
Fall
2009
 The
Problem
with
Redundancy
 Hourly_Emps SSN
 123‐22‐2366
 231‐31‐5368
 131‐24‐3650
 434‐26‐3751
 612‐67‐4134
 Name
 AXshoo
 Smiley
 Smethurst
 Guldu
 Madayan
 Lot
 48
 22
 35
 35
 35
 Ra3ng
 8
 8
 5
 5
 8
 Hourly_wages
 10
 10
 7
 7
 10
 Hours_worked
 40
 30
 30
 32
 40
 Suppose
hourly
wages
are
determined
by
raCng
 Redundant
storage
:
(8,10)
stored
mulCple
Cmes
 Update
anomaly
:
change
hourly
wages
in
row
1
 Inser3on
anomaly
:
requires
knowing
hourly
wages
for
 the
raCng
 •  Dele3on
anomaly
:
deleCng
all
(8,10)
loses
info
 •  •  •  •  11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 2
 Hourly_Emps SSN
 Using
Two
Smaller
Tables
 Name
 Lot
 48
 22
 35
 35
 Ra3ng
 8
 8
 5
 5
 8
 Hours_ worked
 40
 30
 30
 32
 40
 5
 8
 RatingWages Ra3ng
 Hourly_ wages
 7
 10
 123‐22‐2366
 AXshoo
 231‐31‐5368
 Smiley
 434‐26‐3751
 Guldu
 612‐67‐4134
 Madayan
 131‐24‐3650
 Smethurst
 35
 •  Nota%on:

denote
relaCon
schema
by
lisCng
the
a]ributes
 SNLRWH
 •  Update
anomaly
:

Can
we
change
W
for
AXshoo?
 •  Inser3on
anomaly
:
What
if
we
want
to
insert
an
employee
 and
don’t
know
the
hourly
wage
for
his
raCng?
 •  Dele3on
anomaly
:
If
we
delete
all
employees
with
raCng
5,
 do
we
lose
the
informaCon
about
the
wage
for
raCng
5?
 11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 3
 Hourly_Emps SSN
 Name
 DecomposiCon
 Lot
 48
 22
 35
 35
 Ra3ng
 8
 8
 5
 5
 8
 Hours_ worked
 40
 30
 30
 32
 40
 RatingWages Ra3ng
 Hourly_ wages
 5
 8
 7
 10
 123‐22‐2366
 AXshoo
 231‐31‐5368
 Smiley
 434‐26‐3751
 Guldu
 612‐67‐4134
 Madayan
 131‐24‐3650
 Smethurst
 35
 •  Remove
redundancy
by
decomposiCon
 •  Pros:
less
redundancy
less
anomalies
 •  Cons:
retrieving
the
hourly
wage
of
an
employee
 requires
a
join
 11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 –  Since
hourly
wage
is
completely
determined
by
raCng,
 factor
out
hourly
wage.
 4
 FuncConal
Dependency
 •  A
funcConal
dependency
X
‐>
Y
holds
over
relaCon
R
if,
 for
every
allowable
instance
r
of
R:
 for
all
tuples
t1,t2 in r, πX(t1)
=
πX(t2)

implies

πY(t1)
=
πY(t2)
 –  i.e.,
given
two
tuples
in
r,
if
the
X
values
agree,
then
the
Y
 values
must
also
agree.

(X
and
Y
are
sets
of
a]ributes.)
 –  •  An
FD
is
a
statement
about
all
allowable
relaCons.
 –  –  Must
be
idenCfied
based
on
semanCcs
of
applicaCon.
 Given
some
allowable
instance
r1
of
R,
we
can
check
if
it
 violates
some
FD
f,
but
we
cannot
tell
if
f
holds
over
R!
 However,
K
‐>
R
does
not
require
K
to
be
minimal!
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 5
 •  K
is
a
candidate
key
for
R
means
that
K
‐>
R
 –  11/12/2009
 Hourly_Emps SSN
 123‐22‐2366
 231‐31‐5368
 131‐24‐3650
 434‐26‐3751
 612‐67‐4134
 Name
 AXshoo
 Smiley
 FD
Example
 Lot
 48
 22
 35
 35
 35
 Ra3ng
 8
 8
 5
 5
 8
 Hourly_wages
 10
 10
 7
 7
 10
 Hours_worked
 40
 30
 30
 32
 40
 Smethurst
 Guldu
 Madayan
 •  Two
FDs
on
Hourly_Emps:
 ssn
is
the
key:



S

‐>
SNLRWH

 –  ra%ng
determines
hourly_wages:



R
‐>
W
 –  11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 6
 Reasoning
about
FDs
 •  Given
some
FDs,
we
can
usually
infer
 addiConal
FDs:
 –  ssn ‐> did,

did ‐> lot implies



ssn ‐> lot •  Armstrong’s
Axioms

 –  Let
X,
Y,
Z
are
sets
of
a]ributes:
 –  Reflexivity:

If

X
is
a
subset
of
Y,

then


Y
‐>
X

 –  Augmenta%on:

If

X
‐>
Y,

then


XZ

‐>
YZ


for
any
Z
 –  Transi%vity:

If

X
‐>
Y

and

Y
‐>
Z,

then


X
‐>
Z
 •  These
are
sound
and
complete inference
rules
 for
FDs!
 11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 7
 Hourly_Emps SSN
 123‐22‐2366
 231‐31‐5368
 131‐24‐3650
 434‐26‐3751
 612‐67‐4134
 Example:
Armstrong’s
Axioms
 Name
 AXshoo
 Smiley
 Smethurst
 Guldu
 Madayan
 Lot
 48
 22
 35
 35
 35
 RaCng
 8
 8
 5
 5
 8
 Hourly_Wages
 10
 10
 7
 7
 10
 Hours_worked
 40
 30
 30
 32
 40
 •  •  •  Reflexivity:

If

X
is
a
subset
of
Y,

then


Y
‐>
X

 –  –  –  Augmenta%on:

If

X
‐>
Y,

then


XZ

‐>
YZ


for
any
Z
 S
‐>
N,
then
SLR
‐>
NLR
 SNLR
is
a
subset
of
SNLRWH,
SNLRWH
‐>
SNLR
 Transi%vity:

If

X
‐>
Y

and

Y
‐>
Z,

then


X
‐>
Z
 S
‐>
R,
R
‐>
W,
then
S
‐>
W
 11/12/2009
 Lipyeow
Lim
‐‐
University
of
Hawaii
at
Manoa
 8
 ...
View Full Document

This note was uploaded on 11/15/2010 for the course ICS 321 taught by Professor Lim during the Fall '09 term at University of Hawaii, Manoa.

Ask a homework question - tutors are online