Chapter 5 The Entity Relationship Model 1 Purpose

Chapter 5 The Entity Relationship Model 1

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

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

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 last. Name 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

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

Identifiers / Keys Superkey: an attribute or set of attributes that uniquely identifies an entity (can always tell one entity instance from entity set) std. Id 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 stu. Id, credits is a superkey, only stu. Id is a candidate key stdname, house# is a candidate (no one attribute is a superkey) If Student entity contains stu. Id and ssn as atributes, both stu. Id and ssn are candidate keys

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 last. Name 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 stu. Id in Enroll entity

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

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. 4 shows several representation methods

Figure 5. 8 Showing Cardinalities on ER Diagram

Relationship Participation Constraints 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 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 Student Faculty Teaches Enroll Class

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

Roles: Examples erson airp 1 Ch Chair. Member Faculty M Me mber s es ch ea Teaches M t M is ta ug ht b y Faculty Student 1 d ise Ad vis es Advise M is v ad by

Dependencies Dependency is a type of constrain There a number of dependencies Existence dependency Identifier dependency Referential dependency

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

Existence Dependency and Weak Entities Example Dept. Name Office Department Hires fac. Id name rank Faculty

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

Identifier Dependency Address B_Name Building Has Ap_No B_Name Rent Apartments …

Referential Dependency Dept. Name Office Department Hires fac. Id name rank Faculty Dept. Name

ER Diagram Example dept. Code office dept. Name Department Has. Major Employs Chairs isbn Offers stu. Id title Textbook last. Name publisher Student author first. Name major fac. Id last. Name credit Class Faculty. Class. Textbook first. Name Teaches Faculty rank

ER Diagram Example

E-R Diagram description Student: stu. Id, last. Name, first. Name, major, credits Each student has a unique id and has at most one major Department: dept. Code, dept. Name, office Each department has a unique code and a unique name, and that each department has one office designated as the departmental office Faculty: fac. Id, last. Name, first. Name, rank fac. Id is unique and that every faculty member must belong to department. One faculty member in each department is the chairperson. Class: class. Number, sched, room class. Number consists of dept. Code, course. Number, section Textbook: isbn, author, title, publisher A book can have multiple authors Evaluation: date, rater, rating Evaluation is a weak entity, dependent on Faculty