Database Design Entity Relationship Model Entity Relationship Model

Database Design Entity Relationship Model

Entity Relationship Model Main Components of the ER Model u Entities 개체 Thing about which to collect data → u e. g. , STUDENT, TEACHER Attributes 속성 Characteristics of entities → Attribute Domain set of possible values Relationships 관계 • Association between entities TEACHER STUDENT ID Major GPA Name Office email Entity Relationship Diagram (ERD) u u ER model forms the basis of an ER diagram ERD represents the conceptual view 개념 뷰 of the database Design 2

E-R Model: Attribute Types Simple attribute Composite attribute 단순 속성 → cannot be subdivided 복합 속성 → can be subdivided into multiple attributes → Replace with multiple simple attributes Age Gender Marital Status Name 34 Male Single James T. Kirk Single-valued attribute 일가 속 First Name Middle Name Last Name James Tiberius Kirk Home Phone Cell Phone Work Phone 070 -1234 -5678 010 -1234 -5678 Multi-valued attribute 다중값 속성 성 → can have only one value → can have many values → Avoid if possible Race Gender Birthdate Phone Vulcan Male 1945 -03 -22 070 -1234 -5678 010 -1234 -5678 Name Simple Composite Database Design First Name Middle Name Last Name 3

E-R Model: Multi-valued Attributes 다중값 속성 Avoid Multi-valued attributes (MVA) 1. Replace with multiple single-valued attributes (SVA). Car_Color �Car_Top. Color, Car_Trim. Color, Car_Body. Color, Car_Interior. Color could be problematic 2. Create a new entity composed of original multi-valued attribute’s components Car_Color �CAR_COLOR (Car_Vin, Col_Section, Col_Color) Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 4

E-R Model: Multi-valued Attributes MVA → multiple SVA ID Name Home. Phone Cell. Phone Work. Phone 1234 James Kirk 070 -1234 -5678 010 -1234 -5678 053 -1234 -5678 2345 Spock 010 -2345 -6789 3456 Khan 010 -3456 -7890 Cell. Phone 2 Cell. Phone 3 010 -4567 -7890 010 -5678 -7890 MVA → Entity ID Name ID Phone. Type Phone. Number 1234 James Kirk 1234 home 070 -1234 -5678 2345 Spock 1234 cell 010 -1234 -5678 3456 Khan 1234 work 053 -1234 -5678 2345 cell 010 -2345 -6789 3456 cell 010 -3456 -7890 3456 cell 2 010 -4567 -7890 3456 cell 3 010 -5678 -7890 3456 cell 4 010 -6789 -7890 Database Design 5

E-R Model: Relationships Relationship 관계 u Association between entities ® Connectivity & Cardinality are established by business rules Connectivity 관계유형 Type/Classification of Relationships ® 1: 1, 1: M, M: N Cardinality 관계차수 (min, max) = minimum/maximum number of occurrences of the related entity Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 6

ERM: Relationship Strength Weak (non-identifying) Relationship PK of related entity does not contain PK component of parent entity u 관련된 개체의 PK가 모개체의 PK요소를 포함하지 않음 CRS_ID CRS_Name Credit Class_ID CRS_ID Room DB Database Design 3 DB-s 15 DB 421 IT Internet Technology 3 IT-s 15 IT 403 WP Web Programming 3 DB-s 16 DB 421 IT-s 16 IT 403 Strong (identifying) Relationship PK of related entity contains PK component of parent entity 관련된 개체의 PK가 모개체의 PK요소를 포함함 CRS_ID CRS_Name Credit DB Database Design IT WP Database Design CRS_ID Section Room 3 DB s 15 421 Internet Technology 3 IT s 15 403 Web Programming 3 DB s 16 421 IT s 16 403 7

ERM: Relationship Strength Ø Relationship strength is determined by table design (PK), but it can be driven by Business rules A student can decide his/her major after 2 years Dpt_ID CRS_Name Stud_ID Dpt_ID LIS Library & Information Science 12345 LIS Kirk CS Computer Science 23456 LIS Spock HIST History 34567 CS Sulu 45678 Name Kahn A student must decide his/her major in the beginning Dpt_ID CRS_Name Dpt_ID Snum Name LIS Library & Information Science LIS 15001 Kirk CS Computer Science LIS 15002 Spock HIST History CS 15001 Sulu LIS 16001 Kahn Database Design 8

ERM: Relationship Participation Optional Participation 선택관계 (Child) Entity is not required to participate in a relationship ® Minimum cardinality of the optional entity is 0 CLASS is optional to Course - i. e. , Course does not have to generate a class COURSE CRS_ID CLASS CRS_Name Credit DB Database Design IT WP Database Design CRS_ID Section Room 3 DB s 15 421 Internet Technology 3 IT s 15 403 Web Programming 3 DB s 16 421 9

ERM: Relationship Participation Mandatory Participation 필수관계 u Entities must participate in a relationship ® Minimum cardinality of the mandatory entity is 1 CLASS is mandatory to Course - i. e. , Course has to generate a class COURSE CRS_ID CLASS CRS_Name Credit DB Database Design 3 DBs 15 DB 421 IT Internet Technology 3 ITs 15 IT 403 WP Web Programming 3 DBs 16 DB 421 Database Design Class_ID CRS_ID Room 10

ERM: Relationship Strength vs. Participation Ø Participation & Strength do not determine each other Ø Ø q Relationship strength is determined by the composition of the related table’s PK Relationship participation is based on business rules DEPENDENT (weak) EMPLOYEE and DEPENDENT ü Strong & Optional relationship Dpd_ID Emp_ID An employee has many dependent. 123 s 1 123 Kirk 234 s 1 234 Spock ® An employee may not have any dependent ® DEPENDENT is optional to EMPLOYEE A dependent belong to one employee. ® A dependent must belong to an employee ® PK of DEPENDENT must contain PK of EMPLOYEE Strong relationship 1 M has EMPLOYEE (0, M) DEPENDENT (1, 1) 345 s 2 123 s 2 Sulu 123 Kahn DEPENDENT (strong) Dseq Emp_ID Name s 1 123 Kirk s 1 234 Spock s 2 123 Kahn s 2 Database Design Name Sulu 11

ERM: Relationship Strength vs. Participation Ø Participation & Strength do not determine each other Ø Ø q Relationship strength is determined by the composition of the related table’s PK Relationship participation is based on business rules CLASS (weak) PHD_STUD and CLASS ü Weak & Mandatory relationship Class_ID CRS_ID st_ID A doctoral student can teach many classes. DBs 15 a DB g 123 DBs 15 b DB kiyang 403 ITs 15 a IT kiyang 421 ITs 16 a IT ® A doctoral student must teach a class ® CLASS is mandatory to PHD_STUD A class is taught by one doctoral student. ® A class can be taught by a professor ® PK of CLASS does not contain PK of PHD_STUD Weak relationship 1 (1, M) Database Design M teaches PHD_STUD CLASS (1, 1) pf_ID Room 403 g 123 403 CLASS (strong) CRS_ID Section st_ID DB s 15 g 123 DB s 15 kiyang 403 IT s 15 kiyang 421 IT S 15 g 123 pf_ID Room 403 12

ERM: Relationship Degree Ø Relationship Degree indicates the number of associated entities. Unary Relationship exists between occurrences of same entity set e. g. , Recursive relationship CRS_ID CRSname Prereq DB 1 Database Design DB 2 Web Database DB 1 DB 3 Database Projects DB 2 Binary Relationship Two entities associated Most common • higher-order relationships are often decomposed into binary relationships Ternary Three entities associated e. g. , DOCTOR, PATIENT, DRUG Database Design PRS_ID Dr_ID Pat_ID Drug_ID expire. Date 160101 -121 Frnknstein 55 4567 hrn 21 pr 16/06/25 100415 -001 Strangelove 21 4567 thc 16 ts 20/04/15 160101 -122 Dr. Who 6 1234 trs 10 by 16/12/31 13

ERM: Composite Entities Composite Entity (i. e. , Bridge Entity) Transforms a M: N relationship into two 1: M relationships Contains primary keys of the “bridged” entities • May also contain additional attributes that play no role in connective process Typically has strong relationships with the “bridged” entities M N enrolls in STUDENT (0, M) CLASS A student doesn’t have to enroll in a class A class has to have minimum 10 students (10, M) Class_ID 1 STUDENT (0, M) Database Design M (1, 1) M 1 ENROLL CLASS (1, 1) (10, M) Stud_ID Grade DB-s 15 1234 A IT-s 15 1234 A DB-s 16 2345 B IT-s 16 3456 C 14

ERM: Ø Problem Ø Entity Supertype & Subtype Unshared characteristics of certain entity subtypes (e. g. EMPLOYEE & PILOT) Solution • Supertype (parent) & lower-level Subtype (child) entities ® Subtypes inherit the attributes and relationships of the supertype ® 1: 1 relationship Shared attributes EMP_ID EMPLOYEE (Supertype) 1 is email Phone 1234 Kirk jtkiri@trek. org 010 -1111 2345 Spock spock@logic. vulcan 010 -2222 3456 Sulu sulu@star. ac. jp 070 -1212 -3456 0007 Pike cpike@trek. org 011 -0007 1 Unique attributes PILOT (Subtype) Database Design Name EMP_ID Pilot_License Flight. HR 1234 enpr-sc 213 g 1923 0007 enpr-cg 123 k 355 15

Subtypes: Overlapping vs. Non-overlapping (Disjoint) Overlapping Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 16

Exercises

Database Design: University Database 1. Construct a data model of a typical university. → To keep track of class & advising information by department (double-major allowed) Database Objectives Generate a course listing by semester (e. g. , professor, department, class time & place) For each professor, generate the classes taught & student grades for each class. For each student, generate the complete listing of advising sessions (e. g, date, time, advisor) For each school, generate a list of all employees by department. For each department, generate a listing of all double-major students. Business Rules A school can have many departments. Each department belongs to one school. A department has many employees. An employee works in one departments. An employee can be a professor or a staff. A course generates many classes. Each class belongs to a course. A professor teaches many classes. A class is taught by one professor. A student can take many classes. A class has many students. A student belongs to one department. A department has many students. A student can double-major in a department. A department can have many double-major students. Database Design 18

Database Design: University Database 1. Construct a data model of a typical university. To keep track of class & advising information by department (double-major allowed) → Business Rules A school can have many departments. Each department belongs to one school. A department has many employees. An employee works in one departments. An employee can be a professor or a staff. A course generates many classes. Each class belongs to a course. A professor teaches many classes. A class is taught by one professor A student can take many classes. A class has many students. A student belongs to one department. A department has many students. A student can double-major in a department. A department can have many double-major students. M 1 DEPARTMENT 1 M has 1 M major 2 has STUDENT 1 M ENROLL has M 1 SCHOOL Database Design M EMPLOYEE 1 1 is 1 PROFESSOR 1 M CLASS M 1 COURSE 19

Database Design: University Database 2. Convert the data model of a university to a relational schema. Sample DB Database Design 20

Database Design: University Database 3. One of the database objective is to generate a list of students advised for a given year & dates of advising sessions for each students for each professor. Modify the ERD to support this DB objective. Business Rules A professor can meet many times with an advisee. A student can meet many times with a professor Sample DB M 1 1 DEPARTMENT 1 has M M major 2 STUDENT 1 1 M M has ENROLL ADVISING M M 1 SCHOOL Database Design M EMPLOYEE 1 is 1 1 PROFESSOR 1 1 M CLASS M 1 COURSE 21

Database Design: University Database 3. Data model below corrects the following problems with the previous data model. The departmental advisor information is missing -- A student may consult with a professor who is not his/her departmental advisor Ø A professor can advise many students. A student is advised by one professor. The department designation for courses is missing -- A professor may teach courses outside of his/her department. Ø A department offers many courses. A course is offered by one department. Sample DB 1 M offers 1 1 DEPARTMENT 1 has M M STUDENT M major 2 1 1 M M has 1 SCHOOL Database Design has advises 1 M EMPLOYEE 1 is ENROLL ADVISING 1 M M 1 1 PROFESSOR 1 M CLASS M M 1 COURSE 22