1 / 80

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications. C. Faloutsos - A. Pavlo Lecture#2: E-R diagrams. 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?).

courtney
Download Presentation

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Carnegie Mellon Univ.Dept. of Computer Science15-415/615 - DB Applications C. Faloutsos - A. Pavlo Lecture#2: E-R diagrams

  2. 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?) CMU SCS 15-415/615

  3. Database Design • Requirements Analysis • Conceptual Design • Logical Design • Schema Refinement • Physical Design • Security Design CMU SCS 15-415/615

  4. Database Design user’s needs high level (ER) Tables Normalization Indices etc Access controls • Requirements Analysis • Conceptual Design • Logical Design • Schema Refinement • Physical Design • Security Design CMU SCS 15-415/615

  5. Problem’ Maintain • Develop an application for U.G. admin: • Student info • Who-takes-what class • Class rosters • Transcripts • If you are the *new* DBA, what would you rather inherit: CMU SCS 15-415/615

  6. This or this ? drop table if exists student; create table student (ssn fixed, name char(20)); drop table if exists takes; create table takes (ssn fixed, cid char(10), grade fixed); Student Takes Course CMU SCS 15-415/615

  7. True story • Health insurance company • Wants to catch (some of the abundant) fraud • Schema: • patients, visit doctors, get medicine, • Doctors perform operations, … • Nurses monitor patients, … • etc etc • Q: How many tables do you think it spans? CMU SCS 15-415/615

  8. True story • Schema: • patients, visit doctors, get medicine, • Doctors perform operations, … • Nurses monitor patients, … • etc etc • Q: How many tables do you think it spans? 10? 20? 30? CMU SCS 15-415/615

  9. True story • Schema: • patients, visit doctors, get medicine, • Doctors perform operations, … • Nurses monitor patients, … • etc etc • Q: How many tables do you think it spans? 10? 20? 30? • A: 120 PAGES of schema CMU SCS 15-415/615

  10. Motivation & upcoming conclusion: • E-R diagrams are excellent documentation tools Student Takes Course CMU SCS 15-415/615

  11. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • ER modeling questions CMU SCS 15-415/615

  12. Tools Entities (‘entity sets’) Student Relationships (‘rel. sets’) and mapping constraints N M Takes P attributes Course CMU SCS 15-415/615

  13. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets CMU SCS 15-415/615

  14. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets CMU SCS 15-415/615

  15. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets CMU SCS 15-415/615

  16. ... name STUDENT ssn INSTRUCTOR primary key = unique identifier -> underline issn CMU SCS 15-415/615

  17. ... name STUDENT c-id ssn c-name COURSE INSTRUCTOR issn but: sections of course (with different instructors)? CMU SCS 15-415/615

  18. ssn STUDENT c-id COURSE SECTION s-id but: s-id is not unique... (see later) INSTRUCTOR issn CMU SCS 15-415/615

  19. ssn STUDENT c-id COURSE SECTION s-id Q: how to record that students take courses? INSTRUCTOR issn CMU SCS 15-415/615

  20. ssn STUDENT N c-id takes M COURSE s-id SECTION INSTRUCTOR issn CMU SCS 15-415/615

  21. STUDENT N c-id takes M s-id SECTION COURSE N teaches 1 INSTRUCTOR CMU SCS 15-415/615

  22. STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR CMU SCS 15-415/615

  23. Cardinalities 1 1 • 1 to 1 (example?) • 1 to N • N to M 1 N N M CMU SCS 15-415/615

  24. Cardinalities 1 1 has CAPITAL COUNTRY 1 N owns CAR PERSON N M takes SECTION STUDENT CMU SCS 15-415/615

  25. Cardinalities has CAPITAL COUNTRY Book’s notation: owns CAR PERSON takes SECTION STUDENT CMU SCS 15-415/615

  26. Cardinalities 1 1 has CAPITAL COUNTRY Book’s notation vs 1 to N notation 1 N owns CAR PERSON N M takes SECTION STUDENT CMU SCS 15-415/615

  27. Cardinalities 1 1 has CAPITAL COUNTRY 1 N owns CAR PERSON N M takes SECTION STUDENT CMU SCS 15-415/615

  28. ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY ?:1 ?:N owns CAR ?? PERSON ?:N ?:M takes SECTION ?? STUDENT CMU SCS 15-415/615

  29. ‘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 CMU SCS 15-415/615

  30. ‘Total/partial’ participation 1:1 1:1 total, total has CAPITAL COUNTRY 1:1 0:N owns CAR partial, total PERSON 1:N 0:M takes SECTION partial, total STUDENT CMU SCS 15-415/615

  31. ‘Total/partial’ participation Is it ‘legal’? partial, total 1:1 0:N owns CAR PERSON CMU SCS 15-415/615

  32. ‘Total/partial’ participation Is it ‘legal’? partial, total NO! why not? 1:1 0:N owns CAR PERSON CMU SCS 15-415/615

  33. Subtle concept: Weak entities • ‘section’ has no unique-id of its own!(?) c-id 1 N s-id has COURSE SECTION CMU SCS 15-415/615

  34. 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 CMU SCS 15-415/615

  35. Weak entities • Other example(s) of weak entities? ??id 1 N ?? ?? ??? ??? CMU SCS 15-415/615

  36. Weak entities • Other example(s) of weak entities? e-id 1 N name has Dependent Employee CMU SCS 15-415/615

  37. More details • self-relationships - example? ?? ?? ?? ?? CMU SCS 15-415/615

  38. More details • self-relationships - example? manages 1 EMPLOYEE N CMU SCS 15-415/615

  39. More details • self-relationships - example? Has-friend ?? FB user ?? CMU SCS 15-415/615

  40. More details • 3-way and k-way relationships? CMU SCS 15-415/615

  41. More details • 3-way and k-way relationships? Rare, but possible: N M EMPLOYEE TOOL uses P PROJECT CMU SCS 15-415/615

  42. More details • 3-way and k-way relationships? Rare, but possible: N M reviews user keyword P App-store/amazon reviews app CMU SCS 15-415/615

  43. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • ER modeling questions CMU SCS 15-415/615

  44. 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) ] CMU SCS 15-415/615

  45. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • ER modeling questions Basic Advanced/ rare CMU SCS 15-415/615

  46. eg., students: part time (#credit-hours) and full time (major) Specialization name STUDENT ssn IS-A FT-STUDENT PT-STUDENT major #credits CMU SCS 15-415/615

  47. Observations • Generalization: exact reverse of ‘specialization’ • attribute inheritance • could have many levels of an IS-A hierarchy CMU SCS 15-415/615

  48. A C B More details • Overlap constraints • Covering constraints CMU SCS 15-415/615

  49. A C B More details • Overlap constraints • can an entity belong to both ‘B’ and ‘C’? • Covering constraints • can an ‘A’ entity belong to neither ‘B’ nor ‘C’? CMU SCS 15-415/615

  50. A C B More details • Overlap constraints - examples? No overlap with overlap A A C C B B CMU SCS 15-415/615

More Related