Chapter 19 The Evils of Redundancy Redundancy is at the root of several problems associated with relational schemas redundant storage insertdeleteupdate anomalies Integrity constraints in particular ID: 634374
Download Presentation The PPT/PDF document "Schema Refinement and Normal Forms" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Chapter 19
Schema Refinement and
Normal FormsSlide2
The Evils of Redundancy
Redundancy
is at the root of several problems associated with relational schemas:
redundant storage, insert/delete/update anomalies
(more on this later)Integrity constraints, in particular
functional dependencies
, can be used to identify schemas with such problems and to suggest refinements.
Main refinement technique:
decomposition
(replacing ABCD with, say, AB and BCD, or ACD and ABD).
Decomposition should be used judiciously:
Is there reason to decompose a relation?
What problems (if any) does the decomposition cause?Slide3
Functional Dependencies (FDs)
A
functional dependency
X
→ Y (read “X determines Y”) holds over relation R if, for every allowable instance
r
of R:
t1
r, t2
r,
X(t1) = X(t2) Y(t1) = Y(t2)Example: SSN → Name (If two tuples have the same SSN, they must also have the same name)
Given two tuples in r
if their X values agree
then their Y values must also agree
X and Y are sets of attributes
∏:
Relational projection operatorSlide4
Notations
Consider relation obtained from
Hourly_Emps:
Hourly_Emps (ssn
, name, lot, rating, hrly_wages, hrs_worked
)Notation:
We will denote this relation schema by listing the attributes:
SNLRWH
This is really the
set
of attributes {S,N,L,R,W,H}.
Sometimes, we will refer to all attributes of a relation by using the relation name. (e.g.,
Hourly_Emps
for SNLRWH)
Some FDs on Hourly_Emps:ssn determines the tuple: S → SNLRWH rating determines hrly_wages: R → WS N L R W HSlide5
Functional Dependencies (FDs)
An FD “X→Y” is a statement about
all
allowable instances of a relation.
Must be identified based on semantics of application.
Given some allowable instance of R, we can check if it violates some FD
f
, but we cannot tell if
f
holds over R!
“K is a candidate key for relation R” means that K
→
R
(
i.e., The K value determines a tuple in R)However, K → R does not require K to be minimal! (If A → R, we also have AB → R; but AB is not a candidate key)Slide6
Anomalies
Problems due to R
→
W :
Update anomaly
: Can we change W in just the 1st tuple of SNLRWH?
Insertion anomaly
:
What if we want to insert an employee and don’t know the hourly wage for his rating?
Deletion anomaly
:
If we delete all employees with rating 5, we lose the information about the wage for rating 5!
Using two smaller tables
solve the problems
Hourly_Emps2
WagesSlide7
Anomalies
Problems due to R
→
W :
Update anomaly
: Can we change W in just the 1st tuple of SNLRWH?
Insertion anomaly
:
What if we want to insert an employee and don’t know the hourly wage for his rating?
Deletion anomaly
:
If we delete all employees with rating 5, we lose the information about the wage for rating 5!
Using two smaller tables
solve the problems
Hourly_Emps2
Wages
This is the topic of this chapterSlide8
F
f
1
f
2
f
3
Reasoning About FDs
Given some FDs, we can usually infer additional FDs:
{
ssn
→
did
, did → lot } implies ssn → lotAn FD f is implied by a set of FDs
F if f holds whenever all FDs in F
hold.F+ = closure of F is the set of all FDs that are implied by F.F+
f’Implies
fImpliesf”
Implies
f
Closure of
FSlide9
Armstrong’s Axiom
Armstrong’s Axioms (X, Y, Z are sets of attributes):
Reflexivity
:
If X
Y, then Y → X
Augmentation
:
If X
→
Y, then X
Z
→
YZ for any ZTransitivity: If X → Y and Y → Z, then X → ZThese are sound and complete inference rules for FDs!
Couple of additional rules (that follow from AA):
Union: If X → Y and X →
Z, then X → YZDecomposition: If X → YZ, then X →
Y and X → ZSlide10
Reasoning About FDs - Example
Example:
Contracts(
c
id,
sid,jid,
d
id,
p
id,
q
ty,
v
alue
), and:C is the key: C → CSJDPQV (C is a candidate key)Project purchases each part using single contract: JP → CDept purchases at most one part from a supplier: SD → PJP → C, C → CSJDPQV imply JP → CSJDPQVSD → P implies SDJ → JPSDJ → JP, JP → CSJDPQV imply SDJ → CSJDPQV
These are also candidate keys
Contract ID
Supplier ID
Project IDDepartment IDPart ID
QuantitySlide11
Closure of a FD set
X
→
AB
…
F
+
=
closure of F
is the set of all FDs that are implied by
F
F
Explicit set
Implicit set
F
F
+f1f
2f3f
ImpliesSlide12
Attribute Closure
(≠ closure of a FD set)
X
X
→
AB
…
F
X
+
=
attribute closure
of an attribute set X
wrt
to a set of FD
F is the set of
all attributes A such that X
→
A is in F+
Any attribute whose value can be determined
from the value of X (according to F
+
)
R
X
+
X
X
→
AB
…
F
+Slide13
Computing Attribute Closure
Closure = X;
Repeat until there is no change {
if there is an FD
U
→
V
in
F
+
such that
U
closure, then set closure = closure V }If left-hand side is in the closure, add the right hand side to the closure
X+
U→V…
F
+U
VSlide14
Attribute Closure - Example
Relation ABCDEF with FD’s {AB→C, BC
→AD, D→
E, CF→B
}What is {A, B}+
?
Initially,
{
A, B
}
+
= {
A
,
B} AB→C {A, B}+ = {A, B, C} BC→AD {A, B}+ = {A, B, C, D} D→E {A, B}+ = {A, B, C, D, E}
CF→B
F is on the left-hand side, we cannot include F
B
{A,B}+A
C
Make sure to consider the closure of the FD set
F
F
+Slide15
Reasoning About FDs (Contd.)
Computing the closure of a set of FDs can be expensive.
Size of closure is exponential in # attributes!
Typically, we just want to check if a given X →
Y is in the closure of a set of FDs. An efficient check:Compute
X+ wrt
F
Check if
Y
is in
X
+
(i.e., Do we have X → Y ?)F
F
+
If Yes
X → Y ?
X
+
X
YSlide16
Normal Forms
Role of FDs in detecting redundancy:
Consider a relation
R
with 3 attributes, ABC
. Given A →
B:
Several tuples could have the same A value, and if so, they’ll all have the same B value - redundancy !
R
→
W Slide17
Normal Forms
Role of FDs in detecting redundancy:
Consider a relation
R
with 3 attributes, ABC
. Given A →
B:
Several tuples could have the same A value, and if so, they’ll all have the same B value - redundancy !
No FDs hold:
There is no redundancy here
No FD
No redundancy
Slide18
Normal Forms
Role of FDs in detecting redundancy:
Consider a relation
R
with 3 attributes, ABC
. Given A →
B:
Several tuples could have the same A value, and if so, they’ll all have the same B value - redundancy !
No FDs hold:
There is no redundancy here
Note
:
A
→
B potentially causes problems. However, if we know that no two tuples share the same value for A, then such problems cannot occur (a normal form)S → NLRH No redundancy because no two tuples have the same value for SSlide19
Normal Forms
Role of FDs in detecting redundancy:
Consider a relation
R
with 3 attributes, ABC
. Given A →
B:
Several tuples could have the same A value, and if so, they’ll all have the same B value - redundancy !
No FDs hold:
There is no redundancy here
Note
:
A
→
B potentially causes problems. However, if we know that no two tuples share the same value for A, then such problems cannot occur (a normal form)If a relation is in a certain normal form (BCNF, 3NF, etc.), it is known that certain kinds of problems are avoided/minimized. This can be used to help us decide whether decomposing the relation will help.Slide20
Boyce-
Codd
Normal Form (BCNF)
Relation
R is in
BCNF if, for all X → A
in
F,
A
X
(called a
trivial
FD), orX is a superkey (i.e., contains a key of R)X1. . .X7ABCX1.
. .X6
AX7C
XR
Trivial FD
X
R
In other words,
R
is in BCNF if the only non-trivial FDs that hold over
R
are key constraints (i.e.,
X
must be a
superkey
!)
R
A relation
F
The set of FD hold over
R
X
A subset
of the attributes of
R
A
An attribute of
R
X1
∙∙∙ X2 → ABC
a
superkeySlide21
BCNF is Desirable
Consider the relation:
X
→A
“X
→
A”
The 2
nd
tuple also has y2 in the third column
an example of redundancy
Such a situation cannot arise in a BCNF relation:
BCNF X must be a key We must have X→Y and X→A (X→YA) The two tuples are identical This situation cannot happen in a relation !!XYA
xy1
axy2?
Should be “a”
Not in BCNF !!Slide22
BCNF: Desirable Property
A relation is in BCNFevery entry records a piece of information that cannot be inferred (using only FDs) from the other entries in the relation instanceNo redundant information !
?
Must be “7” because we have R
→WSlide23
BCNF: Desirable Property
A relation is in BCNFevery entry records a piece of information that cannot be inferred (using only FDs) from the other entries in the relation instanceNo redundant information !
A relation
R
(A
BC)
B
→
C
:
The value of
B
determines
C
, and the value of C can be inferred from another tuple with the same B value redundancy ! (not BCNF)A→BC: Although the value of A determines the values of B and C, we cannot infer their values from other tuples because no two tuples in R have the same value for A no redundancy ! (BCNF)Key constraint is the only form of FDs allowed in BCNFSlide24
Third Normal Form (3NF)
Relation
R
is in
3NF
if, for all X
→
A
in
F
A
X (called a trivial FD), orX is a superkey (containing some key), orA is part of some key for R. Example: Relation Reserves(SBDC) with the FD C → SSame as in BCNF
RA relation
FThe set of FD hold over RXA subset of the attributes of R
AAn attribute of R
Sailor
Boat
Date
Credit CardSlide25
Third Normal Form (3NF)
Relation
R
is in
3NF
if, for all X
→
A
in
F
A
X (called a trivial FD), orX is a superkey (containing some key), orA is part of some key for R. Same as in BCNFRA relationFThe set of FD hold over RX
A subset of the attributes of R
AAn attribute of R
Minimality of a key is crucial hereIf A is part of some superkey, then this condition would be true for any relation (because we can add any additional attribute to the superkey to make a bigger superkey).Slide26
Third Normal Form (3NF)
Relation
R
is in
3NF
if, for all X
→
A
in
F
A
X (called a trivial FD), orX is a superkey (containing some key), orA is part of some key for R. Same as in BCNFRA relationFThe set of FD hold over RXA subset of the attributes of R
A
An attribute of RObservation:If R
is in BCNF, obviously in 3NF.If R is in 3NF, some redundancy is possible. It is a compromise, used when BCNF not achievable (e.g., no ``good’’ decomp, or performance considerations).
Discussed in next few pagesSlide27
Compromise - Two Cases
Suppose X→A
causes a violation of 3NF
X is not a superkey
(not in BCNF), andA is not part of a key (not in 3NF)
A
X
(called a
trivial
FD), or
X is a superkey (containing some key), orA is part of some key for R. 3NFSlide28
Compromise - Two Cases
Suppose X→A
causes a violation of 3NF
X is not a
superkey (not in BCNF), and
A is not part of a key (not in 3NF)Two cases:
CASE 1
:
X
is a
proper subset
of some key
K
(
partial dependency)CASE 2: X is not a proper subset of any key (transitive dependency because we have a chain of dependencies KEY → X → A)KEYXA
KEY
XA
A is not part of the keySlide29
Compromise – Case 1 Example
CASE 1: X is a proper subset of some key K (
partial dependency)
In this case we store (X,A) pairs redundantly
EXAMPLE
: Relation
Reserves
(
SBD
C
)
with FD
S
→ C We store the credit card number for a sailor as many times as there are reservations for that sailor.KEYXASailorBoat
Date
Credit CardSlide30
Compromise - Case 2 Example
CASE 2: X is not a proper subset of any key (transitive dependency
)EXAMPLE:
Hourly_Emps(
SNLRWH) with FD
R →W
(i.e., rating determines wage)
We have
S
→
R
→
W
(transitive dependency)
We cannot record a rating without knowing the hourly wage for that ratingThis condition leads to insertion, deletion, and update anomalies (see page 6)KEY (i.e., S)WRSlide31
Redundancy in 3NF
EXAMPLE
: Relation Reserves(SBD
C) with the FD’s
C →
S and
S
→
C
Reserves
relation is in 3NF. Nonetheless,
we store the credit card number for a sailor as many times as there are reservations for that sailor.
S is part of the key “C → S” does not violate 3NF
C → S
(i.e., Credit card uniquely identifies the sailor) CBD →
SBD (Augmentation axiom) CBD → SBD → SBDC (SBD is a key)
CBD is also a key of Reserves (Transitivity axiom) “S → C” does not violate 3NF
because C is part of the key
3NF is indeed a compromise relative to BCNFSlide32
Motivation for 3NF
3NF weakens the BCNF requirements just enough to ensure that
Lossless-join & dependency-preserving decomposition of a relation into a collection of 3NF relations always possible.
The above guarantee does not exist for BCNF relationsSlide33
Decomposition of a Relation Scheme
Suppose that relation
R
contains attributes
A1 ... An. A
decomposition of R consists of replacing
R
by two or more relations such that:
Each new relation scheme contains a subset of the attributes of
R
(and no attributes that do not appear in
R
), and
Every attribute of
R appears as an attribute in at least one of the new relations.ABC
C
DE
Decomposition
A B C D E
A B C
C D ESlide34
Decomposition of a Relation Scheme
Intuitively, decomposing
R
means we will store instances of the relation schemes produced by the decomposition, instead of instances of
R.
Example: We can decompose SNLRWH into
SNLRH
and
RW
S
N
L
R
W
H
S
NLRH
R
WSlide35
Decomposition Example
Decompositions should be used only when needed.
S
NLRWH
has FDs
S → SNLRWH and
R
→
W
R
→
W causes violation of 3NF; W values repeatedly associated with R values. Solution: Decompose SNLRWH into SNLRH and RW If we just store the projections of SNLRWH tuples onto SNLRH and RW, we might have duplicates in RWSlide36
Lossless Join Decompositions
Decomposition of
R
into
X and Y
is lossless-join w.r.t. a set of FDs F
if, for every instance
r
that satisfies F, we have
X
(r)
Y
(r) = r
It is always true that r X(r) Y(r) However, the other direction might not hold! If it does, the decomposition is lossless-join.
Join two
tables on
B
NOT
LOSSLESS JOIN
We can reconstruct
rSlide37
Problems with Decompositions
There are three potential problems to consider:
Some
queries
become more expensive. e.g., How much did sailor Joe earn? (salary = W
∙
H)
Given instances of the decomposed relations, we may not be able to
reconstruct
the corresponding instance of the original relation!
Fortunately, not in the SNLRWH example.
Checking some
dependencies
may require joining the instances of the decomposed relations.Fortunately, not in the SNLRWH example.Tradeoff: Must consider these issues vs. redundancy.decompose SNLRWH into SNLRH and RW Lossless JoinDependency PreservingSlide38
Lossless Join Decompositions
Definition extended to decomposition into 3 or more relations in a straightforward way.
It is essential that all decompositions used to deal with redundancy be
lossless
! (Avoids Problem in the last page)
Slide39
More on Lossless Join
The decomposition of
R
into
X and Y
is lossless-join wrt F
if and only if
the closure of F contains:
X
Y
→
X, or
X
Y → YExample:Decompose SNLRWH into SNLRH and RW
The intersection must be the primary key of X or Y
Foreign key
The intersection “R” is the primary
key of the 2nd relationSlide40
Given U→
V, we create the first table as UVRemove V from the original table (i.e.,
R-
V) to create the second table
Loss-Less Join Decomposition
the decomposition of R into UV
and
R
-
V
is lossless-join if
U
→
V
holds over R.U
V
U
V
T
U
T
Foreign key
Primary keySlide41
Dependency Preserving Decomposition
Consider
C
SJDPQV
, C is key,
JP →
C
and
SD
→
P
.
BCNF decomposition:
CSJDQV and SDPProblem: Checking JP → C requires a join!CSJDPQV
C
S
J
DQVSD
P
BCNF
decomposition
Checking
JP→C
requires a joinSlide42
Dependency Preserving Decomposition
If
R
is decomposed into
X
,
Y
and
Z
, and we enforce the FDs that hold on
X
, on
Y
and on
Z, then all FDs that were given to hold on R must also hold.RXY
Z
F
F1F2F3
→
Avoids Problem in last pageSlide43
Dependency Preserving Decomposition
If
R
is decomposed into
X
,
Y
and
Z
, and we enforce the FDs that hold on
X
, on
Y
and on
Z, then all FDs that were given to hold on R must also hold.→ Avoids Problem in last page
R
XY
FF1F2Slide44
Projection of a Set of FDs
If
R
is decomposed into
X, ... projection of
F onto X (denoted F
X
) is the set of FDs
U
→
V
in
F+ such that U, V are in Xi.e., FX is the set of FDs in F+, that involve only attributes in X Important to consider F +, not F, in this definition (e.g., B→CD is in FBCD)
R
(
A, B, C,
D, E) F: Set of FD’s
F
+
B→CD
F
B→C
A→B
B→D
F
BCD
Projection of
F
onto the relation
BCDSlide45
Decomposition of
R
into
X
and Y is dependency
preserving if (F
X
F
Y
)
+
= F+We need to enforce only the dependencies in FX and FY ; and all FDs in F+ are then sure to be satisfied
R
X
Y
F
+
F
X
F
Y
R
X
Y
F
+
F
X
F
Y
Dependency Preserving Decompositions
(Contd.)Slide46
Dependency Preserving Decompositions
(Contd.)
Decomposition of
R
into X and Y
is
dependency
preserving
if
(
F
X
FY )+ = F+We need to enforce only the dependencies in FX and FY ; and all FDs in F+ are then sure to be satisfiedTo enforce FX , we need to examine only relation X on inserts to that relation. To enforce FY , we need to examine only relation Y.
R
X
Y
F
+
F
X
F
YSlide47
Dependency Preserving Decomposition
vs. Lossless Join Decomposition
Dependency preserving does not imply lossless join:
ABC
, with
F = {A
→
B
}, decomposed into
AB
and
BC
.
F
AB = {A→B} and FBC = (FAB FBC )+ = {A→B} = F+ The decomposition is dependency preserving. Nonetheless, it is not a lossless-join decomposition !And vice-versa! Consider
CSJDPQV, C
is key, JP → C and SD →P.BCNF decomposition: C
SJDQV and SDPProblem: Checking JP → C requires a join!
The intersection “B” is not a key of either relation
Lossless Join decomposition
Not dependency preserving !Slide48
Dependency Preserving Decompositions:
Example
ABC
,
A
→B, B
→
C
,
C
→
A
, decomposed into
AB
and BC. F+ = F { A→C, B→A, C→B }If we consider only F (instead of F+) FAB = {A→B} and FBC = {B→C}
(FAB
FBC )+ = {A→B, B→C, A→C} ≠ F
+ not dependency preserving ??We need to examine F+ (not F) when computing FAB & FBC
FAB = {A→B, B→A}
and FBC = {B
→
C
,
C
→
B
}
F
AB
F
BC
= {
A
→
B,
B
→
A
,
B
→
C
,
C
→
B
}
(
F
AB
F
BC
)
+
= {
A
→
B,
B
→
A
,
B
→
C
,
C
→
B
,
A
→
C
,
C
→
A
} =
F
+
The decomposition preserves the dependencies !!
Decomposition of
R
into
X
and
Y
is
dependency
preserving
if
(
F
X
F
Y
)
+
=
F
+Slide49
Decomposition into BCNF
Consider relation
R
with FDs
F. If X
→Y violates BCNF, decompose R into
R
-
Y
and
X
Y
.
Repeated application
of this idea will give us a collection of relations that are in BCNF; lossless join decomposition, and guaranteed to terminate.Example: CSJDPQV, key C, JP →C, SD →P, J →SCSJDPQVSDPCSJDQV
J
S
CJDQV
SD →P
J →S
Keep SD as a foreign key to facilitate JOINSlide50
Decomposition into BCNF
In general, several dependencies may cause violation of BCNF. The order in which we ``deal with’’ them could lead to very different sets of relations!
C
SJDPQV
J
S
SD
P
C
SJDQV
C
JDQV
SD
→
P
J
→
SSlide51
In general, there may not be a dependency preserving decomposition into BCNF
BCNF & Dependency Preservation
Example 2
: Decomposition of
CSJDQV
into
SDP, JS,
and
CJDQV
is not dependency preserving (
w.r.t
. the FDs
JP
→
C, SD → P, and J → S).This is a lossless join decomposition. However JP → C is not preserved Adding JPC to the collection of relations gives us a dependency preserving decomposition.JPC tuples stored only for checking FD! (Redundancy!)C
SJDPQV
JSSDP
CSJDQVCJDQV
SD
→
P
J
→
S
JP
→
C
?Slide52
Decomposition into 3NF
To ensure dependency preservation, one idea:
If
X
→ Y is not preserved, add relation XY.
Problem is that
XY
may violate 3NF!
Example: In the following decomposition, adding
JPC
to preserve
JP
→
C does not work if we also have J→ C.Refinement: Instead of the given set of FDs F, use a minimal cover for F (another form of F).CSJDPQVJS
SD
PCSJDQV
CJDQV
SD →P
J
→
S
JP
C
JP
→
C
Does not work if we also have
J
→
CSlide53
Minimal Cover for a Set of FDs
Minimal cover
G
for a set of FDs F is a set of FDs such that
Closure of F = closure of G.
Right hand side of each FD in
G
is a single attribute (e.g.,
ACD
→
E
).
If we modify G by deleting an FD or by deleting attributes from an FD in G, the closure changes (i.e., it is minimal).Intuitively, every FD in G is needed, and ``as small as possible’’ in order to get the same closure as F.Slide54
Minimal Cover: Example
A
→
B
,
EF
→
GH
,
ABCD
→
E, ACDF→EG Compute minimal coverInitial Set = {A→ B, EF→G, EF→H, ABCD → E, ACDF→E, ACDF→G}
We keep A→
B, EF→G, and EF→H because they are minimal and cannot be inferred from the other FDs in the initial setSlide55
Minimal Cover: Example
A
→
B
, EF→
GH
,
ABCD
→
E
,
ACDF→EG Compute minimal coverInitial Set = {A→ B, EF→G, EF→H, ABCD → E, ACDF→E, ACDF→G} We keep A→
B,
EF→G, and EF→H because they are minimal and cannot be inferred from the other FDs in the initial setWe can remove B from
ABCD → E because A→ B
AACD → BACD → E ACD →
E We also have ACD →
E
ABCD
→
E
We use
ACD
→
E
instead of
ABCD
→
E
Slide56
Minimal Cover: Example
A
→
B
, EF→
GH
,
ABCD
→
E
,
ACDF→EG Compute minimal coverInitial Set = {A→ B, EF→G, EF→H, ABCD → E, ACDF→E, ACDF→G} We keep A→
B,
EF→G, and EF→H because they are minimal and cannot be inferred from the other FDs in the initial setWe can remove B from
ABCD → E because A→ B
AACD → BACD → E ACD →
E We also have ACD →
E
ABCD
→
E
We do not keep
ACDF
→
E
because we already keep
ACD
→
E
(i.e.,
F
can be removed)
We use
ACD
→
E
instead of
ABCD
→
E
Slide57
Minimal Cover: Example
A
→
B
,
EF
→
GH
,
ABCD
→
E, ACDF→EG Compute minimal coverInitial Set = {A→ B, EF→G, EF→H, ABCD → E, ACDF→E, ACDF→G} We keep
A→
B, EF→G, and EF→H because they are minimal and cannot be inferred from the other FDs in the initial set
We can remove B from ABCD → E because
A→ B AACD → BACD → E
ACD → E We also have
ACD
→
E
ABCD
→
E
We do not keep
ACDF
→
E
because we already keep
ACD
→
E
(i.e.,
F
can be removed)
We do not keep
ACDF
→
G
because we already keep
ACD
→
E
ACD
→
E
ACDF
→
EF
→
G
ACDF
→
G
A minimal Cover for
F
is {
A
→
B
,
EF
→
G
,
EF
→
H, ACD
→
E
}
We use
ACD
→
E
instead of
ABCD
→
E
Slide58
Minimal Cover: Example
A
→
B
,
EF
→
GH
,
ABCD
→
E, ACDF→EG Compute minimal coverInitial Set = {A→ B, EF→G, EF→H, ABCD → E, ACDF→E, ACDF→G} We keep
A→
B, EF→G, and EF→H because they are minimal and cannot be inferred from the other FDs in the initial set
We can remove B from ABCD → E because
A→ B AACD → BACD → E
ACD → E We also have
ACD
→
E
ABCD
→
E
We do not keep
ACDF
→
E
because we already keep
ACD
→
E
(i.e.,
F
can be removed)
We do not keep
ACDF
→
G
because we already keep
ACD
→
E
ACD
→
E
ACDF
→
EF
→
G
ACDF
→
G
A minimal Cover for
F
is {
A
→
B
,
EF
→
G
,
EF
→
H, ACD
→
E
}
We use
ACD
→
E
instead of
ABCD
→
E
Slide59
Minimal Cover: AlgorithmPut FDs in the standard form: Obtain a collection
G of equivalent FDs with a single attribute on the right side (i.e., the initial set)Minimize the left side of each FD: For each FD in G, check each attribute in the left side to determine if it can be deleted while preserving equivalence to
F+
Delete redundant FDs: Check each remaining FD in G to determine if it can be deleted while preserving equivalence to
F+Slide60
Lossless-Join Dependency-Preserving Decomposition into 3NF
Compute a minimal cover F of the original set of FDsObtain a lossless-join decomposition: R
1, R
2, … Rn
such that each one is in 3NF
Determine the projection of F onto each Ri
, i.e.,
F
i
Identify the set
N
of FDs in F that is not preserved,
i.e.
, not included in (F1F2 … Fn)+For each FD X→A in N, create a relation schema XA and add it to the result setOptimization: If N contains {X→A1, X → A2, … X → Ak}, replace them with X → A
1A2…
Ak to produce only one relationSlide61
Lossless-Join & Dependency-Preserving 3NF ExampleA minimal cover:
F = {SD →
P, J →
S, JP→
C}F
SDP = {SD
→
P
},
F
JS
= {
J
→S} (FSDP FJS)+ = {SD →P, J →S}JP→C is not in (FSDP FJS)+
add relation
JPC(SDP, CJDQV, JS, and JPC) is a lossless-join dependency preserving 3NF decomposition CSJDPQV
JSSDP
CSJDQVCJDQV
SD
→
P
J
→
S
JP
C
JP
→
CSlide62
Good News !
A decomposition into 3NF relations that is lossless-join and dependency-preserving is always possibleSlide63
Refining an ER Diagram
1st diagram translated:
Workers(
S,N,L,D,Si
)
Departments(D,M,B)
Lots associated with workers.
Suppose all workers in a
dept
are assigned the same lot: D
→
L
Redundancy; fixed by:
Workers2(
S,N,D,Si) Dept_Lots(D,L)Can fine-tune this: Workers2(S,N,D,Si) Departments(D,M,B,L)
lot
mname
budget
did
since
name
Works_In
Departments
Employees
ssn
Before:
lot
mname
budget
did
since
name
Works_In
Departments
Employees
ssn
After:
Refining
CombinedSlide64
Schema Refinement
Should a good ER design not lead to a collection of relations free of redundancy problems ?ER design is a complex and subjective processSome FDs as constraints are not expressible in terms of ER diagrams Decomposition of relations produced through ER design might be necessarySlide65
Summary of Schema Refinement
If a relation is in BCNF, it is free of redundancies that can be detected using FDs. Thus, trying to ensure that all relations are in BCNF is a good heuristic.
If a relation is not in BCNF, we can try to decompose it into a collection of BCNF relations.
Must consider whether all FDs are preserved.
If a lossless-join, dependency preserving decomposition into BCNF is not possible (or unsuitable, given typical queries), should consider decomposition into 3NF.
Decompositions should be carried out and/or re-examined while keeping
performance requirements
in mind.Slide66
Overview of Query Evaluation
Chapter 12
Spring 2020Slide67
Access Path
Database
A
ccess
path
Matching Other terms
An access path is a method of retrieving candidate tuples:
Examples
: File scan, or index that matches a selection (in the query)
Apply any remaining terms that do not match the index to disqualify some of the candidate tuples
This step does not incur I/
OsSlide68
Similarly, (1) a hash index on <
bid
,
sid
> could be used; (2)
day < 8/9/94
must then be checked
against the candidates
already bought into memory
.
Access Path Example
Query
:
day < 8/9/94 AND bid=5 AND sid=3.Database
B
+ tree on“day
”
Check “
bid
” and “
sid
”
1
2
3
The predicate has 3 terms
A B
+
-tree index on
day
can be used
Then,
bid
=5 and
sid
=3 are checked for each retrieved tuple
1
2
3Slide69
Access Path: Goal
Database
A
ccess
path
Matching other terms
Goal
: Find the most selective access path to retrieve candidate tuples
i.e.,
Find an index or file scan that require the fewest page I/
Os
.Slide70
Simple Nested-Loops Join
1
2
M
.
.
.
R
1
2
N
.
.
.
S
1 page
1
.
.
.
.
.
.
Scan S to look for matches
I/O
Expensive to scan table
S
M times
R
S
JOIN the two pages
Memory
One page at a time
Need to scan S, one page at a time
Cost = M + M·NSlide71
Block Nested-Loops Join
R
S
JOIN the two pages
Memory
Need to scan S M time
S
JOIN
Memory
Bring in R 3 pages at a time
Need to scan S only
times
R
1
2
3
4
If we have 5 pages available
Simple Nested-Loop
Join
Block Nested-Loop JoinSlide72
Join: Index Nested Loops (1)
If there is an index on the join column of one relation (say S), can make it the inner and exploit the index.
Cost: M + ( (M*
pR)
cost of finding matching S tuples)
for each tuple
r
in
R
do
for each tuple
s
in
S
where ri == sj do add <r,
s
> to result
12
M.
..
R
1
2
N
.
.
.
S
1
Index
Fetch into
memory
p
r
tuples/page
→
R has
(
M∙p
R
)
tuples
1 page
R
is the outer relation
S
is the inner relation
Number of tuples in RSlide73
Join: Index Nested Loops (2)For each R
tuple, cost of probing S index (i.e., finding data entry) is about 1.2 for hash index, 2-4 for B+ tree.
Probing
Data
entries
Data Records
Key
Typically 2~4 levels
1.2 I/O
Data entriesSlide74
Join: Index Nested Loops (3)For each
R tuple, cost of probing S index (i.e., finding data entry) is about 1.2 for hash index, 2-4 for B+ tree. Cost of then
finding S
tuples (assuming Alt. (2) or (3) for data entries) depends on clustering.
Clustered index: 1 I/O (typical), Unclustered: up to 1 I/O per matching S tuple
.
1
2
M
.
.
.
R
1
Index
1
2
N
.
.
.
S
p
r
tuples/page
Fetch into
memory
1 pageSlide75
Query Processor
SQL Query
Plan Generator
Plan Cost
Estimator
Evaluation
plan
Query Parser
Catalog Manager
Query Plan EvaluatorSlide76
Cost Estimation
For each plan considered, must estimate cost:
Must
estimate
cost
of each operation in plan tree.
Depends on input cardinalities.
We’ve already discussed how to estimate the cost of operations (sequential scan, index scan, joins, etc.)
Must also
estimate
size of result
for each operation in tree!
Use information about the input relations
For selections and joins, assume independence of predicatesSlide77
Schema for Examples
Sailors (
sid
: integer
,
sname
: string,
rating
: integer,
age
: real)
Reserves (
sid
: integer,
bid: integer, day: dates, rname
: string)
.
.
.
Tuple is 50 bytes
80 tuples/page
500 pages
Sailors
.
.
.
Tuple is 40 bytes
100 tuples/page
1000 pages
ReservesSlide78
Plan Generation – Relational Algebra
SELECT
S.sname
FROM Reserves R, Sailors S
WHERE
R.sid
=
S.sid
AND
R.bid
=100 AND
S.rating
>5
sname( sid
=sid
bid=100 rating>5 (RS))
FROM clause
WHERE clause SELECT clause Slide79
Plan Generation – Relational Algebra
SELECT
S.sname
FROM Reserves R, Sailors S
WHERE
R.sid
=
S.sid
AND
R.bid
=100 AND
S.rating
>5
sname(
sid=sid
bid=100 rating>5 (RS))=
sname( bid=100 rating>5 ( sid=
sid (RS)))= sname( bid=100
rating>5 (R
S))
FROM clause
WHERE clause
SELECT clause Slide80
On-the-fly Processing
sname
(
bid=100
rating>5
(R
S))
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
(On-the-fly)
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
Temp
Save
intermediate result
Read intermediate resultSlide81
Plan Generation -
Execution Plan
SELECT
S.sname
FROM Reserves R, Sailors S
WHERE
R.sid
=
S.sid
AND
R.bid
=100 AND
S.rating
>5
sname
( bid=100 rating>5 (R
S))
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
RA Tree
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
(Simple Nested Loops)
(On-the-fly)
(On-the-fly)
Plan
Slide82
Motivating Example
Cost: 500 + 500
1000 I/
Os
By no means the worst plan!
SELECT
S.sname
FROM Reserves R, Sailors S
WHERE
R.sid
=
S.sid
AND
R.bid=100 AND
S.rating
>5
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
RA Tree
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
(Simple Nested Loops)
(On-the-fly)
(On-the-fly)
Plan
(1,000 pages)
(500 pages)
Outer relationSlide83
Motivating Example
Cost: 500 + 500
1000 I/
Os
By no means the worst plan!
Misses several opportunities: selections could have been `pushed’ earlier, no use is made of any available indexes, etc.
Goal of optimization:
To find more efficient plans that compute the same answer.
SELECT
S.sname
FROM Reserves R, Sailors S
WHERE
R.sid
=S.sid AND
R.bid=100 AND S.rating>5
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
RA Tree
Reserves
Sailors
sid=sid
bid=100
rating > 5
sname
(Simple Nested Loops)
(On-the-fly)
(On-the-fly)
Plan
(1,000 pages)
(500 pages)
Outer relationSlide84
Alternative Plan 1
(No Indexes)
If we use Block Nested Loop join,
join cost = 10+(4
250),
total cost = 2770
.
Reserves
Sailors
sid=sid
bid=100
sname
(On-the-fly)
rating > 5
(Scan;
write to
temp T1)
(Scan;
write to
temp T2)
(Block Nested Loop Join)
Scan T2 for every 3 pages of T1
→ Since T1 has 10 pages, need to scan T2
or 4 times
T1 has 10 pages
T2
JOIN
Memory
Bring in T1 3 pages at a time
Need to scan T2
, or 4 times
T1
1
2
3
4
Only 5 pages available
(1,000 pages)
T1 has 10 pages
(500 pages)
T2 has 250 pagesSlide85
Push Projections
If we `push’ projections, T1 has only
sid
, T2 only
sid
and
sname
:
Reserves
Sailors
sid=sid
bid=100
sname
(On-the-fly)
rating > 5
(Scan;
write to
temp T1)
(Scan;
write to
temp T2)
(Block Nested Loop Join)
sid
Sid,
sname
Reserves
Sailors
sid=sid
bid=100
sname
(On-the-fly)
rating > 5
(Scan;
write to
temp T1)
(Scan;
write to
temp T2)
(Block Nested Loop Join)Slide86
Alternative Plan 1
(No Indexes)
If we `push’ projections, T1 has only
sid
, T2 only
sid
and
sname
:
T1 fits in 3 pages, need to scan T2 only once
cost of BNL drops to under 250 pages,
total cost < 2000.
Reserves
Sailors
sid=sid
bid=100
sname
(On-the-fly)
rating > 5
(Scan;
write to
temp T1)
(Scan;
write to
temp T2)
(Block Nested Loop Join)
sid
Sid,
sname
T1
T2
JOIN
Memory
T1 fits in 3 pages
Need to scan the smaller T2 only once
T1Slide87
Alternative Plan 2 (with Indexes)
Reserves has 100,000 tuples in 1000 pages (100 tuples per page).
There are 100 boats,
equally
popular.
With
clustered
index on
bid
of
Reserves
, we get 1000 tuples (= 100,000/100) in 10 pages (= 1,000/100).
INL with
pipelining
(outer is not materialized). Join column sid is a key for Sailors.At most one matching tuple, unclustered hash index on sid OK.
Cost: Selection of Reserves tuples costs
1.2+10 I/Os. For each of 1,000 Reserve tuples, must get matching Sailors tuple resulting
in 10001.2 = 1200 I/
Os (plus 1,000 more I/Os if Sailors stored as alternative 2).
Reserves
Sailors
sid=sid
bid=100
sname
(On-the-fly)
rating > 5
(Index Nested Loops,
with pipelining )
(On-the-fly)
(
unclustered
hash Index on
sid
OK)
Use clustered
hash index; do not write result to disk
(1,000 pages or
100,000 tuples)
1000 tuples in 10 pages
1000 x 1.2 I/
OsSlide88
Alternative Plan 2 (with Indexes)
Reserves has 100,000 tuples in 1000 pages (100 tuples per page).
There are 100 boats,
equally
popular.
With
clustered
index on
bid
of
Reserves
, we get 1000 tuples (= 100,000/100) in 10 pages (= 1,000/100).
INL with
pipelining
(outer is not materialized). Join column sid is a key for Sailors.At most one matching tuple, unclustered hash index on sid OK.
Cost: Total = (1.2 + 10) + 1200 + 1,000
2,211 IO’s
Reserves
Sailors
sid=sid
bid=100
sname
(On-the-fly)
rating > 5
(Index Nested Loops,
with pipelining )
(On-the-fly)
(
unclustered
hash Index on
sid
OK)
Use clustered
hash index; do not write result to disk
(1,000 pages or
100,000 tuples)
1000 tuples in 10 pages
1000 x 1.2 I/
OsSlide89
Summary
There are several alternative evaluation algorithms for each relational operator.
A query is evaluated by converting it to a tree of operators and evaluating the operators in the tree.
Must understand query optimization in order to fully understand the performance impact of a given database design (relations, indexes) on a workload (set of queries).
Two parts to optimizing a query:
Consider a set of alternative plans.
Must prune search space; typically, left-deep plans only.
Must estimate cost of each plan that is considered.
Must estimate size of result and cost for each plan node.
Key issues
: Statistics, indexes, operator implementations.
Left-deep plan