schema refinement canonical minimal covers n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Schema Refinement: Canonical/minimal Covers PowerPoint Presentation
Download Presentation
Schema Refinement: Canonical/minimal Covers

Loading in 2 Seconds...

play fullscreen
1 / 33

Schema Refinement: Canonical/minimal Covers - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

Schema Refinement: Canonical/minimal Covers. Canonical Cover. Number of iterations of the algorithm for computing the closure of a set of attributes depends on the number of FD’s in F The same will be observed for other algorithms that we will study (such as the decomposition algorithms)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Schema Refinement: Canonical/minimal Covers' - twila


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
schema refinement canonical minimal covers
Schema Refinement:

Canonical/minimal Covers

canonical cover
Canonical Cover
  • Number of iterations of the algorithm for computing the closure of a set of attributes depends on the number of FD’s in F
    • The same will be observed for other algorithms that we will study (such as the decomposition algorithms)
  • Can we “minimize” F?
covers
Covers
  • FD’s can be represented in several different ways without changing the set of legal/valid instances of the relation
  • Let F and G be sets of FD’s. We say “GfollowsfromF”, if every relation instance that satisfies F also satisfies G. In symbols: F⊨ G.

We may also say: “Gis implied byF” or “G is covered by F.”

  • If both F⊨ G and G⊨ F hold, then we say that G and F are equivalent and denote this by F≡G
  • Note that F≡G iff F+≡G+
  • If F≡G we may also say: G is a cover of F and vice versa
canonical cover1
Canonical Cover
  • Let F be a set of FD’s. A canonical / minimal cover of F is a set G of FD’s that satisfies the following:

1. G is equivalent to F; that is, G≡F

2. G is minimal; that is, if we obtain a set H of FD’s from G by deleting one or more of its FD’s, or by deleting one or more attributes from some FD in G, then F ≢H

3. Every FD in G is of the form XA, where A is a single attribute

canonical cover2
Canonical Cover

 A canonical cover G is minimal in two respects:

1. Every FD in G is “required” in order for G to be equivalent to F

2. Every FD in G is as “small” as possible, that is,

  • each attribute on the left hand side is necessary.
  • Recall: the RHS of every FD in G is a single attribute
computing canonical cover
Computing Canonical Cover

Given a set F of FD’s, how to compute a canonical cover G of F?

  • Step 1: Put the FD’s in the simple form
    • Initialize G:=F
    • Replace each FD X →A1A2…Ak inG with X→A1, X→A2, …, X→Ak
  • Step 2: Minimize the left hand side of each FD
    • E.g., for each FD AB → C in G, check if A or B on the LHS is redundant ,i.e.,(G {AB→ C } ⋃ {A → C })+≡F+?
  • Step 3: Delete redundant FD’s
    • For each FD X → A in G, check if it is redundant, i.e., whether

(G {X → A })+≡ F+?

computing canonical cover1
Computing Canonical Cover
  • R={ A,B,C,D,E,H}
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • Step one – put FD’s in the simple form
    • All present FD’s are simple

 G = {AB, DEA,BCE,ACE, BCDA, AEDB}

computing canonical cover2
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • Step two – Check every FD to see if it is left reduced
    • For every FD XA in G, check if the closure of a subset of X determines A. If so, remove the redundant attribute(s) from X
computing canonical cover3
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { A B, DEA,BCE,ACE, BCDA, AEDB }
    • A B

 obviously OK (no left redundancy)

    • DEA
      • D+= D
      • E+= E

 OK (no left redundancy)

computing canonical cover4
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { A B, DEA,BCE,ACE, BCDA, AEDB }
    • BCE
      • B+= B
      • C+= C

 OK (no left redundancy)

computing canonical cover5
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { A B, DEA,BCE,ACE, BCDA, AEDB }
    • ACE
      • A+= AB
      • C+= C

 OK (no left redundancy)

computing canonical cover6
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { A B, DEA,BCE,ACE, BCDA, AEDB }
    • BCDA
  • B+= B
  • C+= C
  • D+= D
  • BC+= BCE
  • CD+= CD
  • BD+= BD

OK (no left redundancy)

computing canonical cover7
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { A B, DEA,BCE,ACE, BCDA, AEDB }
    • AEDB
  • E & D are redundant  we can remove them from AEDB
  • A+= AB
  • G = { A B, DEA,BCE,ACE, BCDA, AB }

G = { DEA,BCE,ACE, BCDA, AB }

computing canonical cover8
Computing Canonical Cover
  • R={ A,B,C,D,E,H}
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • Step 3 – Find and remove redundant FD’s
    • For every FD XA in G
      • Remove XA from G; call the result G’
      • Compute X+under G’
      • If A X+, then XA is redundant and hence we remove the FD XA from G (that is, we rename G’ to G)
computing canonical cover9
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { DEA,BCE,ACE, BCDA, AB }
    • Remove DEA from G
      • G’ = { BCE,ACE, BCDA, AB }
    • Compute DE+under G’
      • DE+= DE (computed under G’)
    • Since A ∉ DE, the FD DEA is not redundant
      • G = { DEA,BCE,ACE, BCDA, AB }
computing canonical cover10
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { DEA,BCE,ACE, BCDA, AB }
    • Remove BCE from G
      • G’ = { DEA,ACE, BCDA, AB }
    • Compute BC+under G’
      • BC+= BC

BCEis not redundant

      • G = { DEA,BCE,ACE, BCDA, AB }
computing canonical cover11
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { DEA,BCE,ACE, BCDA, AB }
    • Remove ACE from G
      • G’ = { DEA,BCE, BCDA, AB }
    • Compute AC+under G’
      • AC+= ACBE

Since E∊ACBE, ACEis redundant  remove it from G

      • G = { DEA,BCE,BCDA, AB }
computing canonical cover12
Computing Canonical Cover
  • R={ A,B,C,D,E,H }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { DEA,BCE,BCDA, AB }
    • Remove BCDA from G
      • G’ = { DEA,BCE, AB }
    • Compute BCD+under G’
      • BCD+= BCDEA
    • This FD is redundant  remove it from G
      • G = { DEA,BCE,AB }
computing canonical cover13
Computing Canonical Cover
  • R={ A,B,C,D,E,F }
  • F = { A B, DEA,BCE,ACE, BCDA, AEDB }
  • G = { DEA,BCE,AB }
    • Remove AB from G
      • G’ = { DEA,BCE }
    • Compute A+under G’
      • A+= A
    • This FD is not redundant (Another reason why this is true?)
      • G = { DEA,BCE,AB }

 G is a minimal cover for F

several canonical covers possible

A

B

A

B

A

B

C

C

C

Several Canonical Covers Possible?
  • Relation R={A,B,C} with F = {A  B, A  C, B  A, B  C, C  B, C  A}
  • Several canonical covers exist
    • G = {A  B, B  A, B  C, C  B}
    • G = {A  B, B  C, C  A}

Can you find more ?

how to deal with redundancy
How to Deal with Redundancy?

Relation Schema:

  • We can decompose this relation into two smaller relations

Star (name, address, representingFirm, spokesPerson)

F = { name  address, representingFirm, spokePerson,

representingFirm spokesPerson }

Relation Instance:

how to deal with redundancy1
How to Deal with Redundancy?

Relation Schema:

Star (name, address, representingFirm, spokesperson)

F = { representingFirm  spokesPerson }

Decompose this relation into the following relations:

Star (name, address, representingFirm)

with F1={ name  address, representingFirm }

and

Firm (representingFirm, spokesPerson)

with F2={ representingFirm  spokesPerson }

how to deal with redundancy2
How to Deal with Redundancy?

Relation Instance before decomposition:

Relation Instances after decomposition:

decomposition
Decomposition
  • A decomposition of a relation schema R consists of replacing R by two or more non-empty relation schemas such that each one is a subset of R and together they include all attributes of R. Formally,

R= {R1,…,Rm} is a decomposition if all conditions below hold:

(0)Ri≠Ø, for all i in {1,…,m}

(1)R1∪…∪Rm= R

(2)Ri ≠Rj,for different i and j in {1,…,m}

    • When m = 2, the decomposition R= { R1, R2 } is called binary
  • Not every decomposition of R is “desirable”
  • Properties of a decomposition?

(1) Lossless-join – this is a must

(2) Dependency-preserving – this is desirable

Explanation follows …

example
Example

Relation Instance:

Decomposed into:

To “recover” information, we join the relations:

Why do we have new tuples?

lossless join decomposition
Lossless-Join Decomposition
  • R is a relation schema and F is a set of FD’s over R.

A binary decomposition of R into relation schemas R1 and R2with attribute sets X and Y is said to be a lossless-join decomposition with respect to F, if for every instance r of R that satisfies F, we have X( r )Y( r ) = r

  • Thm: Let R be a relation schema and F a set of FD’s on R. A binary decomposition of R into R1 and R2with attribute sets X and Y is lossless iff X  Y  Xor X  Y  Y, i.e., this binary decomposition is lossless if the common attributes of X and Y form a key of R1or R2
example lossless join
Example: Lossless-join

Relation Instance:

Decomposed into:

F = { B  C }

To recover the original relation r, we join the two relations:

No new tuples !

example dependency preservation
Example: Dependency Preservation

Relation Instance:

F = { BC, BD, A D }

Decomposed into:

Can we enforce A D?

How ?

dependency preserving decomposition
Dependency-Preserving Decomposition
  • A dependency-preserving decomposition allows us to enforce every FD, on each insertion or modification of a tuple, by examining just one single relation instance
  • Let R be a relation schema that is decomposed into two schemas with attribute sets X and Y, and let F be a set of FD’s over R. The projection of F on X (denoted by FX) is the set of FD’s in F+ that involve only attributes in X
    • Recall that a FD U  V in F+ is in FX if all the attributes in U and V are in X;In this case wesay this FD is “relevant” to X
  • The decomposition of < R,F > into two schemas with attribute sets X and Y is dependency-preserving if ( FX FY )+≡F+
normal forms
Normal Forms
  • Given a relation schema R, we must be able to determine whether it is “good” or we need to decompose it into smaller relations, and if so, how?
  • To address these issues, we need to study normal forms
  • If a relation schema is in one of these normal forms, we know that it is in some “good” shape in the sense that certain kinds of problems (related to redundancy) cannot arise
normal forms1

BCNF

3NF

2NF

1NF

Normal Forms
  • The normal forms based on FD’s are
    • First normal form (1NF)
    • Second normal form (2NF)
    • Third normal form (3NF)
    • Boyce-Codd normal form (BCNF)
  • These normal forms have increasingly restrictive requirements
third normal form
Third Normal Form

Let R be a relation schema, F a set of FD’s on R, X⊆ R, and

A∈ R.

  • We say R w.r.t. F is in 3NF (third normal form), if for every FD X  A in F, at least one of the following conditions holds:
    • A X, that is, X A is a trivial FD, or
    • Xis a superkey, or
    • IfXis not a key, thenAis part of some key of R
  • To determine if a relation <R, F> is in 3NF, we
    • Check whether the LHS of each nontrivial FD in F is a superkey
    • If not, check whether its RHS is part of any key of R
boyce codd normal form
Boyce-Codd Normal Form

Let R be a relation schema, F a set of FD’s on R, X⊆ R, and

A∈ R.

  • We say R w.r.t. F is in Boyce-Codd normal form, if for every FD X  A in F, at least one of the following conditions is true:
    • A  X, that is, X  A is a trivial FD, or
    • X is a super key
  • To determine whether R with a given set of FD’s F is in BCNF
    • Check whether the LHS X of each nontrivial FD in F is a superkey
      • How? Simply compute X+ (w.r.t. F) and check if X+ = R