Lesson 94 of 12124 min read

How to Convert ER Diagrams into Relational Tables

Learn the step-by-step rules for translating an ER diagram into actual relational tables, ready for CREATE TABLE statements.

Author: CodersNexus

How to Convert ER Diagrams into Relational Tables

An ER diagram is a plan — a set of tables doesn't yet exist until that plan is systematically translated into a relational schema. This translation follows a consistent set of rules that, once learned, can be applied mechanically to any ER diagram, turning entities into tables, attributes into columns, and relationships into foreign keys or junction tables.

Key Definitions

  • Mapping rules: The standardized set of conversion rules used to translate ER diagram elements (entities, attributes, relationships) into relational database tables, columns, and keys.
  • Weak entity: An entity that cannot be uniquely identified by its own attributes alone and depends on another entity's primary key as part of its own identification.

What You'll Learn

  • Apply the standard rule set for converting entities into tables.
  • Handle multi-valued attributes correctly by creating a separate table.
  • Convert 1:1, 1:N, and M:N relationships into appropriate keys and tables.
  • Walk through a complete ER-to-schema conversion for the hospital system.

Detailed Explanation

The conversion process follows a clear sequence of rules. Rule 1: every strong entity becomes its own table, with its attributes becoming columns, and one attribute (or combination) chosen as the primary key. Rule 2: simple and composite attributes become columns directly (a composite attribute like address might expand into separate street, city, and postal_code columns). Rule 3: derived attributes are generally not stored as columns at all, since they're calculated on demand via a query (like computing age from date_of_birth). Rule 4: multi-valued attributes cannot become a single column — instead, they require their own separate table, with a foreign key back to the original entity, since a doctor with three phone numbers needs three rows in a doctor_phones table, not one column trying to hold three values.

Rule 5 governs relationships: a 1:1 relationship adds a foreign key with a UNIQUE constraint to either table (commonly the one representing optional or dependent data). A 1:N relationship adds a foreign key to the table on the 'many' side, referencing the 'one' side's primary key. An M:N relationship requires creating an entirely new junction table containing foreign keys to both related entities, exactly as covered in the previous lesson.

Applying these rules to the hospital ER diagram from Lesson 8.2 systematically produces the departments, doctors, patients, and appointments tables that have been used as the running example throughout this course — this lesson makes that translation process explicit and repeatable for any future schema you design.

Visual Summary

A conversion checklist diagram with five numbered rule boxes in sequence: [Rule 1: Entity → Table], [Rule 2: Simple/Composite Attribute → Column(s)], [Rule 3: Derived Attribute → Not Stored, Computed via Query], [Rule 4: Multi-Valued Attribute → Separate Table with Foreign Key], [Rule 5: Relationship → Foreign Key (1:1, 1:N) or Junction Table (M:N)].

Quick Reference

ER Diagram ElementRelational Table Equivalent
Strong entity (Doctor)A table (doctors)
Simple attribute (doctor_name)A column
Derived attribute (patient_age)Not stored; computed via query from date_of_birth
Multi-valued attribute (doctor_phone_numbers)A separate table: doctor_phones(doctor_id, phone_number)
1:N relationship (Department–Doctor)Foreign key department_id in doctors table
M:N relationship (Doctor–Department, if applicable)Junction table doctor_departments

SQL Example


-- Converting the multi-valued attribute "doctor_phone_numbers"
-- into its own table, following Rule 4
CREATE TABLE doctor_phones (
  phone_id    INT PRIMARY KEY AUTO_INCREMENT,
  doctor_id   INT NOT NULL,
  phone_number VARCHAR(20) NOT NULL,
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- A doctor with two phone numbers now correctly occupies two rows,
-- rather than trying to cram two values into one column
INSERT INTO doctor_phones (doctor_id, phone_number) VALUES
  (101, '9876543210'),
  (101, '9123456780');

Trying to store multiple phone numbers directly in the doctors table as a single phone_numbers column (perhaps as a comma-separated string) would violate good relational design, since it would prevent SQL from easily searching, indexing, or validating individual numbers. Following Rule 4, the multi-valued attribute becomes its own table instead, with a foreign key linking each phone number row back to its doctor — Dr. Verma's two numbers now cleanly occupy two separate, queryable rows.

Real-World Examples

  • CRM systems store multiple email addresses or phone numbers per contact in a separate related table rather than cramming them into the main contacts table.
  • E-commerce platforms convert product attributes like 'available colors' or 'available sizes' into separate related tables rather than multi-valued columns.
  • HR systems store multiple certifications per employee in a dedicated certifications table, linked back to the employee via a foreign key.

Common Mistakes to Avoid

  • Storing a multi-valued attribute as a single comma-separated text column instead of creating a proper related table.
  • Storing a derived attribute like age directly, risking it becoming outdated as time passes.
  • Forgetting to apply the correct foreign key placement rule based on relationship cardinality.

Interview Questions

Q1. How do you handle a multi-valued attribute when converting an ER diagram to relational tables?

A multi-valued attribute cannot become a single column, since a column should hold one atomic value. Instead, it becomes its own separate table, with a foreign key referencing the original entity, allowing multiple rows to represent multiple values for a single entity instance.

Q2. Are derived attributes typically stored as columns in the relational schema?

Generally no. Derived attributes, like age calculated from date_of_birth, are usually left out of the physical schema entirely and instead computed on demand using a query, avoiding the risk of stored data becoming stale or inconsistent.

Q3. What is the general rule for converting a 1:N relationship into tables?

A foreign key is added to the table representing the 'many' side of the relationship, referencing the primary key of the table on the 'one' side.

Practice MCQs

1. How should a multi-valued attribute be represented in a relational schema?

  1. As a comma-separated string in one column
  2. As a separate table with a foreign key back to the entity
  3. It should be ignored entirely
  4. As a JSON column always

Answer: B. As a separate table with a foreign key back to the entity

Explanation: Multi-valued attributes require their own table so that multiple values per entity instance can be stored as multiple clean, queryable rows.

2. Are derived attributes typically stored as physical columns?

  1. Yes, always
  2. No, they are typically computed via a query instead
  3. Only if they are numeric
  4. Only in NoSQL databases

Answer: B. No, they are typically computed via a query instead

Explanation: Derived attributes are usually left out of the stored schema and calculated on demand, avoiding data staleness.

Quick Revision Points

  • Multi-valued attributes require their own separate table — a very commonly tested conversion rule.
  • Derived attributes are generally not stored; they are computed via queries.
  • 1:N places a foreign key on the 'many' side; M:N requires a junction table.

Conclusion

  • ER diagram elements map to relational schema elements through a consistent, repeatable set of rules.
  • Multi-valued attributes always require their own separate table, never a single column.
  • Derived attributes are typically computed rather than stored directly.

Converting an ER diagram into actual relational tables follows a consistent, repeatable set of rules: entities become tables, simple and composite attributes become columns, derived attributes are typically left uncomputed in storage and calculated via queries instead, multi-valued attributes require their own dedicated related table, and relationships translate into foreign keys (for 1:1 and 1:N) or junction tables (for M:N). Mastering this mechanical translation process is what allows a database designer to move confidently from a whiteboard diagram to a working, well-structured schema.

Frequently Asked Questions

Each entity becomes a table, attributes become columns, multi-valued attributes become their own separate related table, and relationships become foreign keys (for 1:1 and 1:N) or a junction table (for M:N).

They cannot be stored as a single column. Instead, they require a separate table with a foreign key referencing the original entity, allowing multiple rows to represent the multiple values.

Generally no. Derived attributes are typically computed on demand via a query from other stored data, rather than being stored directly, to avoid the risk of the value becoming outdated.

A foreign key with a UNIQUE constraint is added to one of the two related tables, ensuring each row in that table links to at most one row in the other.

A new junction table is created, containing foreign keys referencing both related entities, since a plain foreign key column cannot represent a relationship where both sides can relate to multiple rows on the other side.