Chapter 16 relational database design and further dependencies
This presentation is the property of its rightful owner.
Sponsored Links
1 / 17

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


  • 122 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Chapter 16: Relational Database Design and Further Dependencies

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)


Chapter 16 relational database design and further dependencies

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?


  • Login