What is a Database Managemen t System? 1. Manages v ery - - PDF document

what is a database managemen t system 1 manages v ery
SMART_READER_LITE
LIVE PREVIEW

What is a Database Managemen t System? 1. Manages v ery - - PDF document

What is a Database Managemen t System? 1. Manages v ery large amoun ts of data. 2. Supp orts ecien t access to v ery large amoun ts of data. 3. Supp orts concurren t access to v.l.a.d. 4. Supp orts secure,


slide-1
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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