Chapter 5 The Entity Relationship Model

1. Chapter 5The Entity Relationship Model

2. Purpose of E-R Model • Facilitates database design • Express logical properties of mini-world of interest within enterprise • Conceptual level model • Not limited to any particular DBMS – Generic Model • E-R diagrams used as design tools • A semantic model – captures meanings

3. Entity • Object that exists and that can be distinguished from other objects • Can be person, place, event, object, concept in the real world • Can be physical object or abstraction • Entity instance is a particular person, place, etc. • Entity type is a category of entities • E.g., enterprise is university, consider all students in university and identify common properties of interest of the students • Entity set is a collection of entity instances of same type • Entity set must be well-defined • Entity type form intension of entity – permanent definition part • Entity instances and actual set form extension of entity – all instances that fulfill the definition at the moment • In E-R diagram, rectangle represents entity set

4. Attributes • Defining properties or qualities of entity type • Represented by oval/ellipse on E-R diagram • Domain – set of allowable values for attribute • Credit hours might be integer values between 0 and 150 • lastName might be all legal last names – a string that might include blanks, hyphens, or other special characters • May have null values for some entity instances – no mapping to domain for those instances

5. Attributes (cont…) • May be multi-valued – use double ellipse on E-R diagram (e.g., student may have more than one email address) • May be composite – use ellipse for composite attribute, with ellipses for components connected to it by lines • May be derived – use dashed oval email address street state city zip age Students Faculty

6. Identifiers / Keys • Superkey: an attribute or set of attributes that uniquely identifies an entity (can always tell one entity instance from entity set) • stdId is superkey for Student entity • stdname, house# together form a superkey • Composite key: key with more than one attribute • stdname, house# make up a composite key • Candidate key: superkey such that no proper subset of its attributes is also a superkey (minimal superkey – has no unnecessary attributes) • Although stuId, credits is a superkey, only stuId is a candidate key • stdname, house# is a candidate (no one attribute is a superkey) • If Student entity contains stuId and ssn as atributes, both stuId and ssn are candidate keys

7. Keys (cont…) • Primary key: the candidate key actually used for identifying entities and accessing records • Non of its attributes may have null value • Alternate key: candidate key not used for primary key • Secondary key: attribute or set of attributes used for accessing records, but not necessarily unique • lastName might be used to find instances in Student, to help narrow down the results • Foreign key: term used in relational model (but not in the E-R model) for an attribute that is primary key of a table and is used to establish a relationship, usually with another table, where it appears as an attribute also • stuId in Enroll entity

8. Relationships • Connections or interactions between entity instances • Represented by diamond on E-R diagram • Relationship type – category of relationships • Relationship set – collection of relationships of same type • Relationship instances – relationships that exist at a given moment • Relationship can have descriptive attributes • Degree of relationship • Binary – links two entity sets; set of ordered pairs • Ternary – links three entity sets; ordered triples • N-ary – links n entity sets; ordered n-tuples

9. Cardinality of Relationships • Number of entity instances to which another entity set can map under the relationship • One-to-one: X:Y is 1:1 if each entity instance in X is associated with at most one entity instance in Y and each entity in Y with at most one entity in X. • One-to-many: X:Y is 1:M if each instance in X can be associated with many entity instance in Y, but each entity in Y with at most one entity instance in X. • Many-to-many: X:Y is M:M if each entity in X can be associated with many entities in Y, and each entity in Y with many entities in X (many = more than one) • Figure 5.4shows several representation methods

10. Figure 5.8 Showing Cardinalities on ER Diagram

11. Relationship Participation Constraints • Partial participation • Not every entity instance must participate • Represented by single line from entity rectangle to relationship diamond • Some students may not be enrolled in any classes, some classes may not have any students • Total participation • Every member of entity set must participate in the relationship • Represented by double line from entity rectangle to relationship diamond • Some students may not be enrolled in any classes, some classes may not have any students • Some faculty may not teach any classes, but every class must have a faculty member teaching it Student Faculty Class Teaches Enroll

12. Roles • Role: function that an entity plays in a relationship • Optional to name role of each entity, but helpful in cases of • Recursive relationship – entity set relates to itself • Multiple relationships between same entity sets

13. Roles: Examples 1 Chairperson M Members M teaches M is taught by Student Faculty Faculty Chair- Member Teaches Advise M is advised by 1 Advises

14. Dependencies • Dependency is a type of constrain • There are a number of dependencies • Existence dependency • Identifier dependency • Referential dependency

15. Existence Dependency • Existence dependency: Entity Y is existence dependent on entity X if each instance of Y must have a corresponding instance of X • In that case, Y must have total participation in its relationship with X • This means a Y entity cannot exist without some X entity, and when X is dropped from the database the Y must be dropped. • For example, A faculty cannot exist without the existence of its relevant department • Here the department will be referred to as Strong, Parent, Owner or Dominant entity • Where as Faculty is Weak, Child, Dependent or Subordinate entity. • Weak entity is denoted by double rectangle in E-R Diagram

16. Existence Dependency and Weak EntitiesExample facId name rank DeptName Office Hires Department Faculty

17. Identifier Dependency • Special type of existence dependency occurs when the weak entity set does not have a candidate key and its instances are indistinguishable without a relationship with another entity. Referential Dependency • Another special type of existence dependency occurs when the weak entity contains as a foreign key the primary key of the corresponding strong entity

18. Identifier Dependency Ap_No B_Name Rent B_Name Address … Has Building Apartments

19. Referential Dependency facId name rank DeptName Office DeptName Hires Department Faculty

20. ER Diagram Example deptName author lastName credit stuId major lastName firstName office deptCode facId isbn rank firstName publisher title Faculty- Class- Textbook HasMajor Employs Teaches Chairs Offers Department Textbook Student Faculty Class

21. ER Diagram Example

22. E-R Diagram description • Student: stuId, lastName, firstName, major, credits • Each student has a unique id and has at most one major • Department: deptCode, deptName, office • Each department has a unique code and a unique name, and that each department has one office designated as the departmental office • Faculty: facId, lastName, firstName, rank • facId is unique and that every faculty member must belong to department. One faculty member in each department is the chairperson. • Class: classNumber, sched, room • classNumber consists of deptCode, courseNumber, section • Textbook: isbn, author, title, publisher • A book can have multiple authors • Evaluation: date, rater, rating • Evaluation is a weak entity, dependent on Faculty