Transcript What is a Database Management System?

Enhanced Entity-Relationship and
Object Modeling
Chapter 4
Database Management
COP4540, SCS, FIU
Supertypes and Subtypes
• Subtype: A subgrouping of the entities in an
entity type that has distinct attributes
• Supertype: An entity type whose subtypes
share common attributes
Database Management
COP4540, SCS, FIU
Employee supertype with three subtypes
Address
Name
SSN
HOURLY
EMPLOYEE
Hourly_rate
EMPLOYEE
Date_hired
SALARED
EMPLOYEE
Annual_salary
CONSULTANT
Stock_option
Contract_No.
Billing_rate
Database Management
COP4540, SCS, FIU
Attribute Inheritance
• Subtype entities inherit values of all
attributes of the supertype
• An occurrence of a subtype is also an
occurrence of the supertype
• All common attributes of subtypes appear in
the supertype
Database Management
COP4540, SCS, FIU
Use of Supertype/Subtype
• There are attributes that apply to some (but
not all) of the instances of an entity.
• The instances of a subtype participate in a
relationship unique to that subtype.
Database Management
COP4540, SCS, FIU
Unique relationship for a subtype
Admit_date
ID
PATIENT
Physician_ID
N
1
RESPONSIBLE
PHYSICIAN
Is_cared_for
Bed_ID
OUTPATIENT
Checkback_date
RESIDENT
PATIENT
1
Is_assigned
1
BED
Date_discharged
Database Management
COP4540, SCS, FIU
Generalization and Specialization
• Generalization: The process of defining a
more general entity from a set of more
specialized entities.
• Specialization: The process of defining one
or more subtypes of the supertype.
• Top-down conceptual refinement process.
• Bottom-up conceptual synthesis.
Database Management
COP4540, SCS, FIU
Example of generalization
(a) Three entity types: CAR, TRUCK, and MOTORCYCLE
Make
VIN
Model
No_of_Passengers
Name
Price
Year
Engine
Displacement
CAR
Make
VIN
Price
Make
Name
Price
Model
Name
VIN
TRUCK
Year
MOTORCYCLE
Model
Capacity
Year
Engine
Displacement
Cab_type
Engine
Displacement
Database Management
COP4540, SCS, FIU
(b) Generalization to VEHICLE supertype
Make
VIN
Price
No_of_Passengers
Model
Year
Name
Engine
Displacement
VEHICLE
CAR
TRUCK
Capacity
Cab_type
Database Management
COP4540, SCS, FIU
Example of specialization
(a) Entity type PART
Unit_price
Description
Part_No
Qty_on_hand
PART
Supplier_ID
Routing_number
Location
Database Management
COP4540, SCS, FIU
(b)
Specialization to MANUFACTURED PART
and PURCHASED PART
Description
Part_No
PART
Supplier_ID
Location
SUPPLIER
Qty_on_hand
Routing_number
Unit_Price
N
MANUFACTURED
PART
PURCHASED
PART
M
Supplies
Database Management
COP4540, SCS, FIU
Constraints in Supertype/Subtype
Relationships (1)
• Completeness Constraints: Whether an
instance of a supertype must also be a
member of at least one subtype.
– Total Specialization Rule: the double line
convention
– Partial Specialization Rule: default (single line)
Database Management
COP4540, SCS, FIU
Example of Completeness constraints
Admit_date
ID
PATIENT
Physician_ID
N
1
RESPONSIBLE
PHYSICIAN
Is_cared_for
Bed_ID
OUTPATIENT
Checkback_date
RESIDENT
PATIENT
1
Is_assigned
1
BED
Date_discharged
Database Management
COP4540, SCS, FIU
Constraints in Supertype/Subtype
Relationships (2)
• Disjointness Constraints: Whether an
instance of a supertype may simultaneously
be a member of two (or more) subtypes
– Disjoint Rule: the symbol “d”
– Overlap Rule: the symbol “o”
Database Management
COP4540, SCS, FIU
Example of disjointness constraints
(a) Disjoint rule
Admit_date
ID
PATIENT
Physician_ID
N
1
RESPONSIBLE
PHYSICIAN
Is_cared_for
d
OUTPATIENT
Checkback_date
Bed_ID
RESIDENT
PATIENT
Date_discharged
1
Is_assigned
1
BED
Database Management
COP4540, SCS, FIU
(b) Overlap rule
Description
Part_No
Supplier_ID
Location
PART
SUPPLIER
Qty_on_hand
o
Routing_number
Unit_Price
N
MANUFACTURED
PART
PURCHASED
PART
M
Supplies
Database Management
COP4540, SCS, FIU
Constraints in Supertype/Subtype
Relationships
• Subtype Discriminators: An attribute of the
supertype whose values determine the target
subtypes(s).
– Disjoint (one subtype for each instance)
– Overlapping (multiple subtypes possible)
• Such subtype is said to be predicatedefined.
Database Management
COP4540, SCS, FIU
Example of Subtype Discriminator (1)
Address
Name
SSN
EMPLOYEE
Date_hired
Type
d
HOURLY
EMPLOYEE
Hourly_rate
SALARED
EMPLOYEE
Annual_salary
CONSULTANT
Stock_option
Contract_No.
Billing_rate
Database Management
COP4540, SCS, FIU
Example of Subtype Discriminator (2)
M?
Description
Location
Supplier_ID
P?
Part_type
Part_No
PART
SUPPLIER
Qty_on_hand
o
Routing_number
Unit_Price
N
MANUFACTURED
PART
PURCHASED
PART
M
Supplies
Database Management
COP4540, SCS, FIU
Modeling of UNION Types Using
Category
• A category has two or more supertypes.
• Attribute inheritance works more
selectively in the case of categories.
• If a category is total (not partial), it may be
represented alternatively as a specialization.
Database Management
COP4540, SCS, FIU
Example of Category (1)
PERSON
COMPANY
U

ACCOUNT
HOLDER
M
N
Has_Acct
BANK
Database Management
COP4540, SCS, FIU
Example of Category (2)
BUILDING
LOT
PROPERTY
d
U

PROPERTY
BUILDING
LOT
Database Management
COP4540, SCS, FIU
An EER Conceptual schema for a
university database
Figure 4.10
Database Management
COP4540, SCS, FIU