复试-877学长数据库002课件0版

4 High-Level

Database

ModelsIn

a

design

phase,

we

address

and

answerquestions

about

what

informationwill

bestored,

how

information

elements

will

berelated

to

one

another,

what

constraints

such

askeys

or

referential

integrity

may

be

assumed,andso

on.The

nottaion

in

which

the

design

is

expressed:entity-relationship

diagram(实体-联系图),UML(建模语言),ODL(对象描述语言).IdeasHigh-LeveldesignRelationalschemaRelationaldatabaseThe

Entity/Relationship

ModelDesign

is

a

serious

business.The

“boss”

knows

they

want

a

database,but

they

don’t

know

what

they

want

in

it.Sketching

the

key

components

is

anefficient

way

to

develop

a

workingdatabase.The

Entity/Relationship

ModelThe

E/R

model

allows

us

to

sketchdatabase

designs.Kinds

of

data

and

how

they

connect.Not

how

d

hanges.Designs

are

pictures

called

entity-relationship

diagrams.The

Entity/Relationship

ModelPrincipal

elementtypes:Entity:“thing”

or

object.Entity

sets:

a

collection

of

similar

entities.Similar

to

a

class

in

object-orientedlanguages.Attributes:

properties

of

the

entities

in

anentity

set.

Attributes

are

simple

values,

egers

or

character

strings.Relationships:

connections

amon oormore

entity

sets.4.1.4

Entity-Relationship

DiagramsIn

anentity-relationship

diagram:Entityset

=rectangle.Attribute

=

oval,

with

aline

to

the

rectanglerepresenting

i tity

set.Moviestitle

yearlengthfilm

typeStarsnameaddress4.1.4

Entity-Relationship

DiagramsA

relationship

is

represented

by

a

diamond,with

lines

to

each

of

the

entity

setsinvolved.MoviestitleyearlengthgenreStars-inOwnsStarsStudiosnameaddressnameaddressFigure

Instances

of

an

E/R

DiagramThe

current

“value”

of

an

entity

set

is

theset

of

entities

that

belong

to

it.The

current

“value”

of

a

relationship

is

aset

(or

a

list)

about

currently

relatedentities,

one

from

each

of

the

relatedentitysets.4.1.5 Instances

of

an

E/R

DiagramFor

therelationship

stars-in,

we

mighthave

arelationship

set

like:–

The

tuples

of

a

relationship

set

are

not

really

tuples

ofa

relation,

since

their

components

are

entities.MoviesStarsStar

WarsStar

WarsCarrie

FisherMarkHamillWayne’s

WorldDana

CarveyWayne’s

WorldMikeMeyers4.1.6

Multiplicity

of

Binary

E/R

RelationshipsMultiplicity(多样性):a

kind

of

tative

restriction

of

arelationship

between

entity

sets.Two

essential

multiplicities:One:

relating

to

zero

or

one

entity,

atmost

one.Many:

relating

to

zero

or

more

entities.4.1.6

Multiplicity

of

Binary

E/R

RelationshipsMultiplicity

of

the

relationshipfromentity

set

A

toentity

set

B:One-one:

if

ea ember

ofeitherentity

set

can

be

connected

toatmostone

member

ofthe

other

set.Many-one

(One-many):

if

eachmember

of

B

can

be

connected

tozero,one,

or

many

membersof

A;

andeachmember

of

A

isconnectedtoatmostonemember

ofB.Many-many:

if

ea ember

ofeitherset

canbe

connectedto

manymembersof

theother

set.AB4.1.6

Multiplicity

of

Binary

E/R

RelationshipsRepresenting

“Multiplicity”:Show

a

many-one

relationship

by

an

arrowentering

the“one”side.Show

a

one-one

relationship

by

arrows

enteringboth

entitysets.Rounded

arrow

=

“exactly

one,”

i.e.,

each

entityof

thetheset

is

related

to

exactly

one

entity

ofset.4.1.6

Multiplicity

of

Binary

E/R

RelationshipsExample:

Relationship

Used-by

betweenentity

sets

Workers

and

Equipments.WorkersUsedbyEquipmentsspecificationPartsPnoPnamecolorsupplyUsedbyrsProductsSnameaddressPrnoPrnameweightSno4.1.6

Multiplicity

of

Binary

E/R

RelationshipsExample:4.1.7

Multiway

RelationshipsSometimes,

we

need

a

relationship

that

connectsmore

than

two

entitysets.A

ternary

(three-way)

relationship

is

common.MoviesStarsStudiosContractsContracts

is

a

ternary

relationship.The

relationship

can

be

described

by

a

3-tuples

of

theform:

(studio,

star,

movie)4.1.7

Multiway

RelationshipsHow

to

ascertain

the

multiplicity

of

aternary

relationship?–

Confirming

an

entity

in

entity

set

A

and

anentity

inentity

set

B,

andthenjudging

whetherit

is

related

to

one

or

many

entities

in

entitysetC.MoviesStarsStudiosContracts4.1.7

Multiway

RelationshipsConsider：

the

relationship

among“readers”,

“books”

and

“administrators”

ina

library.Consider：

the

relationship

among“students”,

“teachers”

and

“courses”

in

theeducational

administration

of

auniversity.4.1.8

Roles（角色）

inRelationshipsSometimes

an

entity

set

appears

morethanonce

in

a

relationship.Label

the

edges

between

the

relationshipand

the

entity

set

with

names

called

roles.Sequel-ofMoviesOriginalSequelConsider

themanage

relationship(superior-inferior)between

the

entityset

“employees”

anditself.4.1.8

Roles

in

RelationshipsExample4.7:

A

four-wayrelationshipMoviesStarsStudiosContractsStudioof

starProducing

StudioasleaderSalesmanDepartmennamedepidwork

for4.1.8

Roles

in

RelationshipsThere

may

be

more

than

one

relationshipbetween

two

entity

sets.empididnonamegenderphone4.1.9

Attributes

on

RelationshipsSometimes

it

is

useful

to

attach

an

attributeto

a

relationship.Think

of

this

attribute

as

a

property

oftuples

in

the

relationship

set.We

may

associate

the

attributes

“salary”

and“signdate”

with

the

ternary

relationship“contracts”.We

may

record

an

attribute

“time”

associatedwith

the

binary

relationship

“stars-in”

between“movies”

and

“stars”.4.1.9

Attributes

on

RelationshipsMoviesStarsStudiosContractsSigndateSalary4.1.10

Converting

Multiway

Relationships

to

BinaryHow

to

convert

multiway

relationships

tobinary

without

losing

any

information?–

Take

a

ternary

relationship

for

example:①Convert

the

ternary

relationship

to

a

newentity

setcalled

a

connecting

entity

set(连接实体集).②

Confirmthe

attributes

oftheconnectingentityset.③

Build

three

binary,

many-one

relationshipfrom

the

connecting

entity

setto

each

of

thethree

old

entitysets.4.1.10

Converting

Multiway

Relationships

to

BinaryContractsStudiosStudiosStudio-ofStarsStar-of

sSigndateSalaryMoviesM

Movie-of4.1.10

Converting

Multiway

Relationships

to

BinaryWhat

is

a

connecting

entity

set?It’s

a

kind

of

special

entity

sets,

and

it

canrepresent

a

relationship.It

generally

hasattributes.Which

relationships

should

be

converted

toentity

sets?Multiway

relationshipsMany-many

relationshipsThe

relationships

associated

with

attributes4.1.11

Subclasses

in

the

E/R

ModelOften,anentityset

contains

certain

entitiesthat

have

special

properties

not

associated

withall

members

of

the

set.

If

so,

we

find

it

useful

todefine

certainspecial-case

entity

sets,

orsubclasses,

each

with

its

own

special

attributesand

relationships.We

connect

an

entity

set

to

its

subclasses

usinga

relationship

called

isa.An

isa

relationship

is

a

special

kind

ofrelationship,

every

isa

relationship

is

one-onerelationship.4.1.11

Subclasses

in

the

E/R

ModelExample

4.10:

Cartoons

are

a

kind

ofmovies.Not

every

movie

is

an

cartoon,

but

some

are.Let

us

suppose

that

in

addition

to

all

the

properties(attributes

and

relationships)

of

movies,

cartoonsalso

have

the

relationship

voices

withstars.Isa

triangles

indicate

the

subclassrelationship.MoviestitleyearlengthgenreCartoonsisaStarsVoicesMurdersisaweapon4.1.11

Subclasses

in

the

E/R

ModelIn

OO,

objectsare

in

one

classonly.Subclasses

inherit(继承)from

superclasses.E/R

entities

have

representatives(表示)inthe

superclass

and

all

subclasses

to

whichthey

belong.Rule:

if

entity

e

is

represented

in

a

subclass,then

e

is

represented

in

the

superclass.The

Entity/Relationship

ModelExercises:P138

4.1.1

Design

Principlesyze

requirement

andDesign

principles:–

The

principles

guiding

us

todesign

system.Why

do

we

need

design

principles?In

order

to

reflect

requirement

truly

and

comple

y,constitute

a

good

design,

andavoid

usualproblems.Faithfulness(忠实性)Avoiding

redundancySimplicity

countsChoosing

the

Right

RelationshipsPicking

the

Right

Kind

of

Element4.2.1

FaithfulnessFaithfulness:The

design

should

be

faithful

to

thespecifications

of

the

application.Entity

sets,

relationships

and

their

attributesshould

reflect

reality.It’s

the

most

basic

principle.Keep

consistency(一致),andhave

no

self-contradiction(自相矛盾).Example

4.13:

Courses,

Instructors

andTeaches.4.2.2

Avoiding

RedundancyRedundancy(冗余)occurs

when

we

saythe

same

thing

in

two

or

more

differentways.Redundancy

wastes

space

and

(moreimportantly)

encourages

inconsistency.–

Thetwoinstances

ofthesame

factmaye

inconsistent

if

we

change

one

andforget

tochange

theother

one.4.2.2

Avoiding

RedundancyGood

designThis

design

gives

the

address

of

each

studio

exactly

once.Moviestitleyearlengthfilm

typeOwnsStudiosnameaddress4.2.2

Avoiding

RedundancyBad

designThis

design

states

the

studio

of

a

movie

twice:

as

anattribute

and

as

a

related

entity.MoviestitleyearlengthstudioOwnsStudiosnameaddressfilm

type4.2.2

Avoiding

RedundancyBad

designMoviestitleyearlengthstudioNamefilm

typeThis

design

repeats

the

studio’s

address

once

for

eachmovie

and

loses

the

address

if

there

are

temporarily

nomovies

for

astudio.studioAdd4.2.3

Simplicity

CountsSimplicity

counts:–

Avoid

introducing

more

elements

into

yourdesign

than

is

absolu y

necessary.MoviesHoldingStudiosRepresentsOwnsHolding

means

the

ownership

of

a

movie.

It’s

aunnecessary

entity

set

without

any

attribute.If

there

is

a

one-one

relationship

between

twoentity

sets

which

have

same

keys,

we

shouldconsider

uniting

them.4.2.4

Choosing

the

Right

RelationshipsAvoid

redundancy–

Adding

to

our

design

every

possiblerelationship

is

not

often

a

goodidea.Example

4.15Do

we

also

need

relationships:Stars-inand

Owns?

Itdepends

on

our

assumptions

regarding

the

threerelationship

in

question.MoviesStarsStudiosContracts4.2.4

Choosing

the

Right

RelationshipsExample

4.16Moviestitleyearlengthfilm

typeStars-inOwnsStudiosnameaddressStarsnameaddressWorks-for4.2.5

Picking

the

Right

Kind

of

ElementSometimes

we

have

options

regardingthetype

of

design

element

used

to

represent

areal-world

concept.–

More

reasonable,

more

natural,

simpler,closer

torequirement4.2.5

Picking

the

Right

Kind

of

ElementAnentity

Set

or

anattribute?If

something

has

more

information

associated

withit

than

just

its

name,

it

probably

needs

to

be

anentity

set.In

E/R

model,

each

attribute

of

an

entity

setusually

has

a

single

value.

So,

If

it

has

more

thanone

values,

it

probably

needs

to

be

an

entity

set.If

it

has

only

itsname

to

contribute

to

the

design,then

it

probably

is

better

made

an

attribute.Don’t

use

an

entity

set

when

an

attribute

will

do.新员工综合信息登记表MoviesStarsStudiosContractsStar-ofMovie-ofStudio-of4.2.5

Picking

the

Right

Kind

of

ElementA

multiway

relationship

or

a

connecting

entityset?–

We

should

decide

based

on

the

requirement

ysis.Example

2.18：Signdate

Salary4.2

Design

PrinciplesExercises:P145

Constraints(约束) in

the

E/R

ModelConstraints:Thesemantic(语义的)condition

or

restriction

about

someproperty.Constraints

decide

the

design

quality

of

a

database.The

modeling

ofconstraints:Describe

constraints

bya

normative(规范的)way

tocompletedesign.Why

do

we

modelconstraints?There

is

more

information

than

the

names

of

entity

sets,relationships

and

attributes.This

information

can’t

exist

independently,but

beaddedtoexistingelements.This

additional

information

often

takes

the

form

ofconstraints

on

entity

sets,

relationships

andattributes.Keys

in

the

E/R

ModelA

key

is

a

set

of

attributes

for

one

entityset

such

that

no

two

entities

in

this

setagree

on

all

the

attributes

of

the

key.–

Itis

allowed

for

twoentities

to

agree

onsome,but

not

all,

ofthekey

attributes.We

must

designate(指定)a

key

for

everyentity

set.4.3.2

Representing

Keys

in

the

E/R

ModelKeys

in

E/R

Diagrams:–

Underline

the

key

attribute(s).Moviestitleyearlengthgenre4.3.2

Representing

Keys

in

the

E/R

ModelIs

there

only

one

key

for

an

entity

set?Example:Employee:

employee

number,

ID

numberThere

may

be

more

than

one

key

for

anentityset.It’s

customary(通常)to

designate

one

key

for

anentity

set,called

primary

key(主键).Other

keys,called

secondary

keys,would

eithernotbe

indicated

or

wouldbe

listed

in

asidecomment(旁注)attached

to

the

diagram.4.3.3

Referential

Integrity(参照完整性)Reference(参照,

):A

way

of

relating

objects;A

reference

requires

the

referenced

object

must

exist.If

a

referenced

object

is

deleted

while

the

referenceremain,“dangling(悬挂)”is

appeared.Referential

integrity:A

kind

of

single-value

constraints:

the

entity“referenced”

by

the

relationship

must

exists

in

ourdatabase.Example:

the

relationship

Owns

between

Movies

andStudios4.3.3

Referential

IntegrityHow

to

express

referential

integrity

inE/R

diagrams?–

rounded

arrowMoviesStudiosOwns4.3.4

Other

Kinds

of

Constraintsconstraints:Restrict

the

valueof

an

attributeto

be

ina

limited

set.For

example,

the

value

of

length

isbetween

20

and

240.Degree

constraint:Restrict

the

multiplicity

of

arelationship.Moviestitle

yearfilm

typeStarsinStarsnam

eaddresslength{20-240}<=104.4

Weak

Entity

Sets(弱实体集)Occasionally,

entities

of

an

entity

set

need“help”

to

identify

them

uniquely.An

entity

set

is

said

to

be

weak

if

its

someor

all

key

attributes

belong

toanotherentity

set.CrewsnumberUnit-ofStudiosaddressnam

eCauses

of

Weak

Entity

SetsEntity

sets

fall

into

a

hierarchy(层次).Example:Studios

and

Crews;SalesOrder

and

Salesitem；Employees

and

their

Resumes(履历)crewChiefname4.4.1

Causes

of

Weak

Entity

SetsExample:–

SalesOrder

and

Salesitem；SalesOrderordernoUnit-ofSalesitemsalescostlinenosigndatetyProductcontainstypespecpricedescprodidfactoryCauses

of

Weak

Entity

SetsConnecting

entity

setsMoviesStarsStudiosSigndateSalarynameaddrtitleyearnameaddrStar-ofMovie-ofStudio-ofContractsContractNo?4.4.2

Requirements

for

Weak

Entity

SetsIf

E

is

a

weak

entity

set,

then

each

of

the

entity

sets

F

thats

one

or

more

of

E’s

key

attributes

must

be

related

toE

by

a

relationship

R

called

supporting

relationship,

and

theentity

sets

F

are

supporting

entity

sets.The

following

conditionsmustbe

obeyed:R

must

be

a

binary,

many-one

relationship

from

E

to

Fand

must

have

referential

integrity

from

E

to

F.Putanother

way,

a

rounded

arrow

from

R

to

F

must

bejustified.TheattributesthatF sfor

the

key

ofE

mustbekey

attributesof

F.ERF4.4.2

Requirements

for

Weak

Entity

Sets零部件装配零件号主件子件数量If

F

is

itself

weak,

then

thekey

attributes

of

Fd

toEmaybeattributes

of

someentityset

to

which

F

is

connectedby

a

many-onerelationship.If

there

are

several

many-one

relationships

from

E

toF,

then

each

relationshipmay

be

used

to

supply

acopy

ofthe

key

attributesof

F

to

help

form

the

key

ofE.4.4.3

Weak

Entity

Set

Notation(符号)Double

diamond

for

supporting

many-one

relationship.Double

rectangle

for

the

weak

entity

set.ERFExampleWe'll

build

a

marketing

database

system

for

a

salecompany

(supermarket).

It

will

manage

all

thefollowing

information:1.

Manage

all

departments'

information

in

thecompany

(such

as

"Shanghai

sale

department","JiangSu

sale

department").

Also

manage

everysalesman

information

in

those

departmentsincluding

exclusive

employee

number,

ID

cardnumber,

and

some

private

information

(such

asname,

gender,

birthday

and

phone

number).

Bythe

way

one

of

salesmen

will

a

the

departmentmanager

in

hisdepartment.ExampleManage

a

group

of

customers:

name,

province,city,

company

name,

phone

number.Manage

all

the

products'

information:manufacturers

(e.g.

Chunlan,

Hailer

),

types

(e.g.motorcycle,

air

conditioner

),

specifications

(e.g."MT125",

"RE1500"

),

prices,

descriptions.Manage

sales

orderwhich

record

each

deal

hasbeen

done.Notes:

every

sales

order

contains

an

unique

orderNo.

,

sign

date,

a

corresponding

customer,

asalesman,

and

at

least

one

kind

of

products.

Anyty

andhe

totalproduct

in

the

order

should

have

itsunit

price

which

will

be

used

to

calcuprices.DepartmentSalesmanCustomerSalesorderSalesitemsales

forruns

foras

headerworksforunit-ofdeptidnameempididnonamegenderphonenameprovcityphoneunitordernosigndatecustidlinenosalescosttyProductcontainstypespecpricedescprodidfactory4.4

Weak

Entity

Sets(弱实体集)Exercises:P156

4.4.1

4.4.2完成一个

管理数据库的E/R图设计。4.5 From

E/R

Diagrams

to

Relational

DesignsMain

idea:Conversion

from

entity

sets

to

relationsConversion

from

relationships

to

relationsHandling weak

entity

sets

and

subclasses4.5.1 From

Entity

Sets

to

RelationsMoviesEntity

sets

->

relations.–

Attributes

->

attributes.title

yearlengthgenreStars-inOwnsStarsStudiosnam

eaddressnam

eaddressMovies

(title,

year,

length,

genre)Stars

(name,

address)Studios

(name,

address)4.5.2

From

E/R

Relationships

to

RelationsHow

to

convert

multiway

relationships

to

relations?Convert

multiway(多元)

relationships

to

binary(二元)relationships

ly.How

to

convert

binary

relationships

torelations?According

to

multiplicity

of

the

relationship:1.

A

many-many

relationship

R

from

E1

to

E2Convert

R

to

a

relation

whose

attributes

are

composedof

the

attributes

ofR

andthekey

attributes

ofE1andE2.Example:

Enrollment(

)

between

StudentsandCoursesEnrollment(Sno,

Cno,

score)4.5.2

From

E/R

Relationships

to

RelationsMoviestitle

yearlengthgenreStars-inOwnsStarsStudiosnam

eaddressnam

eaddressMovies

(title,

year,

length,

genre)Stars

(name,

address)Studios

(name,

address)Stars-in

(title,

year,

starName)4.5.2

From

E/R

Relationships

to

Relations2.

A

many-onerelationship

R

from

E1

to

E2R

can

be

converted

to

a

relation,

but

not

mustbe.Add

the

key

attributes

of

E2

to

the

relationE1.Example:

Belong

between

Departments

and

EmployeesEmployees(EmpID,

name,...

,DeptID

)Departments(DeptID,

name)Belong(EmpID,DeptID)4.5.2

From

E/R

Relationships

to

RelationsMoviestitle

yearlengthgenreStars-inOwnsStarsStudiosnam

eaddressnam

eaddressMovies

(title,

year,

length,

genre,)

studiosName)Stars

(name,

address)Studios

(name,

address)Stars-in

(title,

year,

starName)4.5.2

From

E/R

Relationships

to

Relations3.

A

one-one

relationship

R

from

E1

to

E2R

can

be

converted

to

a

relation,

but

usually

not.There

are

two

ways:Add

the

key

attributes

of

E2

to

the

relationE1.Add

the

key

attributes

of

E1

to

the

relationE2.Anone-onerelationships

may

be

merged

intoanother.Example:

Header

betweenDepartments

and

EmployeesEmployees

(EmpID,name,

...

,leadDept)Departments

(DeptID,

name)Employees

(EmpID,name,

...

)Departments

(DeptID,

name,

leaderID)Sequel-ofMovies4.5.2

From

E/R

Relationships

to

RelationsIfoneentity

set

is

involved

several

times

inarelationship,

in

different

roles,

or

the

same

attributename

appear

twice

or

more

in

a

relation,

then

weneed

to

rename

to

avoid

duplication.OriginalSequelMovies

(title,

year,

length,

genre)Movies

(title,

year,

length,

genre,

title,

year)Movies

(title,

year,

length,

genre,

originalTitle,

originalYear)4.5.4 Handling

Weak

Entity

SetsConversion

from

weak

entity

sets

torelations

：Convert

weak

entity

set

E

to

arelationwhose

attributes

are

composed

of

theattributesofE

and

thekey

attributes

ofF.Can

not

convert

R

toa

relation.ERF4.5.4 Handling

Weak

Entity

SetsStudios

(name,

address)Crews

(studioName,

number,

crewChief)CrewsnumberUnit-ofStudiosnam

eaddresscrewChief4.5.4 Handling

Weak

Entity

SetsContracts

(starName,

studioName,

title,

year,

salary,

signdate)MoviesStarsStudiossigndatesalarynameaddrtitleyearnameaddrStar-ofMovie-ofStudio-ofContracts4.5 From

E/R

Diagrams

to

Relational

DesignsExercises:P163

.24.6 Converting

Subclass

structures

to

RelationsSubclasses:

Three

Approa