ER Diagram to Relational Schema

From picture to tables

An ER (entity–relationship) diagram is how designers sketch a database: entities (rectangles), the relationships between them (diamonds, with cardinalities such as 1:N), and attributes (ovals, with keys underlined). A relational database stores only tables. A short list of rules turns one into the other, and it is a favourite exam question.

The mapping rules

A relationship’s own attributes travel with it: they go wherever its foreign key goes (rules 3 and 4) or into its table (rule 5).

Example 1: University

Diagram: Department (did, dname), Student (sid, name, address(street, city), {phone}), Course (cid, title, credits). Major links Department 1:N Student. Enrolls links Student M:N Course and has the attribute grade.

Primary keys are in bold. In SQL:

CREATE TABLE Department (
  did    INTEGER PRIMARY KEY,
  dname  TEXT NOT NULL
);
CREATE TABLE Student (
  sid     INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  street  TEXT,                                  -- composite attribute address,
  city    TEXT,                                  -- flattened into its parts
  did     INTEGER REFERENCES Department(did)     -- 1:N Major: FK on the N side
);
CREATE TABLE Course (
  cid      TEXT PRIMARY KEY,
  title    TEXT NOT NULL,
  credits  INTEGER
);
CREATE TABLE Enrolls (                           -- M:N: a table of its own
  sid    INTEGER REFERENCES Student(sid),
  cid    TEXT    REFERENCES Course(cid),
  grade  TEXT,
  PRIMARY KEY (sid, cid)
);
CREATE TABLE Student_phone (                     -- multivalued attribute {phone}
  sid    INTEGER REFERENCES Student(sid),
  phone  TEXT,
  PRIMARY KEY (sid, phone)
);

Example 2: Company (weak entity and 1:1)

Diagram: Employee (eid, name, salary), Department (dno, dname), Project (pno, pname) and the weak entity Dependent (partial key dep_name, relation), owned by Employee through the identifying relationship Has. Works_For links Department 1:N Employee. Manages links Employee 1:1 Department, with attribute start_date, and every department must have a manager. Works_On links Employee M:N Project, with attribute hours.

The foreign key for Manages is called mgr_eid. A plain eid would be confusing, and with several relationships to the same table, role names like this are essential.

Code

entities = {
    'Department': {'key': ['did'], 'attrs': ['did', 'dname']},
    'Student':    {'key': ['sid'], 'attrs': ['sid', 'name', ('address', ['street', 'city'])], 'multi': ['phone']},
    'Course':     {'key': ['cid'], 'attrs': ['cid', 'title', 'credits']},
}
relationships = [                       # (name, one side, many side, cardinality, attributes)
    ('Major',   'Department', 'Student', '1:N', []),
    ('Enrolls', 'Student',    'Course',  'M:N', ['grade']),
]

tables = {}
for name, e in entities.items():                       # rule 1: entity -> table
    cols = []
    for a in e['attrs']:
        cols += a[1] if isinstance(a, tuple) else [a]  # flatten composite attributes
    tables[name] = {'cols': cols, 'pk': e['key']}
for rel, one, many, card, attrs in relationships:
    if card == '1:N':                                  # rule 4: FK on the N side
        tables[many]['cols'] += tables[one]['pk'] + attrs
    elif card == 'M:N':                                # rule 5: a table of its own
        keys = tables[one]['pk'] + tables[many]['pk']
        tables[rel] = {'cols': keys + attrs, 'pk': keys}
for name, e in entities.items():                       # rule 6: multivalued attributes
    for m in e.get('multi', []):
        keys = e['key'] + [m]
        tables[f'{name}_{m}'] = {'cols': keys, 'pk': keys}

for name, t in tables.items():                         # primary-key columns get a *
    print(f"{name}({', '.join(c + '*' if c in t['pk'] else c for c in t['cols'])})")
# Department(did*, dname)
# Student(sid*, name, street, city, did)
# Course(cid*, title, credits)
# Enrolls(sid*, cid*, grade)
# Student_phone(sid*, phone*)

After mapping

The tables above are a good start, and they are usually already in a high normal form. Still, check them against their functional dependencies and normalize if needed. Foreign keys are what joins follow to bring the data back together.

Common mistakes

• Putting the 1:N foreign key on the 1 side. A department can’t store a list of employees in one column.
• Using foreign keys for M:N. It needs its own table.
• Repeating columns for a multivalued attribute (phone1, phone2). Make a separate table.
• Forgetting the owner’s key in a weak entity’s primary key. The partial key alone is not unique.
• Forgetting the relationship’s attributes. grade belongs in Enrolls, not in Student or Course.