Chapter

1:

Introductionn

Applications

of

Database

Systemsn

Database

Systems

versus

File

Systemsn

View

of

Datan

Data

Modelsn

Database

Languagen

Database

Users

and

Administratorn

Transaction

Managementn

Database

System

Structuren

Application

Architecturesn

History

of

Database

Systemsn

SummaryDatabase

Management

System

(DBMS)Collection

of

interrelated

data.Set

of

programs

to

access

the

data.DB

contains

information

about

a

particular

enterprise.DBMS

provides

an

environment

that

is

both

convenientand

efficient

to

use.

Database

systems

are

designed

to

manage

large

bodiesof

information.DBS

must

ensure

the

safety

of

the

information

stored.Several

users.Data

Database(DB) Database

Management

System(DBMS) Database

System(DBS)1

Applications

of

Database

SystemsDatabase

Applications:Banking:

all

transactionsAirlines:

reservations,

schedulesUniversities:

registration,

gradesSales:

customers,

products,

purchasesManufacturing:

production,

inventory,

orders,

supply

chainHuman

resources:

employee

records,

salaries,

tax

deductionsDatabases

touch

all

aspects

of

our

lives2

Database

Systems

versus

File

SystemsIn

the

early

days,

database

applications

were

built

on

top

offile

systemsDrawbacks

of

using

file

systems

to

store

data:Data

redundancy

and

inconsistencyMultiple

file

formats,

duplication

of

information

in

different

filesDifficulty

in

accessing

dataNeed

to

write

a

new

program

to

carry

out

each

new

taskData

isolation

—

multiple

files

and

formatsIntegrity

problems

Integrity

constraints

(e.g.

account

balance

>

0)

become

part

ofprogram

codeHard

to

add

new

constraints

or

change

existing

ones2

Database

Systems

versus

File

Systems(Cont.)Drawbacks

of

using

file

systems

(cont.)Atomicity

of

updates

Failures

may

leave

database

in

an

inconsistentstate

with

partial

updates

carried

out

E.g.

transfer

of

funds

from

one

account

toanother

should

either

complete

or

not

happen

atallConcurrent

access

by

multiple

usersConcurrent

accessed

needed

for

performance

Uncontrolled

concurrent

accesses

can

lead

toinconsistencies

E.g.

two

people

reading

a

balance

andupdating

it

at

the

same

timeSecurity

problems3.1

Data

Abstractio3o3nView

of

Data

Physical

level

describes

how

a

record

(e.g.,

customer)

isstored.

Logical

level:

describes

data

stored

in

database,

and

therelationships

among

the

data.type

customer

=

recordname

:

string;street

:

string;city

:

integer;end;

View

level:

application

programs

hide

details

of

datatypes.

Views

can

also

hide

information

(e.g.,

salary)

forsecurity

purposes.3

View

of

Data

(Cont.)An

architecture

for

a

database

system3

View

of

Data

(Cont.)3.2

Instances

and

Schemas

Similar

to

types

and

variables

in

programminglanguagesSchema

–

the

logical

structure

of

the

database

e.g.,

the

database

consists

of

information

about

aset

of

customers

and

accounts

and

the

relationship

between

them)

Analogous

to

type

information

of

a

variable

in

aprogram

Physical

schema:

database

design

at

the

physicallevel

Logical

schema:

database

design

at

the

logicallevel3

View

of

Data

(Cont.)

Instance

–

the

actual

content

of

the

database

at

a

particular

point

intimeAnalogous

to

the

value

of

a

variable

Physical

Data

Independence

–

the

ability

to

modify

the

physicalschema

without

changing

the

logical

schemaApplications

depend

on

the

logical

schema

In

general,

the

interfaces

between

the

various

levels

andcomponents

should

be

well

defined

so

that

changes

in

some

partsdo

not

seriously

influence

others.4

Data

ModelsA

collection

of

tools

for

describingdatadata

relationshipsdata

semanticsdata

constraintsEntity-Relationship

modelRelational

modelOther

models:

object-oriented

model、object-relational

datamodelsemi-structured

data

modelsOlder

models:

network

model

and

hierarchicalmodelExample4o.f1scEhEenmtatiiinttyh-e-ReRnetliltayt-trieoloantsisohnisihpipMmooddeel

l4E.-R1mEondnetliotfyreRaellwaortlidonship

Model

(Cont.)Entities

(objects)E.g.

customers,

accounts,

bank

branchRelationships

between

entitiesE.g.

Account

A-101

is

held

by

customer

Johnson

Relationship

set

depositor

associatescustomers

with

accountsWidely

used

for

database

design

Database

design

in

E-R

model

usually

converted

todesign

in

the

relational

model

(coming

up

next)which

is

used

for

storage

and

processingExample

of4t.a2buRlearldadaattiaoinnatlheMroeldaetlional

modelcustomer-nameCustomer-idcustomer-streetcustomer-city192-83-7465JohnsonAlma019-28-3746SmithNorth192-83-7465JohnsonAlma321-12-3123JonesMain019-28-3746SmithNorthPalo

AltoRyePalo

AltoHarrisonRyeaccount-numberA-101A-215A-201A-217A-201AttributesA

Sample

Relational

Database5

Database

Language5.1

Data

Definition

Language

(DDL)Specification

notation

for

defining

the

database

schemaE.g.create

table

account

(account-number

char(10),balanceinteger)

DDL

compiler

generates

a

set

of

tables

stored

in

a

datadictionaryData

dictionary

contains

metadata

(i.e.,

data

about

data)database

schemaData

storage

and

definition

language

language

in

which

the

storage

structure

andaccess

methods

used

by

the

database

systemare

specified

Usually

an

extension

of

the

data

definitionlanguage5

Database

Language

(Cont.)5.2

Data

Manipulation

Language

(DML)

Language

for

accessing

and

manipulating

the

dataorganized

by

the

appropriate

data

modelTwo

classes

of

languages

Procedural

–

user

specifies

what

data

is

requiredand

how

to

get

those

data

Nonprocedural

–

user

specifies

what

data

isrequired

without

specifying

how

to

get

those

dataSQL

is

the

most

widely

used

query

language5.3

SQLSQL:5wiDdaetlyabusaesdenoLna-pnrgouceadguera(lCloanngtu.ag)e

E.g.

find

the

name

of

the

customer

with

customer-id

192-83-7465select

customer.customer-namefromcustomerwhere

customer.customer-id

=

‘192-83-7465’

E.g.

find

the

balances

of

all

accounts

held

by

thecustomer

with

customer-id

192-83-7465select

account.balancefromdepositor,

accountwhere

depositor.customer-id

=

‘192-83-7465’

and

depositor.account-number

=account.account-number5

Database

Language

(Cont.)Application

programs

generally

access

databases

through

one

ofLanguage

extensions

to

allow

embedded

SQL

Application

program

interface

(e.g.

ODBC/JDBC)

which

allow

SQLqueries

to

be

sent

to

a

database6

Database

Users

AND

Database

AdministratorDatabase

Users

Application

programmers

–

interact

with

systemthrough

DML

calls

Sophisticated

users

–

form

requests

in

a

databasequery

language

Specialized

users

–

write

specialized

databaseapplications

that

do

not

fit

into

the

traditional

dataprocessing

framework

Naive

users

–

invoke

one

of

the

permanentapplication

programs

that

have

been

writtenpreviously

E.g.

people

accessing

database

over

the

web,bank

tellers,

clerical

staff6

Database

Users

AND

Database

Administrator(Cont.)Database

administrator"s

duties

include:Schema

definitionStorage

structure

and

access

method

definitionSchema

and

physical

organization

modificationGranting

user

authority

to

access

the

databaseRoutine

maintenance

A

tran7saTctrtriaonnsiascatciolololnecMtaionnaogfeompeenrnrattions

that

performs

a

single

logical

function

in

a

database

application

Transaction-management

component

ensures

that

thedatabase

remains

in

a

consistent

(correct)

state

despitesystem

failures

(e.g.,

power

failures

and

operatingsystem

crashes)

and

transaction

failures.Atomicity、consistency、isolation、durability

Concurrency-control

manager

controls

the

interactionamong

the

concurrent

transactions,

to

ensure

theconsistency

of

the

database.Failure

recovery8

Database

System

StructureStorage

Manager

Storage

manager

is

a

program

module

that

provides

the

interfacebetween

the

low-level

data

stored

in

the

database

and

theapplication

programs

and

queries

submitted

to

the

system.The

storage

manager

components

include:Authorization

and

integrity

managerTransaction

managerFile

managerBuffer

managerData

files,Data

dictionary,Indices8

Database

System

Structure

(Cont.)The

Query

ProcessorDDL

interpreterDML

compilerQuery

evaluation

engineOverall

System

Structure9

Application

Architectures·Two-tier

architecture: E.g.

client

programs

usingODBC/JDBC

to

communicate

with

a

database·Three-tier

architecture:E.g.web-basedapplications,

andapplications

built

using

“middleware”Basic

ConceptsEntity

SetsA

database

can

be

modeled

as:a

collection

of

entities,relationship

among

entities.

An

entity

is

an

object

that

exists

and

isdistinguishable

from

other

objects.Example:

specific

person,

company,

event,

plantEntities

have

attributesExample:

people

have

names

and

addresses

An

entity

set

is

a

set

of

entities

of

the

same

type

thatshare

the

same

properties.

Example:

set

of

all

persons,

companies,

trees,holidaysEntity

Sets

customer

and

loancustomer-idamountcustomer-

customer-

customer-nameloan-streetcitynumberAttributes

An

entiBtayay

sisircepCreosnecncnteepd

tbysa(sCetoononfta.t)tributes,

that

isdescriptive

properties

possessed

by

all

members

ofan

entity

set.

Domain

–

the

set

of

permitted

values

for

eachattributeAttribute

types:Simple

and

composite

attributes.Single-valued

and

multi-valued

attributesE.g.

multivalued

attribute:

phone-numbersDerived

attributesCan

be

computed

from

other

attributesE.g.

age,

given

date

of

birthComposite

AttributesRelationsBhaipsSiectsConcepts

(Cont.)

A

relationship

is

an

association

among

severalentitiesExample:Hayesdepositorcustomer

entityA-102relationship

setaccount

entityA

relationship

set

is

a

mathematical

relation

amongn

2

entities,

each

taken

from

entity

sets{(e1,

e2,

…

en)

|

e1

E1,

e2

E2,

…,

en

En}where

(e1,

e2,

…,

en)

is

a

relationshipExample:(Hayes,

A-102)depositorRelationship

Set

borrowerAn

attribute

can

also

be

property

of

a

relationship

set.For

instaBncae,sthie

cdcepoCsiotonr

rceleatpiotnsshi(p

CsCeot

bnettwe.e)n)entity

setscustomer

and

account

may

have

the

attribute

access-dateDegree

of

a

Relationship

Set

RefersBtoansuimbcerCoofnencteitpytsset(sCtohnanttp.a)rticipate

in

arelationship

set.

Relationship

sets

that

involve

two

entity

sets

are

binary(or

degree

two).

Generally,

most

relationship

sets

in

adatabase

system

are

binary.Relationship

sets

may

involve

more

than

two

entity

sets.HE.g.Suppose

employees

of

a

bank

may

havejobs

(responsibilities)

at

multiple

branches,

withdifferent

jobs

at

different

branches. Then

there

is

a

Rtelrantairoynsrheilpastiboentswheiepn

msoerte

btehtawnetewno

entity

setsarereamrpel.oyMeoes,t

rejloabtiaonndshbirpasncahre

binary.

(More

on

this

later.)Mapping

CardinalCiotinesstraints

Express

the

number

of

entities

to

which

anotherentity

can

be

associated

via

a

relationship

set.Most

useful

in

describing

binary

relationship

sets.

For

a

binary

relationship

set

the

mapping

cardinalitymust

be

one

of

the

following

types:One

to

oneOne

to

manyMany

to

oneMany

to

manyConstraints

(Cont.)One

to

one One

to

manyNote:

Some

elements

in

A

and

B

may

not

be

mapped

to

anyelements

in

the

other

setConstraints

(Cont.)Many

to

one Many

to

manyNote:

Some

elements

in

A

and

B

may

not

be

mapped

to

anyelements

in

the

other

setParticipation

Constraints

The

particCipoantsitotnroafianntesnt(iCtyonsett.E)in

a

relationshipset

R

is

said

to

be

total

if

every

entity

in

E

participatesin

at

least

one

relationship

in

R.

For

example,

we

expect

every

loan

entity

to

berelated

to

at

least

one

customer

through

theborrower

relationship.

If

only

some

entities

in

E

participate

in

relationship

inR,

the

participation

of

entity

set

E

in

relationship

R

issaid

to

be

partial.

For

example,

the

participation

of

customer

in

theborrower

relationship

set

is

therefore

set

istherefore

partial.n

A

candidate

key

of

an

entity

set

is

a

minimal

superkeyHCustomer-id

is

canKdiedyatse

key

of

customerHaccount-number

is

candidate

key

of

accountn

Although

several

candidate

keys

may

exist,

one

ofthe

candidate

keys

is

selected

to

be

the

primarykey.n

Relationship

Setsn

The

combination

of

primary

keys

of

the

participatingentity

sets

forms

a

super

key

of

a

relationship

set.n

Entity

Setsn

A

super

key

of

an

entity

setis

a

setof

one

ormore

attributes

whose

valueseach

entity.uniquelydetermineH(customer-id,

account-number)

is

the

super

key

ofdepositorH

NOTE:

this

means

a

pair

of

entity

sets

can

have

atmost

one

relationship

in

a

particular

relationship

set.4

E.g.

if

we

wish

to

track

all

access-dates

to

eachaccount

by

each

customer,

we

cannot

assume

arelationship

Kfoeryesac(hCaocnctets.s). We

can

use

amultivalued

attribute

thoughn

Must

consider

the

mapping

cardinality

of

the

relationshipset

when

deciding

the

what

are

the

candidate

keysn

Need

to

consider

semantics

of

relationship

set

inin

case

of

more

than

oneselecting

the

primary

keycandidate

keyE-R

Diagram

with

a

Ternary

Relationship返回E-R

Diagramsn

Rectangles

represent

entity

sets.n

Diamonds

represent

relationship

sets.n

Lines

link

attributes

to

entity

sets

and

entity

sets

torelationship

sets.n

Ellipses

representattributesn

Double

ellipses

represent

multivalued

attributes.n

Dashed

ellipses

denote

derived

attributes.n

Underline

indicates

primary

key

attributesE-R

Diagram

With

Composite,

Multivalued,

and

DerivedAttributesRelationship

Sets

with

AttributesEntity

sets

of

a

relationship

need

not

be

distinct

The

labels

“manager”

and

“Rwoolrkeers”

arecalledroles;

they

specify

howemployee

entities

interact

via

the

works-for

relationship

set.

Roles

are

indicated

in

E-R

diagrams

by

labeling

the

lines

that

connectdiamonds

to

rectangles.

Role

labels

are

optional,

and

are

used

to

clarify

semantics

of

therelationshipWe

express

caCradirnadliitnyaclolnisttryainCtsobnysdtrarwianig

neittshser

a

directed

line(

),

signifying“one,”

or

an

undirected

line

(—),signifying“many,”between

the

relationship

set

and

the

entity

set.E.g.:

One-to-onerelationship:

A

customer

is

associated

with

at

most

one

loan

via

therelationship

borrowerA

loan

is

associated

with

at

most

one

customer

via

borrowerOne-To-Many

Relationship

In

the

one-to-many

relationship

a

loan

is

associatedwith

at

most

one

customer

via

borrower,

a

customer

isassociated

with

several

(including

0)

loans

viaborrowerMany-To-One

Relationships

In

a

many-to-one

relationship

a

loan

is

associated

withseveral

(including

0)

customers

via

borrower,

acustomer

is

associated

with

at

most

one

loan

viaborrowerMany-To-Many

Relationship

A

customer

is

associated

with

several

(possibly0)

loans

via

borrower

A

loan

is

associated

with

several

(possibly

0)customers

via

borrowerParticipation

of

an

Entity

Set

in

a

Relationship

Setn

Total

participation

(indicated

by

double

line): every

entity

in

theentity

set

participates

in

at

least

one

relationship

in

therelationship

setn

E.g.

participation

of

loan

in

borrower

is

totaln

every

loan

must

have

a

customer

associated

to

it

viaborrowern

Partial

participation:

some

entities

may

not

participate

in

anyrelationship

in

the

relationship

setn

E.g.

participation

of

customer

in

borrower

is

partialAlternative

Notation

for

Cardinality

Limitsn

Cardinality

limits

can

also

express

participationconstraintsH

The

telephone

entity

set

with

attributes

telephone-number

and

locationH

The

relationship

set

emp-telephone,

which

denotesthe

association

between

employees

and

thetelephones

thatDetsheiyghnavIessuesH

What

constitutes

an

attribute,

and

what

constitutesan

entity

set?H

A

common

mistake

is

to

use

the

primary

key

of

anentity

set

as

another

entity

set,

instead

of

using

arelationship.H

Another

related

mistake

that

people

sometimes

makeis

to

designate

the

primary

key

attributes

of

therelated

entity

sets

as

attributes

of

the

relationshipn

Use

of

entity

sets

vs.

attributesH

The

employee

entity

set

with

attribute

employee-namen

Use

of

entity

sets

vs.

relationship

setsH

We

assumed

that

a

bank

loan

is

modeled

as

anentity.H

An

alternative

is

to

model

a

loan

not

as

an

entity,but

rather

as

a

relationship

between

customers

andbranches,with

loan-number

and

amount

as

descriptive

attributes.n

Binary

versuDsens-iargynrIelsastuiuoenssh(ipCosentts.)Some

relationships

that

appear

to

be

non-binary

maybe

better

represented

using

binary

relationshipsHE.g. A

ternary

relationship

parents,

relating

a

childto

his/her

father

and

mother,

is

best

replaced

bytwo

binary

relationships,

father

and

mother4

Using

two

binary

relationships

allows

partialinformation

(e.g.

only

mother

being

know)HBut

there

are

some

relationships

that

are

naturallynon-binary2.RB,

relating

E1.

RA,

relating

E

and

Aand

B3.

RC,

relating

E

and

CHCreate

a

special

identifying

attribute

for

EHAdd

any

attributes

of

R

to

EHFor

each

relationship

(ai

,

bi

,

ci)

in

R,

create4

E.g.

works-on3

In

general,

any

non-binary

relationship

can

berepresented

using

binary

relationships

by

creating

anartificial

entity

set.HReplace

R

between

entity

sets

A,

B

and

C

by

anentity

seDteEs,iagnnd

ItIhsrseeuersesla(tCioonnsthi.p)sets:1.

a

new

entity

ei

in

the

entity

set

Eai

)

to

RA3.

add

(ei

,

bi

)

to

RB4.

add

(ei

,

ci

)

to

RC2.

add

(ei

,Design

Issues

(Cont.)Placement

of

relationship

attributesCan

make

access-date

an

attribute

of

account,

instead

of

arelationship

attribute,

if

each

account

can

have

only

onecustomerH

I.e.,

the

relationship

from

account

to

customer

is

many

toone,

or

equivalently,

customer

to

account

is

one

to

manyDesign

Issues

(Cont.)

An

entity

set

that

does

not

have

a

primary

key

is

referred

to

asa

weak

entity

setW.

eak

Entity

Sets

The

existence

of

a

weak

entity

set

depends

on

the

existenceof

a

identifying

entity

set

it

must

relate

to

the

identifying

entity

set

via

a

total,

one-to-many

relationship

set

from

the

identifying

to

the

weak

entitysetIdentifying

relationship

depicted

using

a

double

diamond

The

discriminator

(or

partial

key)

of

a

weak

entity

set

is

the

set

of

attributes

that

distinguishes

among

all

the

entities

of

a

weak

entity

set.

The

primary

key

of

a

weak

entity

set

is

formed

by

the

primarykey

of

the

strong

entity

set

on

which

the

weak

entity

set

isexistence

dependent,

plus

the

weak

entity

set’s

discriminator.Weak

Entity

Sets

(Cont.)We

depict

a

weak

entity

set

by

double

rectangles.

We

underline

the

discriminator

of

a

weak

entity

set

with

adashed

line.payment-number

–

discriminator

of

the

payment

entity

setPrimary

key

for

payment

–

(loan-number,

payment-number)Weak

Entity

Sets

(Cont.)multivalued,composite

attributeA

weak

entity

set

may

be

more

appropriately

modeledas

an

attribute

if

it

participates

in

only

the

identifyingrelationship,and

if

it

has

few

attributes.

Conversely,aweak-entity-set

representation

will

more

aptly

model

asituation

where

the

set

participates

in

relationshipsother

than

the

identifying

relationship,

and

where

theweak

entity

set

has

several

attributes.

In

a

uniWveeraskiktyE,natcioturyseSiestasst(rConognetn.ti)ty

and

a

course-offering

can

be

modeled

as

a

weak

entity

The

discriminator

of

course-offering

would

be

semester(including

year)

and

section-number

(if

there

is

morethan

one

section)

If

we

model

course-offering

as

a

strong

entity

we

wouldmodel

course-number

as

an

attribute.Then

the

relationship

with

course

would

be

implicit

inthe

course-number

attributeSpecializationTop-downEdxetseignndperdocEes-sR;

FwFeedaetsuigrneastesubgroupings

within

an

entity

set

that

are

distinctivefrom

other

entities

in

the

set.These

subgroupings

become

lower-level

entity

setsthat

have

attributes

or

participate

in

relationships

thatdo

not

apply

to

the

higher-level

entity

set.Depicted

by

a

triangle

component

labeled

ISA

(E.g.customer

“is

a”

person).superclass-subclass

relationship.

Attribute

inheritance

–

a

lower-level

entity

set

inheritsall

the

attributes

and

relationship

participation

of

thehigher-level

entity

set

to

which

it

is

linked.GeneErxatlieznadtieodn

E-R

Features

(Cont.)

A

bottom-up

design

process

–

combine

a

number

of

entity

sets

that

share

the

same

features

into

ahigher-level

entity

set.

Specialization

and

generalization

are

simpleinversions

of

each

other;

they

are

represented

in

anE-R

diagram

in

the

same

way.

The

terms

specialization

and

generalization

areused

interchangeably.Extended

E-R

Features

(Cont.)

Can

have

multiple

specializations

of

an

entity

setbased

on

different

features.

E.g.

permanent-employee

vs.

temporary-employee,in

addition

to

officer

vs.

secretary

vs.

tellerEach

particular

employee

would

bea

member

of

one

of

permanent-employee

ortemporary-employee,and

also

a

member

of

one

of

officer,

secretary,

orteller

The

ISA

relationship

also

referred

to

as

superclass

-subclass

relationshipAttribute

Inheritancesaid

to

be

inherited

by

the

lower-level

entity

sets.HA

lower-level

entity

set(or

subclass)also

inheritsparticipation

in

the

relationship

sets

in

which

itshigher-level

entity(or