# Third Normal Form (3NF) Compliance Guide

## Document Control

| Attribute | Value |
|-----------|-------|
| **Document ID** | COMP-3NF-001 |
| **Version** | 1.0.0 |
| **Status** | Active |
| **Last Updated** | October 10, 2025 |
| **Owner** | Data Architecture Team |
| **Review Cycle** | Quarterly |
| **Prerequisites** | [1NF Compliance](./1NF.md), [2NF Compliance](./2NF.md) |

## Executive Summary

This document establishes the compliance requirements, validation procedures, and implementation guidelines for Third Normal Form (3NF) in database design within the AI Integration Workflow Standardize project. 3NF builds upon Second Normal Form (2NF) by eliminating transitive dependencies, ensuring that all non-key attributes depend directly and solely on the primary key. This normalization level minimizes data redundancy, prevents update anomalies, and establishes a robust foundation for data integrity and maintainability.

## 1. Overview

### 1.1 Purpose

Third Normal Form (3NF) compliance ensures that all database tables are structured to:
- Maintain all 1NF and 2NF requirements
- Eliminate transitive dependencies between non-key attributes
- Ensure direct functional dependency of all non-key attributes on the primary key
- Minimize data redundancy and update anomalies
- Establish a foundation for Boyce-Codd Normal Form (BCNF) when needed

### 1.2 Scope

This compliance framework applies to:
- All relational database tables (PostgreSQL, MySQL, SQLite, Oracle)
- Data models for AI workflow metadata and configuration
- User, authentication, and authorization data structures
- Audit, logging, and tracking tables
- Reference and lookup tables
- Business entity models and domain objects
- Integration and synchronization tables

### 1.3 Regulatory Alignment

3NF compliance supports:
- **ISO/IEC 27001** - A.8.2 (Information Classification), A.8.3 (Media Handling)
- **ISO/IEC 27002** - Control 5.9 (Inventory of Information Assets), Control 8.11 (Data Masking)
- **ISO/IEC 27701** - Control 7.4.1 (Identify Basis for PII Processing)
- **ISO 9001** - Clause 7.5 (Documented Information), Clause 8.1 (Operational Planning)
- **PCI DSS** - Requirement 3 (Protect Stored Cardholder Data)
- **CMMI** - Process Area: Configuration Management, Data Management, Decision Analysis

## 2. Third Normal Form Requirements

### 2.1 Core Principles

A table is in Third Normal Form (3NF) if and only if:

1. **2NF Compliance**: The table must first satisfy all Second Normal Form requirements
2. **No Transitive Dependencies**: No non-prime attribute (non-key column) depends on another non-prime attribute
3. **Direct Dependency**: Every non-key attribute must depend directly on the primary key, not indirectly through another attribute

**Key Concept**: A transitive dependency occurs when a non-key attribute determines another non-key attribute (A → B → C, where A is the primary key).

### 2.2 Transitive Dependency Definitions

#### Transitive Dependency (Violation)
- **Definition**: When attribute C depends on attribute B, which depends on primary key A (creating an indirect path A → B → C)
- **Notation**: If A → B and B → C, then A → C is a transitive dependency
- **Example**: `employee_id → department_id → department_name`
  - employee_id determines department_id
  - department_id determines department_name
  - Therefore, department_name transitively depends on employee_id

#### Direct Dependency (Compliant)
- **Definition**: Attribute B depends directly on primary key A with no intermediate attributes
- **Notation**: A → B (no intermediate dependencies)
- **Example**: `employee_id → employee_name` (direct relationship)

### 2.3 Detailed Requirements

#### REQ-3NF-001: Second Normal Form Compliance (MANDATORY)
- **Requirement**: Table must satisfy all 2NF requirements before 3NF validation
- **Validation**: All REQ-2NF-001 through REQ-2NF-005 must pass
- **Severity**: Critical
- **Reference**: [2NF Compliance Guide](./2NF.md)

#### REQ-3NF-002: No Transitive Dependencies (MANDATORY)
- **Requirement**: All non-key attributes must depend directly on the primary key
- **Validation**: No non-key attribute can determine another non-key attribute
- **Severity**: Critical
- **Scope**: All tables regardless of primary key structure

#### REQ-3NF-003: Transitive Dependency Documentation (MANDATORY)
- **Requirement**: Document all functional dependencies during schema design
- **Validation**: Functional dependency analysis identifies all transitive relationships
- **Severity**: High
- **Deliverable**: Dependency diagrams and documentation

#### REQ-3NF-004: Proper Decomposition for Transitivity (MANDATORY)
- **Requirement**: Tables with transitive dependencies must be decomposed
- **Validation**: Create separate tables for each entity with its direct attributes
- **Severity**: Critical
- **Method**: Lossless-join and dependency-preserving decomposition

#### REQ-3NF-005: Reference Table Extraction (MANDATORY)
- **Requirement**: Extract lookup/reference data into separate tables
- **Validation**: No descriptive attributes stored redundantly
- **Severity**: High
- **Pattern**: Create dedicated reference tables for categorical data

## 3. Compliance Validation

### 3.1 Automated Validation

#### Database Schema Analysis Script

```sql
-- 3NF Compliance Validation Query
-- Identifies potential transitive dependencies through pattern analysis

-- Pattern 1: Tables with potential lookup/reference data
WITH potential_transitive_deps AS (
    SELECT 
        c.table_schema,
        c.table_name,
        c.column_name as potential_determinant,
        array_agg(c2.column_name) as potential_dependents
    FROM information_schema.columns c
    JOIN information_schema.columns c2
        ON c.table_schema = c2.table_schema
        AND c.table_name = c2.table_name
        AND c.column_name != c2.column_name
    WHERE c.table_schema NOT IN ('pg_catalog', 'information_schema')
        AND c.table_type = 'BASE TABLE'
        -- Look for _id or _code columns (potential foreign keys to reference data)
        AND (c.column_name LIKE '%_id' OR c.column_name LIKE '%_code')
        -- Look for _name or _description columns (potential transitive data)
        AND (c2.column_name LIKE '%_name' OR c2.column_name LIKE '%_description'
             OR c2.column_name LIKE '%_type' OR c2.column_name LIKE '%_status')
        -- Exclude primary keys
        AND c.column_name NOT IN (
            SELECT kcu.column_name
            FROM information_schema.key_column_usage kcu
            JOIN information_schema.table_constraints tc
                ON kcu.constraint_name = tc.constraint_name
            WHERE tc.constraint_type = 'PRIMARY KEY'
                AND tc.table_name = c.table_name
        )
    GROUP BY c.table_schema, c.table_name, c.column_name
)
SELECT 
    table_schema,
    table_name,
    potential_determinant,
    potential_dependents,
    'Potential transitive dependency detected' as issue_type,
    'HIGH' as severity,
    'Review if ' || array_to_string(potential_dependents, ', ') || 
    ' depend on ' || potential_determinant || ' rather than primary key' as description
FROM potential_transitive_deps;

-- Pattern 2: Columns with naming patterns suggesting lookup data
SELECT 
    table_schema,
    table_name,
    string_agg(column_name, ', ') as lookup_columns,
    'Missing reference table' as issue_type,
    'MEDIUM' as severity,
    'Consider extracting to reference table' as recommendation
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    AND (
        column_name ~ '^(status|type|category|level|priority|state)_name$'
        OR column_name ~ '^(status|type|category|level|priority|state)_description$'
    )
GROUP BY table_schema, table_name
HAVING COUNT(*) > 0;

-- Pattern 3: Detect redundant descriptive data through sampling
-- (This requires actual data analysis - example for PostgreSQL)
DO $$
DECLARE
    tbl RECORD;
    col_id TEXT;
    col_desc TEXT;
    distinct_pairs INTEGER;
    distinct_ids INTEGER;
    redundancy_ratio NUMERIC;
BEGIN
    FOR tbl IN 
        SELECT DISTINCT table_schema, table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
    LOOP
        -- Find potential ID/description pairs
        FOR col_id IN 
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_schema = tbl.table_schema 
                AND table_name = tbl.table_name
                AND column_name LIKE '%_id'
        LOOP
            FOR col_desc IN 
                SELECT column_name 
                FROM information_schema.columns 
                WHERE table_schema = tbl.table_schema 
                    AND table_name = tbl.table_name
                    AND column_name LIKE '%_name'
            LOOP
                -- Check if description always maps 1:1 with ID (indicating transitive dependency)
                EXECUTE format(
                    'SELECT COUNT(DISTINCT (%I, %I)), COUNT(DISTINCT %I) 
                     FROM %I.%I WHERE %I IS NOT NULL',
                    col_id, col_desc, col_id,
                    tbl.table_schema, tbl.table_name, col_id
                ) INTO distinct_pairs, distinct_ids;
                
                IF distinct_ids > 0 AND distinct_pairs = distinct_ids THEN
                    RAISE NOTICE 'Potential 3NF violation in %.%: % → % (1:1 mapping)',
                        tbl.table_schema, tbl.table_name, col_id, col_desc;
                END IF;
            END LOOP;
        END LOOP;
    END LOOP;
END $$;
```

#### TypeScript Validation Helper

```typescript
/**
 * 3NF Compliance Validator
 * Validates data structures against Third Normal Form requirements
 */

interface FunctionalDependency {
  determinant: string[];
  dependent: string[];
}

interface TransitiveDependency {
  primaryKey: string[];
  intermediate: string;
  dependent: string[];
  path: string; // e.g., "PK → intermediate → dependent"
}

interface TableSchema {
  name: string;
  primaryKey: string[];
  columns: string[];
  foreignKeys?: {
    columns: string[];
    references: { table: string; columns: string[] };
  }[];
}

export class ThirdNormalFormValidator {
  
  /**
   * Validates if a table schema satisfies 3NF
   */
  static validate3NF(
    schema: TableSchema,
    functionalDependencies: FunctionalDependency[]
  ): {
    isCompliant: boolean;
    violations: Array<{
      type: string;
      description: string;
      severity: string;
      transitivePath: string;
      intermediate: string;
      dependent: string[];
    }>;
  } {
    const violations: Array<{
      type: string;
      description: string;
      severity: string;
      transitivePath: string;
      intermediate: string;
      dependent: string[];
    }> = [];

    // Get non-key attributes
    const nonKeyAttributes = schema.columns.filter(
      col => !schema.primaryKey.includes(col)
    );

    // Find transitive dependencies
    const transitiveDeps = this.findTransitiveDependencies(
      schema.primaryKey,
      nonKeyAttributes,
      functionalDependencies
    );

    transitiveDeps.forEach(trans => {
      violations.push({
        type: 'TRANSITIVE_DEPENDENCY',
        description: `Attribute(s) ${trans.dependent.join(', ')} transitively depend on primary key through ${trans.intermediate}`,
        severity: 'CRITICAL',
        transitivePath: trans.path,
        intermediate: trans.intermediate,
        dependent: trans.dependent
      });
    });

    return {
      isCompliant: violations.length === 0,
      violations
    };
  }

  /**
   * Identifies transitive dependencies in functional dependencies
   */
  private static findTransitiveDependencies(
    primaryKey: string[],
    nonKeyAttributes: string[],
    functionalDependencies: FunctionalDependency[]
  ): TransitiveDependency[] {
    const transitiveDeps: TransitiveDependency[] = [];

    // Build dependency map
    const depMap = new Map<string, string[]>();
    functionalDependencies.forEach(fd => {
      if (fd.determinant.length === 1) {
        depMap.set(fd.determinant[0], fd.dependent);
      }
    });

    // Check each non-key attribute
    nonKeyAttributes.forEach(attr => {
      // See if this attribute determines other non-key attributes
      const determined = depMap.get(attr) || [];
      const nonKeyDetermined = determined.filter(d => 
        nonKeyAttributes.includes(d) && d !== attr
      );

      if (nonKeyDetermined.length > 0) {
        // Found transitive dependency: PK → attr → nonKeyDetermined
        transitiveDeps.push({
          primaryKey,
          intermediate: attr,
          dependent: nonKeyDetermined,
          path: `${primaryKey.join('+')} → ${attr} → ${nonKeyDetermined.join(', ')}`
        });
      }
    });

    return transitiveDeps;
  }

  /**
   * Suggests 3NF-compliant decomposition
   */
  static suggestDecomposition(
    schema: TableSchema,
    transitiveDependencies: TransitiveDependency[]
  ): {
    originalTable: TableSchema;
    referenceTableS: TableSchema[];
    migrationNotes: string[];
  } {
    const referenceTables: TableSchema[] = [];
    const remainingColumns = new Set(schema.columns);
    const migrationNotes: string[] = [];

    // For each transitive dependency, create a reference table
    transitiveDependencies.forEach((trans, index) => {
      const refTableName = `${trans.intermediate.replace(/_id$/, '')}_ref`;
      
      referenceTables.push({
        name: refTableName,
        primaryKey: [trans.intermediate],
        columns: [trans.intermediate, ...trans.dependent],
        foreignKeys: []
      });

      // Remove dependent columns from original table
      trans.dependent.forEach(col => remainingColumns.delete(col));

      migrationNotes.push(
        `Create reference table '${refTableName}' for ${trans.intermediate} ` +
        `with attributes: ${trans.dependent.join(', ')}`
      );
      migrationNotes.push(
        `Add foreign key from ${schema.name}.${trans.intermediate} to ${refTableName}.${trans.intermediate}`
      );
    });

    return {
      originalTable: {
        ...schema,
        columns: Array.from(remainingColumns),
        foreignKeys: [
          ...(schema.foreignKeys || []),
          ...referenceTables.map(rt => ({
            columns: [rt.primaryKey[0]],
            references: { table: rt.name, columns: rt.primaryKey }
          }))
        ]
      },
      referenceTableS: referenceTables,
      migrationNotes
    };
  }

  /**
   * Detects potential transitive dependencies based on naming patterns
   */
  static detectPotentialTransitiveDependencies(schema: TableSchema): {
    warnings: string[];
    suggestions: Array<{
      intermediate: string;
      potentialDependents: string[];
    }>;
  } {
    const warnings: string[] = [];
    const suggestions: Array<{
      intermediate: string;
      potentialDependents: string[];
    }> = [];

    const nonKeyColumns = schema.columns.filter(
      col => !schema.primaryKey.includes(col)
    );

    // Pattern 1: _id columns with corresponding _name/_description columns
    const idColumns = nonKeyColumns.filter(col => 
      col.endsWith('_id') || col.endsWith('_code')
    );

    idColumns.forEach(idCol => {
      const baseNme = idCol.replace(/_id$/, '').replace(/_code$/, '');
      const potentialDependents = nonKeyColumns.filter(col => 
        col.startsWith(baseNme + '_') && col !== idCol
      );

      if (potentialDependents.length > 0) {
        warnings.push(
          `Potential transitive dependency: ${idCol} may determine ${potentialDependents.join(', ')}`
        );
        suggestions.push({
          intermediate: idCol,
          potentialDependents
        });
      }
    });

    // Pattern 2: Status/type/category columns with description columns
    const categoryColumns = nonKeyColumns.filter(col =>
      /^(status|type|category|level|priority|state)$/i.test(col)
    );

    categoryColumns.forEach(catCol => {
      const descCols = nonKeyColumns.filter(col =>
        col.startsWith(catCol + '_') && 
        (col.endsWith('_name') || col.endsWith('_description'))
      );

      if (descCols.length > 0) {
        warnings.push(
          `Consider reference table: ${catCol} determines ${descCols.join(', ')}`
        );
        suggestions.push({
          intermediate: catCol,
          potentialDependents: descCols
        });
      }
    });

    return { warnings, suggestions };
  }

  /**
   * Analyzes data for actual transitive dependencies (requires database access)
   */
  static async analyzeDataForTransitivity(
    db: Database,
    tableName: string,
    potentialIntermediate: string,
    potentialDependents: string[]
  ): Promise<{
    isTransitive: boolean;
    confidence: number;
    analysis: string;
  }> {
    // Check if intermediate → dependent has 1:1 mapping
    const query = `
      SELECT 
        COUNT(DISTINCT ${potentialIntermediate}) as unique_intermediates,
        COUNT(DISTINCT (${potentialIntermediate}, ${potentialDependents.join(', ')})) as unique_combinations
      FROM ${tableName}
      WHERE ${potentialIntermediate} IS NOT NULL
    `;

    const result = await db.query(query);
    const uniqueIntermediates = result.rows[0].unique_intermediates;
    const uniqueCombinations = result.rows[0].unique_combinations;

    const isTransitive = uniqueIntermediates === uniqueCombinations;
    const confidence = uniqueIntermediates > 0 
      ? (uniqueIntermediates / uniqueCombinations) 
      : 0;

    const analysis = isTransitive
      ? `Strong transitive dependency: ${potentialIntermediate} has 1:1 mapping with dependent attributes`
      : `Weak or no transitive dependency (${confidence.toFixed(2)} confidence)`;

    return { isTransitive, confidence, analysis };
  }
}

/**
 * Example usage and test cases
 */
export namespace ThirdNormalFormExamples {
  
  export function exampleViolation(): void {
    // Example: Employee table with department information
    const schema: TableSchema = {
      name: 'employees',
      primaryKey: ['employee_id'],
      columns: [
        'employee_id',
        'employee_name',
        'department_id',        // Intermediate
        'department_name',      // Transitive: depends on department_id
        'department_location',  // Transitive: depends on department_id
        'department_manager',   // Transitive: depends on department_id
        'salary',
        'hire_date'
      ]
    };

    const functionalDependencies: FunctionalDependency[] = [
      { 
        determinant: ['employee_id'], 
        dependent: ['employee_name', 'department_id', 'salary', 'hire_date'] 
      },
      { 
        determinant: ['department_id'], 
        dependent: ['department_name', 'department_location', 'department_manager'] 
      }
    ];

    const result = ThirdNormalFormValidator.validate3NF(schema, functionalDependencies);
    
    console.log('3NF Compliance:', result.isCompliant);
    console.log('Violations:', result.violations);

    if (!result.isCompliant) {
      const transitiveDeps = result.violations.map(v => ({
        primaryKey: schema.primaryKey,
        intermediate: v.intermediate,
        dependent: v.dependent,
        path: v.transitivePath
      }));

      const decomposition = ThirdNormalFormValidator.suggestDecomposition(
        schema,
        transitiveDeps
      );
      
      console.log('Suggested decomposition:', decomposition);
      console.log('Migration notes:', decomposition.migrationNotes);
    }
  }
}
```

### 3.2 Manual Validation Checklist

| Check ID | Validation Item | Method | Frequency |
|----------|----------------|--------|-----------|
| CHK-3NF-001 | Verify 2NF compliance first | Automated check | Per deployment |
| CHK-3NF-002 | Identify non-key attributes | Schema review | Per schema change |
| CHK-3NF-003 | Document functional dependencies | Manual analysis | Per table |
| CHK-3NF-004 | Detect transitive dependencies | Automated + manual | Per schema change |
| CHK-3NF-005 | Validate reference table extraction | Code review | Per normalization |
| CHK-3NF-006 | Verify lossless decomposition | Integration tests | Per migration |
| CHK-3NF-007 | Review lookup/reference patterns | Schema review | Monthly |

## 4. Implementation Guidelines

### 4.1 Correcting Non-Compliant Designs

#### Violation Type 1: Employee with Department Information

**Non-Compliant (Violates 3NF):**
```sql
CREATE TABLE employees (
    employee_id UUID PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    email VARCHAR(255) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    -- Transitive dependencies below:
    department_id VARCHAR(20),         -- Intermediate attribute
    department_name VARCHAR(200),      -- Depends on department_id
    department_location VARCHAR(200),  -- Depends on department_id
    department_budget DECIMAL(12,2),   -- Depends on department_id
    department_manager_id UUID         -- Depends on department_id
);
```

**Functional Dependencies:**
- `employee_id → employee_name, email, hire_date, salary, department_id` (Direct)
- `department_id → department_name, department_location, department_budget, department_manager_id` (Transitive!)
- **Violation**: employee_id → department_id → department_name (transitive path)

**3NF Compliant Decomposition:**
```sql
-- Reference table for departments
CREATE TABLE departments (
    department_id VARCHAR(20) PRIMARY KEY,
    department_name VARCHAR(200) UNIQUE NOT NULL,
    department_location VARCHAR(200),
    department_budget DECIMAL(12,2),
    department_manager_id UUID REFERENCES employees(employee_id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Employee table with only direct dependencies
CREATE TABLE employees (
    employee_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_name VARCHAR(200) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    department_id VARCHAR(20) REFERENCES departments(department_id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Index for common queries
CREATE INDEX idx_employees_department ON employees(department_id);
CREATE INDEX idx_employees_hire_date ON employees(hire_date);
```

**Benefits:**
- Department information stored once (no redundancy)
- Updating department name: 1 row vs. potentially thousands
- Adding departments without employees: possible
- Data consistency: guaranteed through foreign key

#### Violation Type 2: Products with Category Details

**Non-Compliant (Violates 3NF):**
```sql
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    product_description TEXT,
    price DECIMAL(10,2),
    stock_quantity INTEGER,
    -- Transitive dependencies below:
    category_code VARCHAR(20),          -- Intermediate
    category_name VARCHAR(100),         -- Depends on category_code
    category_description TEXT,          -- Depends on category_code
    category_tax_rate DECIMAL(5,2),     -- Depends on category_code
    -- More transitive dependencies:
    supplier_id UUID,                   -- Intermediate
    supplier_name VARCHAR(200),         -- Depends on supplier_id
    supplier_contact VARCHAR(255),      -- Depends on supplier_id
    supplier_country VARCHAR(100)       -- Depends on supplier_id
);
```

**3NF Compliant Decomposition:**
```sql
-- Product categories reference table
CREATE TABLE product_categories (
    category_code VARCHAR(20) PRIMARY KEY,
    category_name VARCHAR(100) UNIQUE NOT NULL,
    category_description TEXT,
    category_tax_rate DECIMAL(5,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT true
);

-- Suppliers reference table
CREATE TABLE suppliers (
    supplier_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_name VARCHAR(200) UNIQUE NOT NULL,
    supplier_contact VARCHAR(255),
    supplier_email VARCHAR(255),
    supplier_country VARCHAR(100),
    supplier_rating DECIMAL(3,2) CHECK (supplier_rating BETWEEN 0 AND 5)
);

-- Products with only direct dependencies
CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_name VARCHAR(200) NOT NULL,
    product_description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    category_code VARCHAR(20) REFERENCES product_categories(category_code),
    supplier_id UUID REFERENCES suppliers(supplier_id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_products_category ON products(category_code);
CREATE INDEX idx_products_supplier ON products(supplier_id);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = true;
```

#### Violation Type 3: Workflow Executions with Status Details

**Non-Compliant (Violates 3NF):**
```sql
CREATE TABLE workflow_executions (
    execution_id UUID PRIMARY KEY,
    workflow_id UUID NOT NULL,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    -- Transitive dependencies below:
    status_code VARCHAR(20),            -- Intermediate
    status_name VARCHAR(100),           -- Depends on status_code
    status_description TEXT,            -- Depends on status_code
    status_is_terminal BOOLEAN,         -- Depends on status_code
    status_severity VARCHAR(20),        -- Depends on status_code
    -- More transitive dependencies:
    ai_provider_id VARCHAR(50),         -- Intermediate
    ai_provider_name VARCHAR(100),      -- Depends on ai_provider_id
    ai_provider_endpoint VARCHAR(500),  -- Depends on ai_provider_id
    ai_provider_model VARCHAR(100)      -- Depends on ai_provider_id
);
```

**3NF Compliant Decomposition:**
```sql
-- Execution status reference table
CREATE TABLE execution_statuses (
    status_code VARCHAR(20) PRIMARY KEY,
    status_name VARCHAR(100) UNIQUE NOT NULL,
    status_description TEXT,
    is_terminal BOOLEAN DEFAULT false,
    severity VARCHAR(20) CHECK (severity IN ('info', 'warning', 'error', 'critical')),
    display_order INTEGER,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- AI provider reference table
CREATE TABLE ai_providers (
    provider_id VARCHAR(50) PRIMARY KEY,
    provider_name VARCHAR(100) UNIQUE NOT NULL,
    api_endpoint VARCHAR(500),
    documentation_url VARCHAR(500),
    is_enabled BOOLEAN DEFAULT true,
    rate_limit_per_minute INTEGER,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- AI models reference table
CREATE TABLE ai_models (
    model_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_id VARCHAR(50) REFERENCES ai_providers(provider_id),
    model_name VARCHAR(100) NOT NULL,
    model_version VARCHAR(50),
    capabilities JSONB,
    cost_per_token DECIMAL(10,6),
    UNIQUE(provider_id, model_name, model_version)
);

-- Workflow executions with only direct dependencies
CREATE TABLE workflow_executions (
    execution_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id UUID NOT NULL REFERENCES workflows(workflow_id),
    status_code VARCHAR(20) REFERENCES execution_statuses(status_code),
    model_id UUID REFERENCES ai_models(model_id),
    started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMPTZ,
    execution_duration_ms INTEGER GENERATED ALWAYS AS 
        (EXTRACT(EPOCH FROM (completed_at - started_at)) * 1000) STORED,
    input_tokens INTEGER,
    output_tokens INTEGER,
    total_cost DECIMAL(10,4),
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for common query patterns
CREATE INDEX idx_executions_workflow ON workflow_executions(workflow_id, started_at DESC);
CREATE INDEX idx_executions_status ON workflow_executions(status_code);
CREATE INDEX idx_executions_model ON workflow_executions(model_id);
CREATE INDEX idx_executions_timerange ON workflow_executions(started_at, completed_at);
```

### 4.2 Design Patterns for 3NF Compliance

#### Pattern 1: Reference/Lookup Tables

**Principle**: Extract all categorical and lookup data into dedicated reference tables.

```sql
-- Status reference table
CREATE TABLE workflow_statuses (
    status_id SERIAL PRIMARY KEY,
    status_code VARCHAR(20) UNIQUE NOT NULL,
    status_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT true
);

-- Priority reference table
CREATE TABLE priority_levels (
    priority_id SERIAL PRIMARY KEY,
    priority_code VARCHAR(20) UNIQUE NOT NULL,
    priority_name VARCHAR(50) NOT NULL,
    priority_value INTEGER UNIQUE NOT NULL,
    description TEXT
);

-- Main entity with references
CREATE TABLE workflows (
    workflow_id UUID PRIMARY KEY,
    workflow_name VARCHAR(200) NOT NULL,
    status_id INTEGER REFERENCES workflow_statuses(status_id),
    priority_id INTEGER REFERENCES priority_levels(priority_id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
```

#### Pattern 2: Entity Separation

**Principle**: Separate distinct entities even when relationships exist.

```sql
-- User entity
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role_id INTEGER REFERENCES user_roles(role_id)
);

-- Role entity (not embedded in users)
CREATE TABLE user_roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    permissions JSONB
);

-- Organization entity
CREATE TABLE organizations (
    org_id UUID PRIMARY KEY,
    org_name VARCHAR(200) UNIQUE NOT NULL,
    org_type_id INTEGER REFERENCES org_types(org_type_id)
);

-- Organization type entity
CREATE TABLE org_types (
    org_type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    default_features JSONB
);

-- User-Organization membership (many-to-many)
CREATE TABLE user_organizations (
    user_id UUID REFERENCES users(user_id),
    org_id UUID REFERENCES organizations(org_id),
    joined_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    is_admin BOOLEAN DEFAULT false,
    PRIMARY KEY (user_id, org_id)
);
```

#### Pattern 3: Hierarchical Data

**Principle**: Use self-referencing foreign keys for hierarchical structures.

```sql
-- Category hierarchy (3NF compliant)
CREATE TABLE categories (
    category_id UUID PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id UUID REFERENCES categories(category_id),
    level INTEGER,
    sort_order INTEGER,
    is_leaf BOOLEAN DEFAULT false,
    UNIQUE(parent_category_id, category_name)
);

-- Products reference categories (no transitive deps)
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    category_id UUID REFERENCES categories(category_id),
    price DECIMAL(10,2)
);

-- Efficient category path queries with materialized path
CREATE TABLE category_paths (
    category_id UUID PRIMARY KEY REFERENCES categories(category_id),
    path_ids UUID[] NOT NULL,
    path_names TEXT[] NOT NULL,
    depth INTEGER NOT NULL
);
```

#### Pattern 4: Temporal/Historical Data with Proper Normalization

**Principle**: Keep current state normalized; historical snapshots can reference lookup tables.

```sql
-- Current status lookup
CREATE TABLE task_statuses (
    status_id SERIAL PRIMARY KEY,
    status_code VARCHAR(20) UNIQUE NOT NULL,
    status_name VARCHAR(100) NOT NULL,
    next_allowed_statuses INTEGER[]
);

-- Current task state (normalized)
CREATE TABLE tasks (
    task_id UUID PRIMARY KEY,
    task_name VARCHAR(200) NOT NULL,
    current_status_id INTEGER REFERENCES task_statuses(status_id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Status change history (references lookup table)
CREATE TABLE task_status_history (
    history_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID REFERENCES tasks(task_id),
    from_status_id INTEGER REFERENCES task_statuses(status_id),
    to_status_id INTEGER REFERENCES task_statuses(status_id),
    changed_by UUID REFERENCES users(user_id),
    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    reason TEXT
);
```

### 4.3 Migration Strategy

#### Phase 1: Dependency Analysis

```sql
-- Step 1: Identify all functional dependencies
-- Document dependencies for each table

-- Step 2: Identify transitive dependencies
-- Look for patterns: A → B and B → C where B is not a key

-- Step 3: Plan reference table extraction
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_schema = 'public'
    AND (column_name LIKE '%_name' OR column_name LIKE '%_description')
    AND column_name NOT IN (
        SELECT column_name 
        FROM information_schema.key_column_usage
        WHERE constraint_name LIKE '%_pkey'
    )
ORDER BY table_name, column_name;
```

#### Phase 2: Reference Table Creation

```sql
-- Example: Extract department information
BEGIN;

-- Step 1: Create reference table
CREATE TABLE departments_new (
    department_id VARCHAR(20) PRIMARY KEY,
    department_name VARCHAR(200) UNIQUE NOT NULL,
    department_location VARCHAR(200),
    department_budget DECIMAL(12,2)
);

-- Step 2: Populate from existing data
INSERT INTO departments_new (department_id, department_name, department_location, department_budget)
SELECT DISTINCT 
    department_id,
    department_name,
    department_location,
    department_budget
FROM employees_old
WHERE department_id IS NOT NULL;

-- Step 3: Verify uniqueness
DO $$
BEGIN
    ASSERT (SELECT COUNT(*) FROM departments_new) = 
           (SELECT COUNT(DISTINCT department_id) FROM employees_old WHERE department_id IS NOT NULL),
           'Department extraction failed - duplicate departments detected';
END $$;

COMMIT;
```

#### Phase 3: Schema Transformation

```sql
BEGIN;

-- Step 1: Create new normalized table
CREATE TABLE employees_new (
    employee_id UUID PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    email VARCHAR(255) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id VARCHAR(20) REFERENCES departments_new(department_id)
);

-- Step 2: Migrate data (excluding transitive attributes)
INSERT INTO employees_new (
    employee_id, employee_name, email, hire_date, salary, department_id
)
SELECT 
    employee_id, employee_name, email, hire_date, salary, department_id
FROM employees_old;

-- Step 3: Verify data integrity
DO $$
DECLARE
    old_count INTEGER;
    new_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO old_count FROM employees_old;
    SELECT COUNT(*) INTO new_count FROM employees_new;
    
    ASSERT old_count = new_count, 
           'Row count mismatch: old=' || old_count || ', new=' || new_count;
    
    -- Verify join produces same data
    ASSERT NOT EXISTS (
        SELECT 1 FROM employees_old e
        LEFT JOIN employees_new en ON e.employee_id = en.employee_id
        LEFT JOIN departments_new d ON en.department_id = d.department_id
        WHERE e.department_name != d.department_name
    ), 'Department data mismatch after migration';
END $$;

-- Step 4: Drop old table and rename
DROP TABLE employees_old;
ALTER TABLE employees_new RENAME TO employees;
ALTER TABLE departments_new RENAME TO departments;

COMMIT;
```

## 5. Monitoring and Compliance Verification

### 5.1 Continuous Monitoring

```typescript
/**
 * 3NF Compliance Monitor
 * Automated detection and reporting of transitive dependencies
 */

export class ThirdNFComplianceMonitor {
  
  async runComplianceCheck(db: Database): Promise<{
    compliant: boolean;
    violations: ComplianceViolation[];
    summary: ComplianceSummary;
  }> {
    const violations: ComplianceViolation[] = [];

    // Step 1: Get all tables
    const tables = await this.getAllTables(db);

    // Step 2: For each table, analyze for transitive dependencies
    for (const table of tables) {
      const schema = await this.getTableSchema(db, table);
      const potentialViolations = ThirdNormalFormValidator
        .detectPotentialTransitiveDependencies(schema);

      // Step 3: Validate potential violations with actual data
      for (const suggestion of potentialViolations.suggestions) {
        const dataAnalysis = await ThirdNormalFormValidator
          .analyzeDataForTransitivity(
            db,
            table,
            suggestion.intermediate,
            suggestion.potentialDependents
          );

        if (dataAnalysis.isTransitive && dataAnalysis.confidence > 0.95) {
          violations.push({
            table,
            violationType: '3NF',
            severity: 'CRITICAL',
            description: `Transitive dependency: ${suggestion.intermediate} → ${suggestion.potentialDependents.join(', ')}`,
            recommendation: `Extract to reference table: ${suggestion.intermediate}_ref`
          });
        } else if (dataAnalysis.confidence > 0.7) {
          violations.push({
            table,
            violationType: '3NF',
            severity: 'HIGH',
            description: `Likely transitive dependency (${(dataAnalysis.confidence * 100).toFixed(0)}% confidence)`,
            recommendation: 'Review functional dependencies manually'
          });
        }
      }
    }

    return {
      compliant: violations.filter(v => v.severity === 'CRITICAL').length === 0,
      violations,
      summary: this.generateSummary(violations, tables.length)
    };
  }

  private async getAllTables(db: Database): Promise<string[]> {
    const result = await db.query(`
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
      ORDER BY table_name
    `);
    return result.rows.map(r => r.table_name);
  }

  private async getTableSchema(db: Database, tableName: string): Promise<TableSchema> {
    const columnsResult = await db.query(`
      SELECT column_name
      FROM information_schema.columns
      WHERE table_schema = 'public' AND table_name = $1
      ORDER BY ordinal_position
    `, [tableName]);

    const pkResult = await db.query(`
      SELECT kcu.column_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
      WHERE tc.table_schema = 'public'
        AND tc.table_name = $1
        AND tc.constraint_type = 'PRIMARY KEY'
    `, [tableName]);

    return {
      name: tableName,
      primaryKey: pkResult.rows.map(r => r.column_name),
      columns: columnsResult.rows.map(r => r.column_name)
    };
  }

  private generateSummary(violations: ComplianceViolation[], totalTables: number): ComplianceSummary {
    const tablesWithViolations = new Set(violations.map(v => v.table)).size;
    
    return {
      totalTables,
      compliantTables: totalTables - tablesWithViolations,
      tablesWithViolations,
      criticalViolations: violations.filter(v => v.severity === 'CRITICAL').length,
      highViolations: violations.filter(v => v.severity === 'HIGH').length,
      mediumViolations: violations.filter(v => v.severity === 'MEDIUM').length,
      compliancePercentage: ((totalTables - tablesWithViolations) / totalTables * 100).toFixed(2)
    };
  }
}
```

### 5.2 CI/CD Integration

```yaml
# .github/workflows/database-3nf-compliance.yml
name: Database 3NF Compliance Check

on:
  pull_request:
    paths:
      - 'database/migrations/**'
      - 'database/schemas/**'
  schedule:
    - cron: '0 0 * * 0'  # Weekly on Sunday

jobs:
  compliance-check:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Node.js
        uses: actions/setup-node@v3
        with:
          node-version: '18'
      
      - name: Install Dependencies
        run: npm ci
      
      - name: Setup Test Database
        run: |
          docker-compose up -d postgres
          sleep 5
          npm run db:migrate
          npm run db:seed:test-data
      
      - name: Run 1NF Compliance Check
        run: npm run db:validate:1nf
      
      - name: Run 2NF Compliance Check
        run: npm run db:validate:2nf
      
      - name: Run 3NF Compliance Check
        run: |
          npm run db:validate:3nf -- --output=json > 3nf-results.json
          npm run db:validate:3nf -- --output=markdown > 3nf-report.md
      
      - name: Analyze Transitive Dependencies
        run: npm run db:analyze:transitive-deps
      
      - name: Check Compliance Threshold
        run: |
          compliance=$(jq -r '.summary.compliancePercentage' 3nf-results.json)
          threshold=95
          if (( $(echo "$compliance < $threshold" | bc -l) )); then
            echo "❌ 3NF compliance ($compliance%) is below threshold ($threshold%)"
            exit 1
          fi
          echo "✅ 3NF compliance: $compliance%"
      
      - name: Upload Compliance Report
        uses: actions/upload-artifact@v3
        with:
          name: 3nf-compliance-report
          path: |
            3nf-report.md
            3nf-results.json
      
      - name: Comment on PR
        if: github.event_name == 'pull_request'
        uses: actions/github-script@v6
        with:
          script: |
            const fs = require('fs');
            const report = fs.readFileSync('3nf-report.md', 'utf8');
            const results = JSON.parse(fs.readFileSync('3nf-results.json', 'utf8'));
            
            let emoji = results.compliant ? '✅' : '⚠️';
            let summary = `${emoji} **3NF Compliance: ${results.summary.compliancePercentage}%**\n\n`;
            summary += `- Total Tables: ${results.summary.totalTables}\n`;
            summary += `- Compliant: ${results.summary.compliantTables}\n`;
            summary += `- Violations: ${results.summary.tablesWithViolations}\n`;
            
            if (results.violations.length > 0) {
              summary += '\n### Critical Issues\n';
              results.violations
                .filter(v => v.severity === 'CRITICAL')
                .forEach(v => {
                  summary += `- **${v.table}**: ${v.description}\n`;
                });
            }
            
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `## 3NF Compliance Report\n\n${summary}\n\n<details>\n<summary>Full Report</summary>\n\n${report}\n</details>`
            });
```

## 6. Training and Awareness

### 6.1 Developer Guidelines

**Quick Reference Card:**

✅ **DO:**
- Ensure 1NF and 2NF compliance first
- Extract all lookup/reference data into separate tables
- Create dedicated tables for each distinct entity
- Use foreign keys to maintain relationships
- Document all functional dependencies
- Separate entity attributes from relational data

❌ **DON'T:**
- Store descriptive attributes that depend on non-key columns
- Embed category/status/type descriptions in main tables
- Allow non-key attributes to determine other non-key attributes
- Skip dependency analysis for new tables
- Create tables with multiple responsibilities

**Common Transitive Dependency Patterns:**

```
❌ Bad: employee_id → department_id → department_name
✅ Good: employee_id → department_id (FK to departments table)

❌ Bad: product_id → category_code → category_name
✅ Good: product_id → category_code (FK to categories table)

❌ Bad: order_id → status_code → status_description
✅ Good: order_id → status_code (FK to statuses table)
```

### 6.2 Functional Dependency Documentation Template

```markdown
# Functional Dependency Analysis: [TABLE_NAME]

## Current Schema
- **Primary Key**: column1
- **Non-Key Columns**: [list all]

## Functional Dependencies

### Direct Dependencies (✅ Compliant)
- PK → column2, column3, column4
- These attributes depend directly on the primary key

### Transitive Dependencies (❌ Violation)
- PK → intermediate_col → dependent_col1, dependent_col2
- Description: [explain the transitive path]

## Decomposition Plan

### Reference Table 1: [intermediate_entity]
**Purpose**: Store attributes related to [entity description]
- **Primary Key**: intermediate_col
- **Columns**: intermediate_col, dependent_col1, dependent_col2

**Migration Steps**:
1. Create reference table
2. Populate with DISTINCT values from current table
3. Add foreign key constraint
4. Remove transitive columns from original table

### Updated Original Table: [table_name]
- **Primary Key**: column1
- **Columns**: column1, column2, column3, column4, intermediate_col (FK)
- **Foreign Keys**: intermediate_col → [intermediate_entity](intermediate_col)

## Verification Queries

```sql
-- Verify no data loss
SELECT COUNT(*) FROM original_table; -- Should match after migration

-- Verify referential integrity
SELECT COUNT(*) FROM original_table o
LEFT JOIN reference_table r ON o.intermediate_col = r.intermediate_col
WHERE r.intermediate_col IS NULL; -- Should be 0
```
```

### 6.3 Code Review Checklist

For all database schema changes:

- [ ] 1NF compliance verified
- [ ] 2NF compliance verified  
- [ ] All functional dependencies documented
- [ ] No transitive dependencies (X → Y → Z) exist
- [ ] All lookup/reference data extracted to separate tables
- [ ] Foreign key constraints defined for all references
- [ ] Reference tables have appropriate indexes
- [ ] Data migration scripts tested
- [ ] Lossless-join decomposition verified
- [ ] No information loss after normalization
- [ ] Query patterns updated for new schema
- [ ] Performance impact assessed and acceptable
- [ ] 3NF compliance validation passes

## 7. Exceptions and Exemptions

### 7.1 Approved Exceptions

**EXC-3NF-001: Denormalization for Performance**
- **Justification**: Critical read-heavy queries require denormalized data
- **Scope**: Read-only materialized views, caching layers, analytics tables
- **Conditions**:
  - Must maintain normalized source of truth
  - Must have automated synchronization
  - Must document performance benefit (minimum 3x improvement)
  - Must not be used for writes
- **Examples**: Reporting dashboards, analytics aggregations, search indexes
- **Review**: Quarterly with performance metrics

**EXC-3NF-002: Audit Trails and Event Logs**
- **Justification**: Historical snapshots capture point-in-time state
- **Scope**: Immutable audit logs, event sourcing, compliance records
- **Conditions**:
  - Clearly marked as historical/immutable
  - Not used for operational queries
  - Normalized tables exist for current state
  - Required for regulatory compliance
- **Examples**: Audit logs, change history, compliance snapshots
- **Review**: Annual

**EXC-3NF-003: Computed/Derived Attributes**
- **Justification**: Performance optimization for frequently calculated values
- **Scope**: Cached calculations, denormalized aggregates
- **Conditions**:
  - Marked as computed/derived (triggers, generated columns)
  - Source data in normalized form
  - Automatic updates via triggers or materialized views
- **Examples**: Total amounts, age calculations, status summaries
- **Review**: Semi-annual

### 7.2 Denormalization Best Practices

When approved denormalization is necessary:

```sql
-- Pattern 1: Materialized View (PostgreSQL)
-- Maintain normalized source
CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    customer_id UUID REFERENCES customers(id),
    order_date DATE
);

CREATE TABLE order_items (
    order_id UUID REFERENCES orders(order_id),
    product_id UUID REFERENCES products(product_id),
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- Denormalized view for reporting
CREATE MATERIALIZED VIEW order_summary_denormalized AS
SELECT 
    o.order_id,
    o.order_date,
    c.customer_id,
    c.customer_name,           -- Denormalized
    c.customer_tier,           -- Denormalized
    COUNT(oi.product_id) as item_count,
    SUM(oi.quantity * oi.unit_price) as total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date, c.customer_id, c.customer_name, c.customer_tier;

-- Refresh policy
CREATE INDEX idx_order_summary_customer ON order_summary_denormalized(customer_id);
CREATE INDEX idx_order_summary_date ON order_summary_denormalized(order_date);

-- Auto-refresh (requires pg_cron or similar)
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_denormalized;
```

```sql
-- Pattern 2: Generated/Computed Columns
CREATE TABLE employees (
    employee_id UUID PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department_id VARCHAR(20) REFERENCES departments(department_id),
    birth_date DATE,
    -- Computed column (always in sync)
    full_name VARCHAR(201) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
    age INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM age(current_date, birth_date))) STORED
);
```

```sql
-- Pattern 3: Cached Aggregates with Triggers
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    product_name VARCHAR(200),
    category_id UUID REFERENCES categories(category_id)
);

CREATE TABLE categories (
    category_id UUID PRIMARY KEY,
    category_name VARCHAR(100),
    product_count INTEGER DEFAULT 0  -- Cached aggregate
);

-- Trigger to maintain cache
CREATE OR REPLACE FUNCTION update_category_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE categories SET product_count = product_count + 1
        WHERE category_id = NEW.category_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE categories SET product_count = product_count - 1
        WHERE category_id = OLD.category_id;
    ELSIF TG_OP = 'UPDATE' AND NEW.category_id != OLD.category_id THEN
        UPDATE categories SET product_count = product_count - 1
        WHERE category_id = OLD.category_id;
        UPDATE categories SET product_count = product_count + 1
        WHERE category_id = NEW.category_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_category_count
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION update_category_count();
```

## 8. References and Resources

### 8.1 Standards and Specifications

- **Codd, E.F.** (1971). "Further Normalization of the Data Base Relational Model"
- **Kent, William** (1983). "A Simple Guide to Five Normal Forms in Relational Database Theory"
- **Zaniolo, Carlo** (1982). "A New Normal Form for the Design of Relational Database Schemata"
- **Date, C.J.** (2019). "Database Design and Relational Theory: Normal Forms and All That Jazz"
- **Elmasri & Navathe** (2015). "Fundamentals of Database Systems" (7th Edition)
- **ISO/IEC 9075** - SQL Standard
- **ISO/IEC 27001:2022** - Information Security Management
- **ISO 9001:2015** - Quality Management Systems

### 8.2 Internal Documentation

- [1NF Compliance Guide](./1NF.md)
- [2NF Compliance Guide](./2NF.md)
- [BCNF Compliance Guide](./BCNF.md) (Next level)
- [Database Design Standards](../technical/database-design-standards.md)
- [Functional Dependency Analysis Guide](../technical/functional-dependency-analysis.md)
- [Data Modeling Best Practices](../technical/data-modeling.md)
- [Agent Data Engineer Guidelines](../../.cursor/rules/agent-data-engineer.mdc)

### 8.3 Tools and Automation

- **3NF Validator**: `/scripts/validate-3nf.ts`
- **Transitive Dependency Analyzer**: `/tools/transitive-dep-analyzer`
- **Reference Table Generator**: `/tools/generate-reference-tables`
- **Schema Normalizer**: `/tools/normalize-schema`
- **Compliance Dashboard**: `/web/compliance/3nf-dashboard`

## 9. Compliance Metrics and KPIs

### 9.1 Key Performance Indicators

| Metric | Target | Current | Status |
|--------|--------|---------|--------|
| Tables in 3NF | 100% | TBD | 🔄 |
| Transitive Dependencies | 0 | TBD | 🔄 |
| Reference Tables Coverage | 100% | TBD | 🔄 |
| 3NF Compliance Score | ≥95% | TBD | 🔄 |
| Functional Dependencies Documented | 100% | TBD | 🔄 |
| Lookup Data Extraction | 100% | TBD | 🔄 |

### 9.2 Reporting

**Monthly Compliance Report includes:**
- Overall 3NF compliance percentage
- Transitive dependencies detected and remediated
- Reference tables created
- Functional dependency documentation coverage
- Exception requests processed
- Denormalization justifications reviewed
- Training completion rates
- Schema review metrics

### 9.3 Compliance Trends

Track over time:
- Compliance percentage trend
- Number of violations by severity
- Time to remediate violations
- Reference table growth
- Schema complexity metrics

## 10. Revision History

| Version | Date | Author | Changes |
|---------|------|--------|---------|
| 1.0.0 | 2025-10-10 | Data Architecture Team | Initial release - comprehensive 3NF compliance framework |

---

## Appendix A: Transitive Dependency Examples

### A.1 Classic Examples

**Example 1: Employee-Department**
```
employees (employee_id, name, dept_id, dept_name, dept_location)

Functional Dependencies:
- employee_id → name, dept_id
- dept_id → dept_name, dept_location
- employee_id → dept_id → dept_name (TRANSITIVE!)

Solution: Extract departments table
```

**Example 2: Product-Category**
```
products (product_id, name, price, category_code, category_name, tax_rate)

Functional Dependencies:
- product_id → name, price, category_code
- category_code → category_name, tax_rate
- product_id → category_code → category_name (TRANSITIVE!)

Solution: Extract categories table
```

**Example 3: Order-Status**
```
orders (order_id, customer_id, status_code, status_name, status_description)

Functional Dependencies:
- order_id → customer_id, status_code
- status_code → status_name, status_description
- order_id → status_code → status_name (TRANSITIVE!)

Solution: Extract order_statuses table
```

### A.2 Less Obvious Examples

**Example 4: Computed/Derived Attributes**
```
projects (project_id, start_date, end_date, duration_days)

If duration_days = end_date - start_date:
- project_id → start_date, end_date
- (start_date, end_date) → duration_days
- project_id → (start_date, end_date) → duration_days (TRANSITIVE!)

Solution: Use computed column or remove duration_days
```

**Example 5: Geographic Hierarchy**
```
addresses (address_id, street, city, state, country, country_code)

If country → country_code:
- address_id → street, city, state, country
- country → country_code
- address_id → country → country_code (TRANSITIVE!)

Solution: Extract countries table
```

## Appendix B: Normalization Decision Tree

```
┌─────────────────────────────────┐
│ Is the table in 1NF?            │
│ (Atomic values, PK exists)      │
└───────────┬─────────────────────┘
            │ NO
            ├──────> Fix 1NF violations first
            │
            │ YES
            ▼
┌─────────────────────────────────┐
│ Does table have composite PK?   │
└───────────┬─────────────────────┘
            │ YES
            ├──────> Check for partial dependencies (2NF)
            │         Found? Extract to separate tables
            │
            │ NO or FIXED
            ▼
┌─────────────────────────────────┐
│ Do non-key attributes depend    │
│ on other non-key attributes?    │
└───────────┬─────────────────────┘
            │ YES
            ├──────> Transitive dependency (3NF violation)
            │         Extract reference tables
            │
            │ NO
            ▼
┌─────────────────────────────────┐
│ Table is in 3NF ✅              │
│ Consider BCNF for advanced cases│
└─────────────────────────────────┘
```

## Appendix C: Real-World Case Studies

### C.1 E-Commerce Platform Migration

**Challenge**: Large e-commerce database with extensive transitive dependencies

**Original Issues**:
- Product table contained category descriptions (category_code → category_name, description, tax_rate)
- Order table contained status details (status_code → status_name, description, is_terminal)
- Customer table contained tier information (tier_code → tier_name, discount_rate, benefits)

**Solution Implementation**:
1. Created 12 reference tables for lookup data
2. Migrated 2.5M product records
3. Updated 500+ application queries

**Results**:
- Storage reduced: 850MB → 420MB (51% reduction)
- Update anomalies eliminated: 100%
- Query performance: 2-4x improvement for category/status queries
- Maintenance time: 60% reduction for taxonomy updates
- Data consistency: 100% (vs. 94% pre-normalization)

### C.2 AI Workflow Management System

**Challenge**: Workflow execution system with embedded configuration metadata

**Original Issues**:
- Execution table contained provider details (provider_id → provider_name, endpoint, model_list)
- Execution table contained status metadata (status → description, severity, next_states)
- Massive redundancy for frequently used providers/statuses

**Solution Implementation**:
1. Extracted ai_providers reference table
2. Extracted ai_models reference table  
3. Extracted execution_statuses reference table
4. Created provider_capabilities junction table

**Results**:
- Storage reduced: 12GB → 3.2GB (73% reduction)
- Provider metadata updates: instant (vs. batch updates)
- New provider integration: 5 minutes (vs. 2 hours migration)
- Status workflow changes: no downtime (vs. application deployment)
- Query performance: 5-8x improvement for provider analytics

### C.3 Multi-Tenant SaaS Application

**Challenge**: Tenant and user management with organizational hierarchy

**Original Issues**:
- User table contained org details (org_id → org_name, org_type, org_tier, features)
- User table contained role details (role_id → role_name, permissions, access_level)
- Subscription information duplicated across tenant users

**Solution Implementation**:
1. Extracted organizations table
2. Extracted organization_types table
3. Extracted user_roles table
4. Extracted subscription_tiers table
5. Created proper foreign key relationships

**Results**:
- Storage efficiency: 40% reduction
- Organization updates: single row vs. hundreds
- Role permission changes: instant propagation
- Multi-tenancy isolation: improved with FK constraints
- Schema clarity: significantly improved for new developers

---

**Document End**

*For questions or clarification on 3NF compliance, contact the Data Architecture Team or refer to the [Data Engineer Agent](../../.cursor/rules/agent-data-engineer.mdc).*