Phawat
Back to Blog

Learned on 25 Feb 2026

ER Basics

Foundational notes on ER diagram notation, participation/cardinality combinations, psql basics, and ER-to-SQL mapping strategies.

DatabasesSQL

ER Basics

ER design elements

  • Square: entity
  • Diamond: relationship
  • Oval: attribute
  • Circle / ISA: inheritance

Special ER notation

  • Double-lined square: weak entity
  • Double-lined diamond: identifying relationship
  • Double-lined oval: multi-valued attribute
  • Dotted-lined oval: derived attribute

Participation

  • Thick line: total participation
  • Thin line: partial participation

Subclasses

Sub-classes can be:

  • Disjoint: only one
  • Overlapping: zero or more

Cardinality / participation combinations

  • D + thin -> 0 or 1
  • D + thick -> 1 only
  • O + thin -> 0 or more
  • O + thick -> 1 or more

Weak entity

  • A weak entity has no key of its own.

psql basics

  • \d: describe
  • \q: quit

Insert / file input

  • INSERT: add new tuples into tables
  • -f: read input from file rather than stdin

Prompts

  • =#: normal prompt
  • -#: continuation prompt (continue from previous command)

DDL

  • DDL: data definition language

ER to SQL mapping

  • Entity -> table
  • Attribute -> column
  • Key attribute -> primary key
  • Domain constraint -> check(...)
  • Total participation -> cannot map directly

Relationship mapping

  • 1:N becomes a foreign key in the many-side table
  • N:M becomes its own table with two foreign keys
  • Relationship attributes become columns in the mapped table

Junction table notes (for N:M)

  • Primary key is composite
  • Two foreign keys

Mapping hierarchies to SQL

ER style

One table per entity (superclass + each subclass).

Object-oriented style

One table per subclass (duplicating superclass attributes).

Single-table style

One table for the whole hierarchy (with NULLs).

When to use which

  • Subclasses are disjoint, queried independently -> object-oriented style
  • Subclasses overlap, superclass queried often -> ER style
  • Few subclass-specific attributes, simple queries -> single table
  • Subclasses have many unique attributes -> ER style or object-oriented style

To update later

  • Add worked examples converting ER diagrams to SQL schemas
  • Add examples of weak entities and identifying relationships
  • Add trade-offs for each hierarchy-mapping style