SQL Server Interview Question #164

What is Third Normal Form (3NF)?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

Third Normal Form requires the table to satisfy 2NF and removes inappropriate transitive dependencies of non-key attributes on other non-key attributes.

For example, if an Employees table stores DepartmentId and DepartmentName, and DepartmentName is determined by DepartmentId, department information normally belongs in a Departments table. Employees then stores DepartmentId as a foreign key.

Code Example

CREATE TABLE dbo.Departments
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Employees
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    DepartmentId INT NOT NULL,
    CONSTRAINT FK_Employees_Departments
        FOREIGN KEY (DepartmentId)
        REFERENCES dbo.Departments(Id)
);