notes.npwitknpwitk.com ↗

09 Normalization in Database

Updated 4 Oct 2026

Lecture 9: Normalization in Database

Course: CSS 325 - Database Systems
Semester: 1/2025
Instructor: Asst.Prof.Dr. Preecha Tangworakitthaworn
Email: preecha.aj@siit.tu.ac.th

Outline

  • Concept of Normalization and Denormalization
  • Normalization Process

Review: Previous Concepts

From the previous lecture, we discussed:

  • Problems of poor database design
  • Database Keys
  • Functional Dependencies (FDs)
  • Functional Dependency Diagram

Problems of Poor Database Design

Key Issues

  1. Unclear semantics

    • Conceptually: attributes, entity types, and relationship types
    • Logically: view and base relations

  2. Data redundancy and update anomalies

  3. NULL values in tuples

  4. Generation of spurious tuples

Data Quality Issues

1. Spurious Data (Duplication)

Example:

idnameemail
101XX@gmail.com
102YX@gmail.com
103YX@gmail.com

Problem: Duplicate data that shouldn't exist

2. Sparse Data (Many NULLs)

Example:

idnameemail
101NULLNULL
102YX@gmail.com
103NULLNULL

Problem: Too many NULL values wasting space

Ideal Solution: Dense Data

idnameemail
101XX@gmail.com
102YY@gmail.com
103ZZ@gmail.com

Case Study: Invoice Data

Initial Invoice Table (NOT Relational)

The first version shown is not a relational table because:

  • It contains repeating groups (multiple products per order in same row)
  • Violates First Normal Form

Poor INVOICE Table (Relational but Poorly Designed)

OrderIDOrder DateCustomer IDCustomer NameCustomer AddressProductIDProduct DescriptionProduct FinishProduct StandardPriceOrdered Quantity
100610/24/20102Value FurniturePlano, TX7Dining TableNatural Ash800.002
100610/24/20102Value FurniturePlano, TX5Writer's DeskCherry325.002
100610/24/20102Value FurniturePlano, TX4Entertainment CenterNatural Maple650.001
100710/25/20106Furniture GalleryBoulder, CO114-Dr DresserOak500.004
100710/25/20106Furniture GalleryBoulder, CO4Entertainment CenterNatural Maple650.003

Why is this poorly structured?

  • Contains spurious data (redundant information)
  • Customer information repeated for each product
  • Product information repeated for each order

How to fix it? → Use Normalization!

What is Normalization?

Definition

Normalization is the process for evaluating and correcting relation schemas to:

  • Minimize data redundancies
  • Minimize data anomalies
  • Achieve good database design

Key Concepts

  • Based on normal forms (NFs) (standard form of tables)
  • Based on functional dependencies (FDs) (concept from last week)
  • Involves decomposition/splitting of tables

Normal Forms

Essential Normal Forms

  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)

Advanced Normal Forms

  • Boyce-Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  • Fifth Normal Form (5NF)
  • Domain Key Normal Form (DKNF)

Note: Advanced forms depend on business domain requirements and specific needs

Important Notes

  • Higher normal forms are better than lower normal forms (structural point of view)
  • 3NF is generally sufficient for most business database systems
  • Denormalization: Process of going from higher NF to lower NF
    • Results in increased performance
    • But causes greater data redundancy
    • Trade-off: 3NF ← denormalization → 1NF

Relationships of Normal Forms

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}
\draw (0,0) circle (4cm) node[yshift=3.5cm] {1NF};
\draw (0,0) circle (3.2cm) node[yshift=2.8cm] {2NF};
\draw (0,0) circle (2.4cm) node[yshift=2cm] {3NF/BCNF};
\draw (0,0) circle (1.6cm) node[yshift=1.2cm] {4NF};
\draw (0,0) circle (0.8cm) node[yshift=0.4cm] {5NF};
\draw (0,0) circle (0.3cm) node {DKNF};
\end{tikzpicture}
\end{document}
Normal FormCharacteristic
1NFTable format, no repeating groups, and PK identified
2NF1NF and no partial dependencies
3NF2NF and no transitive dependencies
BCNFEvery determinant is a candidate key (special case of 3NF)
4NF3NF and no independent multivalued dependencies

Review: Functional Dependencies (FDs)

Definition

Suppose relational schema RR has nn attributes A1,A2,…,AnA_1, A_2, \ldots, A_n; that is, R={A1,A2,…,An}R = \{A_1, A_2, \ldots, A_n\}

Functional dependency is a constraint between two sets of attributes from the database.

Denoted by: X→YX \rightarrow Y where X⊆RX \subseteq R and Y⊆RY \subseteq R

Meaning

For any two tuples t1t_1 and t2t_2 in relation state r(R)r(R):

  • If t1[X]=t2[X]t_1[X] = t_2[X]
  • Then t1[Y]=t2[Y]t_1[Y] = t_2[Y]

Interpretation:

  • Values of YY depend on values of XX (Y is functionally dependent on X)
  • Values of XX determine values of YY (X functionally determines Y)

Example

StudentID→Name\text{StudentID} \rightarrow \text{Name}

SELECT name FROM student WHERE StudentID = 101;

Should return only 1 record

Examples with Relation State

Given relation:

ABCD
a1b1c1d1
a1b2c2d2
a2b2c2d3
a3b3c4d3

FDs that do NOT hold:

  • A→BA \rightarrow B (same A has different B values)
  • B,C→DB, C \rightarrow D
  • C→DC \rightarrow D
  • D→A,BD \rightarrow A, B
  • D→AD \rightarrow A

FDs that MAY hold:

  • B→CB \rightarrow C
  • A,B→CA, B \rightarrow C
  • B→AB \rightarrow A
  • A,C→DA, C \rightarrow D
  • A,D→BA, D \rightarrow B

Functional Dependency Diagram

Basic FD Notation

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node[draw, minimum width=1.5cm, minimum height=0.8cm] (x) at (0,0) {X};
\node[draw, minimum width=1.5cm, minimum height=0.8cm] (y) at (3,0) {Y};
\draw[->] (x) -- (y) node[midway, above] {FD};
\end{tikzpicture}
\end{document}

X→YX \rightarrow Y means:

  • X determines Y
  • Y is dependent on X

Example: Project-Employee Dependency Diagram

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick, node distance=0.2cm]
% Boxes
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=yellow!30] (projnum) {PROJ\_NUM};
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=cyan!30, right=of projnum] (projname) {PROJ\_NAME};
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=yellow!30, right=of projname] (empnum) {EMP\_NUM};
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=cyan!30, right=of empnum] (empname) {EMP\_NAME};
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=cyan!30, right=of empname] (jobclass) {JOB\_CLASS};
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=cyan!30, right=of jobclass] (chghour) {CHG\_HOUR};
\node[draw, minimum width=1.8cm, minimum height=0.8cm, fill=cyan!30, right=of chghour] (hours) {HOURS};
 
% Arrows for full dependency
\draw[<-] (projname.north) -- ++(0,0.8) -| (projnum.north);
\draw[<-] (empname.north) -- ++(0,1.2) -| (projnum.north);
\draw[<-] (jobclass.north) -- ++(0,1.6) -| (projnum.north);
\draw[<-] (chghour.north) -- ++(0,2.0) -| (projnum.north);
\draw[<-] (hours.north) -- ++(0,2.4) -| (projnum.north);
 
% Arrows for partial dependencies
\draw[<-] (projname.south) -- ++(0,-0.5) -| (projnum.south);
\draw[<-] (empname.south) -- ++(0,-0.8);
\draw[<-] (jobclass.south) -- ++(0,-0.8);
\draw[<-] (chghour.south) -- ++(0,-1.2);
 
\draw[<-] (empname.south) -- ++(0,-1.0) -| (empnum.south);
\draw[<-] (jobclass.south) -- ++(0,-1.0) -| (empnum.south);
\draw[<-] (chghour.south) -- ++(0,-1.0) -| (empnum.south);
 
% Transitive dependency
\draw[<-] (chghour.south) to[out=-90, in=-90] (jobclass.south);
 
\node[below=2.5cm of projnum] {Partial dependency};
\node[below=2.5cm of chghour] {Transitive dependency};
\node[below=2.5cm of empnum] {Partial dependencies};
\end{tikzpicture}
\end{document}

Functional Dependencies:

PROJ_NUM→PROJ_NAME\text{PROJ\_NUM} \rightarrow \text{PROJ\_NAME}
EMP_NUM→EMP_NAME, JOB_CLASS, CHG_HOUR\text{EMP\_NUM} \rightarrow \text{EMP\_NAME, JOB\_CLASS, CHG\_HOUR}
JOB_CLASS→CHG_HOUR\text{JOB\_CLASS} \rightarrow \text{CHG\_HOUR} (Transitive)

Types of Functional Dependencies

Type 1: Partial Dependency

Definition: X→YX \rightarrow Y when XX is part of a key (composite key)

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node[draw, minimum width=1.5cm] (x) {X};
\node[draw, minimum width=1.5cm, right=0.2cm of x] (y) {Y};
\node[draw, minimum width=1.5cm, right=0.2cm of y] (z) {Z};
 
\draw[<-] (y.south) -- ++(0,-0.8) -| (x.south);
\node[below=1cm of x] {\underline{Key}};
\node[above=0.5cm of y] {FD (Partial)};
\end{tikzpicture}
\end{document}

Characteristic: "Key determines non-key"

Example:

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node[draw, minimum width=1.5cm] (id) {ID};
\node[draw, minimum width=1.5cm, right=0.2cm of id] (name) {Name};
\node[draw, minimum width=1.5cm, right=0.2cm of name] (email) {Email};
\node[draw, minimum width=1.5cm, right=0.2cm of email] (addr) {Address};
 
\draw[<-] (name.north) -- ++(0,0.5) -| (id.north);
\draw[<-] (email.north) -- ++(0,0.8) -| (id.north);
\draw[<-] (addr.north) -- ++(0,1.1) -| (id.north);
 
\node[below=0.3cm of id] {\underline{PK}};
\end{tikzpicture}
\end{document}
  • FD1 is called Partial Dependency
  • FD2 is called Partial Dependency
  • FD3 is called Transitive Dependency

Type 2: Transitive Dependency

Definition: X→YX \rightarrow Y when XX is a non-key attribute

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node[draw, minimum width=1.5cm] (x) {X};
\node[draw, minimum width=1.5cm, right=0.2cm of x] (y) {Y};
\node[draw, minimum width=1.5cm, right=0.2cm of y] (z) {Z};
 
\draw[<-] (y.south) -- ++(0,-0.8) -| (x.south);
\draw[<-] (z.south) -- ++(0,-0.8) -| (y.south);
 
\node[below=1cm of x] {Key};
\node[above=0.5cm of y] {FD1};
\node[above=0.5cm of z] {FD2 (Transitive)};
\end{tikzpicture}
\end{document}

Characteristic: "Non-key determines non-key"

Type Special: Full Dependency

Domain Concept:

Domain 1 (Keys) and Domain 2 (Non-keys)

\usepackage{tikz}
\usetikzlibrary{shapes.geometric}
\begin{document}
\begin{tikzpicture}
% Domain 1
\node[ellipse, draw, minimum width=3cm, minimum height=2cm] (d1) at (0,0) {};
\node at (0,1.5) {Domain 1};
\node[draw, minimum width=1cm] at (-0.5,0) {X};
\node[draw, minimum width=1cm] at (0.5,0) {Y};
\node[draw, minimum width=1cm] at (0,-0.5) {Z};
 
% Domain 2
\node[ellipse, draw, minimum width=2.5cm, minimum height=1.8cm] (d2) at (5,0) {};
\node at (5,1.5) {Domain 2};
\node[draw, minimum width=0.8cm] at (4.7,0) {1};
\node[draw, minimum width=0.8cm] at (5.3,0) {2};
 
% Arrow
\draw[<-, thick] (d2) -- (d1);
 
\node[right=0.5cm of d2] {Full FD};
\end{tikzpicture}
\end{document}
  • FD1 is Partial Dependency
  • FD is Full Dependency

Full vs Partial Example:

\usepackage{tikz}
\usetikzlibrary{shapes.geometric, positioning}
\begin{document}
\begin{tikzpicture}
% First example - Partial + Full
\node[ellipse, draw, minimum width=3cm, minimum height=2cm] (d1a) at (0,0) {};
\node at (0,1.5) {Domain 1};
\node[draw] at (-0.5,0) {X};
\node[draw] at (0.5,0) {Y};
\node[draw] at (0,-0.6) {Z};
 
\node[ellipse, draw, minimum width=2.5cm, minimum height=1.8cm] (d2a) at (5,0) {};
\node at (5,1.5) {Domain 2};
\node[draw] at (4.7,0) {1};
\node[draw] at (5.3,0) {2};
 
\draw[<-, thick] (4.7,0) to[out=180, in=0] (-0.5,0);
\draw[<-, thick] (5.3,0) to[out=180, in=0] (0,0.3);
\draw[<-, thick] (5.3,0.3) to[out=180, in=30] (0,-0.3);
 
\node[below=2.5cm of d1a] {Partial: Full};
 
% Second example - BCNF Full
\node[ellipse, draw, minimum width=3cm, minimum height=2cm] (d1b) at (0,-6) {};
\node at (0,-4.5) {Domain 1};
\node[draw] at (-0.5,-6) {X};
\node[draw] at (0.5,-6) {Y};
\node[draw] at (0,-6.6) {Z};
 
\node[ellipse, draw, minimum width=3cm, minimum height=2cm] (d2b) at (5,-6) {};
\node at (5,-4.5) {Domain 2};
\node[draw] at (4.7,-6) {1};
\node[draw] at (5.3,-6) {2};
\node[draw] at (6,-6) {A};
 
\draw[<-, thick] (4.7,-6) to[out=180, in=0] (-0.5,-6);
\draw[<-, thick] (5.3,-6) to[out=180, in=0] (0.5,-6);
\draw[<-, thick] (6,-6) to[out=180, in=0] (0,-6.3);
 
\node[below=2.5cm of d1b] {BCNF: Full};
\end{tikzpicture}
\end{document}

Normalization Process Steps

\usepackage{tikz}
\usetikzlibrary{shapes, arrows.meta, positioning}
\begin{document}
\begin{tikzpicture}[
    block/.style={rectangle, draw, minimum width=3cm, minimum height=1cm, text centered},
    process/.style={ellipse, draw, minimum width=3cm, minimum height=1cm, text centered},
    arrow/.style={-Stealth, thick}
]
 
% Left side - Essential NFs
\node[block] (table) at (0,0) {Table with\\multivalued\\attributes};
\node[block, below=0.8cm of table] (1nf) {First\\normal form};
\node[block, below=0.8cm of 1nf] (2nf) {Second\\normal form};
\node[block, below=0.8cm of 2nf] (3nf) {Third\\normal form};
 
\node[process, right=2cm of table] (remove1) {Remove\\multivalued\\attributes};
\node[process, right=2cm of 1nf] (remove2) {Remove\\partial\\dependencies};
\node[process, right=2cm of 2nf] (remove3) {Remove\\transitive\\dependencies};
\node[process, right=2cm of 3nf] (remove4) {Remove remaining\\anomalies resulting\\from multiple\\candidate keys};
 
\draw[arrow] (table) -- (1nf);
\draw[arrow] (1nf) -- (2nf);
\draw[arrow] (2nf) -- (3nf);
\draw[arrow, dashed] (table) -- (remove1);
\draw[arrow, dashed] (1nf) -- (remove2);
\draw[arrow, dashed] (2nf) -- (remove3);
\draw[arrow, dashed] (3nf) -- (remove4);
 
% Right side - Advanced NFs
\node[block, right=8cm of table] (bcnf) {Boyce-Codd\\normal form};
\node[block, below=0.8cm of bcnf] (4nf) {Fourth\\normal form};
\node[block, below=0.8cm of 4nf] (5nf) {Fifth\\normal form};
 
\node[process, right=2cm of bcnf] (rmv1) {Remove\\multivalued\\dependencies};
\node[process, right=2cm of 4nf] (rmv2) {Remove\\remaining\\anomalies};
 
\draw[arrow, dashed] (bcnf) -- (rmv1);
\draw[arrow, dashed] (4nf) -- (rmv2);
\draw[arrow] (bcnf) -- (4nf);
\draw[arrow] (4nf) -- (5nf);
 
% Red box around essential forms
\draw[red, thick] ([xshift=-0.3cm, yshift=0.3cm]table.north west) rectangle ([xshift=0.3cm, yshift=-0.3cm]3nf.south east);
 
\end{tikzpicture}
\end{document}

Important Note

3rd Normal Form is generally considered sufficient for most database systems

First Normal Form (1NF)

Definition

A relation is in 1NF if:

  • Domain of each attribute contains only atomic (simple, indivisible) values
  • Value of any attribute in a tuple must be a single value from the domain

Purpose

The goal of 1NF is to remove Multivalued Attributes!

Three Main Steps of 1NF

  1. Eliminate the repeating groups (or multivalued attributes)
  2. Identify the primary key
  3. Identify all dependencies

Example: Student Groups

Before 1NF (Not Relational):

GrBusinessID
1Banking5888037, 5888068, 5888079, 5888119
2Airlines5888139, 5888203, 5888204, 5888251

Problem: ID column contains multiple values (repeating group)

After 1NF (Normalized):

GrBusinessID
1Banking5888037
1Banking5888068
1Banking5888079
1Banking5888119
2Airlines5888139
2Airlines5888203
2Airlines5888204
2Airlines5888251

Note: This creates spurious data, but it's acceptable for 1NF

Requirements for 1NF

  • All key attributes are defined
  • No repeating groups in the table
  • All attributes are dependent on the primary key
  • All functional dependencies are identified (draw dependency diagram)

1NF Example with Dependency Diagram

Relation Schema

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick, node distance=0.15cm]
% Primary key boxes
\node[draw, minimum width=1.5cm, minimum height=0.7cm, fill=yellow!30] (stdssn) {StdSSN};
\node[draw, minimum width=1.5cm, minimum height=0.7cm, fill=cyan!30, right=of stdssn] (stdcity) {StdCity};
\node[draw, minimum width=1.5cm, minimum height=0.7cm, fill=cyan!30, right=of stdcity] (stdclass) {StdClass};
\node[draw, minimum width=1.6cm, minimum height=0.7cm, fill=yellow!30, right=of stdclass] (offerno) {OfferNo};
\node[draw, minimum width=1.5cm, minimum height=0.7cm, fill=cyan!30, right=of offerno] (offterm) {OffTerm};
\node[draw, minimum width=1.5cm, minimum height=0.7cm, fill=cyan!30, right=of offterm] (offyear) {OffYear};
\node[draw, minimum width=1.6cm, minimum height=0.7cm, fill=cyan!30, right=of offyear] (courseno) {CourseNo};
\node[draw, minimum width=1.5cm, minimum height=0.7cm, fill=cyan!30, right=of courseno] (crsdesc) {CrsDesc};
\node[draw, minimum width=1.6cm, minimum height=0.7cm, fill=cyan!30, right=of crsdesc] (enrgrade) {EnrGrade};
 
% Partial dependencies from StdSSN
\draw[<-] (stdcity.south) -- ++(0,-0.8) -| (stdssn.south);
\draw[<-] (stdclass.south) -- ++(0,-0.8) -| (stdssn.south);
 
% Partial dependencies from OfferNo
\draw[<-] (offterm.north) -- ++(0,0.8) -| (offerno.north);
\draw[<-] (offyear.north) -- ++(0,0.8) -| (offerno.north);
\draw[<-] (courseno.north) -- ++(0,0.8) -| (offerno.north);
\draw[<-] (crsdesc.north) -- ++(0,1.2) -| (offerno.north);
 
% Transitive dependency
\draw[<-] (crsdesc.south) to[out=-90, in=-90] (courseno.south);
 
% Full dependency
\draw[<-] (enrgrade.north) -- ++(0,1.5) -| (stdssn.north);
\draw[<-] (enrgrade.north) -- ++(0,1.5) -| (offerno.north);
 
\node[below=1.5cm of stdssn] {Partial};
\node[below=1.5cm of offerno] {Partial};
\node[below=1.5cm of courseno] {Transitive};
\end{tikzpicture}
\end{document}

Functional Dependencies

Partial:
StdSSN‾→StdCity, StdClass\underline{\text{StdSSN}} \rightarrow \text{StdCity, StdClass}

Partial:
OfferNo‾→OffTerm, OffYear, CourseNo, CrsDesc\underline{\text{OfferNo}} \rightarrow \text{OffTerm, OffYear, CourseNo, CrsDesc}

Transitive:
CourseNo→CrsDesc\text{CourseNo} \rightarrow \text{CrsDesc}

Full (Partial + Full):
StdSSN‾,OfferNo‾→EnrGrade\underline{\text{StdSSN}}, \underline{\text{OfferNo}} \rightarrow \text{EnrGrade}

Second Normal Form (2NF)

Definition

A relation schema RR is in 2NF if:

  • Every nonprime attribute in RR is fully functionally dependent on the primary key of RR

Recall: A nonprime attribute is an attribute that is NOT a member of any candidate key.

Visual Example

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node at (0,2) {\textbf{EMP\_PROJ}};
\node[draw, minimum width=1.2cm] (ssn) at (0,1) {Ssn};
\node[draw, minimum width=1.5cm] (pnumber) at (2,1) {Pnumber};
\node[draw, minimum width=1.2cm] (hours) at (4.5,1) {Hours};
\node[draw, minimum width=1.3cm] (ename) at (7,1) {Ename};
\node[draw, minimum width=1.4cm] (pname) at (9.5,1) {Pname};
\node[draw, minimum width=1.5cm] (ploc) at (12,1) {Plocation};
 
% FD1 - Partial
\draw[<-] (hours.south) -- ++(0,-0.6) node[below] {FD1} -| (ssn.south);
\draw[<-] (hours.south) -- ++(0,-0.6) -| (pnumber.south);
 
% FD2 - Partial
\draw[<-] (ename.north) -- ++(0,0.6) node[above] {FD2} -| (ssn.north);
 
% FD3 - Partial
\draw[<-] (pname.north) -- ++(0,0.6) node[above] {FD3} -| (pnumber.north);
\draw[<-] (ploc.north) -- ++(0,0.6) -| (pnumber.north);
 
\end{tikzpicture}
\end{document}

This relation is NOT in 2NF because:

  • FD2 and FD3 make nonprime attributes partially dependent on the primary key
  • Primary key is (Ssn, Pnumber), but Ename only depends on Ssn
  • Primary key is (Ssn, Pnumber), but Pname and Plocation only depend on Pnumber

Conversion to 2NF

Steps

  1. Make new tables to eliminate partial dependencies (for nonprime attributes)
  2. Reassign corresponding dependent attributes

Requirements for 2NF

A table is in 2NF when it:

  • Is in 1NF
  • Includes no partial dependencies

Example: Invoice to 2NF

Original 1NF Table with Partial Dependencies

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick, node distance=0.1cm]
\node[draw] (oid) {OrderID};
\node[draw, right=of oid] (odate) {OrderDate};
\node[draw, right=of odate] (cid) {CustomerID};
\node[draw, right=of cid] (cname) {CustomerName};
\node[draw, right=of cname] (caddr) {CustomerAddress};
\node[draw, right=of caddr] (pid) {ProductID};
\node[draw, right=of pid] (pdesc) {ProductDesc};
\node[draw, right=of pdesc] (pfinish) {ProductFinish};
\node[draw, right=of pfinish] (pprice) {ProductPrice};
\node[draw, right=of pprice] (oqty) {OrderedQty};
 
% Full dependency bracket
\draw[<-, thick] (oqty.north) -- ++(0,1.8) node[above, align=center] {Full\\Dependency} -| (oid.north);
\draw[<-, thick] (oqty.north) -- ++(0,1.8) -| (pid.north);
 
% Partial dependencies from OrderID
\draw[<-, cyan, thick] (odate.south) -- ++(0,-0.8) -| (oid.south);
\draw[<-, cyan, thick] (cid.south) -- ++(0,-0.8) -| (oid.south);
\draw[<-, cyan, thick] (cname.south) -- ++(0,-0.8) -| (oid.south);
\draw[<-, cyan, thick] (caddr.south) -- ++(0,-0.8) -| (oid.south);
 
% Partial dependencies from ProductID
\draw[<-, cyan, thick] (pdesc.south) -- ++(0,-1.2) -| (pid.south);
\draw[<-, cyan, thick] (pfinish.south) -- ++(0,-1.2) -| (pid.south);
\draw[<-, cyan, thick] (pprice.south) -- ++(0,-1.2) -| (pid.south);
 
% Transitive dependencies
\draw[<-, red, thick] (cname.north) to[out=90, in=90] (cid.north);
\draw[<-, red, thick] (caddr.north) to[out=90, in=90] (cid.north);
 
\node[below=1.8cm of oid, cyan] {Partial Dependencies};
\node[below=1.8cm of pid, cyan] {Partial Dependencies};
\node[above=1.2cm of cname, red] {Transitive Dependencies};
 
\end{tikzpicture}
\end{document}

Functional Dependencies:

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}
\node[align=left] at (0,0) {
$\underline{\text{OrderID}} \rightarrow \text{OrderDate, CustomerID, CustomerName, CustomerAddress}$\\[0.3cm]
$\text{CustomerID} \rightarrow \text{CustomerName, CustomerAddress}$\\[0.3cm]
$\underline{\text{ProductID}} \rightarrow \text{ProductDescription, ProductFinish, ProductStandardPrice}$\\[0.3cm]
$\underline{\text{OrderID}}, \underline{\text{ProductID}} \rightarrow \text{OrderQuantity}$
};
\end{tikzpicture}
\end{document}

Therefore, this is NOT in 2nd Normal Form

After Converting to 2NF

Removing Partial Dependencies → Getting into Second Normal Form

ORDERLINE (3NF):

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}
\node[draw, minimum width=1.5cm] at (0,0) {\underline{OrderID}};
\node[draw, minimum width=1.5cm] at (2,0) {\underline{ProductID}};
\node[draw, minimum width=2cm] at (4.5,0) {Ordered Quantity};
\end{tikzpicture}
\end{document}

PRODUCT (3NF):

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}
\node[draw, minimum width=1.8cm] at (0,0) {\underline{ProductID}};
\node[draw, minimum width=2.2cm] at (2.5,0) {ProductDescription};
\node[draw, minimum width=2cm] at (5.2,0) {ProductFinish};
\node[draw, minimum width=2.5cm] at (7.8,0) {Product StandardPrice};
\end{tikzpicture}
\end{document}

CUSTOMERORDER (2NF):

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node[draw] (oid) {\underline{OrderID}};
\node[draw, right=0.1cm of oid] (odate) {OrderDate};
\node[draw, right=0.1cm of odate] (cid) {CustomerID};
\node[draw, right=0.1cm of cid] (cname) {CustomerName};
\node[draw, right=0.1cm of cname] (caddr) {CustomerAddress};
 
% Transitive Dependencies
\draw[<-, red, thick] (cname.south) -- ++(0,-0.8) -| (cid.south);
\draw[<-, red, thick] (caddr.south) -- ++(0,-0.8) -| (cid.south);
 
\node[below=1.3cm of cid, red] {Transitive Dependencies};
\end{tikzpicture}
\end{document}

Important Note

Partial dependencies are removed, but there are still transitive dependencies

A partial dependency can exist only when a table's primary key is composed of several attributes. A table with a single-attribute primary key is automatically in 2NF once it is in 1NF.

Third Normal Form (3NF)

Definition

3NF is based on the concept of transitive dependency.

X→YX \rightarrow Y in a relation schema RR is a transitive dependency if there exists a set of attributes ZZ in RR that is:

  • Neither a candidate key
  • Nor a subset of any key of RR
  • And both X→ZX \rightarrow Z and Z→YZ \rightarrow Y hold

Visual Example

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node at (0,2) {\textbf{EMP\_DEPT}};
\node[draw] (ename) at (0,1) {Ename};
\node[draw] (ssn) at (1.5,1) {\underline{Ssn}};
\node[draw] (bdate) at (3,1) {Bdate};
\node[draw] (addr) at (4.5,1) {Address};
\node[draw] (dnum) at (6.5,1) {Dnumber};
\node[draw] (dname) at (8.5,1) {Dname};
\node[draw] (dmgr) at (10.5,1) {Dmgr\_ssn};
 
% Dependencies from Ssn
\draw[<-] (ename.south) -- ++(0,-0.6) -| (ssn.south);
\draw[<-] (bdate.south) -- ++(0,-0.6) -| (ssn.south);
\draw[<-] (addr.south) -- ++(0,-0.6) -| (ssn.south);
\draw[<-] (dnum.south) -- ++(0,-0.6) -| (ssn.south);
 
% Transitive dependencies
\draw[<-] (dname.north) -- ++(0,0.8) -| (dnum.north);
\draw[<-] (dmgr.north) -- ++(0,0.8) -| (dnum.north);
 
\end{tikzpicture}
\end{document}

Z={Dnumber}→{Dname,Dmgr_ssn}Z = \{\text{Dnumber}\} \rightarrow \{\text{Dname}, \text{Dmgr\_ssn}\}

Conversion to 3NF

Steps

  1. Make new tables to eliminate transitive dependencies
    • Write a copy of its determinant as a primary key for a new table
    • Determinant: Any attribute whose value determines other values within a row
  2. Reassign corresponding dependent attributes

Requirements for 3NF

A table is in 3NF when it:

  • Is in 2NF
  • Contains no transitive dependencies

Example: CUSTOMERORDER to 3NF

Before 3NF (Still has Transitive Dependencies)

CUSTOMERORDER (2NF):

\usepackage{tikz}
\usetikzlibrary{positioning}
\begin{document}
\begin{tikzpicture}[>=stealth, thick]
\node[draw] (oid) {\underline{OrderID}};
\node[draw, right=0.1cm of oid] (odate) {OrderDate};
\node[draw, right=0.1cm of odate] (cid) {CustomerID};
\node[draw, right=0.1cm of cid] (cname) {CustomerName};
\node[draw, right=0.1cm of cname] (caddr) {CustomerAddress};
 
% Transitive Dependencies
\draw[<-, red, thick] (cname.south) -- ++(0,-0.8) -| (cid.south);
\draw[<-, red, thick] (caddr.south) -- ++(0,-0.8) -| (cid.south);
 
\node[below=1.3cm of cid, red] {Transitive Dependencies};
\end{tikzpicture}
\end{document}

After Converting to 3NF

Removing Transitive Dependencies → Getting into Third Normal Form

ORDER (3NF):

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}
\node[draw, minimum width=1.5cm] at (0,0) {\underline{OrderID}};
\node[draw, minimum width=1.8cm] at (2,0) {OrderDate};
\node[draw, minimum width=1.8cm] at (4,0) {CustomerID};
\end{tikzpicture}
\end{document}

CUSTOMER (3NF):

\usepackage{tikz}
\begin{document}
\begin{tikzpicture}
\node[draw, minimum width=1.8cm] at (0,0) {\underline{CustomerID}};
\node[draw, minimum width=2.2cm] at (2.5,0) {CustomerName};
\node[draw, minimum width=2.5cm] at (5.2,0) {CustomerAddress};
\end{tikzpicture}
\end{document}

Result

Transitive dependencies are removed.

All tables are now in Third Normal Form (3NF).

Summary of Normal Forms Based on Primary Keys

Normal FormTestRemedy (Normalization)
First (1NF)Relation should have no multivalued attributes or nested relations.Form new relations for each multivalued attribute or nested relation.
Second (2NF)For relations where primary key contains multiple attributes, no nonkey attribute should be functionally dependent on a part of the primary key.Decompose and set up a new relation for each partial key with its dependent attribute(s). Make sure to keep a relation with the original primary key and any attributes that are fully functionally dependent on it.
Third (3NF)Relation should not have a nonkey attribute functionally determined by another nonkey attribute (or by a set of nonkey attributes). That is, there should be no transitive dependency of a nonkey attribute on the primary key.Decompose and set up a relation that includes the nonkey attribute(s) that functionally determine(s) other nonkey attribute(s).

Key Concepts Summary

Normalization Process

  1. 1NF: Remove multivalued attributes (repeating groups)

    • Identify PK
    • Identify all FDs

  2. 2NF: Remove partial dependencies

    • Create separate tables for partial keys
    • Keep full dependencies together

  3. 3NF: Remove transitive dependencies

    • Create separate tables for determinants
    • Eliminate non-key → non-key dependencies

Types of Dependencies

  • Partial Dependency: Key → Non-key (part of composite key determines non-key)
  • Full Dependency: Entire Key → Non-key
  • Transitive Dependency: Non-key → Non-key

Important Notes

  • 3NF is generally sufficient for most business databases
  • Higher normal forms (BCNF, 4NF, 5NF) exist but are rarely needed
  • Denormalization may be used for performance, but increases redundancy
  • Always start by identifying ALL functional dependencies

Final Exam Notes

  • Know how to identify FDs from relation states
  • Understand partial vs transitive dependencies
  • Be able to draw dependency diagrams
  • Practice normalization from unnormalized tables to 3NF
  • Remember: 1NF → 2NF → 3NF (progressive elimination of anomalies)

Linked from