ER Diagrams

Designing Before Building

Before you create a single table, you need to design your database. ER diagrams (Entity-Relationship diagrams) are the standard way to visualize and plan database schemas. They help you identify entities, their attributes, and how they relate to each other.

Entities and Attributes

• Entity — A thing or concept you want to store data about. Represented as a rectangle. Examples: Student, Course, Order.
• Attribute — A property of an entity. Represented as an oval. Examples: Name, Email, Price.
• Key Attribute — An attribute that uniquely identifies an entity. Underlined in the diagram.

ER Diagram Notation

  Chen Notation (classic):

  ┌──────────┐         ┌──────────┐
  │ Student  │         │ Course   │
  └────┬─────┘         └────┬─────┘
       │                     │
  ┌────┴────┐           ┌────┴────┐
  │ StudentID│           │ CourseID│
  │ Name    │           │ Title   │
  │ Email   │           │ Credits │
  └─────────┘           └─────────┘

  Relationship: ◇ Enrolls ◇
  ──○ M ──◇── 1 ──○──
  Student       Enrolls     Course
  (Many)                   (Many)

Cardinality

Cardinality defines how many instances of one entity can relate to instances of another:

  One-to-One (1:1):       One-to-Many (1:N):     Many-to-Many (M:N):
  ┌───┐   ┌───┐          ┌───┐   ┌───┐          ┌───┐   ┌───┐
  │ A │───│ B │          │ A │───│ B │          │ A │───│ B │
  └───┘   └───┘          └───┘ 1│  └───┘         └───┘ M│  └───┘
  1:1                      1│ N                    M│ N
                           (depts→emps)         (students↔courses)

Example ER Diagram: University

  ┌────────┐         ┌────────────┐         ┌────────┐
  │Student │──── M ──┤  Enrolls   ├── N ────│Course  │
  │--------│         └────────────┘         │--------│
  │StdID(PK)│         ┌────────────┐         │CrsID(PK)│
  │Name    │──── 1 ──┤ Belongs To ├── N ────│Title   │
  │Email   │         └────────────┘         │Credits │
  └────────┘                               └────────┘
       │                                        │
       │ N                                      │ 1
       ▼                                        ▼
  ┌────────────┐                         ┌────────────┐
  │ Department │                         │ Instructor │
  │------------│                         │------------│
  │DeptID (PK) │                         │InsID (PK)  │
  │DeptName    │                         │Name        │
  └────────────┘                         │DeptID (FK) │
                                         └────────────┘

From ER Diagram to Tables

Once you have your ER diagram, converting it to tables is straightforward:

• Each entity becomes a table.
• Each attribute becomes a column.
• 1:N relationships: put the primary key of the "1" side as a foreign key in the "N" side.
• M:N relationships: create a junction table with foreign keys from both sides.