To Previous Chapter To Table of Contents To Bottom of Page To Next Chapter

Chapter 3 - The Entity-Relationship Model

  1. Elements of the Entity-Relationship Model
    Introduced by Peter Chen in 1976
    • Entities
      entity - something that can be identified in the users' work environment (something that the user wants to track); i.e. EMPLOYEE Mary Doe, CUSTOMER 12345, SALES-ORDER 1000, etc.
      entity class - groups of entities of a given type; i.e. EMPLOYEE
    • Attributes
      attribute (property) - describes a characteristic of an entity; i.e. EmployeeName, DateOfHire, JobSkillCode
      attributes can be single or multiple valued (ie. courses taken); can also be composite (i.e. Address)
    • Identifiers
      identifier attributes that name the entity instance (i.e. EmployeeID, EmployeeName)
      identifiers can be unique or non unique
      composite identifiers consist of more than one attribute (i.e. [FirstName, LastName, PhoneExtension])
    • Relationships
      relationship - association between entities
      degree of a relationship - number of entities in the relationship (most relationships are binary relationships
      THREE TYPE OF BINARY RELATIONSHIPS:
      • 1:1 - STUDENT - HIGH-SCHOOL-GRADUATED-FROM
      • 1:N (1 to many) - DEPARTMENT - FACULTY
      • N:M (many to many) - BOOK - AUTHOR

      maximum cardinality - maximum number of elements that occur on one side of a relationship
      These are examples of HAS-A relationships
      ENTITY-RELATIONSHIP DIAGRAMS
      Entity-Relationship Diagram
      minimum cardinality:
      • Use oval (0) to indicate that an entity is not required
      • Use hash (|) to indicate that an entity must exist
      • Use crow's feet (/) to indicate that an entity may have many instances

      recursive relationships - relationship among entities of a single class
      SHOWING ATTRIBUTES IN ENTITY-RELATIONSHIP DIAGRAMS
      can be show in ellipses hanging from the ENTITY or RELATIONSHIP or listed below the ENTITY or RELATIONSHIP
    • Weak Entities
      weak entity requires the existence of another type of entity (i.e. DEPENDENT-EMPLOYEE) and is designated by rounding the corners of the weak entity and its relationship [logical dependence]
      ID-dependent entity identifier of one entity includes the identifier of another entity (i.e. BUILDING-APARTMENT, PRODUCT-VERSION, TEXTBOOK-EDITION) [physical dependence]
      ambiguity: Is STUDENT a weak entity when considered with an ADVISOR? Is ORDER a weak entity when considered with SALESPERSON? Is PRESCRIPTION a weak entity with respect to PATIENT? Is ASSIGNMENT a weak entity wrt PROJECT?
    • Subtype Entities
      subtype - entity that contains optional set of attributes and is part of a supertype [IS-A relationship]
      subtype entities
      CLIENT Contains
      ClientNumber
      ClientName
      AmountDue       		INDIVIDUAL-CLIENT Contains
      Address
      SocialSecurityNumber
      PARTNERSHIP-CLIENT Contains
      ManagingPartnerName
      Address
      TaxIDNbr       		CORPORATE-CLIENT Contains
      ContactPerson
      PhoneNbr
      TaxIDNbr

      Note that subtypes can inherit attributes from their supertype
    • Documentation of Business Rules (constraints are not part of E-R Diagram, but need to be documented)
    • the Entity-Relationship Model and CASE Tools: EasyCase, Accelerator, etc.
  2. Examples
    1. EXAMPLE 1: THE JEFFERSON DANCE CLUB
      wants an IS to keep track of students and the classes they have taken; how many and which type of lessons have been taught by each teacher; be able to compute average cost per lesson for each instructor
      • Entities (NOUNS)
        1. LESSON (Private and Group)
        2. TEACHER (Full-time and Parttime)
        3. DANCE
        4. CUSTOMER (STUDENT)
      • Relationships
        1. IS-A Relationship between TEACHER and FULL-TIME-TEACHER and PART-TIME-TEACHER
        2. HAS-A Relationships between TEACHER and PRIVATE-LESSON(2:N) and GROUP-LESSON(1:N)
        3. HAS-A Relationships between CUSTOMER and PRIVATE-LESSON(2:N) and GROUP-LESSON(N:M)
        4. HAS-A Relationship between TEACHER and DANCE((N:M)
      • Final E-R Diagram
      • Evaluating the E-R Data Model [Best to make modifications now)
        • Who has taught which private lessons?
        • Which customers have taken a private lesson from Jack?
        • Who are the full-time teachers?
        • Which teachers are scheduled to attend the dance on Friday?
    2. EXAMPLE 2: SAN JUAN SAILBOAT CHARTERS
      wants an IS that keeps records of its customers and charters; keep track of equipment on the boats; keep track of personnel hired for charters; keep track of maintenance on the boats
      • ENTITIES
        1. LEASE(CHARTER)
        2. BOAT
        3. CUSTOMER
        4. OWNER
        5. EQUIPMENT
        6. ITINERARY/WEATHER
        7. PART-TIME-CREW
        8. SCHEDULED-MAINENANCE
        9. REPAIR (UNSCHEDULED-MAINTENANCE)
        10. REPAIR-FACILITY
      • RELATIONSHIPS
        1. OWNER - BOAT(N:M)
        2. OWNER - EQUIPMENT (N:M)
        3. BOAT - SCHEDULED-MAINTENANCE(1:N)
        4. BOAT - LEASE (1:N)
        5. LEASE - EQUIPMENT (N:M)
        6. LEASE - REPAIR (1:N)
        7. LEASE - PART-TIME-CREW (N:M)
        8. CUSTOMER - LEASE (1:N)
        9. LEASE - WEATHER-ITINERARY (1:1)
  3. Databases as Models of Models
    "Does this design accurately represent the users' model of his/her environment?"

To Previous Chapter To Table of Contents To top of page To Next Chapter