Data Modeling Using EntityRelationship Diagrams ERDs

1
Lecture-5

• Data Modeling Using Entity-Relationship Diagrams
  (ERDs)

2
N-ary relationship types

• We have suppliers (entity type) supplying
  specific quantities of parts (entity type) for
  projects (entity type)

3
(No Transcript)
4
ER Modeling Tools

• Believe it or not MS Word will do a good job
• gtgtClick on the Insert Tab
• gtgtOn the Illustrations sections, Click on Shapes
• gtgt Between Basic Shapes and FlowCharts,
  youve got everything you need
• MS Visio also works fine even though its diagrams
  look different than what we saw in class
• HOMEWORK NEXT TUESDAySCHEDULE CHANGE

5
COMPANY ER Schema Diagram

• 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

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

6
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?
• University Database
• Faculty Employees (Rank, Tenure, Department,
  Classes, etc )
• Administration Employees (Office Registrar,
  Admissions)
• Other Employees (Janitors, Lawn Care, etc )
• Extended Entity-Relationship (EER or E2R) Model

7
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

8
Subclasses
1- Subclass ? Superclass ? 2- Superclass ?
Subclasses? 3- Subclass_A ? Subclass_B ??
Superclass/Subclasses or IS-A relationships OOP
Attributes and relationships inheritance
9
Instances of a specialization
10
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
• An entity of a subclass inherits all attributes
  and relationships of the superclass

11
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
• Can have multiple sets of subclasses
• Attributes of a subclass are called specific
  attributes
• TypingSpeed of SECRETARY

12
Several Specializations - JobType- MethodOfPay
- ManagementResponsibilities
Disjointness Constraint (d/o) (disjoint/overlappin
g next slide)
Completeness Constraint
d
No need to be inclusive! (non-managers)
Specific Attributes
Specific Relationships
13
O
d
14
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

15
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

16
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

17
Generalization
d
18
(No Transcript)
19
d
20
(No Transcript)
21

• Hierarchy vs. lattice
• Exercise 4.20 in book p. 135