- 111 Views
- Uploaded on
- Presentation posted in: General

Chapter 16: Relational Database Design and Further Dependencies

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

Chapter 16: Relational Database Design and Further Dependencies

TA: Zhe Jiang

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

- 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:

Database Design Phases

ER-Diagram

Relational Tables

Which choice is good?

How to guarantee it?

Formal Norm Theory

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?

- 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

- 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 XAi 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.

- Exercise:
- Universal relation
- FD: {PLC, LCAP, AC}
- 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).

- 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

- Inference rules: One FD could infer another
- trivial: IR1: IR1 (reflexive rule)
- If X Y, then X Y.

- non-trivial: IR2-IR4
- {XY} |= XZYZ
- {XY, YZ} |= XZ
- {XYZ} |=XY

- trivial: IR1: IR1 (reflexive rule)
- Closure of set of dependencies
- Closure of F: F+, set of all FDs could be inferred.
- Use IR1 to IR3;

- 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={XYZ, XW, WU, YV}, U(X,Y,Z,W,U,V)

Find: X+ ?

- 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

- Definition
- Minimal Cover of dependency set F
- definition: Can’t find subset that is equivalent to F

- Minimal Cover of dependency set F
- break down right-hand-side, X{A1,A2,…An} to XA1, XA2, …XAn
- 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 XA in F, if result still equivalent to F.

- Example:
- F={PLCA, LCAP, AC}
- What is “minimal cover” of F?

- 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_ssnEsal, Ephone, Dno; PnoPname, plocation};
- What is the key?

FD3

FD1: Property_id Lot#, County, Area

FD2: Lot#, County Area, Property_id

FD3: AreaCounty

Simpler Version:

F={PLCA, LCAP, AC}

- What is the minimal cover G?
- Decompose G

- Simpler Version:
- F={PLCA, LCAP, AC}
- First Case:
- Minimal cover GX:
- F: {PL, PC, PA, LCA, LCP,AC}
- Minimal cover GX: {PLC, LCAP, AC}
- Design X:
- 3. R1(P,L,C), R2(L,C,A,P), and R3(A,C)
- 4. R2(L,C,A,P)

- Simpler Version:
- F={PLCA, LCAP, AC}
- Second Case:
- Minimal cover GX:
- F: {PL, PC, PA, LCA, LCP,AC}
- Minimal cover GX: {PLA, LCP, AC}
- Design Y:
- 3. S1(P,A,L), S2(L,C,P), and S3(A,C)
- 4. No redundant relations.

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