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 Category Entity Generalization Entity Account Savings Account Checking Account Loan Account

11. Person Employee Student Administrator Graduate Undergraduate Professor 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 Loan Account Checking Account Savings Account Account Nbr (FK) Account Nbr (FK) Account Nbr (FK) Checking Open Date Checking Review Date Checking Balance Available Balance Loan Open Date Loan Review Date Original Loan Amount Interest Rate Loan Balance Savings Open Date Savings Review Date Savings Balance Interest Rate

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. Big Company Small Monster Company Other Small Company Small Subsidiary Big Subsidiary 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