Chapter

2The

Relational

Model

ofData第2章關系數據模型2

The

Relational

Model

of

DataWhat

is

a

data

model?What

is

a

relational

data

model?How

to

define

a

relation

schema

in

SQL?Which

operations

can

be

in

the

relational model?

What

are

the

result

of

these operations?The

operations

in

the

relational

model

can

be expressed

in

either

an

algebra,

called“relational

algebra”.關系數據模型關系代數Relational

algebra

can

express

not

only operations,

but

also

constraints

on

relations.約束2.1

An

Overview

of

Data

ModelsWhat is

adata

model?Mathematical

representation

of

data.Examples:

relational

model

=

tables; semistructured

model

=

trees/graphs.Operations

on

data.Constraints.2.1

An

Overview

of

Data

ModelsSeveral

data

modelsThe

relational

model,

including

object- relational

extensionsThe

semistructured-data

model,

including XML

and

related

standardsThe

hierarchical

modelThe

network

model層次模型網狀模型2.2

Basics

of

the

Relational

ModelRelation

關系A

two-dimensional

table

called

a

relation.titleyearlengthfilmTypeStar

Wars1977124colorWayne’s

World199295colorMighty

Ducks1991104color…………Movies2.2

Basics

of

the

Relational

ModelWhyRelations?Very

simple

model. Oftenmatches

how

we

thinkabout

data. Abstract

model

that

underlies

SQL,the

most

important

databaselanguage

today.2.2.1

AttributesAttribute

屬性Names

for

the

columns

of

the

relation,describe

the

meaning

of

entries

in

thecolumn

below.

Such

as

length

of

Movies.An

attribute

have

a

name.Any

two

attributes

of

a

relation

can’thave

same

name.title

year

lengthfilmTypeMighty

Ducks…1991…Wayne’s

World

1992

95104…colorcolorcolor…AttributesStar

Wars

1977

124(columnheaders)2.2.2

SchemasRelation

schema

=

relation

name

andattribute

list.Optionally:

types

of

attributes.Example:

Movies

(

title,

year,

length,filmtype)

or

Movies

(

title:

string,

year:int,

length:

int,

filmtype:

string)Database

=

collection

of

relations.Database

schema

=

set

of

all

relationschemas

in

the

database.關系模式2.2.3

TuplesTuples(rows)titleyear

lengthfilmTypeStar

Wars1977124colorWayne’s

World199295colorMighty

Ducks1991104color…………Tuples

元組The

rows

of

a

relation,

other

than

theheader

row

containing

the

attributes,

arecalled

tuples.There

may

be

no

tuple

in

a

relat分io量n.A

tuple

has

one

component

for

eachattribute

of

the

relation.2.2.3

TuplesHow

to

describe

atuple?Use

commas

to

separate

components,and

use

parentheses

to

surround

thetuple.Example:

(Star

Wars,

1977,

124,

color)We

should

always

use

the

order

in

whichthe

attributes

were

listed

in

the

relationschema.2.2.3

TuplesThe

mapping

of

tuples

and

objects:A

relationA

tuple

-------

a

class

------

a

objectA

component

of

a

tuple

------

aproperty

of

a

object2.2.3

TuplesThedifference

of

tuples

andobjects:Objects

have

identities,

while

tupleshave

not.A

class

could

have

two

different

objectswith

the

same

values

in

all

attributes,but

a

tuple

can’t

appear

more

than

oncein

a

relation.2.2.4

DomainsDomains

域A

domain

is

an

elementary

type,

such

asinteger,

char(n),

date,

time.Each

attribute

of

a

relation

is

a

domain,that

is,

a

particular

elementary

type.Each

component

of

any

tuple

must

beatomic.Movies

(

title:

string,

year:

int,

length:

int,filmtype:

string)Can

not

be

broken

intosmaller

components2.2.5

Equivalent

Representations

of

a

RelationWecan

reorder

theattributes

of

a relation,

without

changing

the relation.Wecan

reorderthe

tuples

of

a relation,

without

changing

the relation.titleyearlengthfilmTypeStar

Wars1977124colorWayne’s

World199295colorMighty

Ducks1991104color…………2.2.7

Keys

of

RelationsWhat

is

a

key?A

set

of

attributes

forms

a

key

for

arelation

if

we

do

not

allow

two

tuplesin

a

relation

instance

to

have

the

samevalues

in

all

the

attribute

of

the

key.Movies

(

title,

year,

length,

genre

)

employee-ID,

Social-Securitynumber,student-ID,

drivers’

licensenumbersandautomobile

registration

number鍵，關鍵字，碼2.2.7

Keys

of

Relations

Key

ofthe

relationMovies

(

title,

year,length,

genre,

studioName,starName):{title,

year}

?{title,

year,

starName}

?2.2.8

An

Example

Database

SchemaFollowing

is

an

example

of

database

application:We'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

sales

department","JiangSu

sales

department").

Also

manage

everysalesman

information

in

those

departments

includingexclusive

employee

number,

ID

card

number,

andsome

private

information

(such

as

name,

gender,birthday

and

phone

number).

By

the

way

one

ofsalesmen

will

act

as

the

department

manager

in

hisdepartment.2.2.8

An

Example

Database

Schema

Manage

a

group

of

customers:

name,

province,

city,company

name,

phone

number.Manage

all

the

merchandises'

information: manufacturers

(e.g.

Chunlan,

Hailer

),

types

(e.g. motorcycle,

air

conditioner

),

specifications

(e.g. "MT125",

"RE1500"

),

prices,

descriptions.

Manage

sales

order

which

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.

Eachmerchandise

in

the

order

should

have

its

quantityand

unit

price

which

will

be

used

to

calculate

thetotal

prices.2.2.8

An

Example

Database

SchemaCustomer

(custid,

name,

prov,

city,

phone,

company)Merchandise

(merid,

manufacturer,

type,

spec,

price,desc)Salesman

(empid,

idno,

name,

gender,

phone,

deptid)Department

(deptid,

name,

headerid)Salesorder

(orderno,

signdate,

empid,

custid)Salesitem

(orderno,

lineno,

merid,

unitprice,

quantity)2.2

Basics

of

the

Relational

ModelExercises:p282.2.1

2.2.32.3

Defining

a

Relation

Schema

in

SQL

SQL

is

primarily

a

query

language,

forgetting

information

from

a

database.

SQL

also

includes

a

data-definitioncomponent

for

describing

databaseschemas.2.3.1

Relations

in

SQLThree

kinds

of

relations:Tables

exist

in

the

database

and

canbe

modified

by

changing

their

tuples,as

well

as

queried.Views

are

defined

by

a

computation.Temporary

tables

are

constructed

bythe

SQL

language

processor

when

itperforms

its

job

of

executing

queriesand

data

modifications.2.3.2

Data

TypesAll

attributes

must

have

a

data

type.Character

strings

of

fixed

or

varying

length.Char(n),

varchar(n)

Bit

strings

of

fixed

or

varying

length.Bit(n),

bit

varying(n)integer

valuesTinyint,

Smallint,

Int

|

integer,

Bigint

Floating-point

numbersReal,

double,

floatDecimal

|

dec(precision,

scale),numeric(precision,

scale)

Dates

and

timesDate

yyyy-mm-ddTime

hh:mm:ss.sssssBooleanTure,

False,

Unknown2.3.3

Simple

Table

DeclarationsHow

to

declare

a

relation

schema?CREATE

TABLE

salesman(empididnonamegenderphonedeptidchar(10)

PRIMARY

KEY,char(18)

UNIQUE,char(8)

NOT

NULL,tinyint

NOT

NULL,char(20)

NULL,integer

NULL)2.3.3

Simple

Table

DeclarationsCREATE

TABLE

department(integer

PRIMARY

KEY,deptidnameheaderidchar(40)

NOT

NULL,char(10)

NULL)Note

that

there

is

no

cognominal tables

in

a

database.2.3.4

Modifying

Relation

SchemasHow

to

delete

a

table?DROP

TABLE

T;Note

that

all

records

in

T

will

be

deleted.2.3.4

Modifying

Relation

Schemas

How

to

modify

the

schema

of

an

existingrelation?ALTER

TABLE

R

Add

column-name

datatype [NOT]

NULLALTER

TABLE R

Drop

column-nameALTER

TABLE R

Modify

column-name datatype [NOT]

NULLALTER

TABLE R

Rename

SALTER

TABLE R

Rename

column-name1

To column-name2Example:Alter

Table

department

Rename

name

Todeptname;2.3.5

Default

ValuesThe

use

of

default

values:When

we

create

or

modify

tuples,

we sometimes

do

not

have

values

for

all components.SQL

provides

the

NULL

value

as

default

value.When

we

declare

an

attribute

and

its

data

type, we

may

add

the

keyword

DEFAULT

and

an appropriate

value.Example:

In

relation

salesorder:signdate

data

NOT

NULL

DEFAULT

current

date;Autoincrement

is

a

default

value

to

integer

values.缺省值2.3.6

Declaring

KeysHow

to

declare

a

primary

key?There

are

two

way

to

declare

a

primary

key in

SQL

statement.1.

We

may

declare

an

attribute

to

be

a

primarykey

when

that

attribute

is

listed

in

therelation

schema.Example:CREATE

TABLE

department(deptid

integer

PRIMARY

KEY,name

char(40) NOT

NULL,headerid

char(10)

NULL);2.3.6

Declaring

Keys2.

We

may

add

to

the

list

of

items

in

theschema

an

additional

declaration

that

says

aparticular

attribute

or

set

of

attributes

formsthe

primary

key.Example:CREATE

TABLE

salesitem(ordernolinenochar(10),char(4),prodid

char(6)

NOT

NULL,unitprice

float

NOT

NULL,quantity

int

NOT

NULL,Primary

Key(orderno,

lineno));2.3

Defining

a

Relation

Schema

in

SQLExercises:p362.3.12.4

An

Algebraic

Query

LanguageMathematical

system

consisting

of:Operands

---

variables

or

valuesfrom

which

new

values

can

beconstructed.Operators

---

symbols

denotingprocedures

that

construct

newvalues

from

given

values.2.4.2

What

is

an

AlgebraWhat

is

Relational

Algebra?An

algebra

whose

operands

are

relationsor

variables

that

represent

relations.Operators

are

designed

to

do

the

mostcommon

things

that

we

need

to

do

withrelations

in

a

database. The

result

is

an

algebra

that

can

beused

as

a

query

language

for

relations.2.4.3

Overview

of

Relational

AlgebraThe

operations

of

relational

algebra

fall

into

four

broadclasses:the

usual

set

operations:

union,

intersection

anddifference

(on

two

relations)operations

that

remove

parts

of

a

relation:

(on

arelation)Selection:

eliminates

some

rows

(tuples)Projection:

eliminates

some

colunms

(attributes)2.4.3

Overview

of

Relational

Algebraoperations

that

combine

the

tuples

of

tworelationsCartesian

product:

pairs

the

tuples

of

tworelations

in

all

possible

waysJoin:

selectively

pair

tuples

from

two

relationsNatural

joinTheta-joinrenaming:

does

not

affect

the

tuples

of

arelation,

but

changes

the

relation

schema.2.4.4

Set

Operations

on

RelationsOperations

on

sets

R

and

S:R∪S

=

{

t

|

t∈R

or

t∈S

}:

unionthe

set

of

elements

that

are

in

R

or

S

orboth,

and

an

element

appears

only

once.并2.4.4

Set

Operations

on

Relations2.4.4

Set

Operations

on

RelationsOperations

on

sets

R

and

S

(Cons.):R∩S

=

{

t|

t

∈R

and

t∈S

}:intersectionthe

set

of

elements

that

are

in

both

Rand

S.交2.4.4

Set

Operations

on

Relations2.4.4

Set

Operations

on

RelationsOperations

on

sets

R

and

S

(Cons.):R

-

S

=

{

t

|

t∈R

and

not

t∈S

}:differencethe

set

of

elements

that

are

in

R

but

notin

S.Note

that

R差–S

is

different

from

S–R.2.4.4

Set

Operations

on

Relations2.4.4

Set

Operations

on

RelationsConditions

on

R

and

S:R

and

S

must

have

schemas

withidentical

sets

of

attributes.Before

we

compute

the

set-theoreticoperations,

the

columns

of

R

and

Smust

be

ordered

so

that

the

order

ofattributes

is

the

same

for

bothrelations.2.4.4

Set

Operations

on

RelationsRSR∪SR∩SR

–

SABa1a1b1b2ABa1a1b1b32.4.4

Set

Operations

on

RelationsRSR∪SR∩SR

–

SABa1a1b1b2BCa1a1b1b32.4.5

ProjectionProjection

投影:Produce

from

a

relation

R1

a

newrelation

that

has

only

some

of

R2’scolumns.Denotation:

R1:=∏A1,A2,…,An

(R2)A1,A2,…,An

is

a

list

of

attributes

fromthe

schema

of

R2.R1

is

constructed

by

looking

at

eachtuple

of

R2,

extracting

the

attributes

onlist

A1,A2,…,An,

in

the

order

specified,and

creating

from

those

components

atuple

for

R1.Eliminate

duplicate

tuples,

if

any.2.4.5

ProjectiontitleyearlengthinColorstudioNameProducerC#Star

Wars1977124TrueFox12345Mighty

Ducks1991104TrueDisney67890Wayne’s

World199295TrueParamount99999titleyearlengthStar

Wars1977124Mighty

Ducks1991104Wayne’s

World199295Example:

Movieπtitle,year,length(Movie)πinColor(Movie)inColorTrueExampleStudent

(Sid,

name,

phone,

sex)Find

IDsand

namesof

allstudents.Πsid,

name(student)2.4.6

SelectionSelection

選擇:Produce

from

a

relation

R

a

newrelation

with

a

subset

of

R’s

tuple.Denotation:

σC(R)Result:

The

schema

for

the

resultingrelation

is

the

same

as

R’s

schema,

andthe

tuples

in

the

resulting

relation

arethose

that

satisfy

some

condition

C

thatinvolves

the

attributes

of

R.Expression

of

condition

C:Operands:

constants

or

attributes

of

ROperators:

=

≠

>

≥

<

≤

NOT

AND

OR2.4.6

SelectionExample:

MovietitleyearlengthinColorstudioNameProducerC#Star

Wars1977124TrueFox12345Mighty

Ducks1991104TrueDisney67890Wayne’s

World199295TrueParamount99999titleyearlengthinColorstudioNameProducerC#Star

Wars1977124TrueFox12345Mighty

Ducks1991104TrueDisney67890σlength

≥100(Movie):σlength

≥100

AND

studioName=‘FOX’(Movie):titleyearlengthinColorstudioNameProducerC#Star

Wars1977124TrueFox12345ExampleStudent

(Sid,

name,

phone,

sex)Find

all

female

students.σ

sex=‘F’

(student)2.4.7

Cartesian

ProductCartesian

product

笛卡爾積The

Cartesian

product

of

two

sets

Rand

S

is

the

set

of

pairs

that

can

beformed

by

choosing

the

first

elementof

the

pair

to

be

any

element

of

R

andthe

second

an

element

of

S.Denotation:

R×S2.4.7

Cartesian

ProductResult:The

relation

schema

for

the

resultingrelation

is

the

attributes

of

R

andthen

S,

in

order.To

disambiguate

an

attribute

A

that

isin

the

schemas

of

both

R

and

S,

weuse

R.A

for

the

attribute

from

R

andS.A

for

the

attribute

from

S.The

tuples

in

the

resulting

relationare

pairs

the

tuples

of

R

and

S

in

allpossible

ways.The

number

of

tuples

is

NR×NS.2.4.7

Cartesian

Product演示2.4.7

Cartesian

ProductAB1234BCD25647891011AR.BS.BCD1225612478129101134256344783491011Example:

RSR×S

？2.4.8

Natural

JoinsNatural

join

connects

two

relations

by:Equating

attributes

of

the

same

name,andProjecting

out

one

copy

of

each

pair

ofequated

attributes.Denotation:

R

S2.4.8

Natural

Joins演示2.4.8

Natural

JoinsAE1234BCD25647891011Example:

RSRSNote

that

the

natural

join

of

two

relations

Rand

S

is

invalid,

if

R

and

S

have

no

commonattributes.2.4.8

Natural

JoinsBCD2342357810Example:

UVU

VABC162738979ABCD1162273384510A

tuple

that

fails

to

pairwith

any

tuple

of

the

otherrelation

in

join

is

sometimessaid

to

be

a

dangling

tuple.2.4.9

Theta-JoinsTheta-join:Pair

tuples

using

an

arbitrary

condition.Denotation:

R

C

SResult:Take

the

product

of

R

and

S.Select

from

the

product

only

thosetuples

that

satisfy

the

condition

C.2.4.9

Theta-JoinsBCD2342357810Example:

UVUA<DVABC123678979AU.BU.CV.BV.CD1232341232351237810678781097978102.4.10

Combining

Operations

to

Form

QueriesCombining

operations:Form

expressions

of

arbitrarycomplexity

by

applying

operatorseither

to

given

relations

or

to

relationsthat

are

the

result

of

applying

one

ormore

relational

operators

to

relations.Use

parentheses

when

necessary

toindicate

grouping

of

operands.ExampleStudent

(Sid,

name)Course(Cid,

name)Enrollment(

Sid,Cid,score)Find

names

of

all

studentswho learned

‘C1’.Find

names

of

all

studentswho learned

both

‘C1’

and‘C2’.Find

names

of

all

studentswho didn’t

learn‘C1’.2.4.11

Naming

and

RenamingRenaming

改名Denotation:

ρS

(A1,

A2,…,An)

(R)Result:

the

resulting

relation

has

exactlythe

same

tuples

as

R,

but

the

name

of

therelation

is

S.

And

the

attributes

of

theresult

relation

S

are

named

A1,

A2,

...,

An,in

order

from

the

left.Denotation:

ρS

(R)Result:

only

change

the

name

of

therelation

to

S

and

leave

the

attributes

asthey

are

in

R.2.4.11

Naming

and

RenamingAB1234BCD25647891011Example:

RSAR.BS.BCD1225612478129101134256344783491011R×SR×ρS

(X,

C,

D)

(S)ABXCD1225612478129101134256344783491011An

equivalent

expression:

ρRS

(A,

B,

X,

C,

D)

(R×S)2.4.11

Naming

and

RenamingExample:

Salesman(empid,

name)Find

the

employee

IDs

and

names

of

all

pairs of

employees

who

have

the

same

name.s2.empid,πs1.empid,

(ρS1(Salesman)∞=

AND(Salesman)))s1.empid<s2.empid(ρS22.4.12

Relationships

Among

Operations

Some

of

the

operations

can

be

expressed

interms

of

other

relational-algebra

operations.R∩S

＝

R—(R—S)θ-join:

R

∞c

S

＝σc

(R

×

S)Natural

join：R

∞

S

＝

πL

(σc

(R

×

S)) C

is

the

form

where

R.A1=S.A1

ANDR.A2=S.A2

AND

...

AND

R.An=S.Anwhere

A1,

A2,

...,

An

are

all

theattributes

appearing

in

the

schemas

ofboth

R

and

S. L

is

the

list

of

attributes

in

the

schemaof

R

followed

by

those2.4.12

Relationships

Among

Operations These

three

operations

are

calleddependent

operations

which

can

beexpressed

by

other

operations,

andthe

six

other

operations

areindependent

operations.Schemas

for

ResultsUnion,

intersection,

and

difference:

the schemas

of

the

two

operands

must

be the

same,

so

use

that

schema

for

the result.Selection:

schema

of

the

result

is

the same

as

the

schema

of

the

operand.Projection:

list

of

attributes

tells

us

the schema.Schemas

for

ResultsProduct:

schema

is

the

attributes

of

both relations.Use

R.A,

etc.,

to

distinguish

twoattributes

named

A.Theta-join:

same

as

product.Natural

join:

union

of

the attributes

of the

two

relations.Renaming:

the

operator

tells

the

schema.2.4.14

ExampleCustomer

(custid,

name,

prov,

city,

phone,company)Merchandise

(merid,

f