miniworld - PowerPoint Presentation

Slide1

miniworld

Requirements & collection analysis

Conceptual Design

Data Model Mapping

Database Requirements

Conceptual Schema (

ER diagram

)

DBMS independent

DBMS specific

Conceptual Schema (

Relations

)

refinement

primary key constraint

foreign key constraintSlide2

Schema Refinement and

Normal Forms

Conceptual database design gives us a set of relation schemas and integrity constraints

Given a design, how do we know it is good or not? A design can be evaluated from various perspectives, our focus is on data redundancy

Conceptual design

Schemas

ICsSlide3

The Evils of Redundancy

Redundancy

is at the root of several problems associated with relational schemas:

redundant storage

Insertion/update/deletion anomaliesSlide4

Example

Schema

Hourly_Emps (

ssn, name, lot, rating, hrly_wages, hrs_worked

)Constraints:

ssn is the primary key If two tuples have the same value on

rating, they have the same value on

hrly_wages Slide5

Solution: Decomposition

If we break Hourly_Emps into Hourly_Emps2 and Wages, then we don’t have updates, insertion, deletion anomalies.

Hourly_Emps2

WagesSlide6

Should a relation be decomposed?

If a relation is not in certain form, some problems (e.g., redundancy) will arise, are these problems tolerable?

Aforementioned anomaliesPotential performance loss: Queries over the original relation may required to join the decomposed relations

How to decompose a relation? Two properties must be preserved:lossless-join: the data in the original relation can be recovered from the smaller relations

dependency-preservation: all constraints on the original relation must still hold by enforcing some constraints on each of the small relations

Decomposition ConcernsSlide7

Functional Dependencies (FDs)

In a relation schema R, a set of attributes X functionally determines a set of attributes Y

if and only if whenever two tuples of R agree on X value, they must necessarily agree on the Y value.

XY

X

Y: Y is functionally dependent on X, or

X uniquely determines Y or

X functionally determines Y, or

X determines Y

where r(R) is an instance of R,

Slide8

X

Y

Z

X1

Y2

Z1

X1

Y2

Z2

X2

Y2

Z3

Does this data set violate

X->Y?

Does this data set violate Z->Y?

X

Y

Z

X1

Y1

Z1

X1

Y1

Z2

X1

Y2

Z1

Does this data set violate X->Y?

Does this data set violate

XY->Z?

Does this data set violate Z->X?Slide9

An FD is a statement about

all allowable relations.Must be identified based on semantics of application.

Given some allowable instance r1 of R, we can check if it violates some FD

f, but we cannot tell if f holds over R!A primary key constraint is a special case of an FD

The attributes in the key play the role of X, and the set of all attributes in the relation plays the role of YSlide10

Example 1

Hourly_Emps

(ssn

, name, lot, rating, hrly_wages, hrs_worked)

Notation: We will denote this relation schema by listing the attributes:

SNLRWHThis is really the set of attributes {S,N,L,R,W,H}.

Sometimes, we will refer to all attributes of a relation by using the relation name. (e.g., Hourly_Emps for SNLRWH)

Some FDs on Hourly_Emps:ssn

is the key: SSNLRWH (or {S}{S,N,L,R,W,H})

rating determines

hrly_wages : RWSlide11

dname

budget

did

since

lot

name

ssn

Works_for

Employees

Departments

FD: did->lot

Works_for(

ssn

,name,did,since)

Department (

did

,dname,budget,lot);

Additional Constraints: Employees are assigned parking lots based on their department. All employees in the same department is given the same lot.

Example 2Slide12

A set of dependencies may imply some additional dependencies.

Dependency Reasoning

EMP_DEPT(ENAME,

SSN,BDATE,ADDRESS,DNUMBER,DNAME,DMGRSSN)

F={SSN->{ENAME,BDATE,ADDRESS,DNUMBER},DNUMBER->{DNAME,DMGRSSN} }

F infers the following additional functional dependencies:

F

{SSN}->{DNAME,DMGRSSN}

F

{SSN}->{SSN}

F

{DNUMBER}->{DNAME}Slide13

A set of dependencies may imply some additional dependencies.

Dependency Reasoning

Some important questions

Given a set of attributes X, what attributes can be determined by X

Given an FD set, what other dependencies are implied

Given an FD set F, what is the minimum set of dependencies that is equivalent to FSlide14

Armstrong’s Axioms where 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.

Armstrong’s

AxiomsSlide15

PROOFS

Reflexive rule: If X Y, then X

Y.

Let {t

1

,t

2

}

r(R) such that t

1

[X]=t

2

[X]

Since Y X, t1[X]=t2[X] t

1[Y]=t2[Y]

XY.Slide16

PROOFS (Cont’d)

Transitive rule: If X

Y and YZ, then XZ.

Let XY and (1) YZ (2)

such that t

1

[X]=t

2

[X], (3)we have:

(1) t

1[Y]=t2[Y] (4)(2)&(4) t1[Z]=t

2[Z] (5)(3)&(5) XZSlide17

Augmentation rule: If X

Y, then XZYZ.

Assume that the Augmentation rule is not true.

PROOFS (Cont’d)

t

1

[X] = t

2[X] (1)

t1[Y] = t2[Y] (2)t

1[XZ] = t2[XZ] (3)t1

[YZ] != t2[YZ] (4)

(1)&(3)

t

1

[Z]=t

2[Z] (5)

(2)&(5)

t

1

[YZ]=t

2

[YZ] (6)

(6) Contradicts (4)Slide18

Union

:

If X Y and X

 Z, then X  YZ.

Decomposition:

If XYZ, then XY and X

Z.Pseudotransitive

Rule: If XY and WYZ then WXZ.

Additional Inference Rules for Functional DependenciesSlide19

PROOFS (Cont’d)

Union rule: If X

Y and XZ, then XYZ.

Given XY and (1) XZ. (2)

Applying Augmentation rule on (1), we have XXXY XXY. (3)

Applying Augmentation rule on (2), we have XYZY XYYZ . (4)

Applying Transitive rule on (3) and (4), we have XYZ.Slide20

PROOFS (Cont’d)

Decomposition rule: If X

YZ then XY and XZ.

Given XYZ. (1)Since Y YZ, reflexive rule gives

YZY. (2)Applying Transitive rule on (1) and (2), we have XY.

XZ is derived in a similar way.

Slide21

PROOFS (Cont’d)

Pseudotransitive rule: If X

Y and WYZ, then WXZ.

Given XY (1)and WYZ. (2)

Applying Augmentation rule on (1), we have WXWY. (3)

Applying Transitive rule on (3)&(2), we have WXZ.Slide22

Exercise

Prove or disprove the following inference rules{WY,XZ} |= {WXY}

{XY,XW,WYZ} |= {XZ}

{XY} |= {XYZ}{XY, Z Y} |= {XZY}Prove using inference rules

Disprove by showing a counter example