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

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

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

3. What is an entity ? • An entity is a “thing” in the real world with an independent existence. • 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).

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

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

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

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

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

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

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

11. Relationship Types • A relationship type is represented as diamond-shaped box which is connected by straight lines

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

13. Ternary Relationships supplies PROJECT SUPPLIER PART

14. Ternary Relationships Sells PRODUCT COMPANY COUNTRY

15. Binary Recursive Relationships EMPLOYEE Supervises Marry PERSON

16. Relationships • The role name signifies the role that a participating entity from the entity type plays in each relationship instance • entity e1 plays the role of a supervisor, while entity e2 plays the role of a supervisee supervisee EMPLOYEE e2 Supervises e1 supervisor

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

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

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

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

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

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

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

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

25. Attributes of Relationship Types Works-for N 1 EMPLOYEE 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.

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

27. 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)

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

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

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

31. Notations • Entity Types • singular name, capital letters • Relationship Types • usually singular verbs, capital letters • Attribute • nouns, capitalized • Role names • 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)

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

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

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

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

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

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

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

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

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

41. 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 1

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

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

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

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

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

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

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

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

50. An Example Database Application Company Database