Data Modeling Using EntityRelationship Diagrams ERDs

1
Lecture-5 (09/09/2005)

• Data Modeling Using Entity-Relationship Diagrams
  (ERDs)

2
COMPANY ER Schema Diagram

• Is there a better way to model the
• fact that only some employees are
• managers?

• What if we need to record the salary
• for full-time employees and hourly
• rate for part time ones?

• Only Record dependents of full timers

3
Problems with ER notation

• What if we have different types of entities
  within the same entity type each with a number of
  
• Distinct features?
• Distinct relationships?
• Different DB view? (External, Conceptual and
  Internal Schema design)
• University Database
• Faculty Employees (Rank, Tenure, Department,
  Classes, etc )
• Administration Employees (Office Registrar,
  Admissions)
• Other Employees (Janitors, Lawn Mowers etc )
• Extended Entity-Relationship (EER or E2R) Model

4
Enhanced-ER (EER) Model Concepts

• Includes all modeling concepts of basic ER
• Additional concepts
• Subclasses/superclasses
• Specialization/generalization
• Attribute/relationship inheritance
• Object-oriented concepts
• Others
• More complete and accurate modeling

5
Subclasses
Superclass/Subclasses
Superclass/Subclasses or IS-A relationships OOP
1- Subclass ? Superclass ? 2- Superclass ?
Subclasses? 3- Subclass-A ? Subclass-B ??
Attributes and relationships inheritance
6
Instances of a specialization
7
Subclasses and Superclasses

• Additional meaningful sub-groupings of an entity
  types entities
• EMPLOYEE
• SECRETARY, ENGINEER, MANAGER, TECHNICIAN,
  SALARIED_EMPLOYEE, HOURLY_EMPLOYEE,
• Subclasses of EMPLOYEE
• EMPLOYEE is the Superclass
• Result in superclass/subclass or IS-A
  relationships
• EMPLOYEE/SECRETARY SECRETARY IS-A EMPLOYEE
• EMPLOYEE/TECHNICIAN TECHNICIAN IS-A EMPLOYEE

8
Subclasses and Superclasses

• Member of a subclass member of the superclass
• The subclass member is the same entity in a
  distinct specific role
• Can belong to more than 1 subclass
• A salaried employee who is also an engineer
  belongs to the two subclasses ENGINEER and
  SALARIED_EMPLOYEE
• Superclass entities may not belong to any
  subclass
• Entity of a subclass inherits all attributes and
  relationships of the superclass

9
Specialization

• Is the process of defining a set of subclasses of
  a superclass
• Based upon some distinguishing characteristics of
  the entities in the superclass
• SECRETARY, ENGINEER, TECHNICIAN is a
  specialization of EMPLOYEE based upon job type
• Attributes of a subclass are called specific
  attributes
• TypingSpeed of SECRETARY

10
Several Specializations - JobType- MethodOfPay
- ManagementResponsibilities
Completeness Constraint
Disjointness Constraint (d/o) (disjoin/overlap
next slide)
Specific Attributes --- without subclasses!
d
No need to be inclusive! (non-managers)
Specific Relationships
11

• BACK TO DISJOINTNESS
• Student
• TA
• RA
• GA

O
d
12
Specialization

• We may have several specializations of the same
  superclass
• Based on method of pay is SALARIED_EMPLOYEE,
  HOURLY_EMPLOYEE
• The subclass can participate in specific
  relationship types
• For example, BELONGS_TO of HOURLY_EMPLOYEE

13
Constraints for Generalization and Specialization

• Disjointness Constraint
• Specifies that the subclasses of the
  specialization must be disjointed (an entity can
  be a member of at most one of the subclasses of
  the specialization)
• Specified by d in EER diagram
• If not disjointed, overlap that is the same
  entity may be a member of more than one subclass
  of the specialization
• Specified by o in EER diagram
• Completeness Constraint
• Total specifies that every entity in the
  superclass must be a member of some subclass in
  the specialization/ generalization
• Shown in EER diagrams by a double line
• Partial allows an entity not to belong to any of
  the subclasses
• Shown in EER diagrams by a single line

14
Generalization

• The reverse of the specialization process
• Several classes with common features are
  generalized into a superclass
• Original classes become its subclasses
• CAR, TRUCK generalized into VEHICLE
• Both CAR, TRUCK become subclasses of the
  superclass VEHICLE
• We can view CAR, TRUCK as a specialization of
  VEHICLE
• Alternatively, we can view VEHICLE as
  generalization of CAR and TRUCK

15
Generalization
d
16

• Exercise 4.21 in book p. 119

17
(No Transcript)