Chapter 3 Data Modeling Using the Entity-Relationship ModelSlide 2Entity-Relationship(ER) ModelEntity-Relationship(ER) Model (con’t)DefinitionsDefinitions (con’t)Slide 7AttributesAttribute ExamplesSlide 10Slide 11ER Diagram for COMPANY DatabaseRelationship TypeRelationship Type (con’t)Entity RolesWeak Entity TypeRelationship AttributesRelationshipRelationship (con’t)Slide 20Fig 3.12Fig 3.13Structural ConstraintsStructural Constraint Min, MaxSlide 25A ternary relationship generally represents more information than 3 binary relationshipsSlide 27A Weak Entity with a Terary Identifying RelationshipChapter 3 1Chapter 3Data Modeling Using theEntity-Relationship ModelChapter 3 2Chapter 3 3Entity-Relationship(ER) Model•The ER model is a high-level conceptual data model. It has not been implemented in any commercial DBMS (?), but is a powerful short hand often used in database design for a first rendition of the miniworld.•The ER model was introduced by Peter Chen in 1976, and is now the most widely used conceptual data model.Chapter 3 4Entity-Relationship(ER) Model(con’t)•Much work has been done on the ER model, and various extensions and enhancements have been proposed (see Ch. 21 and the text Conceptual Database Design by Batini/Ceri/Navathe, Benjamin Commings).Chapter 3 5Definitions•An entity is an object in the miniworld.•An attribute of an entity can have a value from a value set (domain)•Each entity belongs to some one entity type s.t. entities in one entity type have the same attributes (so each entity type is a set of similar entities).Chapter 3 6Definitions (con’t)•A key attribute of an entity type is one whose value uniquely identifies an entity of that type.•A combination of attributes may form a composite key.•If there is no applicable value for an attribute that attribute is set to a null value.Chapter 3 7Entity Type / Entity SetEntity Type (Intension): EMPLOYEEAttributes: Name, Age, SalaryEntity Set (Extension):e1 = (John Smith, 55, 80000)e2 = (Joe Doe, 40, 20000)e3 = (Jane Doe, 27, 30000)...Chapter 3 8Attributes•Attributes can be–composite / simple (atomic)–single-valued / multivalued–stored / derived–key / nonkey.Chapter 3 9Attribute ExamplesName = John DoeBirthdate = May 10, 1989Age = 9Degree = nullSSN = 123456789Name = John DoeBirthdate = May 10Birthyear = 1989Age = 9Degree = nullSSN = 123456789Name = Jane DoeBirthdate = July 11, 1960Age = 38Degree = B.S., M.S.SSN = 987654321Chapter 3 10Chapter 3 11EMPLOYEEName, SSN, Sex, Address, Salary, Birthdate, Department, Supervisor, {Works on ( Project, Hours)}WORKS_FOREMPLOYEE DEPARTMENT1NName SSN . . .Relationship instances of WORKS_FOR:{(KV, CS), (Pan, EE), . . .}Chapter 3 12ER Diagram for COMPANY DatabaseChapter 3 13Relationship Type•A relationship type R among n entity types E1,…,En is a set of relationship instances ri, where each ri associates n entities (e1,…,en), s.t. each ej  Ej. Informally, a relationship instance is an association of entities, with exactly one entity from each participating entity type.Chapter 3 14Relationship Type (con’t)•The degree n of a relationship type is the number of participating entity types. •In the ER model relationships are explicitly represented.Chapter 3 15Entity Roles•Each entity type in a relationship type plays a particular role that is described by a role name. Role names are especially important in recursive relationship types where the same entity participates in more than one role:EmployeeSupervisionSupervisor 1 N SuperviseeChapter 3 16Weak Entity Type•A weak entity type is one without any key attributes of its own. Entities belonging to a weak entity type are identified by being related to another entity type ( called identifying owner) through a relationship type ( called identifying relationship), in combination with values of a set of its own attributes (called partial key). A weak entity type has total participation constraint w.r.t. its identifying relationship.Chapter 3 17Relationship Attributes•Relationship types can have attributes as well. in case of 1:1 or 1:N relationships, attributes can be migrated to one of the participating entity types.Chapter 3 18RelationshipChapter 3 19Relationship (con’t)Chapter 3 20Relationship (con’t)Chapter 3 21Fig 3.12Chapter 3 22Fig 3.13Chapter 3 23Structural Constraints•Structural constraints of a relationship type:–Cardinality ratio: Limits the number of relationship instances an entity can participate in, eg. 1:1, 1:N, M:N–Participation constraint: If each entity of an entity type is required to participate in some instance of a relationship type, then that participation is total; otherwise, it is partial.Chapter 3 24Structural Constraint Min, Max•A more complete specification of the structural constraint on a relationship type can be given by the integer pair (min, max), which means an entity must participate in at least min and at most max relationship instances.Chapter 3 25Chapter 3 26A ternary relationship generally represents more information than 3 binary relationshipsChapter 3 27Chapter 3 28A Weak Entity with a Terary Identifying