Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 79 ยท Data Models and ER Modelling

Mapping ER Diagrams to Tables

Turn an ER diagram into relational tables: rules for entities, 1:1, 1:N and M:N relationships, weak entities, multivalued attributes and three ISA strategies.

An ER diagram is a design on paper. A database needs tables, columns and keys. Luckily the translation is almost mechanical: each construct in the diagram has a standard rule, and following the rules gives you a schema that is correct before you start tuning it. This is the step that connects ER modelling to the relational model.

In this lesson you learn the rules one at a time, including three different ways to map an ISA hierarchy, and then apply all of them to a small university schema that runs live in your browser. For the exact syntax of CREATE TABLE see CREATE TABLE in the SQL tutorial; here the focus is on which tables and keys to create.

The mapping rules at a glance

Entity One table, one column per simple attribute.
1:N Foreign key on the "many" side.
M:N A new table with both keys.
Multivalued A separate table, one row per value.

Weak entities and hierarchies add two more rules, below.

1 / 2

Strong entities and their attributes

Rule 1. Every strong entity becomes a table. Every simple attribute becomes a column, and the entity's key becomes the primary key.

  • Composite attributes are flattened into their parts. An address made of street, city and postcode becomes three columns (street, city, postcode). The composite itself gets no column.
  • Derived attributes such as age (computed from birth date) are normally not stored. Compute them in queries or views, so they cannot go stale.
  • If an entity has several candidate keys, pick one as the primary key and mark the others UNIQUE.

Relationships

How you map a binary relationship depends on its cardinality.

Rule 2: one-to-many (1:N). Put the key of the "one" side into the table of the "many" side, as a foreign key. If a department has many employees, each employee row stores dept_id. Relationship attributes (say, start_date) move to the many side too. If participation of the many side is total, make the column NOT NULL.

Rule 3: many-to-many (M:N). A foreign key cannot hold many values, so the relationship becomes a table of its own. Its columns are the keys of both participants plus any relationship attributes, and the combined keys form the primary key. Enrollment between students and courses is the classic case.

Rule 4: one-to-one (1:1). You have three options:

  1. Put a foreign key on either side, with a UNIQUE constraint on it so it stays one-to-one. Prefer the side with total participation, so the column can be NOT NULL and never empty.
  2. Merge both entities into one table, which works when both sides have total participation and are always created together.
  3. Use a separate table for the relationship. This is rarely worth it, except when the relationship has many attributes of its own.

Rule 5: relationships with more than two participants (ternary and beyond, see Weak Entities, Specialization and Aggregation) become a table holding the key of every participant. Which of those columns form the primary key depends on the cardinalities.

Weak entities

Rule 6. A weak entity becomes a table whose primary key is the owner's key plus the partial key. The owner's key is also a foreign key, normally with ON DELETE CASCADE, because a weak entity cannot outlive its owner. The identifying relationship needs no table of its own; the foreign key is the relationship.

Multivalued attributes

Rule 7. A multivalued attribute (an employee with several phone numbers) cannot be one column without breaking first normal form, so it becomes its own table: the owner's key plus one column for the value. The primary key is the pair (owner key, value). Each phone number becomes one row.

ISA hierarchies: three strategies

Rule 8. There is more than one valid mapping for a hierarchy, and the right choice depends on the disjoint/overlapping and total/partial constraints and on how you query.

Strategy Tables Good when Cost
A. Table per type One for the supertype, one per subtype sharing its key Constraints are partial or overlapping; subtypes have many different attributes Joins to see a whole entity
B. Table per subtype only One table per subtype with all inherited columns; no supertype table Disjoint and total; you rarely query across all types Cross-type queries need UNION; shared key uniqueness is hard to enforce
C. Single table One table with all columns and a type column Few subtype attributes; mostly disjoint Many NULLs; subtype columns cannot be NOT NULL without checks

Strategy A is the most general and the safest default, and it is the one used for the earlier Employee example. Strategy C is the simplest to query. Here the two ends side by side:

SQL Strategy A vs strategy C for the same hierarchy

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

Both queries return the same row. Strategy A needed a join; strategy C needed a CHECK constraint to keep the NULLs under control, which is the price of having one wide table.

When unsure, start with strategy A. It keeps every column meaningful (no NULLs for "does not apply") and every constraint declarative. Switch to C only when queries across the whole hierarchy dominate and the subtypes differ in just one or two columns.

A university schema, end to end

Let us map a small diagram. The ER design has:

  • Department (strong, key dept_id) and Professor (strong, key prof_id); a department employs many professors (1:N, total on the professor side).
  • Student (strong, key student_id) with a multivalued attribute email.
  • Course (strong, key code).
  • A many-to-many relationship Enrolls between Student and Course with the attribute grade.
  • Section as a weak entity of Course, with partial key term.

Applying the rules gives the tables below. Read the comments for the rule behind each one.

SQL The university schema from the ER rules

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

Notice what the mapping gave us for free. enrolls points at section, not at course, because the weak entity's full key is (code, term). A student cannot enroll in a section that does not exist, and a department cannot lose a course that is still listed.

SQL Using the mapped schema: multivalued data and aggregates

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

The LEFT JOIN keeps Cleo, who has no email at all. With a single comma-separated email column that question ("how many emails?") would need string parsing, which is why multivalued attributes get their own table.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. An ER diagram has a many-to-many relationship Students-Courses. How is it mapped?

    A single column cannot hold many values, so an M:N relationship becomes its own table whose primary key combines both participants' keys.
  2. 2. What is the primary key of the table for a weak entity?

    The partial key is only unique within one owner, so the owner's key is added. The owner's key part is also a foreign key.
  3. 3. An Employee has a multivalued attribute phone. What should the mapping do?

    One row per value keeps the first normal form and lets you count, search and add values without restructuring the table.
  4. 4. Which ISA mapping strategy has no NULLs for attributes that do not apply, and keeps every constraint declarative?

    With a table per type, each subtype table only has columns that apply to it. The single-table approach needs NULLs and CHECK constraints.
  5. 5. In a one-to-many relationship Department-Professor, where does the foreign key go?

    The foreign key goes on the "many" side, so each professor row stores the key of its one department.

Summary

  • Strong entities become tables; composite attributes are flattened and derived attributes are usually not stored.
  • 1:N relationships put a foreign key on the many side; M:N relationships become a table of their own.
  • 1:1 relationships use a UNIQUE foreign key, preferably on the side with total participation.
  • Weak entities use the owner's key plus the partial key as primary key, with ON DELETE CASCADE.
  • Multivalued attributes get their own table, one row per value.
  • ISA hierarchies can be mapped as table per type, table per subtype or a single table; choose by constraints and query patterns.

Next: Design Case Study: A Hospital Database, where you take a design from requirements all the way to tables.