Database Modeling Using the Entity-Relationship Model (Chapter 3)

1. Database Modeling Using the Entity-Relationship Model(Chapter 3)

2. Entity-Relationship Model • E-R model was introduced by Peter Chen in 1976 • It is a graphical approach to database modeling. • It describes data as entities, relationships and attributes.

3. Elements of ER Model • Key elements: • Entities • Attributes • Relationships • Identifiers

4. Entities • An entity is the basic object that the E-R model represents • An entity is something that can be identified in real world. • Examples • Peter Johns, MIS, 12345 • May Carson, CS, 67890 • Dee Conway, 1134, CS, Prof. • Entities of a given type are grouped into entity classes.

5. STUDENT Entity Class Entity Instances FACULTY Entities (2)

6. Attributes • Entities have attributes that describe the entity’s characteristics. • Types of attributes: single-valued, multi-valued, composite. Example are: Single-value: “Year 2” Multi-valued: {MIS, Marketing} Composite: (FirstName, LastName)

7. Relationships • A relationship indicated how one or more entity classes interact with one and another. • Each entity plays a role in a relationship. • Degree of the relationship: number of entities in a relationship. • Understanding the relationship is important because it will affect how the database is constructed and used.

8. E-R Concepts • entity type = entity class = entity • entity instance = entity occurrence = record • entity type: a set of objects in the real world with the same properties (not the same values) with a physical or conceptual existence • entity instance: each entity has its own values for each attribute

9. Basic E-R Notation Entity Attribute Weak Entity Multivalued Attribute Derived Attribute Relationship Identifying Relationship

10. Basic E-R NotationRelationship degree Unary Binary Ternary

11. E-R Model Constructs • Strong versus Weak Entity Type • Independent versus dependent entity • Identifying owner • Identifying relationship • Weak entity identifier is its partial identifier combined with that of its owner.

12. E-R Model Constructs • Attribute - property or characteristic of an entity type • Simple versus Composite Attribute • Single-valued versus Multivalued Attribute • Stored versus Derived Attributes.

13. Composite Attribute Address Street City State Zip

14. Name Employee_ID Address Skills Years_Employed EMPLOYEE Date_Employed Entity with multivalued attribute (Skills) and derived attribute (Years_Employed)

15. SSN Student_Name Other_Attributes STUDENT Simple and composite Key attributes (a) Simple key attribute

16. Flight_No Date Number_of_Passengers Flight_ID FLIGHT Simple and composite Key attributes (a) Composite key attribute

17. LName FName SSN Name DOB Tel Age STUDENT Stored vs Derived Attributes Derived attribute: the value of an attribute is derived from something else

18. Derived Attributes • Example 1: Age = today date - DOB • Example 2: total number of staff can be calculated by counting the total number of staff entity instances. • Example 3: deposit = 2 * rent

19. Tel_No BRANCH Branch_No Total_ Branch Fax_No Address ZipCode Street City How to Interpret this Model

20. E-R Model Constructs • Identifier or Key - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type. • Simple Key versus Composite Key • Candidate Key

21. E-R Model Constructs • Criteria for selecting Identifiers • Will not change value • Will not be null • No intelligent identifiers (containing e.g. locations or people that might change) • Substitute new, simple keys for long, composite key.

22. Relationships • Definition: a meaningful association among entity classes. • “Meaningful” implies that the relationship allows us to answer questions that could not be answered given only the entity classes. • In general, any number of entity classes may participate in a relationship.

23. CourseId name Professors Courses teaching Relationships • Relationship type versus Instance • Degree of Relationship - number of entity types that participate in it • A relationship is represented by a diamond in the diagram with lines linking to the corresponding entity classes.

24. Relationship Set • Think of the value of relationship set as a table • One column for each of the connected entity sets. • One row for each list of entities, one from each set, which is connected by the relationship.

25. Relationships • Degree of Relationship • Unary Relationship • Binary Relationship • Ternary Relationship • Attributes of Relationship • Many to Many

26. PERSON Relationships of different degrees (a) Unary relationships N 1 Is_married_to EMPLOYEE Manages 1 1 One-to-many One-to-one

27. Role Names • Unary relationship: the same entity class participates more than once in different roles. 1 Supervisor Supervises EMP N Supervisee

28. (b) Binary relationships 1 1 Assigned PARKING PLACE EMPLOYEE One-to-one 1 N Contains PRODUCT PRODUCT LINE One-to-many M N Registers COURSE STUDENT Many-to-many

29. Manages Works_in Role Names • Role names may also be used when two entity classes are associated through more than one relationships 1 1 manager EMP DEPT member of staff 1 N

30. assisting taking Multiway relationship Usually binary relationships (connecting two E.S.) suffice. • However, there are some cases where three or more E.S. must be connected by one relationship. • Example: relationship among students, courses, TA's. 1 N Students Courses TAs N M

31. Enrolls Multiway relationship • What if students are divided into sections, each headed by a TA? • A student would be related to only one of the TA's for the course. Which one? • Need a 3-way relationship to tell. Courses Students N M 1 TAs

32. price Beers Bars sells sells price Prices Beers Bars Attributes on Relationships Because the price jointly depends on beer and bar, it is actually a shorthand for 3-way relationship

33. Converting multiway to 2-way • Any relationship with more than two E.S. can be converted to a collection binary many-to-one relationships without losing any information. • Steps: • Create a new connecting entity set. • Introduce many-to-one relationships from the connecting entity set to each of the entity sets (or roles) involved.

34. price Bars Prices Beers 1 1 1 The Bar The Price The Beer N N N BBP

35. Arranges 4-nary Relationship SOLICITOR FINANCIAL_ INSTITUTION BUYER BID A buyer, advised by a solicitor, and supported by a financial institution, places a bid for a property. A simultaneous relationship among four instances.