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 e�cien 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, atomic access to v.l.a.d. 1

Relational Mo del Based on tables, as: � acct# name balance 12345 Sally 1000.21 34567 Sue 285.48 � � � � � � � � � T o da y used in most DBMS's. � 2

Three Asp ects to Studying DBMS's 1. Mo deling and design of databases. ✦ Allo ws exploration of issues b efore committing to an implemen tation. 2. Programming: queries and DB op 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

En tit y/Rel at io nship Mo del Diagrams to represen t designs. Entity lik e ob ject, = \thing." � Entity set lik e class = set of \similar" � en tities/ob ject s. A ttribute = prop ert y of en tities in an en tit y � set, similar to �elds of a struct. In diagrams, en tit y set rectangle; attribute � ! o v al. ! ID name phone Studen ts heigh t 4

Relationships Connect t w o or more en tit y sets. � Represen ted b y diamonds. � Studen ts T aking Courses Relationship Set Think of the \v alue" of a relationship set as a table. One column for eac h of the connected en tit y � sets. One ro w for eac h list of en tities, one from eac h � set, that are connected b y the relationship. Studen ts Courses Sally CS145 Sally CS244 Jo e CS145 � � � � � � 5

Multiw a y Relationships Usually binary relationships (connecting t w o E.S.) su�ce. Ho w ev er, there are some cases where three � or more E.S. m ust b e connected b y one relationship. Example: relationship among studen ts, � courses, T A's. P ossibly , this E/R diagram is OK: Studen ts T aking Courses Assisting T As 6

W orks in CS145, b ecause eac h T A is a T A of � all studen ts. Connection studen t-T A is only 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 ould b e related to only one of the T A's for CS145. Whic h one? Need a 3-w a y relationship to tell. � 7

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

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

Multiplicit y of Relationship s Man y-man y Man y-one One-one Represen tatio n of Man y-One E/R: arro w p oin ting to \one." � ✦ Rounded arro w = \exactly one." 10

Example: Drink ers Ha v e F a v orite Beers name addr license Bars Serv es F requen ts Beers Lik es Drink ers name manf name addr F a v orite 11

One-One Relationshi ps Put arro ws in b oth directions. Best- Manfs Beers seller Design Issue: Is the rounded arro w justi�ed? Design Issue: Here, man ufacturer is an E.S.; in earlier diagrams it is an attribute. Whic h is righ t? 12

A ttributes on Relationshi ps price Bars Sells Beers Shorthand for 3-w a y relationship: � price Prices Bars Sells Beers 13

A true 3-w a y relationship. � ✦ Price dep ends join tly on b eer and bar. Notice arro w con v en tion for m ultiw a y � relationships: \all other E.S. determine one of these." ✦ Not su�cien tly general to express an y p ossibili t y . ✦ Ho w ev er, if price, sa y , dep ended only on the b eer, then w e could use t w o 2-w a y relationships: price-b eer and b eer-bar. ✦ Or b etter: just mak e price an attribute of b eer. 14

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 onne cting E.S. to represen t � ro ws of 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 others. BBP The- The- The- Bar Beer Price Bars Beers Prices 15

Roles Sometimes an E.S. participates more than once in a relationship. Lab el edges with r oles to distinguish. � Married h usband wife Drink ers Husband Wife d d 1 2 d d 3 4 � � � � � � 16

Buddies 1 2 Drink ers Buddy1 Buddy2 d d 1 2 d d 1 3 d d 2 1 d d 2 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 and b y one husband wife relationship spouse ? 17

Sub classes Sub class = sp ecial case = few er en tities = more prop erties. Example Ales are a kind of b eer. In addition to the pr op erties (= attributes and relationships) of b eers, there is a \color" attribute for ales. 18

E/R Sub classes Assume sub classes form a tree (no m ultiple � inheritance). isa triangles indicate the sub class relation. � name manf Beers isa color Ales 19

Di�eren t Sub class Viewp oin ts 1. E/R viewp oint : An en tit y has a c omp onent in eac h en tit y set to whic h it logicall y b elongs. ✦ Its prop erties are the union of the prop erties of these E.S. 2. Con trasts with obje ct-oriente d viewp oint : An ob ject (en tit y) b elongs to exactly one class. ✦ It inherits prop erties of its sup erclasses. name manf Beers isa P ete's Ale color Ales 20

Keys A key is a set of attributes suc h that no t w o en tities agree on 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 one k ey , but one set of attributes is the \designated" k ey . In E/R diagrams, y ou should underline all � attributes of the designated k ey . 21

Example Supp ose is k ey for Be ers . name name manf Beers isa color Ales Beer name is also k ey for ales. � ✦ In general, k ey at ro ot is k ey for all. 22

Example: A Multiattribute Key dept n um b er hours ro om Courses P ossibly , hours + ro om also forms a k ey , but � w e ha v e not designated it as suc h. 23