Entity Relationship Model ER Modeling Basic Modeling Concepts

Entity Relationship Model: E-R Modeling

Basic Modeling Concepts Model “Description or analogy used to visualize something that cannot be directly observed” -Webster’s Dictionary - Data Models Relatively simple representation of complex real-world data structures → Facilitate communication → Enhance understanding Database System 2

Degrees of Data Abstraction Conceptual Global view of data • identify and describe main data items (e. g. E-R diagram) Hardware and software independent Internal Representation of database as seen by DBMS • adapt conceptual model to specific DBMS (e. g. Access tables) Software dependent External Users’ views of data environment • group requirements & constraints subsets into functional modules • e. g. student registration module, class scheduling module Facilitates development & revalidates the conceptual model Physical Lowest level of abstraction • determine of physical storage devices and access methods software and hardware dependent Database System 3

Data Abstraction Models Database Systems: Design, Implementation, & Management: Rob & Coronel Database System 4

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 5

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 6

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 7

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 8

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 9

Relationship Strengths Existence Dependence Entity’s existence depends on the existence of related entities. • Existence-independent entities can exist apart from related entities. u Weak Entities Existence dependent entities Cannot exist without the related entity e. g. EMPLOYEE claims DEPENDENT is existence-dependent on EMPLOYEE u Strong Entities Can exist apart from all of the related entity → Table with a primary key that does not contain the primary key of related table e. g. EMPLOYEE has SPOUSE EMPLOYEE is existence-independent of SPOUSE Database System 10

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

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 12

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 13

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 14

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 15

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 16

Relationship: Weak Entities Database Systems: Design, Implementation, & Management: Rob & Coronel Strong vs. Weak entities Database System § Strong Entity = existence-independent entity in a strong relationship § Weak Entity ü ü ü existence-dependent entity in a strong relationship inherits all or part of its primary key from parent entity w/ clipped corners in CF model, double-walled in Chen model 17

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 18

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 19

M: N to 1: M Conversion CLASS STUDENT STU_ID STU_NAME CLS_ID CRS_NAME CLS_SECT STU_ID 1234 John Doe 10012 L 546 1 1234 John Doe 10014 10013 L 546 2 2341 Jane Doe 10013 10014 L 548 1 1234 2341 Jane Doe 10014 L 548 1 2341 Jane Doe 10023 L 571 1 2341 STU_ID STU_NAME CLS_ID STU_ID ENR_GRD CLS_ID CRS_NAME CLS_SEC 1234 John Doe 10012 1234 B 10012 L 546 1 2341 Jane Doe 10013 2341 A 10013 L 546 2 10014 1234 C 10014 L 548 1 10014 2341 A 10023 L 571 1 10023 2341 A CLASS STUDENT ENROLL 1. 2. Move the foreign key columns to create a bridge table & add attributes if needed. Collapse the duplicate records in remaining tables. Database System 20

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 21

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

Developing ERD Iterative Process 1. Create detailed narrative of organization’s description of operations 2. Identify business rules based on description of operations 3. Identify main entities and relationships from business rules 4. Develop initial ERD 5. Identify attributes and primary keys that adequately describe entities 6. Revise and review ERD Database System 23

ERD Example: Narrative of operational environment Tiny College is divided into several schools Each school is composed of several departments Each school is administered by a dean Each dean is a member of administrators group A dean is also a professor and may teach classes Administrators and professors are employees Each department offers several courses Each course may have several sections (classes) Each department has many professors and students One of the professors chairs the department Each professor may teach up to 4 classes A student may enroll in several classes Each student has an advisor in his/her department Each student belong to only one department Database System 24

ERD Example: Supertype/Subtype - Each school is administered by a dean - Each dean is a member of administrators group - A dean is also a professor and may teach classes - Administrators and professors are employees Database Systems: Design, Implementation, & Management: Rob & Coronel Professors and administrators have unique characteristics not present in other employees EMPLOYEE supertype, PROFESSOR & ADMINISTRATOR (overlapping) subtypes Professors and administrators have same set of characteristics collapse PROFESSOR and ADMINISTRATOR entities Database System 25

ERD Example: ERD segment 1 Database Systems: Design, Implementation, & Management: Rob & Coronel Professors are employees A professor may be a dean Each school is administered by a dean Each school is composed of several departments Database System 26

ERD Example: ERD segment 2 & 3 Database Systems: Design, Implementation, & Management: Rob & Coronel Each department offers several courses Each course may have several sections (classes) Database System 27

ERD Example: ERD segment 4 & 5 Database Systems: Design, Implementation, & Management: Rob & Coronel Each department has many professors One of the professors chairs the department Each professor may teach up to 4 classes Database System 28

ERD Example: ERD segment 6 & 7 Database Systems: Design, Implementation, & Management: Rob & Coronel A student may enroll in several classes Each department has many students Each student belong to only one department Database System 29

ERD Example: ERD segment 8 & 9 Database Systems: Design, Implementation, & Management: Rob & Coronel Each student has an advisor Class is held in class rooms Database System 30

ERD Example: ERD components Database Systems: Design, Implementation, & Management: Rob & Coronel Database System 31

ERD Example: Merging ERD segments Database System 32

ERD Example: Completed ERD Database Systems: Design, Implementation, & Management: Rob & Coronel Database System 33

Sample Database System 34

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 36

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 37

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

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 39

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 40