Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 79 ยท Data Models and ER Modelling

The Entity-Relationship Model

Learn the ER model: entities, attributes (simple, composite, multivalued, derived), keys, relationships, cardinality and participation, with ER diagrams.

Before you create any tables, you should know what the database has to describe. The entity-relationship (ER) model, introduced by Peter Chen in 1976, is a way to sketch the things a system cares about and how they relate, in a drawing that both designers and non-programmers can read. You design with ER diagrams first and convert them into tables afterwards (Mapping ER Diagrams to Tables).

This lesson covers the core vocabulary: entities, attributes, keys, relationships, cardinality and participation. The next lesson adds extensions such as weak entities and inheritance (Weak Entities, Specialization and Aggregation).

ER diagrams are drawings, so this lesson is read-only. The diagrams use Mermaid's ER notation (crow's foot style), which is a common alternative to Chen's original boxes and diamonds. Both notations carry the same ideas.

Entities and entity sets

An entity is a distinct thing the system tracks: a particular student, a book, a hospital room. An entity set (or entity type) is the collection of similar entities, such as all students. In diagrams you draw the entity set as a box named with a noun: STUDENT, COURSE.

An entity is described by its attributes. An entity must be distinguishable from every other entity in its set; the attributes that make it unique form a key.

Attributes

Kinds of attributes

Simple One atomic value, like age.
Composite Splits into parts, like a full address.
Multivalued Several values, like phone numbers.
Derived Computed from others, like age from birth date.
1 / 2
  • A simple (atomic) attribute holds one indivisible value, such as birth_date.
  • A composite attribute has components: address is street, city, postcode. Choose composite when you sometimes need the whole and sometimes the parts.
  • A multivalued attribute can hold several values for one entity: a person may have many phone numbers. In a relational table this will need its own table.
  • A derived attribute is computed from stored data. age comes from birth_date. You normally do not store it, because it goes stale.
  • An attribute's domain is its set of legal values.

Keys

A superkey is any set of attributes that identifies an entity uniquely. A candidate key is a minimal superkey: remove any attribute and uniqueness is lost. The designer picks one candidate key as the primary key. Keys are covered fully in Keys and Integrity Constraints. In diagrams the key attribute is marked (PK in Mermaid, underlined in Chen's notation).

Good primary keys are stable and never null. A national ID may be unique but can change or be unavailable, so many designers add an artificial surrogate key such as an auto-numbered id.

Relationships

A relationship is an association among entities: a student enrolls in a course. A relationship set is the collection of such associations. Relationships are named with verbs. They can have attributes too: the grade belongs neither to the student nor to the course, but to the enrollment.

The degree of a relationship is the number of entity sets it connects: binary (two, the most common), ternary (three) or higher. A relationship from an entity set to itself is recursive (a unary relationship), for example an employee who manages other employees.

Cardinality constraints

Cardinality says how many entities on one side can relate to one entity on the other.

  • One-to-one (1:1). Each person has at most one passport, and each passport belongs to one person.
  • One-to-many (1:N). A department has many employees, but each employee belongs to one department.
  • Many-to-many (M:N). A student takes many courses, and a course has many students.
erDiagram
    DEPARTMENT ||--o{ EMPLOYEE : employs
    STUDENT }o--o{ COURSE : enrolls
    PERSON ||--o| PASSPORT : holds

Crow's foot: the "many" end has the fork; a bar means exactly one; a circle means optional (zero).

Reading DEPARTMENT ||--o{ EMPLOYEE: one department (||, exactly one) is related to zero or more employees (o{).

Participation constraints

Participation says whether every entity must take part in the relationship.

  • Total participation: every entity of the set must be related. Every employee must belong to a department.
  • Partial participation: some entities may have no relationship. A department might have no employees yet.

In crow's foot notation, the symbol next to the entity box shows the minimum: a bar (|) means at least one (total), a circle (o) means zero is allowed (partial).

A worked example: a small library

Requirements in plain language: the library has books (with a title and ISBN). Each book is written by one or more authors. Members (with a name, several phone numbers, and an address made of street and city) borrow books. A loan records the date borrowed and the due date.

erDiagram
    MEMBER {
        int member_id PK
        string name
        string street "part of address"
        string city "part of address"
    }
    BOOK {
        string isbn PK
        string title
    }
    AUTHOR {
        int author_id PK
        string name
    }
    MEMBER ||--o{ LOAN : makes
    BOOK ||--o{ LOAN : "is borrowed in"
    AUTHOR }o--o{ BOOK : writes
    LOAN {
        date borrowed_on
        date due_on
    }

The borrowing relationship is drawn here as a LOAN box so it can carry attributes.

Decisions visible in the diagram:

  1. address is composite, shown as two stored parts.
  2. Phone numbers are multivalued; they would become a separate MEMBER_PHONE table later. They are left out of the picture to keep it small.
  3. AUTHOR to BOOK is many-to-many (a book may have several authors; an author may write several books).
  4. LOAN connects members and books and carries the dates. The members-borrow-books relationship is itself many-to-many, with attributes.

Trying the ideas in SQL

A diagram does not run, but a few of its ideas can be shown in SQL. This is a stand-in, not the full mapping (that comes in Mapping ER Diagrams to Tables). A derived attribute becomes a computed value in a query or view rather than a stored column:

SQL A derived attribute: age from birth date

Press Run (or Ctrl + Enter) to see the result here.

If you stored age as a column it would be wrong by next year. A multivalued attribute cannot sit in one cell without breaking atomicity, so it moves into a table of its own, and a many-to-many relationship becomes a table of pairs:

SQL Multivalued attribute and M:N as tables

Press Run (or Ctrl + Enter) to see the result here.

The book with two authors needs two rows in writes, which is exactly what "many-to-many" means.

Common design guidelines

  • Entity or attribute? If something has its own properties or relationships (a department with a manager), make it an entity. If it is a plain fact about something (a color), keep it an attribute.
  • Avoid redundancy. Do not store the same fact in both an entity and a relationship.
  • Use the right cardinality. Ask "can one X have many Y?" in both directions.
  • Attributes of a relationship belong to the relationship when they depend on the combination of both sides (grade, loan date).
  • Name with care. Singular nouns for entities, verbs for relationships.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which attribute is derived?

    A derived attribute can be calculated from other stored data, so it normally should not be stored.
  2. 2. A department has many employees, and each employee works for exactly one department. This relationship is:

    One department relates to many employees, but each employee relates to one department: 1:N.
  3. 3. What does total participation of EMPLOYEE in WORKS_IN mean?

    Total participation means every entity in the set takes part in the relationship, at least once.
  4. 4. Where does the attribute 'grade' belong in a student-course ER model?

    A grade depends on the pair of student and course, so it is an attribute of the relationship.
  5. 5. A multivalued attribute like phone numbers will later be represented in tables as:

    Storing several values in one cell breaks atomicity; the standard mapping is a separate table.

Summary

  • ER modeling describes entities (with attributes), keys and relationships before any tables exist.
  • Attributes can be simple, composite, multivalued, derived or optional; keys identify entities.
  • Relationships have a degree, can have attributes, and are limited by cardinality (1:1, 1:N, M:N) and participation (total or partial).
  • Mermaid's crow's foot notation shows cardinality and participation compactly.

Next: Weak Entities, Specialization and Aggregation.