Session Five Many to Many,  Hierarchies,  Recursion,  Complex relationships and the Midterm

Topics

• Many to Many
• Hierarchies
• Recursion
• Supertypes and Subtypes
• Exclusive Relationships
• Data Over Time
• DeZign Practice
• Movie - Adding an Entity and Attributes
• Movie - Adding a relationship
• Worksheet 4
• Project 3 - Many to Many

Many to Many

Power Point - Many to Many

Attributes may seem to be associated with a M:M Relationship.

Resolve that M:M relationship by adding an intersection entity with those attributes.

Attributes only describe entities. If attributes describe a relationship, the relationship must be resolved.

Replace a Many to Many  (M:M) Relationship with:

a new Intersection Entity

and two M:1 relationships.

 

A Reference Entity is an entity that has no mandatory relationship ends connected to it.

Guidelines:

An Intersection Entity is frequently identified by its two originating relationships

The relationships from the intersection entity are always mandatory.

Intersection entities frequently represent real-world business entities.

Intersection entities usually contain consumables like quantity used and dates.

Intersection Entities tend to be high volume and volatile entities.

The UID of an intersection entity is frequently composed of its relationships to the two originating entities.

An intersection entity's relationships to the two originating entities may not be adequate to uniquely define each occurrence of the intersection entity.

Once an intersection entity is identified, search for additional attributes which describe, identify, or help to identify the intersection entity.

Resolve all M:M relationships by the end of the Analysis phase. This forced resolution may result in an Intersection Entity with no attributes.

An Intersection Entity with no attributes -

It is just a two-way cross-reference list between occurrences of the entities.

It  is the exception to the rule that an entity must have attributes to be an entity.

The UID for an empty Intersection Entity is always composed of the relationships of the two entities from which it originated.

Back to the Top

Power Point - Hierarchies and Recursion

Represent hierarchical data as a set of many to one relationships.

The E-R Diagram layout rule "Crows fly east or south" causes hierarchies to be drawn upside-down or sideways!

The UID's for a set of hierarchical entities may be propagated through multiple relationships.

Consider creating artificial attributes (unique, independent, artificial identification codes) to help identify entities in a hierarchical relationship.

• These codes tend to be shorter in length.
• If structure changes often, use independent artificial identifiers.

Back to the Top

• A Recursive Relationship is a relationship between an entity and itself.
• The E-R diagramming convention that shows a recursive relationship is known as a pig's ear.
• A hierarchy can be modeled as a recursive relationship.
• A recursive relationship MUST be optional in both directions. Why?
• Resolve a recursive M:M relationship with an intersection entity and two M:1 relationships to different instances of the original entity.
• Use relationships to model roles. Relationships allow a single entity instance to assume multiple roles.

Back to the Top

Power Point - Subtypes, Super types and Arcs

Use subtypes to model exclusive entity types which have common attributes and common relationships.

 

Supertype an entity that has subtypes.  may be split into two or more mutually exclusive subtypes. may have attributes and relationships shared by its subtypes.

Subtype may not overlap. each subtype may have its own attributes and relationships. may be further subtyped.

A Simple Entity is one which does not have any super or sub types.

Back to the Top

Model two or more mutually exclusive relationships from the same entity using an arc.

• The relationships in an arc frequently have the same relationship name.
• The relationships in an arc must be either all mandatory, or all optional.
• An arc belongs to a single entity, and must only include relationships originating from that entity.
• An entity may have multiple arcs, but a specific relationship can only participate in a single arc.

Back to the Top

• Add additional entities and relationships to the E-R model to accommodate historical data.
• Create an additional entity to track an attribute's values over time.
• Add a new entity to accommodate a relationship that may change over time.

Back to the Top