The Receptionist's Notebook

The Vocabulary You Need First

Partial vs Transitive Dependency

Three Normal Forms at a Glance

Employee–Project — The Fat Table

1NF — Make Every Cell Atomic

Violation: EmpPhones holds a list. Fix: lift phones into their own table, one phone per row.

2NF — Kill the Partial Dependencies

Key is {EmpID, ProjID}, but many columns depend on only half of it. Split by which key-part each group needs.

3NF — Remove the Middle-Man

Transitive chains: EmpID→DeptID→DeptName and ProjID→MgrID→MgrName. Lift each non-key determinant into its own table.

The 3NF Schema in SQL

CREATE TABLE Department (
    DeptID    VARCHAR(10) PRIMARY KEY,
    DeptName  VARCHAR(50)
);
CREATE TABLE Manager (
    ManagerID    VARCHAR(10) PRIMARY KEY,
    ManagerName  VARCHAR(50)
);
CREATE TABLE Employee (
    EmpID   VARCHAR(10) PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID  VARCHAR(10) REFERENCES Department(DeptID)
);
CREATE TABLE Project (
    ProjectID VARCHAR(10) PRIMARY KEY,
    ProjName  VARCHAR(50),
    ManagerID VARCHAR(10) REFERENCES Manager(ManagerID)
);
CREATE TABLE EmpPhone (
    EmpID    VARCHAR(10) REFERENCES Employee(EmpID),
    EmpPhone VARCHAR(20),
    PRIMARY KEY (EmpID, EmpPhone)
);
CREATE TABLE Assignment (
    EmpID       VARCHAR(10) REFERENCES Employee(EmpID),
    ProjectID   VARCHAR(10) REFERENCES Project(ProjectID),
    HoursWorked INT,
    PRIMARY KEY (EmpID, ProjectID)
);

University Classroom — A Key Twist

Same Recipe, Six Final Tables

Student–Course — The Instructor Chain

Decomposed to Five Clean Tables

One Recipe That Solved All Three

Academic vs Industry Lenses

Six Golden Rules of Normalization

One Fact, One Place

🗄️ End of tutorial · Press ← to review, or click Restart