[PDF] - What is a Database Managemen t System? 1. Manages v ery PDF Document

SLIDE 1 What is a Database Managemen t System? 1. Manages v ery large amoun ts

f

data. 2. Supp

rts

ecien t access to v ery large amoun ts

f

data. 3. Supp

rts

concurren t access to v.l.a.d. 4. Supp

rts

secure, atomic access to v.l.a.d. 1

SLIDE 2 Relational Mo del

Based
n

tables, as: acct# name balance 12345 Sally 1000.21 34567 Sue 285.48

T
da

y used in most DBMS's. 2

SLIDE 3 Three Asp ects to Studying DBMS's 1. Mo deling and design

f

databases.

✦

Allo ws exploration

f

issues b efore committing to an implemen tation. 2. Programming: queries and DB

p

erations lik e up date.

✦

SQL = \in tergalacti c datasp eak." 3. DBMS implemen tation. CS145 = (1) + (2), while (3) is co v ered in CS245, CS346, CS347. 3

SLIDE 4 En tit y/Rel at io nship Mo del Diagrams to represen t designs.

Entity

lik e

b

ject, = \thing."

Entity

set lik e class = set

f

\similar" en tities/ob ject s.

A

ttribute = prop ert y

f

en tities in an en tit y set, similar to elds

f

a struct.

In

diagrams, en tit y set ! rectangle; attribute !

v

al. Studen ts ID name phone heigh t 4

SLIDE 5 Relationships

Connect

t w

r

more en tit y sets.

Represen

ted b y diamonds. Studen ts Courses T aking Relationship Set Think

f

the \v alue"

f

a relationship set as a table.

One

column for eac h

f

the connected en tit y sets.

One

ro w for eac h list

f

en tities,

ne

from eac h set, that are connected b y the relationship. Studen ts Courses Sally CS145 Sally CS244 Jo e CS145

5

SLIDE 6 Multiw a y Relationships Usually binary relationships (connecting t w

E.S.)

suce.

Ho

w ev er, there are some cases where three

r

more E.S. m ust b e connected b y

ne

relationship.

Example:

relationship among studen ts, courses, T A's. P

ssibly

, this E/R diagram is OK: Studen ts Courses T aking Assisting T As 6

SLIDE 7

W
rks

in CS145, b ecause eac h T A is a T A

f

all studen ts. Connection studen t-T A is

nly

via the course.

But

what if studen ts w ere divided in to sections, eac h headed b y a T A?

✦

Then, a studen t in CS145 w

uld

b e related to

nly
ne
f

the T A's for CS145. Whic h

ne?
Need

a 3-w a y relationship to tell. 7

SLIDE 8 Studen ts Courses T As Enrolls Studen ts Courses T As Ann CS145 Don Bob CS145 Edy Cal CS145 Don

8

SLIDE 9 Beers-Bars-Drink ers Example

Our

running example for the course. F requen ts Serv es Lik es Bars Beers Drink ers name addr license name name addr manf 9

SLIDE 10 Multiplicit y

f

Relationship s Man y-man y Man y-one One-one Represen tatio n

f

Man y-One

E/R:

arro w p

in

ting to \one."

✦

Rounded arro w = \exactly

ne."

10

SLIDE 11 Example: Drink ers Ha v e F a v

rite

Beers F requen ts Serv es Lik es Bars Beers Drink ers name addr license name name addr manf F a v

rite

11

SLIDE 12 One-One Relationshi ps Put arro ws in b

th

directions. Beers seller Best- Manfs Design Issue: Is the rounded arro w justied? Design Issue: Here, man ufacturer is an E.S.; in earlier diagrams it is an attribute. Whic h is righ t? 12

SLIDE 13 A ttributes

n

Relationshi ps Beers price Bars Sells

Shorthand

for 3-w a y relationship: Beers Bars Sells price Prices 13

SLIDE 14

A

true 3-w a y relationship.

✦

Price dep ends join tly

n

b eer and bar.

Notice

arro w con v en tion for m ultiw a y relationships: \all

ther

E.S. determine

ne
f

these."

✦

Not sucien tly general to express an y p

ssibili

t y .

✦

Ho w ev er, if price, sa y , dep ended

nly
n

the b eer, then w e could use t w

2-w

a y relationships: price-b eer and b eer-bar.

✦

Or b etter: just mak e price an attribute

f

b eer. 14

SLIDE 15 Con v erting Multiw a y to 2-W a y

Baro

que in E/R, but necessary in certain \ob ject-orien ted" mo dels.

Create

a new c

nne

cting E.S. to represen t ro ws

f

a relationship set.

✦

E.g., (Jo e's Bar, Bud, $2.50) for the Sel ls relationship.

Man

y-one relationships from the connecting E.S. to the

thers.

Beers Bars The- Beer Prices Bar The- The- Price BBP 15

SLIDE 16 Roles Sometimes an E.S. participates more than

nce

in a relationship.

Lab

el edges with r

les

to distinguish. Married Drink ers wife h usband Husband Wife d 1 d 2 d 3 d 4

16

SLIDE 17 Drink ers 1 2 Buddies Buddy1 Buddy2 d 1 d 2 d 1 d 3 d 2 d 1 d 2 d 4

Notice

Buddies is symmetric, Marrie d not.

✦

No w a y to sa y \symmetric" in E/R. Design Question Should w e replace husband and wife b y

ne

relationship spouse? 17

SLIDE 18 Sub classes Sub class = sp ecial case = few er en tities = more prop erties. Example Ales are a kind

f

b eer. In addition to the pr

p

erties (= attributes and relationships)

f

b eers, there is a \color" attribute for ales. 18

SLIDE 19 E/R Sub classes

Assume

sub classes form a tree (no m ultiple inheritance).

isa

triangles indicate the sub class relation. isa Beers Ales name manf color 19

SLIDE 20 Dieren t Sub class Viewp

in

ts 1. E/R viewp

int

: An en tit y has a c

mp
nent

in eac h en tit y set to whic h it logicall y b elongs.

✦

Its prop erties are the union

f

the prop erties

f

these E.S. 2. Con trasts with

bje

ct-oriente d viewp

int

: An

b

ject (en tit y) b elongs to exactly

ne

class.

✦

It inherits prop erties

f

its sup erclasses. isa Beers Ales name manf color P ete's Ale 20

SLIDE 21 Keys A key is a set

f

attributes suc h that no t w

en

tities agree

n

all these attributes.

In

E/R mo del, ev ery E.S. m ust ha v e a k ey .

✦

It could ha v e more than

ne

k ey , but

ne

set

f

attributes is the \designated" k ey .

In

E/R diagrams, y

u

should underline all attributes

f

the designated k ey . 21

SLIDE 22 Example Supp

se

name is k ey for Be ers. isa Beers Ales manf color name

Beer

name is also k ey for ales.

✦

In general, k ey at ro

t

is k ey for all. 22

SLIDE 23 Example: A Multiattribute Key Courses dept n um b er hours ro

m
P
ssibly

, hours + ro

m

also forms a k ey , but w e ha v e not designated it as suc h. 23