Converting an ER Diagram into Relational Tables

Concept

An ER diagram is a design, not a database. This note gives the complete, ordered set of rules for converting one into tables. Applying them in order produces a correct schema every time, and this conversion is one of the most frequently examined tasks in the subject.

The seven rules

Rule 1 — Strong entity

Create a table. Include all simple attributes and the leaf attributes of composite ones. The key attribute becomes the primary key.

  STUDENT ( roll_no, name{first,last}, dob )
     -> students ( roll_no PK, first_name, last_name, dob )

Rule 2 — Weak entity

Create a table with its own attributes plus the owner primary key as a foreign key. The primary key is the owner key together with the partial key. Use cascading delete.

  DEPENDANT, owned by EMPLOYEE, partial key name
     -> dependants ( emp_id, name, dob )
        PK ( emp_id, name )
        FK emp_id -> employees ON DELETE CASCADE

Rule 3 — One to many

Put the primary key of the one side into the table on the many side as a foreign key. Make it NOT NULL if participation is total. No new table is created.

  DEPARTMENT 1 ---- N STUDENT, student total
     -> students ( roll_no PK, name, dept_code NOT NULL )
        FK dept_code -> departments

Rule 4 — Many to many

Create a new table. Include the primary keys of both sides as foreign keys, plus every attribute of the relationship. The primary key is the combination of the two foreign keys, extended if the relationship may repeat.

  STUDENT M ---- N COURSE, with marks and semester
     -> enrolments ( roll_no, course_code, semester, marks )
        PK ( roll_no, course_code, semester )
        FK roll_no     -> students
        FK course_code -> courses

  semester is in the key because a student may repeat
  a course in a later semester.

Rule 5 — One to one

Put the primary key of one side into the other as a foreign key with a uniqueness constraint. Choose the side with total participation, so the column can be NOT NULL and no nulls are wasted.

  EMPLOYEE 1 ---- 1 DEPARTMENT, department total on HEADS
     -> departments ( dept_code PK, dept_name,
                      head_emp_id NOT NULL UNIQUE )
        FK head_emp_id -> employees

  Placing it on employees instead would leave the column
  null for almost every employee.

Rule 6 — Multivalued attribute

Create a separate table containing the owner primary key and the attribute. The primary key is both columns together.

  STUDENT with multivalued phone
     -> student_phones ( roll_no, phone )
        PK ( roll_no, phone )
        FK roll_no -> students ON DELETE CASCADE

Rule 7 — n-ary relationship

Create a table containing the primary key of every participating entity type plus the relationship attributes. The primary key is normally the combination of all the foreign keys.

  SUPPLIER, PART, PROJECT with quantity
     -> supplies ( supplier_id, part_id, project_id, quantity )
        PK ( supplier_id, part_id, project_id )

Additional rules

• Derived attributes get no column. Compute them when needed.
• Composite attributes contribute one column per leaf.
• ISA hierarchies use one of the three approaches from the previous note.
• Aggregation — the aggregated relationship becomes a table with its own key; the outer relationship references it.

Complete worked conversion

  DIAGRAM

    DEPARTMENT ( dept_code, dept_name )
    STUDENT    ( roll_no, name{first,last}, dob, age*, phone** )
    COURSE     ( course_code, title, credits )
    DEPARTMENT 1 ==== N STUDENT      student total
    DEPARTMENT 1 ---- N COURSE       course total
    STUDENT    M ---- N COURSE       + marks, semester
    EMPLOYEE   1 ---- 1 DEPARTMENT   HEADS, department total
    EMPLOYEE   1 ==== N DEPENDANT    weak, partial key name

    * derived   ** multivalued

  SCHEMA

    departments ( dept_code PK, dept_name,
                  head_emp_id NOT NULL UNIQUE
                    -> employees )                   rule 5

    employees   ( emp_id PK, emp_name )              rule 1

    students    ( roll_no PK, first_name, last_name,
                  dob,
                  dept_code NOT NULL -> departments ) rules 1, 3
                  -- age is NOT stored

    student_phones ( roll_no, phone )
                  PK ( roll_no, phone )              rule 6

    courses     ( course_code PK, title, credits,
                  dept_code NOT NULL -> departments ) rules 1, 3

    enrolments  ( roll_no, course_code, semester, marks )
                  PK ( roll_no, course_code, semester )
                                                     rule 4

    dependants  ( emp_id, name, dob, relationship )
                  PK ( emp_id, name )                rule 2

  Count check: 3 entity tables + 1 weak + 1 multivalued
  + 1 junction + 1 employee = 7 tables.
  Relationships 1:1 and 1:N produced NO new tables.

Use that count as a self check. Only many to many relationships, weak entities, multivalued attributes and n-ary relationships create new tables. If a one to many relationship produced a table in your answer, you have made an error.

Common mistakes

• Creating a table for a one to many relationship. A foreign key is enough, and the extra table forces a pointless join.
• Putting the foreign key on the one side. It would have to hold many values.
• Forgetting the relationship attributes. Marks belong in the junction table, not anywhere else.
• Giving the junction table a surrogate key and no uniqueness constraint. The same enrolment can then be inserted twice.
• Storing derived attributes. Age is computed, never stored.
• Ignoring participation. Total participation is what makes a foreign key NOT NULL.

Exam and interview questions

1. State the seven conversion rules with an example of each.
2. Where does the foreign key go for 1:N, and where for 1:1?
3. Why does a many to many relationship always need its own table?
4. How is a weak entity converted, and what is its primary key?
5. Which ER constructs create new tables and which do not?

Practice

1. Convert a hospital diagram with DOCTOR, PATIENT, APPOINTMENT (M:N with date and time) and a weak entity PRESCRIPTION LINE.
2. A 1:1 relationship is total on neither side. Where do you put the foreign key, and what is the cost?
3. Given seven tables in a finished schema, state which ER construct produced each one.

Conclusion

Seven rules, applied in order, convert any ER diagram into tables. Remember the two that carry most of the marks: the foreign key goes on the many side, and only many to many, weak entities, multivalued attributes and n-ary relationships create new tables.