Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Problem Carnegie Mellon Univ. Dept. of Computer Science • Develop an application for U.G. admin: 15-415/615 - DB Applications – Student info – Who-takes-what class – Class rosters C. Faloutsos - A. Pavlo – Transcripts Lecture#2: E-R diagrams • How do you proceed? – (Which role(s) are you playing?) Faloutsos - Pavlo CMU SCS 15-415/615 2 CMU SCS CMU SCS Database Design Database Design • Requirements Analysis • Requirements Analysis user’s needs • Conceptual Design • Conceptual Design high level (ER) • Logical Design • Logical Design Tables • Schema Refinement • Schema Refinement Normalization • Physical Design • Physical Design • Security Design • Security Design Indices etc Access controls Faloutsos - Pavlo CMU SCS 15-415/615 3 Faloutsos - Pavlo CMU SCS 15-415/615 4 1
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Problem’ This or this ? Maintain drop table if exists student; • Develop an application for U.G. admin: create table student Student – Student info (ssn fixed, – Who-takes-what class name char(20)); drop table if exists takes; – Class rosters create table takes Takes – Transcripts (ssn fixed, cid char(10), • If you are the *new* DBA, what would you grade fixed); rather inherit: Course Faloutsos - Pavlo CMU SCS 15-415/615 5 Faloutsos - Pavlo CMU SCS 15-415/615 6 CMU SCS CMU SCS True story True story • Health insurance company • Schema: • Wants to catch (some of the abundant) fraud – patients, visit doctors, get medicine, • Schema: – Doctors perform operations, … – patients, visit doctors, get medicine, – Nurses monitor patients, … – Doctors perform operations, … – etc etc – Nurses monitor patients, … • Q: How many tables do you think it spans? – etc etc 10? 20? 30? • Q: How many tables do you think it spans? Faloutsos - Pavlo CMU SCS 15-415/615 7 Faloutsos - Pavlo CMU SCS 15-415/615 8 2
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Motivation & upcoming True story conclusion: • Schema: • E-R diagrams are Student excellent – patients, visit doctors, get medicine, documentation tools – Doctors perform operations, … – Nurses monitor patients, … Takes – etc etc • Q: How many tables do you think it spans? 10? 20? 30? Course • A: 120 PAGES of schema Faloutsos - Pavlo CMU SCS 15-415/615 9 Faloutsos - Pavlo CMU SCS 15-415/615 10 CMU SCS CMU SCS Student Overview Tools Takes Course • concepts Entities (‘entity sets’) – Entities – Relationships Relationships (‘rel. sets’) N M – Attributes and mapping constraints – Specialization/Generalization P – Aggregation – ER modeling questions attributes Faloutsos - Pavlo CMU SCS 15-415/615 11 Faloutsos - Pavlo CMU SCS 15-415/615 12 3
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Example Example Students, taking courses, offered by Students, taking courses, offered by instructors; a course may have multiple instructors; a course may have multiple sections; one instructor per section sections; one instructor per section nouns -> entity sets nouns -> entity sets verbs -> relationship sets verbs -> relationship sets Faloutsos - Pavlo CMU SCS 15-415/615 13 Faloutsos - Pavlo CMU SCS 15-415/615 14 CMU SCS CMU SCS ... Example name STUDENT Students, taking courses, offered by ssn instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets INSTRUCTOR issn primary key = unique identifier -> underline Faloutsos - Pavlo CMU SCS 15-415/615 15 Faloutsos - Pavlo CMU SCS 15-415/615 16 4
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS ... ssn name STUDENT STUDENT c-id c-id ssn c-name COURSE SECTION s-id COURSE INSTRUCTOR issn but: s-id is not INSTRUCTOR unique... (see but: sections of course (with issn later) different instructors)? Faloutsos - Pavlo CMU SCS 15-415/615 17 Faloutsos - Pavlo CMU SCS 15-415/615 18 CMU SCS CMU SCS ssn ssn STUDENT STUDENT N c-id c-id takes M COURSE COURSE SECTION s-id s-id SECTION Q: how to record that INSTRUCTOR INSTRUCTOR issn issn students take courses? Faloutsos - Pavlo CMU SCS 15-415/615 19 Faloutsos - Pavlo CMU SCS 15-415/615 20 5
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS STUDENT STUDENT N N c-id c-id takes takes M M 1 N has s-id s-id SECTION COURSE SECTION COURSE N N teaches teaches 1 1 INSTRUCTOR INSTRUCTOR Faloutsos - Pavlo CMU SCS 15-415/615 21 Faloutsos - Pavlo CMU SCS 15-415/615 22 CMU SCS CMU SCS Cardinalities Cardinalities 1 1 1 1 has • 1 to 1 (example?) CAPITAL COUNTRY • 1 to N 1 1 N N owns • N to M PERSON CAR N M N M takes SECTION STUDENT Faloutsos - Pavlo CMU SCS 15-415/615 23 Faloutsos - Pavlo CMU SCS 15-415/615 24 6
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Cardinalities Cardinalities 1 1 has has CAPITAL CAPITAL COUNTRY COUNTRY Book’s notation 1 Book’s notation: vs N owns owns PERSON CAR PERSON CAR 1 to N notation N M takes SECTION takes SECTION STUDENT STUDENT Faloutsos - Pavlo CMU SCS 15-415/615 25 Faloutsos - Pavlo CMU SCS 15-415/615 26 CMU SCS CMU SCS Cardinalities ‘Total/partial’ participation 1 1 1:1 1:1 has total, total has CAPITAL CAPITAL COUNTRY COUNTRY 1 ?:1 N ?:N owns owns PERSON CAR PERSON CAR ?? N M ?:N ?:M takes SECTION takes SECTION STUDENT ?? STUDENT Faloutsos - Pavlo CMU SCS 15-415/615 27 Faloutsos - Pavlo CMU SCS 15-415/615 28 7
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS ‘Total/partial’ participation ‘Total/partial’ participation 1:1 1:1 1:1 1:1 total, total has total, total has CAPITAL CAPITAL COUNTRY COUNTRY 1:1 1:1 0:N 0:N owns owns PERSON CAR PERSON CAR partial, total partial, total ?:N ?:M 1:N 0:M takes SECTION takes SECTION ?? STUDENT partial, total STUDENT Faloutsos - Pavlo CMU SCS 15-415/615 29 Faloutsos - Pavlo CMU SCS 15-415/615 30 CMU SCS CMU SCS ‘Total/partial’ participation ‘Total/partial’ participation Is it ‘legal’? Is it ‘legal’? partial, total partial, total NO! why not? 1:1 1:1 0:N 0:N owns owns PERSON CAR PERSON CAR Faloutsos - Pavlo CMU SCS 15-415/615 31 Faloutsos - Pavlo CMU SCS 15-415/615 32 8
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Subtle concept: Weak entities Weak entities • ‘section’ has no unique-id of its own!(?) • ‘ weak’ entities: if they need to borrow a unique id from a ‘strong entity - thick box. • ‘c-id’ + ‘s-id’: unique id for SECTION • partial key (eg., ‘s-id’) - dashed-underline • identifying relationship (eg., ‘has’) c-id c-id 1 1 N N s-id has s-id has SECTION SECTION COURSE COURSE Faloutsos - Pavlo CMU SCS 15-415/615 33 Faloutsos - Pavlo CMU SCS 15-415/615 34 CMU SCS CMU SCS Weak entities Weak entities • Other example(s) of weak entities? • Other example(s) of weak entities? ??id e-id 1 1 N N ?? ?? name has ??? Dependent ??? Employee Faloutsos - Pavlo CMU SCS 15-415/615 35 Faloutsos - Pavlo CMU SCS 15-415/615 36 9
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS More details More details • self-relationships - example? • self-relationships - example? ?? manages ?? 1 ?? EMPLOYEE ?? N Faloutsos - Pavlo CMU SCS 15-415/615 37 Faloutsos - Pavlo CMU SCS 15-415/615 38 CMU SCS CMU SCS More details More details • self-relationships - example? • 3-way and k-way relationships? Has-friend ?? FB user ?? Faloutsos - Pavlo CMU SCS 15-415/615 39 Faloutsos - Pavlo CMU SCS 15-415/615 40 10
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS More details More details • 3-way and k-way relationships? Rare, but • 3-way and k-way relationships? Rare, but possible: possible: N N M M EMPLOYEE TOOL ?? uses ?? ?? P P PROJECT Other cases? ?? Faloutsos - Pavlo CMU SCS 15-415/615 41 Faloutsos - Pavlo CMU SCS 15-415/615 42 CMU SCS CMU SCS More details Overview • 3-way and k-way relationships? Rare, but • concepts possible: – Entities N M – Relationships reviews user keyword – Attributes – Specialization/Generalization P – Aggregation App-store/amazon app – ER modeling questions reviews Faloutsos - Pavlo CMU SCS 15-415/615 43 Faloutsos - Pavlo CMU SCS 15-415/615 44 11
Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS More details - attributes Overview • key (or primary key ): unique identifier • concepts • underlined, in the ER diagram – Entities Basic – Relationships • [ not in textbook - FYI: – multivalued or set-valued attributes (eg., ‘dependents’ for – Attributes EMPLOYEE) Advanced/ – Specialization/Generalization – derived attributes (eg., 15% tip) ] – Aggregation rare – ER modeling questions Faloutsos - Pavlo CMU SCS 15-415/615 45 Faloutsos - Pavlo CMU SCS 15-415/615 46 CMU SCS CMU SCS Specialization Observations • eg., students: part • Generalization: exact reverse of name time (#credit- ‘specialization’ hours) and full • attribute inheritance STUDENT ssn time (major) • could have many levels of an IS-A IS-A hierarchy FT-STUDENT PT-STUDENT major #credits Faloutsos - Pavlo CMU SCS 15-415/615 47 Faloutsos - Pavlo CMU SCS 15-415/615 48 12
Recommend
More recommend
