Lecture 3 Entity Relationship diagram ERD Cardinalities Converting

Lecture 3 • Entity Relationship diagram (ERD) • Cardinalities • Converting ERD into tables (relations) • An ERD example • Q 8/p 139 INSS 651 1

Entities An object of interest • Person • Place • Event • Concept Ex: Student Order Customer INSS 651 2

Super & Sub Type Entities Recognize common theme WORKER (SUPER TYPE) • PART TIME (SUB TYPE) • FULL TIME (SUB TYPE) • CONTRACTUAL (SUB TYPE) INSS 651 3

Relationship • “Has A” between entities TEAM PLAYER • “IS A” between super & sub entities WORKER PART TIME FULL TIME INSS 651 4

INHERITANCE • Sub type entities inherit all attributes & procedures of its SUPER entity type • Also the basis of Objects INSS 651 5

Attributes • Properties of an entity • Describes an entity Ex: attribute of an entity STUDENT student name student address etc INSS 651 6

Domain Set of allowable values for an attribute Ex: Student_age (between 15 -85) Student _classification (Graduate or Undergraduate) INSS 651 7

Relationship Exists between entities Recursive. . within itself Binary between TWO entities Ternary between THREE entities INSS 651 8

Nature of Relationship between ENTITIES • 1: 1 (one is to one) • 1: m (one to many) • m: n (many to many) INSS 651 9

Recursive An entity has relationship to itself, it could be 1: 1; 1: m or m: n EX: EMPLOYEES MARRIED TO OTHER EMPLOYEES EMPLOYEE INSS 651 10

CREATE A TABLE WITH EMP_SPOUSE NUMBER ADDED TO IT EMPLOYEE (EMP_NO, EMP_NAME, …, SPOUSE_NO) INSS 651 11

Cardinalities Relates specific occurrences of ONE entity to other, and vice-versa STUDENT COURSE MIN cardinality MAX cardinality INSS 651 12

Min, Max STUDENT < ------- >CLASS What’s the minimum number of classes a student can take zero, what’s the max, say 6 What’s the minimum number of students a class can have say 1, max say 40 (class limit), then STUDENT< ------- >CLASS (0, 6) (1, 40) INSS 651 13

Another Cardinality Example Patient Pat_history What’s the minimum number of patient history a patient can have? 1 What’s the maximum ? Many, say N What’s the minimum number of patient a patient history can belong to? Maximum? INSS 651 14

Patient (1, N) Pat_history (1, 1) INSS 651 15

Converting 1: 1; 1: m and m: n into relations 1: 1 Each entity becomes a relation and PK of either entity becomes a FK in the other entity ASSUME AN EMPLOYEE CAN have only one car and a car belongs to only ONE employee EMPLOYEE CAR Relations: EMPLOYEE (emp_no, emp_name, . . , car_id) CAR (car_id, model, make…) Note: or emp_no could be FK in CAR INSS 651 16

1: m • PK of PARENT becomes FK in the CHILD Ex: Assume: A team can have many players and a player can play for ONLY ONE team TEAM PLAYER TEAM (Team_name, location, manager, . . ) PLAYER (Player_name, …Team_Name) INSS 651 17

M: N • Convert m: n into TWO 1: m by introducing an intersection entity • The intersection contains PK of both entities CLASS STUDENT GRADE STUDENT CLASS (class_id, # of students, . . ) STUDENT (st_id, st_name, GPA, . . ) GRADE (class_id, st_id, semester, grade, . . ) INSS 651 18

EX: Convert following ERD into relations assume a student can be advised by one professor but a professor can advise many students, a club can have many students but a student can join only one club, then the ERD will look as STUDENT PROFESSOR CLUB INSS 651 19

Relations will be STUDENT (St_ID, st_name, …, Club_ID, Prof_ID) Note: we must account for both relationships in STUDENT entity (student, professor) AND (student, club) CLUB (Club_ID, location, date_established, . . ) PROFESSOR (Prof_ID, Prof_name, specialty, . . ) INSS 651 20

Building an ER model Q 8/P 139 Identify entities DINNER ENTRÉE GUEST INSS 651 21

Relationships • Each dinner is based on a single entrée, but each entrée can be served at many dinners ENTREE DINNER INSS 651 22

Cardinality One entrée can be part of a min of 1 dinner and a max of many dinners, say n One dinner can be part of a minimum of one entrée and a max of one entrée entree (1, N) dinner (1, 1) INSS 651 23

Relationship between DINNER and GUEST Note there are TWO relationships A guest can attend many dinners Vice versa A guest can have many dinner invitations Vice versa invitation Dinner Guest INSS 651 24

Relationship between DINNER and GUEST Note there are TWO relationships A guest can attend many dinners Vice versa Dinner Guest Attend INSS 651 25

Relationship between DINNER and GUEST Second relationship A guest can have many dinner invitations Vice versa Dinner invitation Guest INSS 651 26

Converting m: n into two 1: m since Guest and DINNER is m: n we can break into TWO 1: m i. e ATTEND entity is intersection of DINNER & GUEST DINNER ATTEND INSS 651 GUEST 27

cardinality What is ATTEND entity ? a combination of a dinner and a guest • What’s the min number of DINNER in ATTEND? 1 • (a dinner must be part of at lest ONE function (ATTEND) Max number? N (assuming N is total # of dinners) An attend can be part as a min part of one dinner and a max of ONE dinner also • what’s the min number of ATTEND a GUEST can have? 0 • (a guest may not attend any dinner) or they may attend a max of N dinners DINNER (1, N) ATTEND (1, 1) INSS 651 GUEST (0, N) 28

• Same concepts can be applied to INVITATION intersection entity INSS 651 29

ERD Q 8/p 139 INSS 651 30

INSS 651 31