Third Normal Form (3NF)
Identifying Violations
Read aloud is unavailable in this browser.
The Art of Detecting 3NF Violations
Understanding the definition of Third Normal Form is essential, but applying that knowledge to identify violations in real-world schemas requires systematic techniques and practiced intuition. Many 3NF violations are subtle—they hide within seemingly reasonable schema designs and only reveal themselves through careful functional dependency analysis.
This page transforms you from someone who understands 3NF into someone who can recognize violations across any schema. We'll develop multiple complementary approaches: visual pattern recognition, algorithmic detection, and semantic reasoning about business rules.
By the end of this page, you will be able to systematically identify 3NF violations using visual patterns, algorithmic procedures, and semantic analysis. You'll recognize common violation patterns, understand why they occur, and accurately pinpoint the specific functional dependencies causing each violation.
The Violation Detection Framework
Effective 3NF violation detection requires a structured approach. Random inspection misses subtle violations; systematic analysis catches them all.
The Four-Phase Detection Framework:
- •Phase 1: FD Discovery — Identify all functional dependencies from business rules, domain knowledge, and schema analysis. Include both explicit constraints and implicit semantic dependencies.
- •Phase 2: Key Analysis — Determine all candidate keys using attribute closure. Classify every attribute as prime (in any candidate key) or non-prime (in no candidate key).
- •Phase 3: FD Classification — For each non-trivial FD X → A, check: Is X a superkey? Is A prime? Record which condition (if any) is satisfied.
- •Phase 4: Violation Identification — Any FD where X is NOT a superkey AND A is NOT prime is a 3NF violation. Document the violation with its transitive chain.
Always complete Phase 2 (Key Analysis) before Phase 3 (FD Classification). You cannot correctly classify FDs without knowing all candidate keys—an attribute might be prime in a key you haven't discovered yet.
Visual Pattern Recognition
Experienced database designers develop intuition for recognizing 3NF violations visually. Certain patterns in data or schema structure strongly suggest violations.
Pattern 1: The Repeated Group
When the same set of values appears together repeatedly across multiple rows, you likely have a transitive dependency.
| OrderID | ProductID | CategoryID | CategoryName | CategoryTaxRate |
|---|---|---|---|---|
| 1001 | P100 | CAT-01 | Electronics | 8.5% |
| 1002 | P101 | CAT-01 | Electronics | 8.5% |
| 1003 | P102 | CAT-02 | Clothing | 5.0% |
| 1004 | P103 | CAT-01 | Electronics | 8.5% |
| 1005 | P104 | CAT-02 | Clothing | 5.0% |
Notice: CategoryName and CategoryTaxRate repeat identically every time CategoryID appears. This is a visual signal of the transitive chain: OrderID → ProductID → CategoryID → (CategoryName, CategoryTaxRate)
Pattern 2: The Foreign Key with Extras
When a table contains a foreign key AND additional descriptive attributes about that foreign key's entity:
| Column Name | Column Purpose | Violation Signal? |
|---|---|---|
| EmpID | Primary key of this table | No — it's the key |
| DeptID | Foreign key to Department | Maybe — watch what follows |
| DeptName | Describes DeptID, not EmpID | ⚠️ YES - transitive via DeptID |
| DeptBudget | Describes DeptID, not EmpID | ⚠️ YES - transitive via DeptID |
| Salary | Describes EmpID directly | No — direct dependency |
Pattern 3: The Calculated Determinant
When a non-key attribute determines other non-key attributes:
Employee Table:
- EmpID (PK)
- HireDate
- YearsOfService ← derived from HireDate
- SeniorityLevel ← determined by YearsOfService
- SeniorityBenefits ← determined by SeniorityLevel
Here, EmpID → HireDate → YearsOfService → SeniorityLevel → SeniorityBenefits creates multiple transitive violations.
These patterns suggest likely violations, but confirmation requires formal FD analysis. Sometimes repetition is coincidental in sample data, or a 'descriptive' column might actually be functionally determined by the primary key through a business rule you haven't discovered.
The Non-Superkey Determinant Test
One of the most reliable methods for finding 3NF violations is to examine every determinant (left side of an FD) and ask two questions:
The Determinant Test:
For each FD X → A in F:
- Is X a superkey?
- If NOT, is A a prime attribute?
If the answer to BOTH questions is NO, you have a 3NF violation.
Systematic Application:
Applying the Determinant Test
Relation: Project(ProjID, ProjName, ManagerID, ManagerName, Budget)
- •FDs from business rules:
- •• ProjID → ProjName, ManagerID, Budget
- •• ManagerID → ManagerName
- •Step 1: Find candidate keys
- •• {ProjID}⁺ = {ProjID, ProjName, ManagerID, ManagerName, Budget} = all attributes
- •• Candidate key: {ProjID}
- •Step 2: Classify attributes
- •• Prime: ProjID
- •• Non-Prime: ProjName, ManagerID, ManagerName, Budget
- •Step 3: Test each FD determinant
- •• ProjID → ProjName: ProjID is superkey ✓ (passes)
- •• ProjID → ManagerID: ProjID is superkey ✓ (passes)
- •• ProjID → Budget: ProjID is superkey ✓ (passes)
- •• ManagerID → ManagerName: ManagerID is NOT superkey, ManagerName is NOT prime ❌
- •Violation Found: ManagerID → ManagerName violates 3NF
Why the Determinant Test Works:
The determinant test directly implements the modern 3NF definition:
- When X is a superkey, the FD X → A is allowed regardless of A (all attributes depend on a superkey)
- When A is prime, the FD X → A is allowed regardless of X (dependencies involving key attributes are structural)
- Only when BOTH conditions fail do we have a violation: a non-superkey determining a non-prime attribute
This is precisely what transitive dependencies look like: some intermediate non-key attribute (making the determinant a non-superkey) determines another non-key attribute (a non-prime dependent).
You only need to test FDs whose determinants are NOT superkeys. If you've already confirmed X is a superkey, X → A automatically passes for any A. Focus your attention on FDs with 'small' determinants that aren't keys.
Semantic Analysis for Violations
Beyond formal FD analysis, semantic reasoning about what each attribute means helps identify violations that might be missed in pure syntactic analysis.
The Entity-Attribute Alignment Question:
For each attribute A in a relation R with primary key K, ask: "Is A fundamentally a property of the entity identified by K, or is it a property of some intermediate entity represented by another attribute?"
If A is a property of an intermediate entity, you likely have a transitive dependency.
| Attribute | Appears in Entity | But Describes Entity | Mismatch? | Implication |
|---|---|---|---|---|
| ProjName | Project | Project | No | OK — properly placed |
| ManagerName | Project | Manager | ⚠️ Yes | Transitive via ManagerID |
| Budget | Project | Project | No | OK — properly placed |
| DeptLocation | Employee | Department | ⚠️ Yes | Transitive via DeptID |
| CustomerAddress | Order | Customer | ⚠️ Yes | Transitive via CustomerID |
The "About" Heuristic:
For each attribute column, complete this sentence:
"This attribute tells me about [X]."
Then compare X to the entity the table represents:
- Match: Attribute properly belongs here (direct dependency)
- Mismatch: Attribute belongs in another table (transitive dependency candidate)
Example Application:
Table: SalesTransaction(TxnID, TxnDate, CustomerID, CustomerName, ProductID, ProductCategory, Quantity)
- TxnDate tells me about... the Transaction ✓
- CustomerName tells me about... the Customer (not Transaction) ⚠️
- ProductCategory tells me about... the Product (not Transaction) ⚠️
- Quantity tells me about... the Transaction ✓
Potential violations: CustomerName (via CustomerID), ProductCategory (via ProductID)
Understanding the business domain helps you immediately recognize entity misalignments. If you know customers have addresses and orders don't have their own addresses, you'll instantly spot a misplaced CustomerAddress in an Order table without needing formal FD analysis.
Comprehensive Worked Example
Let's work through a complete 3NF violation analysis for a realistic schema.
Scenario: University Course Management
Relation: CourseOffering(OfferingID, CourseID, CourseName, DeptCode, DeptName, SemesterCode, InstructorID, InstructorName, Room, Capacity)
Business Rules:
- Each course offering has a unique ID
- A course has one name and belongs to one department
- Each department has one name
- Each offering has one instructor
- Each instructor has one name
- Each room has a fixed capacity
Step-by-Step 3NF Violation Analysis
Full walkthrough of detecting all violations
- •Step 1: Extract Functional Dependencies
- •F = {
- •
OfferingID → CourseID, SemesterCode, InstructorID, Room - •
CourseID → CourseName, DeptCode - •
DeptCode → DeptName - •
InstructorID → InstructorName - •
Room → Capacity - •}
- •Step 2: Find Candidate Keys
- •{OfferingID}⁺ = All attributes (computing closure confirms this)
- •Candidate key: {OfferingID}
- •Step 3: Classify Attributes
- •Prime: OfferingID
- •Non-Prime: CourseID, CourseName, DeptCode, DeptName, SemesterCode, InstructorID, InstructorName, Room, Capacity
- •Step 4: Test Each FD
- •• OfferingID → (...): OfferingID is superkey ✓
- •• CourseID → CourseName, DeptCode: CourseID not superkey, both dependents non-prime ❌ VIOLATION
- •• DeptCode → DeptName: DeptCode not superkey, DeptName non-prime ❌ VIOLATION
- •• InstructorID → InstructorName: InstructorID not superkey, InstructorName non-prime ❌ VIOLATION
- •• Room → Capacity: Room not superkey, Capacity non-prime ❌ VIOLATION
Violations Summary:
| Violating FD | Transitive Chain | Entity Misaligned |
|---|---|---|
| CourseID → CourseName | OfferingID → CourseID → CourseName | Course info in Offering |
| CourseID → DeptCode | OfferingID → CourseID → DeptCode | Course-Dept relation in Offering |
| DeptCode → DeptName | OfferingID → CourseID → DeptCode → DeptName | Dept info in Offering |
| InstructorID → InstructorName | OfferingID → InstructorID → InstructorName | Instructor info in Offering |
| Room → Capacity | OfferingID → Room → Capacity | Room info in Offering |
Conceptual Analysis:
This relation conflates five entities:
- Offering (the core entity)
- Course (CourseName belongs here)
- Department (DeptName belongs here)
- Instructor (InstructorName belongs here)
- Room (Capacity belongs here)
Each embedded entity creates transitive dependencies. The proper design would separate these into five relations.
Notice that DeptName is TWO levels transitive: OfferingID → CourseID → DeptCode → DeptName. This "chain of chains" is still a single 3NF violation at the DeptCode → DeptName level, but it also indicates deeper structural issues in the relation.
Common 3NF Violation Categories
3NF violations cluster into recognizable categories. Knowing these categories helps you quickly identify violations in unfamiliar schemas.
Category 1: Entity Embedding
A relation contains attributes from an entity it references via foreign key.
Entity Embedding Patterns
- •Order table containing CustomerName, CustomerAddress (Customer embedded)
- •Employee table containing DeptName, DeptLocation (Department embedded)
- •Invoice table containing VendorName, VendorPhone (Vendor embedded)
- •Solution: Extract embedded entity into separate table, keep only FK reference
Category 2: Lookup Value Expansion
A relation contains both a code/ID and its human-readable equivalent.
Lookup Value Expansion Patterns
- •StatusCode and StatusDescription in same table
- •CountryCode and CountryName in same table
- •CategoryID and CategoryName in same table
- •Solution: Create lookup table (Code → Description), reference only code in main table
Category 3: Derived Attribute Chains
A computed or derived attribute determines other attributes.
Derived Attribute Chain Patterns
- •Age → AgeGroup → GroupDiscount (Age determines group which determines discount)
- •Total → TaxBracket → TaxRate (purchasing total determines tax)
- •Score → GradeLetter → PassFail (score determines grade determines pass/fail)
- •Solution: Either compute derived values on-the-fly, or create separate lookup tables
Category 4: Hierarchy Flattening
A relation stores multiple levels of a hierarchy in a single row.
Hierarchy Flattening Patterns
- •City, State, Country all in Address table (State → Country is transitive)
- •Employee, Manager, Director all in Employee table (Manager → Director possible)
- •Category, Subcategory, Product in Product table (Subcategory → Category)
- •Solution: Model hierarchy explicitly with self-referencing table or separate tables per level
A single relation can exhibit multiple violation categories simultaneously. The CourseOffering example showed entity embedding (Course, Department, Instructor, Room) and potentially lookup expansion (DeptCode → DeptName). Analyze systematically to catch all violations.
Violation Documentation Template
When identifying 3NF violations, proper documentation ensures clear communication and guides the correction process. Use this template for each violation:
3NF Violation Documentation Template:
=== 3NF VIOLATION REPORT === RELATION: [Table name]PRIMARY KEY: [Key attribute(s)]CANDIDATE KEYS: [All candidate keys] VIOLATION #N: Violating FD: [X → A] Why X is NOT a superkey: - {X}⁺ = [closure of X] - Missing attributes: [attributes not in closure] Why A is NOT prime: - Candidate keys: [list all] - A does not appear in any candidate key Transitive Chain: [Key] → [intermediate] → [A] Category: [Entity Embedding / Lookup Expansion / Derived Chain / Hierarchy] Anomalies Caused: - Update: [describe] - Insert: [describe] - Delete: [describe] Proposed Resolution: [Decomposition approach] ===========================Completed Documentation Example
Document for Employee-Department violation
- •RELATION: Employee
- •PRIMARY KEY: EmpID
- •CANDIDATE KEYS: {EmpID}
- •
- •VIOLATION #1:
- •Violating FD: DeptID → DeptName
- •
- •Why DeptID is NOT a superkey:
- •
{DeptID}⁺ = {DeptID, DeptName, DeptLocation} - •
Missing: EmpID, EmpName, Salary (not all attributes) - •
- •Why DeptName is NOT prime:
- •
Only candidate key is {EmpID} - •
DeptName ∉ {EmpID} - •
- •Transitive Chain: EmpID → DeptID → DeptName
- •
- •Category: Entity Embedding (Department in Employee)
- •
- •Anomalies:
- •
Update: Changing dept name requires updating all employees - •
Insert: Cannot add new dept without an employee - •
Delete: Losing last employee loses department info - •
- •Proposed Resolution:
- •
Create Department(DeptID, DeptName, DeptLocation) - •
Keep Employee(EmpID, EmpName, DeptID, Salary)
Thorough documentation serves multiple purposes: it validates your analysis, communicates findings to stakeholders, guides the decomposition process, and creates an audit trail for schema evolution. Take time to document properly.
KEEP LEARNING
There’s more to understand.
You’re reading the introduction. Create a free account to read the complete lesson, save notes and keep track of your progress.