Entity-Relationship Diagrams

It is common to show design decisions with diagrams. For relational databases, the standard diagramming technique is called an entity-relationship diagram, or ERD for short. You will see ERDs in textbooks, in database tools, and at work. You are not expected to draw one by hand in this course. You are expected to read one and recognize the schema underneath it.

Each table is a box, listing its columns, with the primary key marked. A line connects two boxes for each relationship between them. The marks at each end show how many rows on one side can be related to a row on the other.

Here is the one-to-many relationship between subject areas and courses:

erDiagram
    subject_areas ||--o{ courses : has
    subject_areas {
        int subject_area_id PK
        string name
    }
    courses {
        string course_number PK
        string title
        int credits
        int subject_area_id FK
    }

Here, each course belongs to exactly one subject area, shown by the two bars next to subject_areas. Each subject area can have zero or more courses, shown by the circle and crow’s foot next to courses.

You draw a many-to-many relationship as two one-to-many lines meeting at the junction table in the middle. Here is courses and requirements:

erDiagram
    courses ||--o{ course_requirements : ""
    requirements ||--o{ course_requirements : ""
    courses {
        string course_number PK
        string title
        int credits
        int subject_area_id FK
    }
    course_requirements {
        string course_number PK, FK
        int requirement_id PK, FK
    }
    requirements {
        int requirement_id PK
        string name
    }

The diagram is not adding a new fact here. It is drawing the same fact the schema already states, in a shape some people find faster to scan.