CS 340 ER → Relational Mapping Cheat Sheet
Chapter 05 — Quick Reference

Look at a diagram.
Know what to do.

Every symbol, line, and shape in an ER/EER diagram maps to a specific relational pattern. This cheat sheet tells you exactly what to do when you see each one.

Symbols Decision Flow Mapping Steps FK & PK Rules EER (Specialization) Union Types DDL Templates Full Example
What you see in diagrams
ER Diagram Symbol Guide
🔲 Shapes = Entities & Relations
□
Rectangle — Regular (strong) entity. Makes its own table.
□□
Double Rectangle — Weak entity. Must borrow its identity.
◇
Diamond — Relationship. May or may not get its own table.
◇
Double Diamond — Identifying relationship (for weak entity).
○
Oval — Attribute. Becomes a column in the entity's table.
⊙
Double Oval — Multivalued attribute. Gets its own table (Step 6).
- - -
Dashed oval — Derived attribute. Usually not stored; skip or compute.
ID
Underlined attribute — Key attribute → becomes Primary Key.
ID
Dashed-underlined — Partial key of a weak entity. Part of composite PK.
🔗 Lines = Participation & Cardinality
—
Single line — Partial participation. Entity may NOT participate (0 or more).
=
Double line — Total participation. Entity MUST participate (at least one).
1
Label "1" — At most one. On either side of a relationship diamond.
N or M
Label "N" or "M" — Many. That side can have multiple instances.
(1,1)
(min, max) notation — Exactly 1. Always total + exactly-one-side.
(0,n)
(0, n) — Zero or more. That entity is optional (partial).
(1,n)
(1, n) — At least one, many max. Total + many.
ℹ️ Read (min, max) away from the entity: Stand at the entity, look away toward the relationship. The numbers describe how many relationships that entity participates in.
🌐 EER Extra Symbols
⊂ ○ ⊂
Circle with lines — Specialization/Generalization hierarchy. Superclass-subclass.
d
"d" in circle — Disjoint. Entity can belong to AT MOST ONE subclass.
o
"o" in circle — Overlapping. Entity can belong to MULTIPLE subclasses at once.
⊃ ∪
∪ symbol (or circle with arc) — Union/Category type. Subclass of multiple different superclasses.
=
Double line to circle — Total specialization. Every superclass member IS in a subclass.
—
Single line to circle — Partial specialization. Superclass member may not be in any subclass.
The core question
When You See a Relationship — What Do You Do?
❓ What kind of relationship is this?
1 : 1    (One-to-One)
No new table needed.

Put the FK in one of the two tables. Choose based on participation:
  • One side is TOTAL → FK goes in the total-participation entity (Foreign Key approach, most common)
  • Both TOTAL → Merge both entities into one table
  • Both PARTIAL → Create a 3rd cross-reference table

Include relationship attributes in whichever table gets the FK.
1 : N    (One-to-Many)
No new table needed.

FK goes in the N-side entity (the "many" side).

The FK references the PK of the 1-side entity.

Include relationship attributes in the N-side table.

Why N-side? Because putting FK on the 1-side would force multi-valued attributes (bad!).
M : N    (Many-to-Many)
Always create a NEW table.

New relationship table contains:
  • FK → PK of Entity A
  • FK → PK of Entity B
  • Both FKs together = Composite PK
  • All relationship attributes
Weak Entity
New table for the weak entity.

Table contains:
  • PK of owner entity (as FK)
  • Partial key of weak entity
  • Composite PK = {owner PK + partial key}
  • All other attributes
Multivalued Attribute
Always create a NEW table.

New table contains:
  • The multivalued attribute (as column)
  • FK → PK of the entity that owns it
  • Composite PK = {attribute + FK}
The official algorithm
9-Step Mapping Algorithm
01
Regular (Strong) Entity Types
Rectangle with single border → straightforward table
Always

Every regular entity becomes its own table. Include all simple attributes. For composite attributes, include only the leaf-level simple parts (not the composite itself).

What to include
1
All simple attributes → columns
2
Composite attributes → expand into leaf parts, drop the composite itself
3
Underlined attribute → Primary Key
4
Derived attribute (dashed oval) → usually skip it
Result schema
EMPLOYEE(
  SSN,
  Fname, Minit, Lname, ← from composite Name
  Street, City, State, ← from composite Address
  Salary, Bdate, Sex
)
underlined = PK
dashed = skip
02
Weak Entity Types
Double-bordered rectangle + double-diamond relationship
Always

Weak entities can't be identified alone — they need their owner. Their PK is always a composite key made of the owner's PK plus their own partial key.

Formula
1
Create a table for the weak entity
2
Add owner's PK as FK in this table
3
Composite PK = {owner PK + partial key}
4
Add all other attributes
Example: DEPENDENT is weak, EMPLOYEE is owner
DEPENDENT(
  Essn, ← FK to EMPLOYEE.SSN
  Dependent_name, ← partial key
  Sex, Bdate, Relationship
)
PK = {Essn, Dependent_name}
⚠️ The weak entity must have total participation in the identifying relationship — always look for that double line!
03
Binary 1:1 Relationship
When both sides show "1" — three sub-approaches
Choose one of 3
Approach A: Foreign Key
Most common ✓
Use when: One side is TOTAL, other is PARTIAL

FK goes in the TOTAL-participation entity.

DEPARTMENT(Dnumber, Dname, Mgr_ssn FK, Mgr_start_date)

Department has total participation in MANAGES → gets the FK to Employee.
Approach B: Merged Table
Both TOTAL
Use when: Both sides are TOTAL (double lines both sides)

Merge both entities and the relationship into one single table.

Include all attributes from both + relationship attributes.

Choose either entity's PK as the combined PK.
Approach C: Cross-Reference
Both PARTIAL
Use when: Both sides are PARTIAL (single lines both sides)

Create a 3rd relationship table with both FKs.

MANAGES(Essn FK, Dnumber FK, Start_date)

PK = either FK (since it's 1:1, either is unique).
💡 Exam tip: Default to Approach A unless told otherwise. Always put FK on the total-participation side to minimize NULLs.
04
Binary 1:N Relationship
One side shows "1", other shows "N" — FK on the N side
Always FK on N-side

This is the most common relationship. The rule is simple: FK always goes in the entity on the N-side (many side). The FK references the PK of the 1-side entity.

Why N-side? Visual explanation
If you put FK in the 1-side:
One CUSTOMER row would need to store many order IDs → multi-valued → ILLEGAL in relational model.

If you put FK in the N-side:
Each ORDER row has exactly one customer ID → clean, atomic. ✓
Example: CUSTOMER (1) → ORDER (N)
CUSTOMER(CustomerID, Name)
ORDER(
  OrderID,
  OrderDate,
  CustomerID ← FK to CUSTOMER
)
Include any relationship attributes in the N-side table.
05
Binary M:N Relationship
Both sides show "M" or "N" — always a new relationship table
New table always

Many-to-many cannot be expressed with just two tables — you MUST create a third relationship table (junction/associative table).

What the new table contains
1
FK → PK of Entity A
2
FK → PK of Entity B
3
Composite PK = {FK-A + FK-B}
4
All relationship attributes become columns
Example: EMPLOYEE (M) WORKS_ON (N) PROJECT
EMPLOYEE(SSN, Name)
PROJECT(Pnumber, Pname)

WORKS_ON(
  Essn, ← FK to EMPLOYEE
  Pno, ← FK to PROJECT
  Hours ← relationship attribute
)
PK = {Essn, Pno}
06
Multivalued Attributes
Double-oval attribute → always its own table
New table always

A double-oval (multivalued attribute) like "Locations" of a department cannot be stored as a column. It violates 1NF. Always extract it into a new table.

Formula
1
New table for the attribute
2
Add the attribute as a column
3
Add entity's PK as FK
4
Composite PK = {attribute + FK}
Example: DEPARTMENT has {Locations}
DEPARTMENT(Dnumber, Dname)

DEPT_LOCATIONS(
  Dnumber, ← FK to DEPARTMENT
  Dlocation ← the multivalued attr
)
PK = {Dnumber, Dlocation}
07
N-ary (Ternary+) Relationships
Diamond connecting 3+ entities → always a new table
New table always

When a diamond touches 3 or more entity rectangles, create a new relationship table. The PK is typically the combination of ALL foreign keys.

Formula
1
New relationship table
2
FK → PK of each participating entity
3
PK = combination of all FKs (unless one side is 1, then exclude that FK from PK)
4
Add all relationship attributes
Example: SUPPLIER supplies PART to PROJECT
SUPPLY(
  Sname, ← FK to SUPPLIER
  PartNo, ← FK to PART
  ProjName, ← FK to PROJECT
  Quantity
)
PK = {Sname, PartNo, ProjName}
The key rules
Primary Keys & Foreign Keys — The Full Rulebook
🔑 Primary Key (PK) Rules
  • Strong entity → its underlined attribute is PK
  • Composite key in ER → combine those attributes as composite PK
  • Weak entity → {owner's PK} + {partial key}
  • M:N relationship table → {FK-A + FK-B} (both FKs combined)
  • Multivalued attribute table → {attribute + entity FK}
  • N-ary table → all FKs combined (usually)
  • 1:1 merged table → either entity's PK (choose one)
  • 1:1 cross-reference table → either FK (since 1:1, unique)
🔗 Foreign Key (FK) Rules
  • FK references the PK of another table (must match)
  • 1:1 (total one side) → FK in the total-participation entity
  • 1:N → FK in the N-side (many side) entity
  • M:N table → has TWO FKs, one to each entity
  • Weak entity → FK is the owner's PK (also part of PK here)
  • Multivalued table → FK is the owning entity's PK
  • FK can be NULL only if participation is PARTIAL on that side
  • Rename FK if needed to avoid naming conflicts (e.g., Essn, Dno)
⚠️ NULL in Foreign Keys
PARTIAL
FK can be NULL — entity doesn't have to participate
TOTAL
FK cannot be NULL — entity must participate (NOT NULL)

Example: If EMPLOYEE has partial participation in MANAGES (not every employee manages), the FK Dept_managed in EMPLOYEE can be NULL for non-managers.
⚠️ Prefer putting FK on the total-participation side to reduce NULLs.
🔄 Unary (Self-Referencing) Relationships
When a diamond connects back to the SAME entity type:
1:N Unary (e.g., EMPLOYEE supervises EMPLOYEE)
EMPLOYEE(
  SSN, Name, ...,
  Super_SSN ← FK to EMPLOYEE.SSN
)
FK references OWN table's PK. NULL for top-level.
M:N Unary (e.g., ITEM contains ITEM)
ITEM(Item_No, Name)
COMPONENT(
  Component_No, ← FK to ITEM
  Item_No, ← FK to ITEM
  Quantity
)
EER Extended Concepts
Specialization & Generalization — 4 Mapping Options
When you see a hierarchy with a circle (d or o)

You have a superclass C with m subclasses {S1, S2, …, Sm}. Choose ONE of the four options based on the constraints shown in the diagram.

Option 8A — Multiple tables, all of them
Use when: Any constraint (works always)

Create table for superclass C + separate table for each subclass.

Each subclass table has: PK (same as superclass PK) + subclass-specific attributes.
Subclass PK is also a FK to superclass.

EMPLOYEE(SSN, Name, ...)
SECRETARY(SSN, TypingSpeed)
ENGINEER(SSN, EngType)
Option 8B — Subclasses only
Use when: Total specialization only (double line)

NO superclass table. Each subclass gets superclass attributes copied in.

Works because every superclass member is guaranteed to be in a subclass.

CAR(VIN, Make, Year, MaxPassengers)
TRUCK(VIN, Make, Year, Tonnage)
← both inherit VEHICLE's attrs
Option 8C — Single table + type column
Use when: Disjoint subclasses

One big table with ALL attributes from ALL subclasses + a "type" discriminator column.

Many NULLs for attributes that don't apply.

EMPLOYEE(SSN, Name, ...,
  JobType, ← 'Secretary'/'Engineer'
  TypingSpeed, EngType)
Option 8D — Single table + boolean flags
Use when: Overlapping subclasses (o in circle)

One table + one boolean flag column per subclass (1=belongs, 0=doesn't).

Entity can be both (Mflag=1 AND Pflag=1).

PART(PartNo, Name, ...,
  Mflag, ← 1 if manufactured
  Pflag, ← 1 if purchased
  ManufacturedBy, PurchasedFrom)
💡 Choosing the right option: Total specialization? Use 8B (clean, no superclass needed). Disjoint? Use 8A or 8C. Overlapping? Use 8D. When in doubt, 8A always works.
Step 9
Union Types (Categories) — Step 9
∪ Union Type — Subclass of multiple different superclasses

A category (UNION type) is when one subclass can belong to one of several different entity types. Example: OWNER can be a PERSON, BANK, or COMPANY — but not all at once.

Key insight: different superclasses = different keys
Because PERSON, BANK, and COMPANY all have different PKs, we can't inherit a single key.

Solution: Create a surrogate key — a new artificial identifier for the category table.

Each superclass entity gets the surrogate key added as a FK (to link back to the category).
Example: OWNER category
PERSON(SSN, Name, OwnerID FK)
BANK(BankCode, Name, OwnerID FK)
COMPANY(CompNo, Name, OwnerID FK)

OWNER(OwnerID, Address)
← surrogate key! own new PK
ℹ️ If all superclasses share the same PK attribute, you can use that instead of a surrogate key.
Write the code
DDL Templates — Copy & Adapt
🏛️ Create Table — Basic
CREATE TABLE TableName (
  col1 INT NOT NULL,
  col2 VARCHAR(50) NOT NULL,
  col3 DATE,
  PRIMARY KEY (col1)
);
🔗 Add Foreign Key
CREATE TABLE Order (
  OrderID INT NOT NULL,
  CustID INT,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (CustID)
    REFERENCES Customer(CustID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
🔑 Composite Primary Key
CREATE TABLE WORKS_ON (
  Essn CHAR(9) NOT NULL,
  Pno INT NOT NULL,
  Hours DECIMAL(3,1),
  PRIMARY KEY (Essn, Pno),
  FOREIGN KEY (Essn)
    REFERENCES EMPLOYEE(SSN),
  FOREIGN KEY (Pno)
    REFERENCES PROJECT(Pnumber)
);
📋 Data Types Quick Reference
Type  Use for
INTwhole numbers, IDs CHAR(n)fixed-length strings VARCHAR(n)variable strings DATEYYYY-MM-DD DECIMAL(m,d)money, hours BOOLEANflags (8D option) ENUM(...)type discriminator FLOAT/DOUBLEdecimals
🛠️ DDL Constraint Keywords
NOT NULLColumn must have a value
UNIQUENo duplicate values (candidate key)
DEFAULT valValue if none provided
ON DELETE CASCADEDelete child rows when parent deleted
ON DELETE SET NULLSet FK to NULL when parent deleted
ON DELETE RESTRICTReject deletion if child rows exist
CHECK (condition)Enforce a custom rule on values
✏️ ALTER & DROP
-- Add a column
ALTER TABLE T ADD col INT;

-- Modify a column
ALTER TABLE T MODIFY col VARCHAR(100);

-- Drop a column
ALTER TABLE T DROP COLUMN col;

-- Drop entire table
DROP TABLE T;

-- Clear all rows, keep table
TRUNCATE TABLE T;
Putting it all together
Complete Walkthrough Example
Worked Example
Company Database — ER → Relational

Given: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT entities with WORKS_FOR (1:N), MANAGES (1:1), WORKS_ON (M:N), CONTROLS (1:N), SUPERVISION (1:N unary), DEPENDENTS_OF (weak) relationships.

Step 1 — Strong Entities
EMPLOYEE(SSN, Fname, Minit, Lname, Address, Salary, Bdate, Sex)

DEPARTMENT(Dnumber, Dname)

PROJECT(Pnumber, Pname, Plocation)
Step 2 — Weak Entity
DEPENDENT(
  Essn, ← FK → EMPLOYEE.SSN
  Dep_name, ← partial key
  Sex, Bdate, Relationship
)
PK = {Essn, Dep_name}
Step 3 — 1:1 MANAGES (FK approach)
DEPARTMENT has total participation
→ FK goes in DEPARTMENT

DEPARTMENT(Dnumber, Dname,
  Mgr_ssn, ← FK→EMPLOYEE.SSN
  Mgr_start_date)
Step 4 — 1:N WORKS_FOR & CONTROLS
N-side gets FK:
EMPLOYEE(..., Dno)
← FK → DEPARTMENT.Dnumber

PROJECT(..., Dnum)
← FK → DEPARTMENT.Dnumber
Step 4 — 1:N Unary SUPERVISION
Self-FK on same table:
EMPLOYEE(...,
  Super_ssn)
← FK → EMPLOYEE.SSN
NULL for top-level supervisors
Step 5 — M:N WORKS_ON
New table required:
WORKS_ON(
  Essn, ← FK→EMPLOYEE.SSN
  Pno, ← FK→PROJECT.Pnumber
  Hours
)
PK = {Essn, Pno}
Step 6 — Multivalued {Locations}
New table required:
DEPT_LOCATIONS(
  Dnumber, ← FK→DEPT
  Dlocation
)
PK = {Dnumber, Dlocation}
Final Relation Count: 7 tables total
EMPLOYEE(SSN, Fname, Minit, Lname, Address, Salary, Bdate, Sex, Dno, Super_ssn)
DEPARTMENT(Dnumber, Dname, Mgr_ssn, Mgr_start_date)
PROJECT(Pnumber, Pname, Plocation, Dnum)
DEPENDENT(Essn, Dep_name, Sex, Bdate, Relationship)
WORKS_ON(Essn, Pno, Hours)
DEPT_LOCATIONS(Dnumber, Dlocation)
underlined = Primary Key
italic green = Foreign Key
orange name = Table name
Before you submit
Mapping Checklist — Don't Forget Anything
✅ Every entity
  • Strong entity → table with all simple attributes
  • Composite attribute expanded into simple parts
  • Derived attribute skipped (not stored)
  • Multivalued attribute gets its OWN table (Step 6)
  • Weak entity → combined PK with owner's PK
✅ Every relationship
  • 1:1 → FK approach (total side gets FK)
  • 1:N → FK in N-side entity
  • M:N → new relationship table, composite PK
  • N-ary → new table, all entity PKs as FKs
  • Unary 1:N → self-referencing FK same table
  • Unary M:N → new table, two FKs to same entity
  • Relationship attributes included correctly
✅ Keys & Constraints
  • Every table has a Primary Key defined
  • All FKs reference existing PKs
  • Total participation → NOT NULL on that FK
  • Partial participation → FK can be NULL
  • EER hierarchy mapped with correct option (8A/B/C/D)
  • Union type has surrogate key if PKs differ