Carnegie Mellon Univ Dept of Computer Science 15

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