Chapter 7 Data Modeling Using the Entity Relationship

Chapter 7 Data Modeling Using the Entity. Relationship (ER) Model 1

Entity Relationship Model (ER) m ER model was proposed by Peter Chen in 1976 m ER model has become the standard tool for conceptual schema design m ER model consists of three basic constructs: entities, attributes and relationships. 2

What is an entity ? m An entity is a “thing” in the real world with an independent existence. m It may be an object with physical existence (e. g. person, car, house, employee), or it may be an object with conceptual existence (company, job, university, course). 3

Entity and Entity Set m Two types of entities: q Strong entity: can exist independently (or can uniquely identify itself) q Weak entity: existence depends on the existence of other (strong) entity or entities m Examples: q An employee is a strong entity but the dependents of the employee could be weak entities q An account in a bank is a strong entity but a transaction could be a week entity 4

Entity and Entity Set m An entity type defines a set of entities that have the same attributes. q STUDENT is an entity type (Schema) m An entity set is a collection of entities of the same entity type m Examples: q Rema, Ali, Amal, Samer, Rana are entity set of an entity type STUDENT 5

Attributes m An entity has a set of attributes that describes it. q Person(SSN, Name, Address, Job-description, Salary). m An entity will have a value for each of its attributes q (999 -010 -201, John Smith, ‘ 20 Alebany Rd, Cardiff, UK’, ‘Manager’, 2500) m The properties of an entity set are called attributes of the entity set. q Students: SSN, Name, Address, GPA, Status, . . . q Books: Title, ISBN, Authors, Publisher, Year, . . . 6

Types of Attributes m Simple (or atomic) attribute is a one which cannot be divided into smaller parts. q Examples: Ø SSN, GPA, Salary. m Composite attribute is an attribute which can be divided into smaller subparts, these subparts represent more basic attributes with independent meanings of their own q Examples: Ø Name: First_Name, Middle_Name, Last_Name Ø Address: Street_Address, City, State, Zip code 7

An Example of a composite attribute Address Street-Address City State Post Code House No. Street Name 8

Types of Attributes m A single-valued attribute is a one which has one (single) value for a particular entity. q Example: Age, Birth. Date m A multi-valued attribute is a one which may have one or more values for the same entity. q College Degrees for Person: 0, 1, 2, 3, … q Color for a Car: 1, 2, …. . q Authors of Books q Phone Number 9

Types of Attributes m A stored attribute is a one whose value is explicitly stored in the database. q e. g. name, birth-date. m Derived-attributes: whose values are computed from other attributes. q Age from Birthdate q Annual Salary from Monthly Salary q No. Of. Employees ==> Count number of employees in the Employee table. 10

Relationship Types m A relationship type is represented as diamondshaped box which is connected by straight lines 11

Relationship Degree of a relationship type is the number of participating entity types: binary relationships, ternary relationships, …. EMPLOYEE Works-for COMPANY Binary Relationships 12

Ternary Relationships SUPPLIER supplies PROJECT PART 13

Ternary Relationships COMPANY Sells PRODUCT COUNTRY 14

Binary Recursive Relationships EMPLOYEE Supervises Marry PERSON 15

Relationships m The role name signifies the role that a participating entity from the entity type plays in each relationship instance m entity e 1 plays the role of a supervisor, while entity e 2 plays the role of a supervisee e 2 EMPLOYEE Supervises e 1 supervisor 16

Cardinality Ratio m Specifies the number of relationship instances that an entity can participate in m Common cardinality ratios for binary relationship types are q 1: 1, q 1: N, and q M: N 17

1: N EMPLOYEE N Works_for 1 COMPANY An employee works for one company, and a company has many employees working for it 18

1: 1 DEPARTMENT 1 Has 1 MANAGER A department has one manager and a manager manages one department 19

M: N EMPLOYEE M Works-on N PROJECT An employee works on many projects, and a project has many employees working on it 20

Participation Constraints m Specifies whether the existence of an entity depends on its being related to another entity via the relationship type m There is total and partial participation 21

Total participation EMPLOYEE N Works-for 1 DEPARTMENT Total participation. Every employee must be related to a department via WORKS-FOR relationship. A department must have at least one employee. 22

Partial participation PERSON 1 Buys N CAR A person may buy a car and car may be bought by a person 23

Total & Partial participation 1 PROFESSOR 1 Manages DEPARTMENT A professor may manage a department (partial participation), but a department must be managed by a professor (total participation). 24

Attributes of Relationship Types N EMPLOYEE 1 Works-for DEPARTMENT Start-Date We may keep a start date attribute to record for each employee the date he/she started work for a certain department. 25

A weak entity type is an entity which does not have any key attributes EMPLOYEE Works-for 1 DEPARTMENT identifying relationship Dependents Fname N Sex DEPENDENT Birthdate Relationship 26

Weak Entity Types • A weak entity type always has a total participation with its identifying entity type • A Weak entity type has a partial key, i. e. this key is enough to identify its extension within the scope of its identifying entity type • In the previous example, the first name is enough to identify kids within a single family, but is not enough to identify entities as stand alone entities (two families may use identical names for their kids) 27

ER Notations Entity Type <Name> Attribute <Name> Key Attribute <Name> Multi-valued attribute 28

ER Diagram Notations <Name> Weak Entity Type <Name> Relationship Type <Name> Identifying Relationship Type 29

ER Notations <Name> Composite Attribute <Name> Derived Attribute <Name> partial key attribute 30

Notations m m m Entity Types q singular name, capital letters Relationship Types q usually singular verbs, capital letters Attribute q nouns, capitalized Role names q are in lowercase letters ER diagrams are drawn such that they are readable from left to right and top to bottom (Except weak entity types) 31

Relationships m Several relationships may exist among the same set of entity sets. Works_in EMPLOYEE DEPARTMENT Manages 32

Degree of a Relationship (1) m Definition: q The degree of a relationship is the number of entity sets participating the relationship. m Recursive relationship Examples: Supervises on Employees is_prerequisite_of on Courses is_classmate_of on Students 33

Degree of a Relationship (2) m Binary relationship (degree = 2) q Examples: Ø takes between Students and Courses Ø owns between Persons and Cars m Ternary relationship (degree = 3) q Examples: Ø orders among Customers, Parts and Suppliers Ø skill_used among Engineers, Skills and Projects 34

Cardinality (1) m One-to-one (1 -to-1) relationship between E 1 and E 2: q for each entity in E 1, there is at most one associated entity in E 2, and vice versa. m Examples of 1 -to-1 relationships: q Binary 1 -to-1 relationship Ø manages between Employees and Departments q recursive 1 -to-1 relationship Ø is_married_to on Persons 35

Cardinality (2) m m One-to-many (1 -to-m) relationship from E 1 to E 2: for each entity of E 1, there are zero or more associated entities of E 2, but for each entity of E 2, there is at most one associated entity of E 1 Examples of 1 -to-m relationships: q binary 1 -to-m relationship Ø advises between Professors and Students q recursive 1 -to-m relationship Ø is_mother_of on Persons m Many-to-one (m-to-1) relationship from E 1 to E 2: same as 1 -to-m relationship from E 2 to E 1 36

Cardinality (3) m Many-to-many (m-to-m) relationship between E 1 and E 2: for each entity in E 1, there are zero or more associated entities in E 2, and vice versa m Examples of m-to-m relationships: q binary m-to-m relationship Ø takes between Students and Courses q recursive m-to-m relationship Ø is_component_of on Parts 37

ER Diagram (1) Recursive relationship is_married_to 1 1 PERSON SSN Name 38 Age

ER Diagram (2) binary relationship PROFESSOR SSN Name 1 m advises Age SSN 39 STUDENT Name Age

ER Diagram (3) ternary relationship ENGINEER Skill_used SKILL PROJECT 40

Role of an Entity Set (1) Definition: The role of an entity set in a relationship is the function it performs in the relationship. Case 1: Role can be determined from properly chosen names. m takes n STUDENT COURSE 1 is_TA_of 41 1

Role of an Entity Set (2) Case 2: Roles need to be explicitly given. is_married_to supervises 1 1 wife husband PERSON 1 m supervisor supervisee EMPLOYEE 42

Attribute of Relationship (1) Where to keep the grade information? STUDENT m takes n COURSE grade 43

Attribute of Relationship (2) Another example: SUPPLIER m n orders PART Quantity r PROJECT 44

Cardinality Constraint min/max (1) m m m One in ER model means zero or one Many in ER model means zero or more Cardinality constraints make them more precise STUDENT (1, 5) takes 45 (15, 60) COURSE

Cardinality Constraint min/max (2) m General format: m 0 min_card max_card q Interpretation: Ø Each entity in E may involve between min_card and max_card relationships in R. E (min_card, max_card) 46 R

Cardinality Constraint min/max (3) m Definition: q If every entity in E involves at least one relationship in R (i. e. , min_card >= 1), E is said to have total participation in R q If min_card = 0, E is said to have partial participation in R 47

Cardinality Constraint min/max (4) Employees has a partial participation. Departments has a total participation. EMPLOYEE (0, 1) manages 48 (1, 1) DEPARTMENT

one-to-one: many-to-many: one-to-many: (0, 1) E (0, m) E 1 E Representing 1 -to-1, 1 -to-m, m-to-m Relationships 49 R R (0, 1) (0, n) (0, 1) m F F

An Example Database Application Company Database 50

An Example Database Application m m m The Company database keeps track of a company’s q Employees, Departments, Projects The following are the requirements and specifications The company is organized into departments. Each department has a: q unique name, unique number q particular employee who manages the department We keep track of the start date when that employee began managing the department A department may have several locations 51

An Example Database Application m m m A department controls a number of projects, each of which has a q unique name, unique number, and single location We store each employee’s q name, social security number, address, salary, sex, and birth date. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department We keep track of the number of hours per week that an employee works on each project We keep track of the direct supervisor of each employee 52

An Example Database Application m m We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee 53

ER diagram for the company database Each department has a unique name, a unique number, particular employee who manages the department. A department may have several locations. Name Number DEPARTMENT 54 Locations Number. Of. Employee

ER diagram for the company database A department controls a number of projects, each of which has a unique name, unique number, and single location Name Number Location PROJECT 55

ER diagram for the company database We store each employee’s name, social security number, address, salary, sex, and birth date. Fname Minit Name Sex BDate Lname SSN EMPLOYEE 56 Address Salary

ER diagram for the company database We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee Fname Sex BDate Relationship DEPENDENT 57

ER diagram for the company database Each department has a particular employee who manages the department We keep track of the start date when that employee began managing the department EMPLOYEE (0, 1) manager Manages (1, 1) DEPARTMENT department managed Start. Date 58

ER diagram for the company database An employee is assigned to one department. EMPLOYEE (1, 1) employee Works_for 59 (1, N) department DEPARTMENT

ER diagram for the company database A department controls a number of projects DEPARTMENT (0, N) Controls controlling department (1, 1) PROJECT controlled project 60

ER diagram for the company database An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee works on each project EMPLOYEE (0, N) Works_on worker (1, N) PROJECT project Hours 61

ER diagram for the company database We keep track of the direct supervisor of each employee EMPLOYEE (0, N) (0, 1) supervisor supervisee Supervises 62

ER diagram for the company database We want to keep track of the dependents of each employee for insurance purposes. EMPLOYEE (0, N) has employee (1, 1) DEPENDENT dependent 63

ER diagram for the company database 64