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. Each end of the line carries a small mark: a single bar means “exactly one,” and a crow’s foot means “zero or more.”

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
    }

The bar sits next to subject_areas, and it means each course has exactly one subject area. The crow’s foot sits next to courses, and it means each subject area has zero or more 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.