1 design theory. 2 overview starting point: set of functional dependencies that describe real-world...
Post on 20-Dec-2015
214 views
TRANSCRIPT
2
OverviewOverview
• Starting Point: Set of functional dependencies that describe real-world constraints
• Goal: Create tables that do not contain redundancies, so that– there is less wasted space
– there is less of a chance to introduce errors in the database
3
Design TheoryDesign Theory
• Armstrong's axioms defined, so that
we can derive functional dependencies
• Need to identify a key:
– find a single key
– find all keys
• Both algorithms use as a subroutine an
algorithm that computes the closure.
4
• Let U be a set of attributes and F be a
set of functional dependencies on U.
• Suppose that X U is a set of
attributes.
• Definition: X+ = { A | F X A}
• We would like to compute X+
Closure of a Set of Closure of a Set of AttributesAttributes
|=
5
Algorithm From ClassAlgorithm From Class
Compute Closure(X, F)
1.C := X
2.While there is a V W in F such
that (V C)and (W C) do C := C W
3.Return C
Complexity?
8
Lossless JoinLossless Join
• Decomposition has a lossless-join
property if the natural join of
projections is always equal to the
original relation.
• Necessary, otherwise original relation
cannot be recreated, even if tables are
not modified.
9
Dependency PreservationDependency Preservation
• Decomposition is dependency
preserving if F can be recovered from
the projections.
• Allows us to check that inserts/updates
are correct without joining the sub-
relations.
10
What about This What about This Decomposition?Decomposition?
S C
Cohen DB
Levy OS
Levy DB
S T
Cohen Smith
Levy Jones
Levy Smith
S C T
Cohe
n
DB Smith
Levy OS Jones
Levy DB Smith
F = {C T}
11
And This?…And This?…
S T
Cohen Smith
Katz Smith
Levy Jones
C T
DB Smith
OS Jones
Algo Smith
S C T
Cohe
n
DB Smith
Katz Algo Smith
Levy OS Jones
F = {C T}
12
And This?And This?
S C
Cohen DB
Katz Algo
Levy OS
C T
DB Smith
OS Jones
Algo Smith
S C T
Cohe
n
DB Smith
Katz Algo Smith
Levy OS Jones
F = {C T}
13
Checking Decomposition Checking Decomposition PropertiesProperties
• Check for a lossless join using the
algorithm from class (a’s and b’s).
• Check for dependency preserving
using an algorithm shown today.
14
Dependency PreservationDependency Preservation
• R=ABC
• Dependencies {AB, BC}
• Decomposition {AB, AC}
• Is it lossless?
• Does it preserve BC?
15
Dependency Preservation Dependency Preservation (cont’d)(cont’d)
A B C
1 10 100
2 10 100
3 20 200
A B
1 10
2 10
3 20
4 20
A C
1 100
2 100
3 200
4 300
16
Definition (1)Definition (1)
• Let R1...Rk be a decomposition of R
• We define Ri (F) to be the set of
dependencies XY in F+ such that
X and Y are in Ri
17
Definitions (2)Definitions (2)
• We say that R1...Rk of R is
dependency preserving if:
(R1 (F) U ... U Rk (F))+ = F+
• Note that one inclusion clearly
always holds.
18
Algorithm (1)Algorithm (1)
/* check if X->Y is preserved */IsPreserved(X,Y,R1…k)Z:=Xwhile changes to Z occur do for i=1 to k do Z:= Z ((Z Ri)+ Ri) if YZ return trueelse return false
19
Algorithm(2)Algorithm(2)
IsDependencyPreserving(F,R1…k)for each X->Y in F do
if not IsPreserved(X,Y,R1…k)return false
return true
20
Example (1)Example (1)
• R=ABCD
• F = {A -> B, B -> C, C -> D, D -> A}
• R1=AB, R2=BC, R3=CD
• Is this decomposition dependency
preserving?
21
Example (2)Example (2)
• R = ABCDE
• F = {A -> ABCDE, BC -> A, DE -> C}
• R1 = ABDE, R2 = DEC
• Is this decomposition dependency
preserving?
23
Minimal Cover(1)Minimal Cover(1)
F is called minimal if:
1. If XY is in F then Y is a single attribute
2. If XA is in F then F - {XA} is not
equivalent to F
3. If XA is in F and Z is in X, then F –
{XA} U {ZA} is not equivalent to F
24
Minimal Cover(2)Minimal Cover(2)
• If G+ = F+ and G is minimal then
G is called a minimal cover of F
• A minimal cover always exist for
a set of functional dependencies
25
Computing a Minimal Computing a Minimal CoverCover
• 3 Steps:
– We may assume that all right sides in F
are singletons (why??)
– For each XA in F and for each B in X,
check if F |= X\B A. If so, substitute
XA with X\BA
– For each XA in F, check if F - {XA} |=
XA. If so, remove XA
27
The Basic IdeaThe Basic Idea
• If a relation R with functional
dependencies F is in a normal
form, then certain problems can
be avoided (e.g., redundancy)
28
Boyce-Codd Normal Form Boyce-Codd Normal Form (BCNF)(BCNF)
• Every dependency XA in F+
must be either
1. Trivial, or
2. X is a super-key for R
29
Third Normal Form (3NF)Third Normal Form (3NF)
• For every dependency XA in F+ one
of the following must hold:
1. XA is trivial
2. X is a super-key for R
3. A is an attribute of a key for R
30
ExampleExample
• Suppose that R = ABC. For each of the following, decide whether R is in BCNF/3NF:– F = {}
– F = {A -> B}
– F = {A -> B, A -> C}
– F = {A -> B, B -> C}
– F = {A -> B, BC -> A}
31
Decomposition into 3NF Decomposition into 3NF (1)(1)
• Given a relation R with functional dependencies F (assume w.l.o.g. that F is minimal):
• Step 1: For each XA in F, create a sub-scheme XA
• Step 2: If no sub-scheme created so far contains a key, add a key as a sub-scheme
32
Decomposition into 3NF Decomposition into 3NF (2)(2)
• Step 3: Remove sub-schemes that are contained in other sub-schemes
• The result is a decomposition into 3NF that is dependency preserving and has a lossless join
33
Example (1)Example (1)
• Find a decomposition into 3NF for the
relational scheme R = ABCDEFGH, with
the functional dependencies F =
{AB, ABCDE, EFGH, ACDFEG}
34
Example (2)Example (2)
• Minimal cover G = {AB, ACDE,
EFG, EFH}
• Key ACDF
• Decomposition: AB, ACDE, EFG, EFH,
ACDF
35
Decomposition into BCNFDecomposition into BCNF
• There always exists a decomposition into
BCNF that has a lossless join
• There does not always exist a decomposition
into BCNF that is dependency preserving
• Example: Consider the relation SBD (sailor,
boat, date) with the F = {SBD, DB}
• There exists a polynomial algorithm for
finding such a decomposition