Roles

1
Roles

• Foreign Keys often play a more specific task in
  an entity than the entity where they are the
  primary key.
• Consider an employee that acts as a manager for a
  department.

2
Department Model
Department
Employee
Depart Nbr(PK) Dept Name Dept Mgr(FK)
Emp Nbr (PK) Emp Name Hire Date
Managed By
3
Department Model Explanation

• The Department Manager is an employee
• The attribute Dept Mgr is the employee number of
  the person who is the manager

4
Role Name

• Role Name A new name for a foreign key
  attribute or group of foreign key attributes,
  which defines the role that it plays in the child
  entity.

5
Other Uses for Roles

• Often you will be forced into using role names,
  since attribute names must be unique in an entity
• This is best exemplified by two entities being
  related by 2 or more relationships

6
Role Relationships

• A Marriage Record is recorded for 1 Husband.
• A Marriage Record is recorded for 1 Wife.

7
Marriage Record
Marriage Record
Person
Records Husband
Mar Nbr(PK) Mar Date Husband(FK) Wife(FK)
Person Nbr (PK) Person Name Birth Date
Records Wife
8
Generalization

• Generalization is hierarchical grouping of
  entities that share common characteristics
• The generalized object descriptions apply to all
  uses. The lower level entities inherit the upper
  level properties
• The entity has some property that uniquely
  differentiate it from the other entities in the
  hierarchy
• Often called a Generalization Hierarchy

9
Generalization Hierarchy

• Generalization Hierarchy -- A hierarchical
  grouping of entities that share common common
  characteristics
• Generalization Entity (Generic Parent) The
  entity at the top of any level of a
  generalization hierarchy
• Category Entity A subset of instances of an
  entity (generalization entity) that share common
  attributes or relationships distinct from other
  subsets.

10
Account Hierarchy
Generalization Entity
Generalization Entity
Account
Savings Account
Loan Account
Checking Account
11
Employee Hierarchy
12
Category Discriminators

• In Generalization Hierarchies there is an
  attribute that determines what kind of entity is
  being described. This is a Category
  Discriminator.
• In our Account example the Category Discriminator
  would identify whether it as a Checking Account,
  Savings Account, or Loan Account.

13
Category Discriminator

• An attribute that determines which category a
  generic parent instance belongs.

14
Accounts
Account
Account Nbr
Account Type
Account Type
Checking Account
Savings Account
Loan Account
Account Nbr (FK)
Account Nbr (FK)
Account Nbr (FK)
Checking Open Date Checking Review Date Checking
Balance Available Balance
Savings Open Date Savings Review Date Savings
Balance Interest Rate
Loan Open Date Loan Review Date Original Loan
Amount Interest Rate Loan Balance
15
Recursive Relationships

• An entity can participate in a relationship in
  which it is both the parent and the child.
• Such a relationship is called a recursive
  relationship.
• Recursive relationships must be nonidentifying.

16
Recursive Relationship

• Recursive Relationship A nonidentifying
  relationship in which the same entity is both the
  parent and the child.
• Note we are talking about entity and not entity
  instance.

17
Recursive Relationships
18
IDEF1X Recursion
Company
Company ID(pk)
Company Name Owner ID (fk)
19
Recursion Notes

• Note it uses a nonidentifying relation
• Note the use of Role Name