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
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]
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.
Examples
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)
LESSON (Private and Group)
TEACHER (Full-time and Parttime)
DANCE
CUSTOMER (STUDENT)
Relationships
IS-A Relationship between TEACHER and FULL-TIME-TEACHER and PART-TIME-TEACHER
HAS-A Relationships between TEACHER and PRIVATE-LESSON(2:N) and GROUP-LESSON(1:N)
HAS-A Relationships between CUSTOMER and PRIVATE-LESSON(2:N) and GROUP-LESSON(N:M)
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?
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
LEASE(CHARTER)
BOAT
CUSTOMER
OWNER
EQUIPMENT
ITINERARY/WEATHER
PART-TIME-CREW
SCHEDULED-MAINENANCE
REPAIR (UNSCHEDULED-MAINTENANCE)
REPAIR-FACILITY
RELATIONSHIPS
OWNER - BOAT(N:M)
OWNER - EQUIPMENT (N:M)
BOAT - SCHEDULED-MAINTENANCE(1:N)
BOAT - LEASE (1:N)
LEASE - EQUIPMENT (N:M)
LEASE - REPAIR (1:N)
LEASE - PART-TIME-CREW (N:M)
CUSTOMER - LEASE (1:N)
LEASE - WEATHER-ITINERARY (1:1)
Databases as Models of Models
"Does this design accurately represent the users' model of his/her environment?"