Entity Relation Model

1. Entity Relation Model Tingting Zhang

2. ER Model • Developed by P. Chen in 1976 • One of the powerful uses of ER modeling is to assure that the data model reflects the user requirements.

3. Entity • An entity is a thing that has an independent existence • Ex: • Mittuniversity • ITM • L111 • DATB03

4. Entity types • Define a set of entities of the same type (share the same structure) • EX: Entity type Entity • University Mittuniversity • Department ITM • Class room L111 • Course DATB03

5. Attributes • An entity is described by its attributes. • EX: • Name • E-mail • ID # • Address • Telephone #

6. Attributes • Domain – the set of permitted values for each attribute • Attribute types: • Simple and composite attributes. • Single-valued and multi-valued attributes • E.g. multivalued attribute: phone-numbers • Derived attributes • Can be computed from other attributes • E.g. age, given date of birth

7. Composite Attributes

8. Birth date telephones name Attributes in ER diagrams • Simple single value attribute • Simple multi valued attribute • Composite Attributes Last name First name

9. Department name Key • An entities of an enetity type usually has an or set of attributes whoes values are distnct for each individual entity in the entity type. • Key in ER diagram

10. Null values • Null: an unknown value or empty value • Used when • Do not know the value of an attributed for a particular entity. • A particular entity may not have an applicable value for an attribute.

11. ID# Name address telephone Entity Type in ER model Customer

12. Relation ship • A relationship is an association among several entities Example: HayesborrowerA-102customer loan entity relationship entity

13. Relationship type • A relationship type among n entity E1, …, En defines a relationship set. • The relationship set is a set of relationship r among n 2 entities, each taken from entity sets {(e1, e2, … en) | e1  E1, e2  E2, …, en  En}where (e1, e2, …, en) is a relationship

14. Relationship Type in ER diagrams

15. Relationship degree (I) One to one One to many

16. Manage ID# address name name address Relationship degree in ER diagrams (I.1) 1 1 Manager Department

17. M 1 Relationship degree in ER diagrams (I.2)

18. Relationship degree (II) Many to one Many to many

19. M 1 Works-for Employee department ID# address name name location Relationship degree in ER diagrams (II.1)

20. M N Works-On Employee Project ID# address name name description Relationship degree in ER diagrams (II.2)

21. Roles • Each entity type that participates in a relationship type plays a particular role in the relationship. • Role name signifies the role that a participating entity from the entity type plays in each relationship instance • Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. • Role labels are optional, and are used to clarify semantics of the relationship

22. Roles in ER diagrams

23. Works-for Works-for 4:N 1 1:1 M Employee Employee department department Constraints on Relationship typesCardinality ratios for binary relationships • Cardinality ratios:

24. Participation of an Entity Set in a Relationship Set • Totalparticipation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set • E.g. participation of loan in borrower is total • every loan must have a customer associated to it via borrower • Partial participation: some entities may not participate in any relationship in the relationship set • E.g. participation of customer in borrower is partial

25. Constraints on Relationship types Participation in ER diagram

26. Cardinality ratios for binary relationships 0:N 1:1

27. M N Works-on Employee project ID# address name name description Hours per week Attributes of relationship types • Relationship types can have attributes

28. Weak entity (I) • An entity set that does not have a primary key is referred to as a weak entity set. • The existence of a weak entity set depends on the existence of a identifying entityset • it must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set • Identifying relationship depicted using a double diamond

29. Weak entity (II) • The partial key of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set. • The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator.

30. Birth date ID# address First name Last name Weak entity (III) First name M 1 Dependents-of Employee dependent The partial key is first name

31. An example database application • The company is organized into departments. Each department has a unique name, a unique number, and a 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. • A department controls a number of projects, each of which has a unique name, a unique number, and a single location • We store each employee’s 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 also keep track of the direct supervisor of each employee. • We want to 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.