Associative Entities
Download
Report
Transcript Associative Entities
Associative Entities
A relationship cannot have attributes
What happens when one or more
attributes exist for a relationship?
Employee – Project – Billing Rate
Associative Entities
An associative entity is an entity type that
associates the instances of more or more
entity types and contains attributes that are
specific to the relationship between those
entity instances.
Converting a relationship to an
associative entity
Example
Employee’s billing rate can vary by project
(employee to project, many-to-many)
Employee
Billing
Rate
Project
Example:
Attribute of the relationship between
property and owner is percent owned
Owner
%
Owned
Property
Ternary Relationship
simultaneous relationship among one
instance from each of exactly three entity
sets
An associative entity is not required but is
typical.
Ternary Relationships –
Cardinality
“one” – one instance of an entity is
associated with each unique pair of the
other two entities.
“many” – more than one instance of an
entity is associated with each unique pair
from each of the other two entities.
Example
(one-to-one-to-one):
Employee is assigned a phone number
for a project. A phone number is used
only for that employee and project.
Example (one-to-one-to-one):
Employee
Has
Project
Phone
Number
Note: An employee could still be assigned to multiple projects but
would have a unique phone number for each project assignment.
Example
(one-to-one-to-many):
Employee assigned to a project works at
one location for that project but can work at
different locations for different projects. At a
location an employee only works on one
project, but there can be many employees
working on that same project.
Example
(one-to-one-to-many):
Employee
Has
Location
Project
Example
(one-to-many-to-many):
Employee on a project has one manager.
Manager can manage several projects.
Each project has one manager. Manager
can manage the same employee on
different projects.
Example
(one-to-many-to-many):
Employee
Has
Manager
Project
Example
(many-to-many-to-many):
Employees use many skills on many
projects and each project has many
employees with varying skills.
Example
(many-to-many-to-many):
Employee
Has
Skill
Project
Ternary Relationships
Why not just make the ternary relationship an
associative entity?
Employee
Location
Project
Good idea if possible. However,
“Business Rules”
Need for separate (strong) entity
What if location also related to other
entities?
No longer valid as associative entity
If you can safely get rid of ternary (or n-ary) relationships, DO IT.
However, don’t trade correctness for ease of implementation.
Example:
Employee assigned to a project works at
one location and is only assigned to one
project. However, projects can be at
more than one location and have
multiple employees working on them at
each location. Multiple projects can be
at the same location.
Draw the E-R Diagram.
Example:
Now, suppose employees have a billing
rate that varies with both project and
location.
Update your E-R Diagram.
Generalization/
Specialization
Generalization: Defining a general entity
type from a set of more specialized ones
(bottom-up).
Specialization:
Defining one or more
specialized entities from a more general one
based on distinguishing characteristics
(top-down).
Examples:
Generalization: Bank account
generalized from checking, savings, and
loan.
Specialization:
Property for sale divided
into single family, duplex, apartment,
commercial, or industrial.
Why use generalization or
specialization?
clarity
more fully describe situation
conversion to OO (inheritance)
different relationships only apply to specific subtypes
different relationships only apply to the supertype
monthly charges only made to checking accounts
customer must have an account with the bank
different (additional) attributes for the subtypes
Generalization/
Specialization
Applies to entities only
Types:
disjoint – can be only one subtype
overlapping – can be more than one
subtype
Completeness Constraint – subtypes
fully inclusive of supertype
Generalization/
Specialization
Disjoint – student can be undergraduate,
masters, or doctoral
Overlapping – major can be history,
philosophy, or mathematics
Example
A company sells products whose price
can change at most once a day. Need to
be able to track the price history.
Multivalued Attribute
Product ID
Product
Description
Price
Price History
Product Type
Effective Date