数据库-最后一次复习课件

2020/11/12Database

Principles

&

Programming2ContentsIntroduction

to

E-R

ConceptsFurther

Details

of

E-R

DiagramsAdditional

E-R

ConceptsCase

StudyNormalization:

PreliminariesFunctional

DependenciesLossless

positionsNormal

Forms2020/11/12Database

Principles

&

Programming3Review

of

E-R

Model

(1)E-R

ModelEntityAttributeIdentifier,

Descriptor,

Composite

Attribute,Multi-Valued

AttributeRelationshipCard(E,

R)

=

(x,y)single-valued/multi-valued

participationmandatory/optional

participationOne-to-One,

Many-to-Many,

Many-to-OneExamples

of

E-R

diagrams

(1)Examples

ofE-R

diagrams

(2)2020/11/12Database

Principles

&

Programming6Review

of

E-R

Model

(2)Transform

E-R

Model

to

RelationsRule

1:

EntityRule

2:

Multi-valued

AttributeRule3:N-N

RelationshipsRule4.

N-1RelationshipsRule

5:

1-1

Relationships:

Optional

on

onesideRule

6:

1-1

Relationships:

Mandatory

on

bothsides2020/11/12Database

Principles

&

Programming7Rule

3:

N-NRelationshipsEmployees(eid,

straddr,

city,

……)Projects(prid,

proj_name,

due_date)EmployeesProjectspercentworks_on(eid,

prid,

percent)(1,

N)

works_on

(0,

N)2020/11/12Database

Principles

&

Programming8Rule

4.

N-1

RelationshipsInstructors(insid,

lname,

……)Course_sections(secid,

insid,

course,

…)insidsecid2020/11/12Database

Principles

&

Programming9Rule

5:

1-1

Relationships:

Optional

on

one

side(ssn,

name,

birthdate)DrivingLicense(DLno,

validdate,

class,

ssn)DrivingLicensehaving(0,1)(1,1)ssn

DLnonamebirthdatevaliddate

class2020/11/12Database

Principles

&

Programming10Rule

6:

1-1

Relationships:

Mandatory

on

both

sidesStudent(ssn,

name,

scid,

department,

class)Student(1,1)StudentCardhaving(1,1)ssn

scidnamedepartment

class2020/11/12Database

Principles

&

Programming11Review

of

E-R

Model

(3)Cardinality

of

Attributes

(x,

y)eidemp_addresszipcode(1,1)Employees(0,1)hobbies(1,N)(1,1)staddress(1,1)city(1,1)state(1,1)2020/11/12Database

Principles

&

Programming12Review

of

E-R

Model

(4)Weak

EntitiesOrdersLine_itemsProductshas_itemfor_prod(1,N)(1,

1)ordnolineno2020/11/12Database

Principles

&

Programming13Review

of

E-R

Model

(5)Generalization

HierarchiessConsultantsEmployeesManagersNon_managersvendornoaddressempidexpensenounion_noReview

of Functional

DependencyFunctional

Dependency

(FD,函数依赖)Def.

of

FDArmstrong’s

Axioms(Armstrong公理)Minimal

Cover

(最小覆盖)Closureof

aSet

of

FDs(函数依赖集的闭包)FDSet

Cover

(函数依赖集的覆盖)Equivalenceof

two

sets

of

FDS

(函数依赖集的等价)Algorithm

6.6.13：最小覆盖计算算法Closure

of

a

Set

of

Attributes

(属性集的闭包)Algorithm

6.6.12：属性集闭包计算算法ABA

does

not

functionallydetermine

B.

Somevalues

of

A

correspondto

more

than

one

valueof

B.Figure6.18 Graphical

Depiction

of

Functional

DependencyABA

functionallydetermines

B.

Eachvalue

of

A

correspondsto

only

one

value

of

B.A

→BA

→B→

B→

AX1X2X3X4AY1Y2Y3Y4B（一对一）B

→

AX1X2X3（一对多）A

BY1Y2Y3Y4(none)X1X2X3Y1Y2Y3B（多对多）AReview

of

Armstrong’s

AxiomsArmstrong’s

AxiomsRule1(自反规则):

If

YX，then

X→YRule

2(传递规则):If

X→Y

and

Y→Z

，then

X→ZRule

3(增广规则):If

X→Y,then

XZ→YZSome

Implications

of

Armstrong’sRule

4(合并规则):If

X→Y

and

X→Z，then

X→YZRule

5(分解规则):If

X→YZ，then

X→Y

and

X→ZRule

6(伪传递规则):If

X→Y，and

WY→Z，then

XW→ZRule

7(聚积规则):If

X→YZ

and

Z→W，then

X→YZWReview

of

Minimal

CoverClosure

of

a

Set

of

FDs(函数依赖集F的闭包)F+

=

{

X→A

|

F╞

X→A

}FD

Set

Cover(函数依赖集的覆盖)F

covers

G

iff G

F+Equivalence

of

two

sets

of

FDS(函数依赖集的等价)F

covers

G

and

G

covers

FReview

of Functional

DependencyClosure

of

a

Set

of

Attributes

(属性集的闭包)Algorithm

6.6.12：属性集闭包计算算法Minimal

Cover

(最小覆盖)Algorithm

6.6.13：最小覆盖计算算法Review

of

Closure

of

a

Set

of

AttributesDef.

6.6.11:

X+F

=

{

A

|

X

→

A

∈

F+

}algorithm

6.6.12X+

:=

X;repeatoldX+

:=

X+;for

each

functional

dependency

YZ

in

Fdoif

Y

X+

thenX+

:=

X+

Z;until

(

oldX+

=

X+

)Algorithm

6.6.13：Minimal

Coverstep

1:

From

the

set

F

of

FDs,

we

create

anequivalent

set

H

of

FDs,with

only

single

attributeson

the

right

side.step

2:

From

the

set

H

of

FDs,successivelyremove

individual

FDs

that

are

inessential

in

H.step

3:

From

the

set

H

of

FDs,successivelyreplace

individual

FDswith

FDsthat

haveasmaller

number

of

attributes

on

theleft-hand

side,as

long

as

theresu oes

not

change

H+.step

4:

From

the

remaining

set

of

FDs,

gather

allFDs

with

equal

left-hand

sidesand

usethe

unionrule

to

create

an

equivalent

set

ofFDsM

where

allleft-hand

sides

are

unique.Review

of

NormalizationThe

process

ofnormalizationpositions

of

table

THead(T)

=

Head(T1)

Head(T2)

......

Head(Tk)Lossless position(无损分解)T

T1

T2

...

TkTheorem

6.7.3

&

6.7.4：无损分解的判定定理Lossy position(有损分解)T

T1

T2

...

TkContent

of

nextDef.6.8.3

FD

P (依赖保持性)Superkey

&KeyAlgorithm

to

Find

Candidate

KeyPRIME

ATTRIBUTE(主属性)NON-PRIME

ATTRIBUTE(非主属性)Normal

Forms:2NF,

3NF,

BCNFAlgorithm

6.8.8246.8 Normal

Formsgorithm

to

Find

CandidateKeyGiven

atable

Twith

a

set

F

of

FDsset

K

:=Head(T)

;for

each

attribute

A

in

K{+compute

(K

–

A)F

;+if

(K

–

A)F

contains

all

the

attributes

in

T,

then{set

K

:=K

–

{

A

}

;}}2020/11/12 Database

Principles

&

ProgrammingBCNF和3NF定义的对比BCNFfor

any

FD

XA

in

F+

that

lies

in

T

(allattributes

of

X

and

A

in

T),

A

is

a

single

attribute

not

in

X,

then

X

must

be

asuperkey

for

T3NFfor

any

FD

XA

implied

by

F

that

lies

in

T,if

A

is

a

single

non-prime

attribute

not

in

X,then

X

must

be

a

superkey

for

T.3NF和2NF定义的对比3NFfor

any

XA

implied

by

F

t