normalization db tuning n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Normalization DB Tuning PowerPoint Presentation
Download Presentation
Normalization DB Tuning

Loading in 2 Seconds...

play fullscreen
1 / 20

Normalization DB Tuning - PowerPoint PPT Presentation


  • 116 Views
  • Uploaded on

Normalization DB Tuning. CS186 Final Review Session. Plan. Functional Dependencies, Rules of Inference Candidate Keys Normal forms (BCNF/3NF) Decomposition BCNF Lossless Dependency preserving 3NF + Minimal cover. Functional Dependencies.

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 'Normalization DB Tuning' - margot


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
normalization db tuning

NormalizationDB Tuning

CS186 Final Review Session

slide2
Plan
  • Functional Dependencies, Rules of Inference
  • Candidate Keys
  • Normal forms (BCNF/3NF)
  • Decomposition
    • BCNF
    • Lossless
    • Dependency preserving
    • 3NF + Minimal cover
functional dependencies
Functional Dependencies
  • A functional dependencyX  Y holds over relation schema R if, for every allowable instance r of R:

t1  r, t2  r, pX (t1) = pX (t2)

implies pY (t1) = pY (t2)

(where t1 and t2 are tuples;X and Y are sets of attributes)

  • In other words: X  Y means

Given any two tuples in r, if the X values are the same, then the Y values must also be the same. (but not vice versa!!)

  • Can read “” as “determines”
slide4

Rules of Inference

  • Armstrong’s Axioms (X, Y, Z are sets of attributes):
    • Reflexivity: If X  Y, then X  Y
    • Augmentation: If X  Y, then XZ  YZ for any Z
    • Transitivity: If X  Y and Y  Z, then X  Z
  • Some additional rules (that follow from AA):
    • Union: If X  Y and X  Z, then X  YZ
    • Decomposition: If X  YZ, then X  Y and X  Z
slide5
Plan
  • Functional Dependencies, Rules of Inference
  • Candidate Keys
  • Normal forms (BCNF/3NF)
  • Decomposition
    • BCNF
    • Lossless
    • Dependency preserving
    • 3NF + Minimal cover
slide6

Candidate Keys

  • R = {A, B, C, D, E}
  • F = { B CD, D  E, B  A, E  C, AD B }
  • Is B  E in F+ ?
  • B+ = B
    • B+ = BCD
    • B+ = BCDA
    • B+ = BCDAE … Yes! and B is a key for R too!
  • Is D a key for R?
    • D+ = D
    • D+ = DE
    • D+ = DEC
    • … Nope!
  • Is AD a key for R? AD+ = AD
  • AD+ = ABD and B is a key, so Yes!
  • Is AD a candidate key for R?
    • A+ = A, D+ = DEC
    • … A,D not keys, so Yes!
  • Is ADE a candidate key for R?
  • … No! AD is a key, so ADE is a superkey, but not a candidate key
slide7
Plan
  • Functional Dependencies, Rules of Inference
  • Candidate Keys
  • Normal forms (BCNF/3NF)
  • Decomposition
    • BCNF
    • Lossless
    • Dependency preserving
    • 3NF + Minimal cover
slide8

Boyce-Codd Normal Form (BCNF)

  • Reln R with FDs F is in BCNF if, for all X  A in F+
    • A  X (called a trivial FD), or
    • X is a superkey for R.
  • In other words: “R is in BCNF if the only non-trivial FDs over R are key constraints.”
slide9

Third Normal Form (3NF)

  • Reln R with FDs F is in 3NF if, for all X  A in F+
    • A  X (called a trivial FD), or
    • X is a superkey of R, or
    • A is part of some candidate key (not superkey!) for R. (sometimes stated as “A is prime”)
  • If R is in BCNF, obviously in 3NF.
    • Lossless-join, dependency-preserving decomposition of R into a collection of 3NF relations always possible.
slide10
Plan
  • Functional Dependencies, Rules of Inference
  • Candidate Keys
  • Normal forms (BCNF/3NF)
  • Decomposition
    • BCNF
    • Lossless
    • Dependency preserving
    • 3NF + Minimal cover
lossless decomposition
Lossless Decomposition
  • The decomposition of R into X and Y is lossless with respect to F if and only ifthe closure of F contains:

X  Y  X, or

X  Y  Y

  • Useful result: If W  Z holds over R and W  Z is empty, then decomposition of R into R-Z and WZ is loss-less.

i.e. the common attributes form a superkey for one side or the other

slide12

Dependency Preserving Decompositions

  • Decomposition of R into X and Y is dependencypreserving if (FX FY ) + = F +
    • i.e., if we consider only dependencies in the closure F + that can be checked in X without considering Y, and in Y without considering X, these imply all dependencies in F +.
  • Important to consider F + in this definition:
    • ABC, A  B, B  C, C  A, decomposed into AB and BC.
    • F+ also contains B  A, A  C, C  B …
  • FAB contains A B and B  A; FBC contains B  C and C  B
  • So, (FAB  FBC)+ contains C  A
bcnf decomposition
BCNF Decomposition
  • For each FD in F+ that violates BCNF, X A
  • Decompose R into (R - A) and XA
  • If either (R - A) or XA is not in BCNF, decompose recursively
  • Guaranteed to be lossless but not dependency preserving
slide14

Minimal Cover for a Set of FDs

  • Minimal coverG for a set of FDs F:
    • Closure of F = closure of G.
    • Right hand side of each FD in G is a single attribute.
    • If we modify G by deleting an FD or by deleting attributes from an FD in G, the closure changes.
  • Intuitively, every FD in G is needed, and ``as small as possible’’ in order to get the same closure as F.
  • e.g., A  B, ABCD  E, EF  GH, ACDF  EG has the following minimal cover:
    • A  B, ACD  E, EF  G and EF  H
    • Do we need ACDF  EG? It can be derived from ACD  E and EF  G. Same for ACDF  E, ACDF  G
3nf decomposition
3NF Decomposition
  • Compute minimal cover
  • For each FD X  A in minimal cover that is not preserved
    • Add relation XA
  • Guaranteed to be lossless AND dependency preserving
slide16

Tuning the Schema

Contracts (Cid, Sid, Jid, Did, Pid, Qty, Val)

Depts (Did, Budget, Report)

Suppliers (Sid, Address)

Parts (Pid, Cost)

Projects (Jid, Mgr)

  • We will concentrate on Contracts, denoted as CSJDPQV. The following ICs are given to hold:
  • JP C, SD P, C is the primary key.
    • C and JP are candidate keys
    • 3NF normal form
bcnf decomposition1
BCNF Decomposition
  • Use SD  P, we get SDP and CSJDQV
  • Lossless but not dependency-preserving (JP  C)
  • Three options
    • Leave it in 3NF without decomposition
    • Add JPC as an extra table (redundancy across relations)
    • Create an assertion to enforce JP  C
      • Acceptable when updates are infrequent
check assertion jp c
Check Assertion (JP  C)

PartInfo: SDP

ContractInfo: CSJDQV

CREATE ASSERTION checkDep

CHECK (NOT EXISTS

(SELECT *

FROM PartInfo PI, ContractInfo CI

WHERE PI.supplierid=CI.supplierid

AND PI.deptid = CI.deptid

GROUP BY CI.projectid, PI.partid

HAVING COUNT (cid) > 1

)

)

Lossless join on SD

Group By JP

Count C

don t forget
Don’t forget..
  • Relational algebra, calculus
  • Query optimization
    • Nested loop, index nested, block nested, hash..
    • Computing costs
    • Enumerating plans

Good luck!