Design Theory

1
Design Theory
2
Overview

• 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 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
Closure of a Set of Attributes

• 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

5
Algorithm From Class

• Compute Closure(X, F)
• C X
• While there is a V ? W in F such that (V ? C)and
  (W ? C) do
• C C ? W
• 3.Return C

Complexity?
6
Example

• RABCDE
• FAB?C, CE?B, D?A, BC?E
• A
• A,B
• B,D

7
Important Decomposition Characteristics
8
Lossless 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 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 Decomposition?
T C S
Smith DB Cohen
Jones OS Levy
Smith DB Levy
F C ? T
T S
Smith Cohen
Jones Levy
Smith Levy
C S
DB Cohen
OS Levy
DB Levy
11
And This?
T C S
Smith DB Cohen
Smith Algo Katz
Jones OS Levy
F C ? T
T C
Smith DB
Jones OS
Smith Algo
T S
Smith Cohen
Smith Katz
Jones Levy
12
And This?
T C S
Smith DB Cohen
Smith Algo Katz
Jones OS Levy
F C ? T
T C
Smith DB
Jones OS
Smith Algo
C S
DB Cohen
Algo Katz
OS Levy
13
Checking Decomposition Properties

• Check for a lossless join using the algorithm
  from class (as and bs).
• Check for dependency preserving using an
  algorithm shown today.

14
Dependency Preservation

• RABC
• Dependencies A?B, B?C
• Decomposition AB, AC
• Is it lossless?
• Does it preserve B?C?

15
Dependency Preservation (contd)
C B A
100 10 1
100 10 2
200 20 3
B A
10 1
10 2
20 3
20 4
C A
100 1
100 2
200 3
300 4
16
Definition (1)

• Let R1...Rk be a decomposition of R
• We define ?Ri (F) to be the set of dependencies
  X?Y in F such that X and Y are in Ri

17
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)
/ check if X-gtY is preserved / IsPreserved(X,Y,R
1k) ZX while changes to Z occur do for i1
to k do Z Z ? ((Z ? Ri) ? Ri) if Y?Z
return true else return false
19
Algorithm(2)
IsDependencyPreserving(F,R1k) for each X-gtY in F
do if not IsPreserved(X,Y,R1k) return
false return true
20
Example (1)

• RABCD
• F A -gt B, B -gt C, C -gt D, D -gt A
• R1AB, R2BC, R3CD
• Is this decomposition dependency preserving?

21
Example (2)

• R ABCDE
• F A -gt ABCDE, BC -gt A, DE -gt C
• R1 ABDE, R2 DEC
• Is this decomposition dependency preserving?

22
Minimal Cover
23
Minimal Cover(1)

• F is called minimal if
• If X?Y is in F then Y is a single attribute
• If X?A is in F then F - X?A is not equivalent
  to F
• If X?A is in F and Z is in X, then F X?A U
  Z?A is not equivalent to F

24
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 Cover

• 3 Steps
• We may assume that all right sides in F are
  singletons (why??)
• For each X?A in F and for each B in X, check if F
  X\B ? A. If so, substitute X?A with X\B?A
• For each X?A in F, check if F - X?A X?A.
  If so, remove X?A

26
Normal Forms
27
The 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 (BCNF)

• Every dependency X?A in F must be either
• Trivial, or
• X is a super-key for R

29
Third Normal Form (3NF)

• For every dependency X?A in F one of the
  following must hold
• X?A is trivial
• X is a super-key for R
• A is an attribute of a key for R

30
Example

• Suppose that R ABC. For each of the following,
  decide whether R is in BCNF/3NF
• F
• F A -gt B
• F A -gt B, A -gt C
• F A -gt B, B -gt C
• F A -gt B, BC -gt A

31
Decomposition into 3NF (1)

• Given a relation R with functional dependencies F
  (assume w.l.o.g. that F is minimal)
• Step 1 For each X?A 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 (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)

• Find a decomposition into 3NF for the relational
  scheme R ABCDEFGH, with the functional
  dependencies F A?B, ABCD?E, EF?GH, ACDF?EG

34
Example (2)

• Minimal cover G A?B, ACD?E, EF?G, EF?H
• Key ACDF
• Decomposition AB, ACDE, EFG, EFH, ACDF

35
Decomposition 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 SB?D, D?B
• There exists a polynomial algorithm for finding
  such a decomposition