Chapter 3 Data Modeling Using the Entity-Relationship Model

1. Chapter 3Data Modeling Using the Entity-Relationship 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. Null Values • An attribute may have null as its value. • Null may mean • not applicable • college degree • Unknown • Missing • height of Smith • Not known • home phone for Smith

12. Keys • Key attribute is an attribute whose values are distinct (unique) for a given entity type. • Keys may be • simple: one attribute (SSN), or • composite: a set of attributes whose values together uniquely identify an entity type • Name(first name, father name, grandfather name, family name)

13. Value Sets or Domains • A value set Vor domain for a simple attribute A specifies the set of values that may be assigned to that attribute for each individual entity. • e.g. Employee.Age • Age >=16 and Age <=60 • V(Age) = dom(Age) = {16, 17, 18, …, 60} • dom(StudentMajors) = {math, cs, ce, med, ee, me, nursing, …}

14. Complex Attribute • {AddressPhone({Phone(AreaCode, Phone#)}, Address(StreetAddress(No, Name, AptNo), City, State))} • The above example states that a person may have several residences and each residence may have several phones. Phone numbers consist of area code and phone number. Address, by contrast, consists of street, city and state. Street address is a composite attribute which consists of street number, name and apartment number. • ( ) : indicates a composite attribute • { }: indicates a multi-valued attribute.

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

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

17. Ternary Relationships supplies PROJECT SUPPLIER PART

18. Ternary Relationships Sells PRODUCT COMPANY COUNTRY

19. Binary Recursive Relationships EMPLOYEE Supervises Marry PERSON

20. 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

21. 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

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

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

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

25. 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

26. 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.

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

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

29. 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.

30. 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

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

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

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

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

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

36. ER Notations Total participation of E2 in R R E1 E2 Cardinality Ratio 1:N for E1 and E2 in R 1 N R E2 E1

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

38. 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

39. 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

40. 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

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

42. 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

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

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

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

46. 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

47. 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

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

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

50. Cardinality Constraint (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