[PDF] - Design Problem OK; let's design a relational DB sc hema for PDF Document

SLIDE 1 Design Problem OK; let's design a relational DB sc hema for b eers- bars-drink ers.

Drink

ers ha v e unique names and addresses. They lik e

ne
r

more b eers and frequen t

ne
r

more bars. They ha v e phones, usually

ne

but sometimes more

r

none.

Bars

ha v e unique names and addresses. They serv e

ne
r

more b eers and are frequen ted b y

ne
r

more drink ers. They c harge a price for eac h b eer they serv e, whic h ma y v ary from b eer to b eer.

Beers

ha v e unique names and man ufacturers. Man ufacturers ha v e unique names and addresses. Beers are serv ed b y

ne
r

more bars and are lik ed b y

ne
r

more drink ers. 1

SLIDE 2 Relational Algebra A small set

f
p

erators that allo w us to manipulate relations in limited, but easily implemen table and useful w a ys. The

p

erators are: 1. Union, in tersection, and dierence: the usual set

p

erators.

✦

But the relation sc hemas m ust b e the same. 2. Sele ction : Pic king certain ro ws from a relation. 3. Pr

je

ction : Pic king certain columns. 4. Pr

ducts

and joins : Comp

sing

relations in useful w a ys. 5. R enaming

f

relations and their attributes. 2

SLIDE 3 Selection R 1 =

C

(R 2 ) where C is a condition in v

lving

the attributes

f

relation R 2 . Example Relation Sells: bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co

rs

3.00 JoeMenu =

bar

=J

e

s (Sells) bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 3

SLIDE 4 Pro jection R 1 =

L

(R 2 ) where L is a list

f

attributes from the sc hema

f

R 2 . Example

beer

;pr ice (Sells) b eer price Bud 2.50 Miller 2.75 Co

rs

3.00

Notice

elimination

f

duplicate tuples. 4

SLIDE 5 Pro duct R = R 1

R

2 pairs eac h tuple t 1

f

R 1 with eac h tuple t 2

f

R 2 and puts in R a tuple t 1 t 2 . Theta-Join R = R 1 . / C R 2 is equiv alen t to R =

C

(R 1

R

2 ). 5

SLIDE 6 Example Sells = bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co

rs

3.00 Bars = name addr Jo e's Maple St. Sue's Riv er Rd. BarInfo = Sells . / S ells:B ar =B ar s:N ame Bars bar b eer price name addr Jo e's Bud 2.50 Jo e's Maple St. Jo e's Miller 2.75 Jo e's Maple St. Sue's Bud 2.50 Sue's Riv er Rd. Sue's Co

rs

3.00 Sue's Riv er Rd. 6

SLIDE 7 Natural Join R = R 1 . / R 2 calls for the theta-join

f

R 1 and R 2 with the condition that all attributes

f

the same name b e equated. Then,

ne

column for eac h pair

f

equated attributes is pro jected

ut.

Example Supp

se

the attribute name in relation Bars w as c hanged to bar, to matc h the bar name in Sells. BarInfo = Sells . / Bars bar b eer price addr Jo e's Bud 2.50 Maple St. Jo e's Miller 2.75 Maple St. Sue's Bud 2.50 Riv er Rd. Sue's Co

rs

3.00 Riv er Rd. 7

SLIDE 8 Renaming

S

(A 1 ;::: ;A n ) (R ) pro duces a relation iden tical to R but named S and with attributes, in

rder,

named A 1 ; : : : ; A n . Example Bars = name addr Jo e's Maple St. Sue's Riv er Rd.

R(bar

;addr ) (Bars ) = bar addr Jo e's Maple St. Sue's Riv er Rd.

The

name

f

the ab

v

e relation is R . 8

SLIDE 9 Com bining Op erations Algebra = 1. Basis argumen ts, 2. W a ys

f

constructing expressions. F

r

relational algebra: 1. Argumen ts = v ariables standing for relations + nite, constan t relations. 2. Expressions constructed b y applying

ne
f

the

p

erators + paren theses.

Query

= expression

f

relational algebra. 9

SLIDE 10 Op erator Precedence The normal w a y to group

p

erators is: 1. Unary

p

erators

,
,

and

ha

v e highest precedence. 2. Next highest are the \m ultiplicati v e"

p

erators, . /, . / C , and . 3. Lo w est are the \additiv e"

p

erators, [, \, and .

But

there is no univ ersal agreemen t, so w e alw a ys put paren theses ar

und

the argumen t

f

a unary

p

erator, and it is a go

d

idea to group all binary

p

erators with paren theses enclosing their argumen ts. Example Group R [

S

. / T as R [ ( (S ) . / T ). 10

SLIDE 11 Eac h Expression Needs a Sc hema

If

[ , \ ,

applied,

sc hemas are the same, so use this sc hema.

Pro

jection: use the attributes listed in the pro jection.

Selection:

no c hange in sc hema.

Pro

duct R

S

: use attributes

f

R and S .

✦

But if they share an attribute A, prex it with the relation name, as R :A, S:A.

Theta-join:

same as pro duct.

Natural

join: use attributes from eac h relation; common attributes are merged an yw a y .

Renaming:

whatev er it sa ys. 11

SLIDE 12 Example Find the bars that are either

n

Maple Street

r

sell Bud for less than $3. Sells(bar, beer, price) Bars(name, addr)

name
addr

=M apleS t: Bars

R(name)
bar
pr

ice<$3 AN D beer =B ud Sells [ 12

SLIDE 13 Example Find the bars that sell t w

dieren

t b eers at the same price. Sells(bar, beer, price) Sells Sells

bar

. /

S

(bar ;beer 1;pr ice)

beer

6=beer 1 13

SLIDE 14 Linear Notation for Expressions

In

v en t new names for in termediate relations, and assign them v alues that are algebraic expressions.

Renaming
f

attributes implicit in sc hema

f

new relation. Example Find the bars that are either

n

Maple Street

r

sell Bud for less than $3. Sells(bar, beer, price) Bars(name, addr) R1(bar) :=

name

( addr =M aple S t: (Bars)) R2(bar) :=

bar

( beer =B ud AN D pr ice<$3 (Sells)) R3(bar) := R1 [ R2 14

SLIDE 15 Example Find the bars that sell t w

dieren

t b eers at the same price. Sells(bar, beer, price) S1(bar,beer1,pric e) := Sells S2(bar,beer,price ,beer 1) := S1 . / Sells S3(bar) =

bar

( beer 6=beer 1 (S2)) 15