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. ✦ Example: bank and its A TM mac hines. 4. Supp orts secure, atomic access to v.l.a.d. ✦ Con trast t w o p eople editing the same UNIX �le | last to write \wins" | with the problem if t w o p eople deduct money from the same accoun t via A TM mac hines at the same time | new balance is wrong whic hev er writes last. 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 DBMS's. most � Amazing success story of theoretical ideas � tak en to $20B industry . \Big three" DBMS companies | Oracle, � Informix, Sybase | are among the largest soft w are companies in the w orld. ✦ IBM also pla ys, with its DB2 and other o�erings. ✦ Microsoft c hallenges in this area to o | c heap DBMS on the desktop? 2
Relational companies also c hallenged b y � startup \ob ject-orien ted DB" companies. ✦ Conjecture: OODB is o v erblo wn; relational companies will prev ail b ecause they are mo ving rapidly to in tro duce OO concepts as needed via \ob ject-relational " v ersions of their pro ducts and the SQL3 emerging standard. 3
Three Asp ects to Studying DBMS's 1. Mo deling and design of databases. 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 CS245AB, CS347. 4
Wh y Study Design? Sk etc h of structure for database needed, b efore implemen tation (sa y as relations in a relational DBMS). Allo ws discussion of issues in simpli�ed terms � b efore commiting to implemen tation. Example: registrar main tains a DB of courses, � studen ts, instructors, etc. What go es in to the DB? What assumptions can w e mak e, e.g., unique instructor for a course? Design Notations En tit y/relationshi p (E/R) and Ob ject Description Language (ODL) will b e co v ered in CS145, and are in common use. F or eac h there is a semimec hanical w a y to � con v ert to relational designs (= sets of tables). 5
En tit y/Rel at io nship Mo del Diagrams similar in spirit to OO mo dels. � lik e ob ject, = \thing." Entity � Entity set lik e class = set of \similar" � en tities/ob ject s. = prop ert y of en tities in an en tit y A ttribute � set, similar to �elds or \instance v ariables." In diagrams, en tit y set rectangle; attribute � ! o v al. ! ID name phone Studen ts heigh t 6
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 � � � � � � 7
Multiw a y Relationships Usually relationships (connecting t w o E.S.) binary 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 8
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. � 9
Studen ts Courses Enrolls T As Studen ts Courses T As Sally CS145 Claire Sue CS145 Ankur Jo e CS145 Claire � � � � � � � � � 10
A Design Issue: Should Multiw a y Relationships b e Split? Dep ends on whether it can b e expressed as � binary relationships. indep endent Example: � F ossils Sp ecies Finds P aleon tologists F ossils Sp ecies P aleon tologists Lucy A. Afarensis Johansson � � � � � � � � � 11
Here, OK to split, b ecause sp ecies related only � to fossil, not to paleon tologist . Of Sp ecies F ossil F ound P aleon tologist 12
A Design Issue: A ttributes Vs. E.S. + Relationships What is wrong with: name sp ecies F ossils �nder 1. Mak es it imp ossible to represen t prop erties of sp ecies (e.g., heigh t and w eigh t of reference individual ) or �nders. 2. Di�cult to represen t a of �nders for a set single fossil. ✦ T ec hnically , E/R allo ws sets as the t yp e of attributes, but it mak es life di�cult when w e con v ert E/R to relational DB's, so a v oid sets as attribute t yp es. But if (1) and (2) are not a problem, e.g., w e � don't w an t to represen t attributes of sp ecies, then E.S. + attributes is simplest and b est. 13
Can W e Solv e (1) b y Adding A ttributes? name sp ecies F ossils �nder refHeigh t refW eigh t In tro duces the great problem of � Redundancy . ✦ No w for ev ery fossil of a sp ecies, w e rep eat the reference heigh t and w eigh t. ✦ W astes space (not so imp ortan t these da ys). ✦ O�ers opp ortunit y to scr** up, e.g., c hange heigh t in only one fossil among sev eral of the same sp ecies (will remain imp ortan t for the foreseeable future). 14
Recommend
More recommend
