Entity-Relationship Model

Abstract

We’re trying to model real-world data subject to real-world constraints. The entity relationship model is a simple method to do so. Design goals:

• Redundancy is bad
• Attributes cannot be lists!
• Trade-off between capturing more constrainsts vs. simplicity
  • → Common Sense > capturing all constraints
• Don’t introduce nonexistent constraints

Components of an E/R Model

1. Entity—with attributes
   1. Key attributes are underlined
   2. ⇒ This is NOT like database keys, in the case that weak entities require both its keys and its supporting entity’s keys to identify it uniquely.
2. Relationship—with attributes
   1. Relationship attributes are not the same as entity attributes: they cannot be duplicated.
   2. e.g. StopsAt cannot have multiple times for each Train-Station pair:

Complex Relationships §

(DevonThink) 3. E/R Design

Multiplicity §

• Many-to-Many: Most types of relations
• Many-to-One
  • half-circle arrow: exactly one
  • Normal arrow: one or zero
• One-to-One

Roles in a Relationship §

• if you’re taking two items from the same entity, the roles need to be clarified.

N-ary Relationships §

• one-to-one (=binary) relationships is not the norm.
• you can break it up into many relationships…
  • …but it’s hard and may require new relations, entities, etc.
• e.g. a isMemberOf relationship can have multiple members, but only (zero or) one initiator for a group—member pair

Weak Entity & Supporting Relationships (=Heirarchical relationships) §

• more data is needed to uniquely identify the thing
• e.g. a room number in a building needs the building identification to uniquely identify it

ISA Relationship (Subclassing) §

• literally “A is-a B” relationship
• Inherits the attributes (with the key), relationships

Translating into Relational Model §

(DevonThink) 4. E/R Translation

1. Entity Set → Table. Train,LocalTrain, etc.
   • Attribute → Column of Table. number, engineer
   • Key attribute → Key number
2. Relationship → Table of keys (on both entities) and attributes if necessary.
3. Weak Entity Set → Table, but including all the keys up the hierarchy
   • ExpressTrainStopsAtStation includes keys of ExpressTrains
   • You need the keys of all supporting entities to uniquely identify it
4. IsA Relationship → Table, but we can choose how to translate it:
   1. Entity-in-all-superclass-and-specific-class
      • Scattered List
   2. Entity-only-in-specific-class
      • Scattered List
   3. All-entities-in-one-table
      • Google BigTable’s approach
      • NULL when a subclass’s attribute doesn’t apply
      • Results in a sparse table (we have good ways to store this these days!)

Full example