Special Relationships in ER Diagrams

Remember where we left off:

Reasonable assumptions: Every CEO runs a Studio, if a studio ceases to exist, then the CEO ceases to be CEO. If a CEO is fired or resigns, then the Studio still exists.

GOAL – How do we model special relationships in ER Diagrams?

Weak Entity Sets

Entity sets are weak when their key attributes come from other classes to which they are related. This happens in “part-of” relationships.

Weak entity sets:

  1. must have a many-to-one relationship
  2. referential integrity on the relationship
  3. supporting entity set supplies the key to weak entity set
  4. double box around weak entity set, double diamond around supporting relationship
  5. weak entity set takes the key from the supporting relationship (weak entity set usually needs a key, but not always – sometimes in a connecting weak entity set where the key is made from lots of connecting entity sets, e.g., Contract connects Actor, Studio, Movie so contract doesn’t need a key)

Think of a another example of a weak entity set, write it down in ER format

Hierarchies

Entity sets can have inheritance and form hierarchies when their key attributes come from super-entity sets. This happens in “is a” hierarchies.

Two types: ER hierarchies Object Orientated hierarchies

In the object-oriented world, objects are in one class only.

Subclasses inherit properties from superclasses. Assume subclasses form a tree. Is-a triangles indicate the subclass relationship and point to the superclass.

In contrast, E/R hierarchies have components in all subclasses to which they belong. This matters when we convert to relations.

For example, “LeapFrog” exists in both Product and EdProduct

Draw the Notre Dame Computer Science Department in ER.

Design Principles

Be Faithful

Design should reflect reality.

Avoid Redundancy

Redundancy wastes space and (more importantly) encourages inconsistency. The two instances of the same fact may become inconsistent if we change one and forget to change the other, related version.

Example:

Don’t list company twice.

Would lose company address if the company didn’t make some product for some time (deletion anomaly like from the first day)

Entity Sets vs Attributes

An entity set should satisfy at least one of the following conditions:

  • It is more than the name of something; it has at least one non-key attribute.   

OR

  • It is the “many” in a many-one or many-many relationship

Don’t overuse Weak Entity Sets

Beginning database designers often doubt that anything could be a key by itself.

  • They make all entity sets weak, supported by all other entity sets to which they are linked.

Eg – SSN is the key for everything and everything is a weak entity set Sometimes it’s good to create keys (CRN, eid, etc)

When should we use weak entity sets?

The usual reason is that there is no global authority capable of creating unique ID’s. Example: it is unlikely that there could be an agreement to assign unique player numbers across all football teams in the world.

Summary

Basic stuff

  • entity, attribute, entity set
  • relation: binary, multiway, converting from multiway
  • relationship roles, attributes on relationships
  • subclasses (is-a)

Constraints

  • on relations
    • many-one, one-one, many-many
    • limitations of arrows
  • keys, single-valued, ref integrity, domain & general constraints

Weak entity set
Design principles

ER Diagrams are important. Do this right and you’ll save yourself a lot of pain.

Updates to the schema: relatively rare. Rather painful. Why?