1 / 17

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

Chapter 16: Relational Database Design and Further Dependencies. TA: Zhe Jiang zhe@cs.umn.edu. 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

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

## PowerPoint Slideshow about 'Chapter 16: Relational Database Design and Further Dependencies' - rocio

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

TA: Zhe Jiang

zhe@cs.umn.edu

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

Outline Dependencies

• Big picture & motivation

• Simple case algorithm (part of 16.3.3)

• Formal algorithm

• Basic concepts (16.1):

• General case algorithm (16.3.3)

Big Picture: Dependencies

Database Design Phases

ER-Diagram

Relational Tables

Which choice is good?

How to guarantee it?

Formal Norm Theory

Motivation Dependencies

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 Dependencies

• 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 Algorithm Dependencies

• 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 Algorithm Dependencies

• 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)

• Remove redundant relations R1 and R3, final answer is R2(L,C,A,P).

General DependenciesCase 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 Dependencies

• 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 Dependencies

• 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 Concepts Dependencies

• 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 Concept Dependencies

• 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 Concepts Dependencies

• Algorithm to find key of R&F

• 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 Dependencies

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 Dependencies

• 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 Example Dependencies

• 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 Dependencies

• 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?