chapter 16 relational database design and further dependencies
Download
Skip this Video
Download Presentation
Chapter 16: Relational Database Design and Further Dependencies

Loading in 2 Seconds...

play fullscreen
1 / 17

Chapter 16: Relational Database Design and Further Dependencies - PowerPoint PPT Presentation


  • 156 Views
  • Uploaded on

Chapter 16: Relational Database Design and Further Dependencies. TA: Zhe Jiang [email protected] Ref: Elmasri, Navathe, Fundamentals of Database Systems, 6th, Addison Wesley, ISBN-10: 0-13-608620-9. Outline. Big picture & motivation Simple case algorithm (part of 16.3.3) Formal algorithm

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 ' Chapter 16: Relational Database Design and Further Dependencies' - rocio


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
chapter 16 relational database design and further dependencies

Chapter 16: Relational Database Design and Further Dependencies

TA: Zhe Jiang

[email protected]

Ref: Elmasri, Navathe, Fundamentals of Database Systems, 6th, Addison Wesley, ISBN-10: 0-13-608620-9.

outline
Outline
  • Big picture & motivation
  • Simple case algorithm (part of 16.3.3)
  • Formal algorithm
    • Basic concepts (16.1):
    • General case algorithm (16.3.3)
slide3

Big Picture:

Database Design Phases

ER-Diagram

Relational Tables

Which choice is good?

How to guarantee it?

Formal Norm Theory

motivation
Motivation

We have:

  • Universal relational schema U(A1,A2, … An).
  • A set of functional dependencies (FDs) from domain knowledge.

Question:

How do we decompose U into sub-relations, so as to satisfy 3NF?

simple case decomposition algorithm
Simple Case Decomposition Algorithm
  • Motivation:
    • Decompose universal relational schema into sub relations which satisfy 3NF
  • Properties:
    • Preserve dependencies (nonlossy design)
    • Non-additive join property (no spurious tuples)
    • Resulting relational schemas are in 3NF
  • Problem Definition:
    • Input: Universal Relation R and a set of functional dependencies F on the attributes of R
    • Output: Sub-relations, FDs.
    • Constraint: the three properties above
simple case decomposition algorithm1
Simple Case Decomposition Algorithm
  • Suppose the FD set given is already “good”minimal cover (defined later)
  • Approach:
    • For each LHS X in F, create a relation schemain D {X U {A1} U {A2} … U {Ak} }.where XAi only dependency with X as LHS.
    • If none of the relation schemas in D contains a key of R, create one relation with key. (How? Introduce later)
    • Eliminate redundant relations.
simple case decomposition algorithm2
Simple Case Decomposition Algorithm
  • Exercise:
    • Universal relation
    • FD: {PLC, LCAP, AC}
    • Q: Does it satisfy 1NF, 2NF, 3NF?
    • Q: How to decompose the relation to satisfy 3NF?
  • Solution:
    • R1(P,L,C); R2(L,C,A,P); R3(A,C)
    • Already contains key.
    • Remove redundant relations R1 and R3, final answer is R2(L,C,A,P).
general case decomposition algorithm
General Case Decomposition Algorithm
    • New info: Transform the given FD set into minimal cover
    • New info: If no key exists, find key of U, then create a relation contain key
  • We will introduce some basic concepts, then formal algorithm
basic concept
Basic Concept
  • Inference rules: One FD could infer another
    • trivial: IR1: IR1 (reflexive rule)
      • If X Y, then X Y.
    • non-trivial: IR2-IR4
      • {XY} |= XZYZ
      • {XY, YZ} |= XZ
      • {XYZ} |=XY
  • Closure of set of dependencies
    • Closure of F: F+, set of all FDs could be inferred.
    • Use IR1 to IR3;
basic concepts
Basic Concepts
  • Closure of left-hand-side under dependency set

Algorithm 16.1

  • Start: X+={X}
  • Grow X+ with new attributes determined by elements in X+
  • Repeat 2 until can’t grow any more.

Exercise:

Given: F={XYZ, XW, WU, YV}, U(X,Y,Z,W,U,V)

Find: X+ ?

basic concepts1
Basic Concepts
  • Equivalence of functional dependencies sets
    • Definition
      • Cover: F covers E if F+ contains E.
      • Equivalent FD sets:
    • Algorithm
      • Check if all left-hand-sides’ closures are same
  • Minimal Cover of dependency set F
    • definition: Can’t find subset that is equivalent to F
basic concept1
Basic Concept
  • Minimal Cover of dependency set F
    • break down right-hand-side, X{A1,A2,…An} to XA1, XA2, …XAn
    • Try reduce size of LHS X in F, e.g. changing X into {X-B} still equivalent to F?
    • Try reduce unnecessary FD in F, e.g. remove XA in F, if result still equivalent to F.
  • Example:
    • F={PLCA, LCAP, AC}
    • What is “minimal cover” of F?
basic concepts2
Basic Concepts
  • Algorithm to find key of R&F
    • Start with K=R.
    • Find A in R such that (K-A)+ contain all attributes.
    • Repeat until size of K is as small as possible
  • Example:
    • U(Emp_ssn, Pno, Esal, Ephone, Dno, Pname, Plocation)
    • F={Emp_ssnEsal, Ephone, Dno; PnoPname, plocation};
    • What is the key?
decomposition algorithm exercise
Decomposition Algorithm: Exercise

FD3

FD1: Property_id Lot#, County, Area

FD2: Lot#, County Area, Property_id

FD3: AreaCounty

Simpler Version:

F={PLCA, LCAP, AC}

  • What is the minimal cover G?
  • Decompose G
decomposition algorithm example
Decomposition Algorithm Example
  • Simpler Version:
  • F={PLCA, LCAP, AC}
  • First Case:
  • Minimal cover GX:
  • F: {PL, PC, PA, LCA, LCP,AC}
  • Minimal cover GX: {PLC, LCAP, AC}
  • Design X:
  • 3. R1(P,L,C), R2(L,C,A,P), and R3(A,C)
  • 4. R2(L,C,A,P)
decomposition algorithm example1
Decomposition Algorithm Example
  • Simpler Version:
  • F={PLCA, LCAP, AC}
  • Second Case:
  • Minimal cover GX:
  • F: {PL, PC, PA, LCA, LCP,AC}
  • Minimal cover GX: {PLA, LCP, AC}
  • Design Y:
  • 3. S1(P,A,L), S2(L,C,P), and S3(A,C)
  • 4. No redundant relations.
exercise
Exercise
  • Given:
    • Universal relation U(A,B,C,D,E,F,G,H,I,J)
    • Functional dependencies F={ {A,B}{C}, {B,D}{E,F}, {A,D}{G,H}, {A}{I}, {H}{J} }.
    • Decompose it into 3NF?
ad