1 / 38

Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications. C. Faloutsos E-R diagrams. Overview. concepts Entities Relationships Attributes Specialization/Generalization Aggregation turning E-R diagrams to tables. Tools. Entitie s (‘entity sets’). N. M.

tanaya
Download Presentation

Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database 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 - Database Applications C. Faloutsos E-R diagrams

  2. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables 15-415 - C. Faloutsos

  3. Tools Entitie s (‘entity sets’) N M Relationships (‘rel. sets’) and mapping constraints P attributes 15-415 - C. Faloutsos

  4. Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per course nouns -> entity sets verbs -> relationships 15-415 - C. Faloutsos

  5. ... name STUDENT ssn INSTRUCTOR issn 15-415 - C. Faloutsos

  6. ... name STUDENT c-id ssn c-name COURSE INSTRUCTOR issn but: sections of course (with different instructors)? 15-415 - C. Faloutsos

  7. ssn STUDENT c-id COURSE SECTION s-id but: s-id is not unique... INSTRUCTOR issn 15-415 - C. Faloutsos

  8. ssn STUDENT N c-id takes M COURSE s-id SECTION INSTRUCTOR issn 15-415 - C. Faloutsos

  9. STUDENT N c-id takes M s-id SECTION COURSE N teaches 1 INSTRUCTOR 15-415 - C. Faloutsos

  10. Cardinalities • 1 to 1 (example?) • 1 to N • N to M 15-415 - C. Faloutsos

  11. STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR 15-415 - C. Faloutsos

  12. More details • ‘weak’ entities: if they need to borrow a unique id from a ‘strong entity - DOUBLE box. • ‘c-id’ + ‘s-id’: unique id for SECTION • discriminator (eg., ‘s-id’) c-id 1 N s-id has SECTION COURSE 15-415 - C. Faloutsos

  13. More details • self-relationships - example? 15-415 - C. Faloutsos

  14. manages 1 EMPLOYEE N 15-415 - C. Faloutsos

  15. More details • 3-way and k-way relationships? N M EMPLOYEE TOOL uses P PROJECT 15-415 - C. Faloutsos

  16. More details - attributes • candidate key (eg., ssn; employee#) • primary key (a cand. key, chosen by DBA) • superkey (eg., (ssn, address) ) • multivalued or set-valued attributes (eg., ‘dependents’ for EMPLOYEE) • derived attributes (eg., 15% tip) 15-415 - C. Faloutsos

  17. More details: • in the text: (eg., ‘total participation’) SECTION 0:N teaches 1:1 INSTRUCTOR 15-415 - C. Faloutsos

  18. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables 15-415 - C. Faloutsos

  19. eg., students: part time (#credit-hours) and full time (major) Specialization name STUDENT ssn IS-A major FT-STUDENT PT-STUDENT #credits 15-415 - C. Faloutsos

  20. Observations • Generalization: exact reverse of ‘specialization’ • attribute inheritance • could have many levels of an IS-A hierarchy 15-415 - C. Faloutsos

  21. treat a relationship as an entity rarely used Aggregation M N MAKER HD CPU 15-415 - C. Faloutsos

  22. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables 15-415 - C. Faloutsos

  23. STUDENT N c-id grade takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR 15-415 - C. Faloutsos

  24. Strong entities just list the attributes, and underline the primary key, eg. STUDENT(ssn, name, address) 15-415 - C. Faloutsos

  25. Multivalued attributes Eg., EMPLOYEE with many dependents: • a new table, with (ssn, dependent-name) 15-415 - C. Faloutsos

  26. Relationships • get primary keys all involved entities • primary key - depends on cardinality • 1 to 1: either eg EMPLOYEE( ssn, empno, name, ...) • 1 to N: the key of the ‘N’ part eg. TEACHES( issn, c-id, s-id) • N to M: both keys - eg TAKES( ssn, c-id, s-id, grade) 15-415 - C. Faloutsos

  27. Relationships • 1 to N: no need for separate table - eg., SECTION( issn, room-num, c-id, s-id) instead of SECTION1(c-id, s-id, room-num) TEACHES(issn, c-id, s-id) • for rel. between strong and corresponding weak entity, no need for table, at all! 15-415 - C. Faloutsos

  28. Generalization/Spec. Two solutions: - one table for each or - no table for super-entity (pros and cons?) 15-415 - C. Faloutsos

  29. Generalization/Special. Eg., STUDENT(ssn, name, address) PT-STUDENT( FT-STUDENT( 15-415 - C. Faloutsos

  30. Generalization/Special. Eg., STUDENT(ssn, name, address) PT-STUDENT( ssn, num-credits) FT-STUDENT( ssn, major) 15-415 - C. Faloutsos

  31. Generalization/Special. no super-entity: [STUDENT(ssn, name, address)] PT-STUDENT( ssn, num-credits FT-STUDENT( ssn, major 15-415 - C. Faloutsos

  32. Generalization/Special. no super-entity: [STUDENT(ssn, name, address)] PT-STUDENT( ssn, num-credits, name, address) FT-STUDENT( ssn, major, name, address) 15-415 - C. Faloutsos

  33. Aggregation • make table, with primary keys of all involved entities 15-415 - C. Faloutsos

  34. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables 15-415 - C. Faloutsos

  35. Summary • E-R Diagrams: a powerful, user-friendly tool for data modeling: • Entities (strong, weak) • Attributes (primary keys, discriminators, derived, multivalued) • Relationships (1:1, 1:N, N:M; multi-way) • Generalization/Specialization; Aggregation 15-415 - C. Faloutsos

  36. attribute (strong) entity set weak entity set relationship set identifying rel. set for weak entity Summary - cont’d multivalued attribute derived attribute 15-415 - C. Faloutsos

  37. A primary key discriminator IS-A A total generalization M l’:h’ N l:h cardinalities cardinalities with limits Summary - cont’d (e.t.c. - see book for alternative notations) 15-415 - C. Faloutsos

  38. Conclusions • E-R Diagrams: a powerful, user-friendly tool for data modeling. 15-415 - C. Faloutsos

More Related