Carnegie Mellon Univ Dept of Computer Science 15
- Slides: 38
Carnegie Mellon Univ. Dept. of Computer Science 15 -415 - Database Applications C. Faloutsos E-R diagrams Carnegie Mellon 15 -415 - C. Faloutsos
Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation • turning E-R diagrams to tables Carnegie Mellon 15 -415 - C. Faloutsos 2
Tools Entitie s (‘entity sets’) N M P Relationships (‘rel. sets’) and mapping constraints attributes Carnegie Mellon 15 -415 - C. Faloutsos 3
Example Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per course nouns -> entity sets verbs -> relationships Carnegie Mellon 15 -415 - C. Faloutsos 4
. . . name STUDENT ssn issn INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos 5
. . . name STUDENT c-id ssn c-name COURSE issn INSTRUCTOR but: sections of course (with different instructors)? Carnegie Mellon 15 -415 - C. Faloutsos 6
ssn STUDENT c-id SECTION s-id issn INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos COURSE but: s-id is not unique. . . 7
ssn STUDENT N takes M s-id issn c-id COURSE SECTION INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos 8
STUDENT N takes c-id M s-id SECTION COURSE N teaches 1 INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos 9
Cardinalities • 1 to 1 (example? ) • 1 to N • N to M Carnegie Mellon 15 -415 - C. Faloutsos 10
STUDENT N c-id takes M s-id SECTION N has 1 COURSE N teaches 1 INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos 11
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 s-id Carnegie Mellon SECTION N has 15 -415 - C. Faloutsos 1 COURSE 12
More details • self-relationships - example? Carnegie Mellon 15 -415 - C. Faloutsos 13
manages 1 EMPLOYEE Carnegie Mellon N 15 -415 - C. Faloutsos 14
More details • 3 -way and k-way relationships? N EMPLOYEE M TOOL uses P PROJECT Carnegie Mellon 15 -415 - C. Faloutsos 15
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) Carnegie Mellon 15 -415 - C. Faloutsos 16
More details: • in the text: (eg. , ‘total participation’) SECTION 0: N teaches 1: 1 INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos 17
Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation • turning E-R diagrams to tables Carnegie Mellon 15 -415 - C. Faloutsos 18
Specialization • eg. , students: part time (#credithours) and full time (major) name ssn STUDENT IS-A major Carnegie Mellon FT-STUDENT 15 -415 - C. Faloutsos PT-STUDENT #credits 19
Observations • Generalization: exact reverse of ‘specialization’ • attribute inheritance • could have many levels of an IS-A hierarchy Carnegie Mellon 15 -415 - C. Faloutsos 20
Aggregation • treat a relationship as an entity • rarely used N CPU Carnegie Mellon M HD 15 -415 - C. Faloutsos MAKER 21
Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation • turning E-R diagrams to tables Carnegie Mellon 15 -415 - C. Faloutsos 22
STUDENT N grade M s-id c-id takes SECTION N has 1 COURSE N teaches 1 INSTRUCTOR Carnegie Mellon 15 -415 - C. Faloutsos 23
Strong entities just list the attributes, and underline the primary key, eg. STUDENT(ssn, name, address) Carnegie Mellon 15 -415 - C. Faloutsos 24
Multivalued attributes Eg. , EMPLOYEE with many dependents: • a new table, with (ssn, dependent-name) Carnegie Mellon 15 -415 - C. Faloutsos 25
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) Carnegie Mellon 15 -415 - C. Faloutsos 26
Relationships • 1 to N: no need for separate table - eg. , SECTION( issn, room-num, c-id, s-id) instead of SECTION 1(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! Carnegie Mellon 15 -415 - C. Faloutsos 27
Generalization/Spec. Two solutions: - one table for each or - no table for super-entity (pros and cons? ) Carnegie Mellon 15 -415 - C. Faloutsos 28
Generalization/Special. Eg. , STUDENT(ssn, name, address) PT-STUDENT( FT-STUDENT( Carnegie Mellon 15 -415 - C. Faloutsos 29
Generalization/Special. Eg. , STUDENT(ssn, name, address) PT-STUDENT( ssn, num-credits) FT-STUDENT( ssn, major) Carnegie Mellon 15 -415 - C. Faloutsos 30
Generalization/Special. no super-entity: [STUDENT(ssn, name, address)] PT-STUDENT( ssn, num-credits FT-STUDENT( ssn, major Carnegie Mellon 15 -415 - C. Faloutsos 31
Generalization/Special. no super-entity: [STUDENT(ssn, name, address)] PT-STUDENT( ssn, num-credits, name, address) FT-STUDENT( ssn, major, name, address) Carnegie Mellon 15 -415 - C. Faloutsos 32
Aggregation • make table, with primary keys of all involved entities Carnegie Mellon 15 -415 - C. Faloutsos 33
Overview • concepts – Entities – Relationships – Attributes – Specialization/Generalization – Aggregation • turning E-R diagrams to tables Carnegie Mellon 15 -415 - C. Faloutsos 34
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 Carnegie Mellon 15 -415 - C. Faloutsos 35
Summary - cont’d (strong) entity set attribute weak entity set multivalued attribute relationship set derived attribute identifying rel. set for weak entity Carnegie Mellon 15 -415 - C. Faloutsos 36
Summary - cont’d A primary key A discriminator IS-A total N M l: h l’: h’ cardinalities with limits Carnegie Mellon 15 -415 - C. Faloutsos generalization (e. t. c. - see book for alternative notations) 37
Conclusions • E-R Diagrams: a powerful, user-friendly tool for data modeling. Carnegie Mellon 15 -415 - C. Faloutsos 38
- Cmu comp bio
- Carnegie mellon interdisciplinary
- Carnegie mellon software architecture
- Bomb lab solutions
- Carnegie mellon software architecture
- Carnegie mellon university research participants
- Cmu mism
- Randy pausch time management
- National robotics initiative
- Carnegie mellon
- 18-213 cmu
- Cmu vpn
- Carnegie mellon
- Carnegie mellon
- Carnegie mellon
- Carnegie mellon
- Carnegie mellon fat letter
- Cmu 15-513
- Assembly bomb lab
- Mellon serbia iskustva
- Carneigh mellon
- Christina mellon
- Bny mellon health savings account
- Zebulun krahn
- Water mellon
- Mellon elf
- Mellon elf
- Mellon elf
- My favorite subject is math
- Ut arlington demographics
- Medecine dentaire constantine
- Ch rahmoune
- State univ grant - sug ug
- Fs boumerdes
- Scolarité univ nantes
- Fs.univ.umbb
- Univ constantine 3
- Pharmacie univ batna 2
- Scolarité pharmacie nantes