The idea: the diagram is the design, the tables are what gets stored

An entity-relationship (ER) diagram describes the data in words a person can check: the things (entity types, boxes), how they are connected (relationship types, diamonds) and their attributes. A relational database stores only tables. The ER-to-relational mapping turns one into the other with a fixed set of rules, so it is the first design step after the diagram. Every rule answers one question: where does the key go? The canvas runs the rules on part of the book's COMPANY database (Elmasri & Navathe, chapter 9): 4 entity types and 5 relationship types become 6 tables with 7 foreign keys.

Left, the ER diagram: EMPLOYEE, DEPARTMENT, PROJECT and the weak entity DEPENDENT, with the relationships WORKS_FOR, MANAGES, CONTROLS, WORKS_ON and DEPENDENTS_OF. Right, the six tables EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT, WORKS_ON and DEPT_LOCATIONS, each primary key column in yellow and each foreign key column marked with a strip in the colour of the table it references.
Entity types become tables; 1:1 and 1:N relationships become foreign key columns; M:N relationships and multivalued attributes become tables of their own.

The six steps

StepER elementBecomesOn the canvas
1regular entity typea table; the key attribute is the PRIMARY KEYEMPLOYEE, DEPARTMENT, PROJECT
2weak entity typea table with the owner's key; PK = owner key + partial keyDEPENDENT(Essn, Dependent_name, …)
31:1 relationshipa foreign key on one side, best the total sideDEPARTMENT.Mgr_ssn, Mgr_start
41:N relationshipa foreign key on the N sideEMPLOYEE.Dno, PROJECT.Dnum
5M:N relationshipa new table with both keys; together they are the PKWORKS_ON(Essn, Pno, Hours)
6multivalued attributea new table: owner key + value, both in the PKDEPT_LOCATIONS(Dnumber, Dlocation)

The order matters: a foreign key can only point at a table that already exists, so entity types come first and relationships after (Demo: map the whole diagram, steps 1 to 6). On the canvas a key that is copied flies as a blue box from the primary key it comes from to its new column.

Entity types and weak entity types

A regular entity type becomes a table with one column per simple attribute. Its key attribute (shown in yellow) becomes the PRIMARY KEY: Ssn for EMPLOYEE, Dnumber for DEPARTMENT, Pnumber for PROJECT. A composite attribute would be split into its parts; a multivalued attribute can't be a column at all and waits for step 6.

A weak entity type (double box) has no key of its own, only a partial key. Wong and Smith both have a daughter called Alice, so Dependent_name alone can't identify a row. The table takes the owner's key through the identifying relationship (double diamond): PRIMARY KEY (Essn, Dependent_name), with Essn a foreign key to EMPLOYEE. ON DELETE CASCADE fits, because a dependent can't exist without the employee. With Dependent_name alone as the key, MariaDB refuses the second Alice with ERROR 1062 Duplicate entry 'Alice' for key 'PRIMARY' (Demo: weak entity borrows the owner's key).

Relationships: where the foreign key goes

The cardinality ratio on the diagram decides it. The rule of thumb: put the key where each row needs exactly one value.

Three rows. 1:N WORKS_FOR: Dno in EMPLOYEE gives one value per row, marked correct; Emp_ssn in DEPARTMENT needs two values for Research, marked wrong. 1:1 MANAGES: Mgr_ssn in DEPARTMENT has no NULL, marked correct; Mgr_dno in EMPLOYEE is NULL for Smith, marked worse. M:N WORKS_ON: a table WORKS_ON with one row per pair, marked correct; a column on either side needs a list, marked wrong.
For each cardinality, the good placement gives one value per cell; the other placement gives lists or NULLs.
  • 1:N (WORKS_FOR, CONTROLS): the key of the 1 side goes into the N side. Each employee works for one department, so EMPLOYEE gets Dno. Put it the other way round and Research needs two Ssns in one cell, which breaks first normal form (Demo: 1:N puts the key on the N side).
  • 1:1 (MANAGES): either side works, but choose the side with total participation (double line): every department has a manager, so DEPARTMENT.Mgr_ssn is never NULL. On the EMPLOYEE side, every employee who manages nothing gets NULL: 5 of the book's 8 employees (Demo: 1:1 puts the key on the total side). If both sides are total, the two tables can also be merged into one.
  • M:N (WORKS_ON): no side works. Wong works on 2 projects and ProductY has 2 employees, so either column would need a list. A new relationship table holds one row per pair, with both keys as foreign keys and together as the primary key (Demo: M:N gets a table of its own).

A relationship's own attributes travel with it: Start_date of MANAGES goes next to Mgr_ssn in DEPARTMENT, and Hours of WORKS_ON goes into WORKS_ON, because hours belong to an (employee, project) pair. A 1:1 or 1:N relationship can also get its own table (the "cross-reference" option), which is useful when few rows take part and the foreign key would be mostly NULL.

Multivalued attributes

Research is in Bellaire and in Houston. A cell holds one value, so {Locations} becomes its own table DEPT_LOCATIONS(Dnumber, Dlocation) with one row per location, and the primary key is both columns. Storing 'Bellaire, Houston' in one column would make it impossible to index, join or check, and repeating the DEPARTMENT row per location would copy Dname and the manager (Demo: multivalued attribute gets a table of its own).

The schema in MariaDB

The DDL panel shows what each step writes. EMPLOYEE and DEPARTMENT point at each other (Dno and Mgr_ssn), so one of the two foreign keys can only be added after both tables exist; that is why steps 3 and 4 use ALTER TABLE … ADD FOREIGN KEY. In MariaDB with InnoDB, foreign keys are checked on every write: deleting Smith while WORKS_ON still has his rows fails with ERROR 1451 Cannot delete or update a parent row, while the DEPENDENT rows of a deleted employee go with it, thanks to ON DELETE CASCADE. InnoDB also creates an index on each foreign key column that has none (here on WORKS_ON.Pno), so these checks and joins stay fast.

See also Database Normalization for checking the tables afterwards with functional dependencies, Relational Algebra for joining them back together, Join Algorithms for how a join on a foreign key runs, and MariaDB Table Elimination for how a unique key lets MariaDB skip a join.

See also Data Models: Relational vs Document vs Graph: the same data stored as tables, as a nested document and as a graph, compared on one résumé.

See also Attribute Closure, Candidate Keys and BCNF to check the keys and the normal form of the tables the mapping produces.

See also Star Schema and OLAP Cube Operations for the same kind of tables reshaped into a star schema for reports.

See also MariaDB Integrity Constraints and Foreign Key Actions for what MariaDB does when a row breaks a key, and how ON DELETE CASCADE, SET NULL and RESTRICT treat the child rows.

What the page leaves out

Step 7 of the algorithm, n-ary relationships (a table with the keys of all n sides), and the mapping of specialization and generalization (one table per class, one table for all, or subclass tables only) and of union types. Composite attributes, and derived attributes such as Number_of_employees, which are usually not stored. The real COMPANY schema has more columns (Fname, Minit, Bdate, Address, Sex), the SUPERVISION recursive relationship and more rows; the page keeps three employees and two departments so every cell can be checked. Choosing data types, NOT NULL and ON UPDATE actions is part of physical design and is only sketched in the DDL.

On the mind map

Open the Databases & Storage mind mapDatabases& StorageSchema and storage: how rows land on diskSchema and storageIndexes: find rows without a full scanIndexesRunning a query: parse, plan, executeRunning a queryCost-based optimizer: estimate rows, pick cheapest planCost-based optimizerQuery rewrites: same answer, simpler queryQuery rewritesTransactions: ACID: isolation and recoveryTransactionsApps and connections: the client side of the databaseApps and connectionsMongoDB: documents, replica sets, shardsMongoDBRelational, document, graphRelational, document, graphone résumé as tables, JSON and a graphNormalizationNormalizationeach fact once: no partial, transitive depsClosure, Keys, BCNFClosure, Keys, BCNFBCNF can lose an FD; 3NF never has toRow vs column storeRow vs column storerows for lookups, columns for SUMStar schema & OLAP cubeStar schema & OLAP cuberoll-up = GROUP BY; slice = WHERELSM vs B+ treeLSM vs B+ treeLSM: fast writes; B+ tree: fast readsSQLite internalsSQLite internalsone file of pages, a b-tree per tableER model to tablesentities, relations, keys become tablesIntegrity constraints & FKsIntegrity constraints & FKs1062 dup, 1452 no parent, 1451 has childrenOpen the Databases & Storage mind mapDatabases & Storagehow rows land on diskSchema and storageRelational, document, graphRelational, document, graphone résumé as tables, JSON and a graphNormalizationNormalizationeach fact once: no partial, transitive depsClosure, Keys, BCNFClosure, Keys, BCNFBCNF can lose an FD; 3NF never has toRow vs column storeRow vs column storerows for lookups, columns for SUMStar schema & OLAP cubeStar schema & OLAP cuberoll-up = GROUP BY; slice = WHERELSM vs B+ treeLSM vs B+ treeLSM: fast writes; B+ tree: fast readsSQLite internalsSQLite internalsone file of pages, a b-tree per tableER model to tablesentities, relations, keys become tablesIntegrity constraints & FKsIntegrity constraints & FKs1062 dup, 1452 no parent, 1451 has children
This page on the Databases & Storage mind map · 55 pages in 8 branches · open it to see the whole map and test yourself in recall mode.