Database Design From Conceptual Design to Physical Relational

Database Design: From Conceptual Design to Physical Relational Implementation University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management IS 257 – Spring 2004 -02 -17 SLIDE 1

Lecture Outline • Review – Access Methods – Indexes and What to index – Parallel storage systems (RAID) – Integrity constraints • Design to Relational Implementation IS 257 – Spring 2004 -02 -17 SLIDE 2

Internal Model Access Methods • Many types of access methods: – Physical Sequential – Indexed Random – Inverted – Direct – Hashed • Differences in – Access Efficiency – Storage Efficiency IS 257 – Spring 2004 -02 -17 SLIDE 3

Indexed Sequential: Two Levels Key Value Address 150 1 385 2 001 003. . 150 Address 385 7 678 8 805 9 … Key Value Address 536 3 678 4 Key Value 251. . 385 455 480. . 536 605 610. . 678 Address 785 5 805 6 791. . 805 IS 257 – Spring 2004 705 710. . 785 2004 -02 -17 SLIDE 4

Indexed Random Becker Harty Actual Value Address Block Number Adams 2 Becker 1 Dumpling 3 Getta 2 Harty 1 Adams Getta Dumpling IS 257 – Spring 2004 -02 -17 SLIDE 5

Btree F B || D || F| || P || Z| H || L || P| R || S || Z| Devils Aces Boilers Cars IS 257 – Spring 2004 Flyers Hawkeyes Hoosiers Minors Panthers Seminoles 2004 -02 -17 SLIDE 6

Inverted CH 145 101, 103, 104 Actual Value Address Block Number CH 145 1 CS 201 2 CS 623 3 PH 345 … CS 201 102 Student name Course Number Adams CH 145 Becker cs 201 Dumpling ch 145 Getta ch 145 Harty cs 623 Mobile cs 623 CS 623 105, 106 IS 257 – Spring 2004 -02 -17 SLIDE 7

When to Use Indexes • Rules of thumb – Indexes are most useful on larger tables – Specify a unique index for the primary key of each table – Indexes are most useful for attributes used as search criteria or for joining tables – Indexes are useful if sorting is often done on the attribute – Most useful when there are many different values for an attribute – Some DBMS limit the number of indexes and the size of the index key values – Some indexes will not retrieve NULL values IS 257 – Spring 2004 -02 -17 SLIDE 8

RAID • Provides parallel disks (and software) so that multiple pages can be retrieved simultaneously • RAID stands for “Redundant Arrays of Inexpensive Disks” – invented by Randy Katz and Dave Patterson here at Berkeley • Some manufacturers have renamed the “inexpensive” part IS 257 – Spring 2004 -02 -17 SLIDE 9

RAID Technology One logical disk drive Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 5 9 * * * 2 6 10 * * * 3 7 11 * * * 4 8 12 * * * Stripe Parallel Reads IS 257 – Spring 2004 -02 -17 SLIDE 10

Raid 0 One logical disk drive Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 5 9 * * * 2 6 10 * * * 3 7 11 * * * 4 8 12 * * * Stripe Parallel Reads IS 257 – Spring 2004 -02 -17 SLIDE 11

RAID-1 Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 3 5 * * * 2 4 6 * * * Stripe Parallel Reads IS 257 – Spring 2004 -02 -17 SLIDE 12

RAID-2 Writes span all drives Disk 1 1 a 2 a 3 a * * * Disk 2 Disk 3 1 b ecc 2 b ecc 3 b ecc * * * Disk 4 ecc ecc * * * Stripe Reads span all drives IS 257 – Spring 2004 -02 -17 SLIDE 13

RAID-3 Writes span all drives Disk 1 Disk 2 Disk 3 Disk 4 1 a 2 a 3 a * * * 1 b 2 b 3 b * * * 1 c 2 c 3 c * * * ecc ecc * * * Stripe Reads span all drives IS 257 – Spring 2004 -02 -17 SLIDE 14

Raid-4 Parallel Writes Disk 1 Disk 2 Disk 3 Disk 4 1 4 7 * * * 2 5 8 * * * 3 6 9 * * * ecc ecc * * * Stripe Parallel Reads IS 257 – Spring 2004 -02 -17 SLIDE 15

RAID-5 Parallel Writes Disk 1 Disk 2 1 5 9 * * ecc 2 6 10 * * ecc Disk 3 3 7 11 * * ecc Disk 4 4 8 12 * * ecc Stripe Parallel Reads IS 257 – Spring 2004 -02 -17 SLIDE 16

Integrity Constraints • The constraints we wish to impose in order to protect the database from becoming inconsistent. • Five types – Required data – attribute domain constraints – entity integrity – referential integrity – enterprise constraints IS 257 – Spring 2004 -02 -17 SLIDE 17

Review • Database Design Process • Normalization IS 257 – Spring 2004 -02 -17 SLIDE 18

Database Design Process Application 1 External Model Application 2 Application 3 Application 4 External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Application 3 Conceptual requirements Conceptual Model Logical Model Internal Model Application 4 Conceptual requirements IS 257 – Spring 2004 -02 -17 SLIDE 19

Today: New Design • Today we will build the COOKIE database from needs (rough) through the conceptual model, logical model and finally physical implementation in Access. IS 257 – Spring 2004 -02 -17 SLIDE 20

Cookie Requirements • Cookie is a bibliographic database that contains information about a hypothetical union catalog of several libraries. • Need to record which books are held by which libraries • Need to search on bibliographic information – Author, title, subject, call number for a given library, etc. • Need to know who publishes the books for ordering, etc. IS 257 – Spring 2004 -02 -17 SLIDE 21

Cookie Database • There are currently 6 main types of entities in the database – – – – Authors (Authors) Books (bibfile) Local Call numbers (callfile) Libraries (libfile) Publishers (pubfile) Subject headings (subfile) Additional entities • Links between subject and books (indxfile) • Links between authors and books (AU_BIB) IS 257 – Spring 2004 -02 -17 SLIDE 22

AUTHORS • author -- The author’s name (We do not distinguish between Personal and Corporate authors) • Au_id – a unique id for the author IS 257 – Spring 2004 -02 -17 SLIDE 23

AUTHORS AU ID Authors IS 257 – Spring 2004 -02 -17 SLIDE 24

BIBFILE • Books (BIBFILE) contains information about particular books. It includes one record for each book. The attributes are: – – – – accno -- an “accession” or serial number title -- The title of the book loc -- Location of publication (where published) date -- Date of publication price -- Price of the book pagination -- Number of pages ill -- What type of illustrations (maps, etc) if any height -- Height of the book in centimeters IS 257 – Spring 2004 -02 -17 SLIDE 25

Books/BIBFILE Title accno Loc Price Books Date Pagination Height IS 257 – Spring 2004 Ill 2004 -02 -17 SLIDE 26

CALLFILE • CALLFILE contains call numbers and holdings information linking particular books with particular libraries. Its attributes are: – accno -- the book accession number – libid -- the id of the holding library – callno -- the call number of the book in the particular library – copies -- the number of copies held by the particular library IS 257 – Spring 2004 -02 -17 SLIDE 27

Local. Info/CALLFILE libid Callno accno Copies CALLFILE IS 257 – Spring 2004 -02 -17 SLIDE 28

LIBFILE • LIBFILE contain information about the libraries participating in this union catalog. Its attributes include: – – – – libid -- Library id number library -- Name of the library laddress -- Street address for the library lcity -- City name lstate -- State code (postal abbreviation) lzip -- zip code lphone -- Phone number mop - suncl -- Library opening and closing times for each day of the week. IS 257 – Spring 2004 -02 -17 SLIDE 29

Libraries/LIBFILE lstate laddress lcity lzip Library lphone Libid Sat. Cl LIBFILE Sun. Op Sat. Op Suncl FCl MOp FOp Th. Cl Mcl Tu. Op IS 257 – Spring 2004 Tu. Cl WOp WCl Th. Op 2004 -02 -17 SLIDE 30

PUBFILE • PUBFILE contain information about the publishers of books. Its attributes include – pubid -- The publisher’s id number – publisher -- Publisher name – paddress -- Publisher street address – pcity -- Publisher city – pstate -- Publisher state – pzip -- Publisher zip code – pphone -- Publisher phone number – ship -- standard shipping time in days IS 257 – Spring 2004 -02 -17 SLIDE 31

Publisher/PUBFILE paddress Publisher pcity PUBFILE pubid pstate pzip Ship pphone IS 257 – Spring 2004 -02 -17 SLIDE 32

SUBFILE • SUBFILE contains each unique subject heading that can be assigned to books. Its attributes are – subcode -- Subject identification number – subject -- the subject heading/description IS 257 – Spring 2004 -02 -17 SLIDE 33

Subjects/SUBFILE subid Subject SUBFILE IS 257 – Spring 2004 -02 -17 SLIDE 34

INDXFILE • INDXFILE provides a way to allow many-to -many mapping of subject headings to books. Its attributes consist entirely of links to other tables – subcode -- link to subject id – accno -- link to book accession number IS 257 – Spring 2004 -02 -17 SLIDE 35

Linking Subjects and Books subid accno INDXFILE IS 257 – Spring 2004 -02 -17 SLIDE 36

AU_BIB • AU_BIB provides a way to allow many to many mapping between books and authors. It also consists only of links to other tables – AU_ID – link to the AUTHORS table – ACCNO – link to the BIBFILE table IS 257 – Spring 2004 -02 -17 SLIDE 37

Linking Authors and Books AU ID accno AU_BIB IS 257 – Spring 2004 -02 -17 SLIDE 38

Some examples of Cookie Searches • Who wrote Microcosmographia Academica? • How many pages long is Alfred Whitehead’s The Aims of Education and Other Essays? • Which branches in Berkeley’s public library system are open on Sunday? • What is the call number of Moffitt Library’s copy of Abraham Flexner’s book Universities: American, English, German? • What books on the subject of higher education are among the holdings of Berkeley (both UC and City) libraries? • Print a list of the Mechanics Library holdings, in descending order by height. • What would it cost to replace every copy of each book that contains illustrations (including graphs, maps, portraits, etc. )? • Which library closes earliest on Friday night? IS 257 – Spring 2004 -02 -17 SLIDE 39

Cookie ER Diagram pubid accno BIBFILE CALLFILE accno AU_BIB LIBFILE libid AU ID PUBFILE INDXFILE pubid SUBFILE AUTHORS accno AU_ID Author IS 257 – Spring 2004 subcode Note: diagram contains only attributes used for linking 2004 -02 -17 SLIDE 40

What Problems? • What sorts of problems and missing features arise given the previous ER diagram? IS 257 – Spring 2004 -02 -17 SLIDE 41

Problems Identified • • Subtitles, parallel titles? Edition information Series information lending status material type designation Genre, class information Better codes (ISBN? ) Missing information (ISBN) IS 257 – Spring 2004 • Authority control for authors • Missing/incomplete data • Data entry problems • Ordering information • Illustrations • Subfield separation (such as last_name, first_name) • Separate personal and corporate authors 2004 -02 -17 SLIDE 42

Problems (Cont. ) • Location field inconsistent • No notes field • No language field • Zipcode doesn’t support plus-4 • No publisher shipping addresses IS 257 – Spring 2004 • No (indexable) keyword search capability • No support for multivolume works • No support for URLs – to online version – to libraries – to publishers 2004 -02 -17 SLIDE 43

Original Cookie ER Diagram pubid accno BIBFILE CALLFILE accno AU_BIB LIBFILE libid AU ID PUBFILE INDXFILE pubid SUBFILE AUTHORS accno AU_ID Author IS 257 – Spring 2004 subcode Note: diagram contains only attributes used for linking 2004 -02 -17 SLIDE 44

Cookie 2: Separate Name Authorities pubid accno BIBFILE CALLFILE accno AUTHBIB LIBFILE libid nameid PUBFILE authtype INDXFILE pubid SUBFILE AUTHFILE accno nameid subcode name IS 257 – Spring 2004 -02 -17 SLIDE 45

Cookie 3: Keywords termid accno termid pubid accno BIBFILE TERMS CALLFILE LIBFILE accno AUTHBIB KEYMAP libid nameid PUBFILE authtype INDXFILE pubid SUBFILE AUTHFILE accno nameid subcode name IS 257 – Spring 2004 -02 -17 SLIDE 46

ser_title Cookie 4: Series seriesid SERIES termid accno termid seriesid pubid accno BIBFILE TERMS CALLFILE LIBFILE accno AUTHBIB KEYMAP libid nameid PUBFILE authtype INDXFILE pubid SUBFILE AUTHFILE accno nameid subcode name IS 257 – Spring 2004 -02 -17 SLIDE 47

ser_title Cookie 5: Circulation seriesid SERIES termid accno termid seriesid pubid accno BIBFILE TERMS CALLFILE LIBFILE accno AUTHBIB KEYMAP libid circid libid nameid PUBFILE authtype pubid PATRON INDXFILE SUBFILE CIRC AUTHFILE accno nameid name IS 257 – Spring 2004 subcode copynumpatronid circid 2004 -02 -17 SLIDE 48

Logical Model: Mapping to Relations • Take each entity – BIBFILE – LIBFILE – CALLFILE – SUBFILE – PUBFILE – INDXFILE • And make it a table. . . IS 257 – Spring 2004 -02 -17 SLIDE 49

Implementing the Physical Database. . . • For each of the entities, we will build a table… • Start up access… • Use “New” in Tables… • Loading data • Entering data • Data entry forms IS 257 – Spring 2004 -02 -17 SLIDE 50

Next Time • • Relational Operations Relational Algebra Relational Calculus Introduction to SQL IS 257 – Spring 2004 -02 -17 SLIDE 51