Faloutsos CMU SCS 15-415/615 CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos Lecture#2: E-R diagrams CMU SCS Problem • Develop an application for U.G. admin: – Student info – Who-takes-what class – Class rosters – Transcripts • How do you proceed? – (Which role(s) are you playing?) Faloutsos CMU SCS 15-415/615 2 CMU SCS Database Design • Requirements Analysis • Conceptual Design • Logical Design • Schema Refinement • Physical Design • Security Design Faloutsos CMU SCS 15-415/615 3 1
Faloutsos CMU SCS 15-415/615 CMU SCS Database Design • Requirements Analysis user’s needs • Conceptual Design high level (ER) • Logical Design Tables • Schema Refinement Normalization • Physical Design • Security Design Indices etc Access controls Faloutsos CMU SCS 15-415/615 4 CMU SCS Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions Faloutsos CMU SCS 15-415/615 5 CMU SCS Tools Entities (‘entity sets’) Relationships (‘rel. sets’) N M and mapping constraints P attributes Faloutsos CMU SCS 15-415/615 6 2
Faloutsos CMU SCS 15-415/615 CMU SCS Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets Faloutsos CMU SCS 15-415/615 7 CMU SCS ... name STUDENT ssn INSTRUCTOR issn primary key = unique identifier -> underline Faloutsos CMU SCS 15-415/615 8 CMU SCS ... name STUDENT c-id ssn c-name COURSE INSTRUCTOR issn but: sections of course (with different instructors)? Faloutsos CMU SCS 15-415/615 9 3
Faloutsos CMU SCS 15-415/615 CMU SCS ssn STUDENT c-id COURSE SECTION s-id but: s-id is not INSTRUCTOR unique... (see issn later) Faloutsos CMU SCS 15-415/615 10 CMU SCS ssn STUDENT c-id COURSE SECTION s-id Q: how to INSTRUCTOR record that issn students take courses? Faloutsos CMU SCS 15-415/615 11 CMU SCS ssn STUDENT N c-id takes M COURSE s-id SECTION INSTRUCTOR issn Faloutsos CMU SCS 15-415/615 12 4
Faloutsos CMU SCS 15-415/615 CMU SCS STUDENT N c-id takes M s-id SECTION COURSE N teaches 1 INSTRUCTOR Faloutsos CMU SCS 15-415/615 13 CMU SCS STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR Faloutsos CMU SCS 15-415/615 14 CMU SCS Cardinalities 1 1 • 1 to 1 (example?) • 1 to N 1 N • N to M N M Faloutsos CMU SCS 15-415/615 15 5
Faloutsos CMU SCS 15-415/615 CMU SCS Cardinalities 1 1 has CAPITAL COUNTRY 1 N owns CAR PERSON N M takes SECTION STUDENT Faloutsos CMU SCS 15-415/615 16 CMU SCS Cardinalities has CAPITAL COUNTRY Book’s notation: owns CAR PERSON takes SECTION STUDENT Faloutsos CMU SCS 15-415/615 17 CMU SCS Cardinalities 1 1 has COUNTRY CAPITAL Book’s notation vs 1 N owns CAR PERSON 1 to N notation N M takes SECTION STUDENT Faloutsos CMU SCS 15-415/615 18 6
Faloutsos CMU SCS 15-415/615 CMU SCS ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY ?:1 ?:N owns CAR ?? PERSON ?:N ?:M takes SECTION ?? STUDENT Faloutsos CMU SCS 15-415/615 19 CMU SCS ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY 1:1 0:N owns CAR partial, total PERSON ?:N ?:M takes SECTION ?? STUDENT Faloutsos CMU SCS 15-415/615 20 CMU SCS ‘Total/partial’ participation 1:1 1:1 total, total has COUNTRY CAPITAL 1:1 0:N owns CAR partial, total PERSON 1:N 0:M takes SECTION partial, total STUDENT Faloutsos CMU SCS 15-415/615 21 7
Faloutsos CMU SCS 15-415/615 CMU SCS Weak entities • ‘section’ has no unique-id of its own!(?) c-id 1 N s-id has SECTION COURSE Faloutsos CMU SCS 15-415/615 22 CMU SCS Weak entities • ‘ 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 1 N s-id has SECTION COURSE Faloutsos CMU SCS 15-415/615 23 CMU SCS More details • self-relationships - example? Faloutsos CMU SCS 15-415/615 24 8
Faloutsos CMU SCS 15-415/615 CMU SCS More details • self-relationships - example? manages 1 EMPLOYEE N Faloutsos CMU SCS 15-415/615 25 CMU SCS More details • 3-way and k-way relationships? Faloutsos CMU SCS 15-415/615 26 CMU SCS More details • 3-way and k-way relationships? Rare, but possible: N M EMPLOYEE TOOL uses P PROJECT Faloutsos CMU SCS 15-415/615 27 9
Faloutsos CMU SCS 15-415/615 CMU SCS Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions Faloutsos CMU SCS 15-415/615 28 CMU SCS More details - attributes • key (or primary key ): unique identifier • underlined, in the ER diagram • [ not in textbook - FYI: – multivalued or set-valued attributes (eg., ‘dependents’ for EMPLOYEE) – derived attributes (eg., 15% tip) ] Faloutsos CMU SCS 15-415/615 29 CMU SCS Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions Faloutsos CMU SCS 15-415/615 30 10
Faloutsos CMU SCS 15-415/615 CMU SCS Specialization • eg., students: part time (#credit- name hours) and full STUDENT ssn time (major) IS-A FT-STUDENT PT-STUDENT major #credits Faloutsos CMU SCS 15-415/615 31 CMU SCS Observations • Generalization: exact reverse of ‘specialization’ • attribute inheritance • could have many levels of an IS-A hierarchy Faloutsos CMU SCS 15-415/615 32 CMU SCS More details A • Overlap constraints • Covering constraints B C Faloutsos CMU SCS 15-415/615 33 11
Faloutsos CMU SCS 15-415/615 CMU SCS More details A • Overlap constraints – can an entity belong to both ‘B’ and ‘C’? • Covering constraints B C – can an ‘A’ entity belong to neither ‘B’ nor ‘C’? Faloutsos CMU SCS 15-415/615 34 CMU SCS More details A • Overlap constraints - examples? B C Faloutsos CMU SCS 15-415/615 35 CMU SCS More details A • Covering constraints - examples? B C Faloutsos CMU SCS 15-415/615 36 12
Faloutsos CMU SCS 15-415/615 CMU SCS Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions Faloutsos CMU SCS 15-415/615 37 CMU SCS Aggregation • computer model (w/ CPU and HD) • and Maker (eg., Dell, HP) M ? N MAKER HD CPU Faloutsos CMU SCS 15-415/615 38 CMU SCS Aggregation • treat a relationship as an entity • used to express a relationship among relationships M N MAKER HD CPU Faloutsos CMU SCS 15-415/615 39 13
Faloutsos CMU SCS 15-415/615 CMU SCS Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions Faloutsos CMU SCS 15-415/615 40 CMU SCS Conceptual design • Entity vs attribute • Entity vs relationship • Binary or ternary relationships? • Aggregation? Faloutsos CMU SCS 15-415/615 41 CMU SCS Entity vs. attribute • Entity EMPLOYEE (w/ emp#, name, job_code, ...) • Q: How about ‘spouse’ - entity or attribute? • Q: How about ‘dependents’? Faloutsos CMU SCS 15-415/615 42 14
Faloutsos CMU SCS 15-415/615 CMU SCS Entity vs. attribute • Entity EMPLOYEE (w/ emp#, name, job_code, ...) • Q: How about ‘spouse’ - entity or attribute? • A: probably, ‘attribute’ is enough • Q: How about ‘dependents’? • A: Entity - we may have many dependents Faloutsos CMU SCS 15-415/615 43 CMU SCS Entity vs. Relationship STUDENT STUDENT 1 N N OR takes TAKES M N SECTION 1 SECTION Faloutsos CMU SCS 15-415/615 44 CMU SCS Binary vs Ternary Relationships • usually, binary relationships are ‘cleaner’: Faloutsos CMU SCS 15-415/615 45 15
Faloutsos CMU SCS 15-415/615 CMU SCS Binary vs. Ternary Relationships name ssn lot pname age Employees Dependents Covers If each policy is owned by just 1 employee: Policies policyid cost Faloutsos CMU SCS 15-415/615 46 CMU SCS Binary vs. Ternary Relationships name ssn lot pname age Dependents Employees Covers If each policy is owned by just 1 Bad design employee: Policies policyid cost Faloutsos CMU SCS 15-415/615 47 CMU SCS Binary vs. Ternary Relationships name ssn lot pname age Employees Covers Dependents If each policy is owned by just 1 Bad design employee: Policies policyid cost Key constraint on Policies would mean policy can only cover 1 dependent! Faloutsos CMU SCS 15-415/615 48 16
Recommend
More recommend
