# Second Normal Form (2NF) Compliance Guide

## Document Control

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

## Executive Summary

This document establishes the compliance requirements, validation procedures, and implementation guidelines for Second Normal Form (2NF) in database design within the AI Integration Workflow Standardize project. 2NF builds upon First Normal Form (1NF) by eliminating partial dependencies, ensuring that all non-key attributes are fully functionally dependent on the entire primary key. This normalization level reduces data redundancy, improves data integrity, and establishes a foundation for Third Normal Form (3NF).

## 1. Overview

### 1.1 Purpose

Second Normal Form (2NF) compliance ensures that all database tables are structured to:
- Maintain all 1NF requirements
- Eliminate partial dependencies on composite keys
- Ensure full functional dependency of all non-key attributes
- Reduce redundancy and update anomalies
- Create a foundation for higher normalization forms (3NF, BCNF)

### 1.2 Scope

This compliance framework applies to:
- All relational database tables (PostgreSQL, MySQL, SQLite, Oracle)
- Tables with composite primary keys or candidate keys
- Data models for AI workflow metadata and configuration
- User authentication and authorization data structures
- Audit, logging, and tracking tables
- Junction/bridge tables in many-to-many relationships
- Historical and temporal data structures

### 1.3 Regulatory Alignment

2NF 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)
- **ISO/IEC 27701** - Control 7.4.1 (Identify Basis for PII Processing)
- **ISO 9001** - Clause 7.5 (Documented Information)
- **PCI DSS** - Requirement 3.4 (Render PAN Unreadable)
- **CMMI** - Process Area: Configuration Management, Data Management

## 2. Second Normal Form Requirements

### 2.1 Core Principles

A table is in Second Normal Form (2NF) if and only if:

1. **1NF Compliance**: The table must first satisfy all First Normal Form requirements
2. **Full Functional Dependency**: Every non-prime attribute (non-key column) must be fully functionally dependent on the entire primary key
3. **No Partial Dependencies**: No non-key attribute can depend on only part of a composite primary key

**Key Concept**: Partial dependency occurs when a non-key attribute depends on only a subset of a composite primary key.

### 2.2 Functional Dependency Definitions

#### Functional Dependency (FD)
- **Definition**: Attribute B is functionally dependent on attribute A if each value of A is associated with exactly one value of B
- **Notation**: A → B (A determines B)
- **Example**: `student_id → student_name` (each student ID maps to one student name)

#### Full Functional Dependency
- **Definition**: Attribute B is fully functionally dependent on a composite key (A, C) if B depends on the entire key and not on any subset
- **Notation**: (A, C) → B, but A ↛ B and C ↛ B
- **Example**: `(course_id, student_id) → grade`, where grade depends on both the course and student

#### Partial Dependency (Violation)
- **Definition**: Attribute B depends on only part of a composite primary key
- **Notation**: If PK = (A, C), then A → B (but (A, C) → B)
- **Example**: `(course_id, student_id) → course_name` where course_name only depends on course_id

### 2.3 Detailed Requirements

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

#### REQ-2NF-002: No Partial Dependencies (MANDATORY)
- **Requirement**: All non-key attributes must depend on the complete primary key
- **Validation**: For tables with composite primary keys, verify no partial dependencies exist
- **Severity**: Critical
- **Scope**: Only applies to tables with composite (multi-column) primary keys

#### REQ-2NF-003: Full Functional Dependency Verification (MANDATORY)
- **Requirement**: Document and verify functional dependencies for all attributes
- **Validation**: Functional dependency analysis for schema changes
- **Severity**: High
- **Deliverable**: Functional dependency documentation for complex schemas

#### REQ-2NF-004: Proper Decomposition (MANDATORY)
- **Requirement**: Tables with partial dependencies must be decomposed without data loss
- **Validation**: Decomposed tables preserve all information and relationships
- **Severity**: Critical
- **Method**: Lossless-join decomposition

#### REQ-2NF-005: Single-Column Primary Key Exemption (INFORMATIONAL)
- **Requirement**: Tables with single-column primary keys automatically satisfy 2NF
- **Validation**: No partial dependency possible with single-column PK
- **Severity**: Informational
- **Note**: Still verify 1NF compliance and consider 3NF requirements

## 3. Compliance Validation

### 3.1 Automated Validation

#### Database Schema Analysis Script

```sql
-- 2NF Compliance Validation Query
-- Identifies tables with composite primary keys that may have partial dependencies

WITH composite_key_tables AS (
    -- Find all tables with composite (multi-column) primary keys
    SELECT 
        tc.table_schema,
        tc.table_name,
        tc.constraint_name,
        COUNT(kcu.column_name) as key_column_count,
        array_agg(kcu.column_name ORDER BY kcu.ordinal_position) as key_columns
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
        AND tc.table_name = kcu.table_name
    WHERE tc.constraint_type = 'PRIMARY KEY'
        AND tc.table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY tc.table_schema, tc.table_name, tc.constraint_name
    HAVING COUNT(kcu.column_name) > 1
),
table_columns AS (
    -- Get all non-key columns for tables with composite keys
    SELECT 
        c.table_schema,
        c.table_name,
        c.column_name,
        c.data_type
    FROM information_schema.columns c
    JOIN composite_key_tables ckt
        ON c.table_schema = ckt.table_schema
        AND c.table_name = ckt.table_name
    WHERE NOT (c.column_name = ANY(
        SELECT unnest(key_columns) 
        FROM composite_key_tables ckt2 
        WHERE ckt2.table_name = c.table_name
    ))
)
SELECT 
    ckt.table_schema,
    ckt.table_name,
    ckt.key_columns as composite_primary_key,
    array_agg(tc.column_name) as non_key_columns,
    'REQUIRES_MANUAL_REVIEW' as review_status,
    '2NF validation needed for partial dependencies' as recommendation
FROM composite_key_tables ckt
LEFT JOIN table_columns tc
    ON ckt.table_schema = tc.table_schema
    AND ckt.table_name = tc.table_name
GROUP BY ckt.table_schema, ckt.table_name, ckt.key_columns;

-- Check for common partial dependency patterns
-- Example: Junction tables with descriptive attributes
SELECT 
    t.table_schema,
    t.table_name,
    'Potential partial dependency in junction table' as issue_type,
    'HIGH' as severity,
    'Review if descriptive columns depend on only one FK' as description
FROM information_schema.tables t
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
    AND t.table_type = 'BASE TABLE'
    AND (
        -- Junction table pattern with extra columns
        SELECT COUNT(*) 
        FROM information_schema.table_constraints tc
        WHERE tc.table_name = t.table_name
            AND tc.table_schema = t.table_schema
            AND tc.constraint_type = 'FOREIGN KEY'
    ) >= 2
    AND (
        -- Has columns beyond the foreign keys
        SELECT COUNT(*) 
        FROM information_schema.columns c
        WHERE c.table_name = t.table_name
            AND c.table_schema = t.table_schema
    ) > (
        SELECT COUNT(DISTINCT 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.table_name = t.table_name
            AND tc.table_schema = t.table_schema
            AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
    );
```

#### TypeScript Validation Helper

```typescript
/**
 * 2NF Compliance Validator
 * Validates data structures against Second Normal Form requirements
 */

interface FunctionalDependency {
  determinant: string[];  // Left side of FD (A → B)
  dependent: string[];    // Right side of FD (A → B)
}

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

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

    // Check 1: Single-column primary key automatically satisfies 2NF
    if (schema.primaryKey.length === 1) {
      return {
        isCompliant: true,
        violations: []
      };
    }

    // Check 2: Identify partial dependencies
    const nonKeyColumns = schema.columns.filter(
      col => !schema.primaryKey.includes(col)
    );

    functionalDependencies.forEach(fd => {
      // Check if this FD represents a partial dependency
      const isPartialDependency = this.isPartialDependency(
        fd,
        schema.primaryKey,
        nonKeyColumns
      );

      if (isPartialDependency) {
        violations.push({
          type: 'PARTIAL_DEPENDENCY',
          description: `Non-key attribute(s) ${fd.dependent.join(', ')} depend on only part of the primary key: ${fd.determinant.join(', ')}`,
          severity: 'CRITICAL',
          determinant: fd.determinant,
          dependent: fd.dependent
        });
      }
    });

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

  /**
   * Checks if a functional dependency is a partial dependency
   */
  private static isPartialDependency(
    fd: FunctionalDependency,
    primaryKey: string[],
    nonKeyColumns: string[]
  ): boolean {
    // FD is a partial dependency if:
    // 1. The dependent is a non-key attribute
    // 2. The determinant is a proper subset of the primary key
    
    const dependentIsNonKey = fd.dependent.some(col => 
      nonKeyColumns.includes(col)
    );

    if (!dependentIsNonKey) return false;

    const determinantIsSubsetOfPK = fd.determinant.every(col =>
      primaryKey.includes(col)
    );

    const determinantIsProperSubset = 
      determinantIsSubsetOfPK && 
      fd.determinant.length < primaryKey.length;

    return determinantIsProperSubset;
  }

  /**
   * Suggests 2NF-compliant decomposition
   */
  static suggestDecomposition(
    schema: TableSchema,
    violations: Array<{
      determinant: string[];
      dependent: string[];
    }>
  ): {
    originalTable: TableSchema;
    decomposedTables: TableSchema[];
  } {
    const decomposedTables: TableSchema[] = [];
    const remainingColumns = [...schema.columns];

    // For each partial dependency, create a new table
    violations.forEach((violation, index) => {
      const newTableName = `${schema.name}_${violation.determinant.join('_')}`;
      
      decomposedTables.push({
        name: newTableName,
        primaryKey: violation.determinant,
        columns: [...violation.determinant, ...violation.dependent],
        foreignKeys: []
      });

      // Remove dependent columns from original table
      violation.dependent.forEach(col => {
        const idx = remainingColumns.indexOf(col);
        if (idx > -1) remainingColumns.splice(idx, 1);
      });
    });

    // Original table retains primary key and non-dependent columns
    const updatedOriginalTable: TableSchema = {
      ...schema,
      columns: remainingColumns,
      foreignKeys: decomposedTables.map(dt => ({
        columns: dt.primaryKey,
        references: { table: dt.name, columns: dt.primaryKey }
      }))
    };

    return {
      originalTable: updatedOriginalTable,
      decomposedTables
    };
  }

  /**
   * Analyzes a table for potential partial dependencies based on naming patterns
   */
  static detectPotentialPartialDependencies(schema: TableSchema): string[] {
    const warnings: string[] = [];

    if (schema.primaryKey.length <= 1) {
      return warnings;
    }

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

    // Check for columns that share prefixes with partial primary keys
    schema.primaryKey.forEach(keyCol => {
      const keyPrefix = keyCol.split('_')[0];
      
      nonKeyColumns.forEach(nonKeyCol => {
        if (nonKeyCol.startsWith(keyPrefix) && keyCol !== nonKeyCol) {
          warnings.push(
            `Column '${nonKeyCol}' may depend only on '${keyCol}' (partial dependency)`
          );
        }
      });
    });

    return warnings;
  }
}

/**
 * Example usage and test cases
 */
export namespace SecondNormalFormExamples {
  
  export function exampleViolation(): void {
    // Example: Course enrollment with partial dependency
    const schema: TableSchema = {
      name: 'course_enrollments',
      primaryKey: ['course_id', 'student_id'],
      columns: [
        'course_id',
        'student_id',
        'enrollment_date',
        'grade',
        'course_name',      // Violation: depends only on course_id
        'course_credits',   // Violation: depends only on course_id
        'student_name'      // Violation: depends only on student_id
      ]
    };

    const functionalDependencies: FunctionalDependency[] = [
      { determinant: ['course_id'], dependent: ['course_name', 'course_credits'] },
      { determinant: ['student_id'], dependent: ['student_name'] },
      { determinant: ['course_id', 'student_id'], dependent: ['enrollment_date', 'grade'] }
    ];

    const result = SecondNormalFormValidator.validate2NF(schema, functionalDependencies);
    
    console.log('2NF Compliance:', result.isCompliant);
    console.log('Violations:', result.violations);

    if (!result.isCompliant) {
      const decomposition = SecondNormalFormValidator.suggestDecomposition(
        schema,
        result.violations
      );
      console.log('Suggested decomposition:', decomposition);
    }
  }
}
```

### 3.2 Manual Validation Checklist

| Check ID | Validation Item | Method | Frequency |
|----------|----------------|--------|-----------|
| CHK-2NF-001 | Verify 1NF compliance first | Automated check | Per deployment |
| CHK-2NF-002 | Identify all composite primary keys | Schema review | Per schema change |
| CHK-2NF-003 | Document functional dependencies | Manual analysis | Per table with composite PK |
| CHK-2NF-004 | Detect partial dependencies | Automated + manual | Per schema change |
| CHK-2NF-005 | Validate decomposition correctness | Test data integrity | Per normalization change |
| CHK-2NF-006 | Review junction table patterns | Manual review | Monthly |

## 4. Implementation Guidelines

### 4.1 Correcting Non-Compliant Designs

#### Violation Type 1: Course Enrollment with Partial Dependencies

**Non-Compliant (Violates 2NF):**
```sql
CREATE TABLE course_enrollments (
    course_id VARCHAR(20),
    student_id UUID,
    enrollment_date DATE,
    grade VARCHAR(2),
    -- Partial dependencies below:
    course_name VARCHAR(200),      -- Depends only on course_id
    course_credits INTEGER,        -- Depends only on course_id
    course_instructor VARCHAR(100),-- Depends only on course_id
    student_name VARCHAR(200),     -- Depends only on student_id
    student_major VARCHAR(100),    -- Depends only on student_id
    PRIMARY KEY (course_id, student_id)
);
```

**Functional Dependencies:**
- `course_id → course_name, course_credits, course_instructor` (Partial!)
- `student_id → student_name, student_major` (Partial!)
- `(course_id, student_id) → enrollment_date, grade` (Full)

**2NF Compliant Decomposition:**
```sql
-- Separate table for course information
CREATE TABLE courses (
    course_id VARCHAR(20) PRIMARY KEY,
    course_name VARCHAR(200) NOT NULL,
    course_credits INTEGER NOT NULL CHECK (course_credits > 0),
    course_instructor VARCHAR(100)
);

-- Separate table for student information
CREATE TABLE students (
    student_id UUID PRIMARY KEY,
    student_name VARCHAR(200) NOT NULL,
    student_major VARCHAR(100)
);

-- Enrollment table with only fully dependent attributes
CREATE TABLE course_enrollments (
    course_id VARCHAR(20),
    student_id UUID,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
    PRIMARY KEY (course_id, student_id),
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE RESTRICT,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);
```

**Benefits of Decomposition:**
- No redundancy: Course/student information stored once
- Update anomaly eliminated: Changing course name updates one row
- Insert anomaly eliminated: Can add courses/students without enrollments
- Delete anomaly eliminated: Removing enrollment preserves course/student data

#### Violation Type 2: Workflow Step Execution with Metadata

**Non-Compliant (Violates 2NF):**
```sql
CREATE TABLE workflow_step_executions (
    workflow_id UUID,
    step_order INTEGER,
    execution_timestamp TIMESTAMPTZ,
    execution_status VARCHAR(20),
    execution_duration_ms INTEGER,
    -- Partial dependencies below:
    step_name VARCHAR(200),        -- Depends only on (workflow_id, step_order)
    step_type VARCHAR(50),         -- Depends only on (workflow_id, step_order)
    step_config JSONB,             -- Depends only on (workflow_id, step_order)
    workflow_name VARCHAR(200),    -- Depends only on workflow_id
    workflow_owner UUID,           -- Depends only on workflow_id
    PRIMARY KEY (workflow_id, step_order, execution_timestamp)
);
```

**2NF Compliant Decomposition:**
```sql
-- Workflow metadata
CREATE TABLE workflows (
    workflow_id UUID PRIMARY KEY,
    workflow_name VARCHAR(200) NOT NULL,
    workflow_owner UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Workflow step definitions
CREATE TABLE workflow_steps (
    workflow_id UUID,
    step_order INTEGER,
    step_name VARCHAR(200) NOT NULL,
    step_type VARCHAR(50) NOT NULL,
    step_config JSONB,
    PRIMARY KEY (workflow_id, step_order),
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
);

-- Step execution history (only execution-specific data)
CREATE TABLE workflow_step_executions (
    execution_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id UUID,
    step_order INTEGER,
    execution_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    execution_status VARCHAR(20) CHECK (execution_status IN ('pending', 'running', 'success', 'failed')),
    execution_duration_ms INTEGER,
    error_message TEXT,
    FOREIGN KEY (workflow_id, step_order) 
        REFERENCES workflow_steps(workflow_id, step_order) 
        ON DELETE CASCADE
);

-- Index for efficient execution queries
CREATE INDEX idx_executions_workflow_time 
    ON workflow_step_executions(workflow_id, execution_timestamp DESC);
```

#### Violation Type 3: Product Orders with Item Details

**Non-Compliant (Violates 2NF):**
```sql
CREATE TABLE order_items (
    order_id UUID,
    product_id UUID,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    -- Partial dependencies below:
    product_name VARCHAR(200),     -- Depends only on product_id
    product_category VARCHAR(100), -- Depends only on product_id
    product_description TEXT,      -- Depends only on product_id
    order_date DATE,               -- Depends only on order_id
    customer_id UUID,              -- Depends only on order_id
    PRIMARY KEY (order_id, product_id)
);
```

**2NF Compliant Decomposition:**
```sql
-- Product catalog
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    product_category VARCHAR(100),
    product_description TEXT,
    current_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Order header
CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    customer_id UUID REFERENCES customers(id),
    order_date DATE DEFAULT CURRENT_DATE,
    order_status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Order line items (only relationship-specific data)
CREATE TABLE order_items (
    order_id UUID,
    product_id UUID,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL, -- Price at time of order
    line_total DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);
```

### 4.2 Design Patterns for 2NF Compliance

#### Pattern 1: Separate Entities from Relationships

**Principle**: Entity attributes belong in entity tables; relationship attributes belong in junction tables.

```sql
-- Entity: AI Providers
CREATE TABLE ai_providers (
    provider_id UUID PRIMARY KEY,
    provider_name VARCHAR(100) UNIQUE NOT NULL,
    api_endpoint VARCHAR(500),
    documentation_url VARCHAR(500),
    supported_models JSONB
);

-- Entity: Workflows
CREATE TABLE workflows (
    workflow_id UUID PRIMARY KEY,
    workflow_name VARCHAR(200) NOT NULL,
    description TEXT
);

-- Relationship: Workflow-Provider configuration (only relationship data)
CREATE TABLE workflow_provider_config (
    workflow_id UUID,
    provider_id UUID,
    is_enabled BOOLEAN DEFAULT true,
    priority INTEGER DEFAULT 0,
    custom_config JSONB,
    last_used_at TIMESTAMPTZ,
    PRIMARY KEY (workflow_id, provider_id),
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE,
    FOREIGN KEY (provider_id) REFERENCES ai_providers(id) ON DELETE RESTRICT
);
```

#### Pattern 2: Historical/Temporal Data

**Principle**: Separate current state from historical records to avoid partial dependencies on timestamps.

```sql
-- Current user status
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    current_status VARCHAR(20) DEFAULT 'active',
    current_role VARCHAR(50)
);

-- Status change history
CREATE TABLE user_status_history (
    history_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    changed_by UUID REFERENCES users(id),
    reason TEXT
);

-- Role assignment history
CREATE TABLE user_role_history (
    history_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL,
    assigned_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    assigned_by UUID REFERENCES users(id),
    revoked_at TIMESTAMPTZ
);
```

#### Pattern 3: Attribute Extraction

**Principle**: Extract attributes that depend on part of the key into their own tables.

```sql
-- Before: Partial dependency on server_id
-- deployment_logs (server_id, deployment_id, timestamp, server_hostname, ...)

-- After: Extract server attributes
CREATE TABLE servers (
    server_id UUID PRIMARY KEY,
    server_hostname VARCHAR(255) UNIQUE NOT NULL,
    server_ip INET,
    server_location VARCHAR(100),
    server_environment VARCHAR(20)
);

CREATE TABLE deployments (
    deployment_id UUID PRIMARY KEY,
    application_name VARCHAR(100),
    version VARCHAR(50),
    deployed_by UUID REFERENCES users(id)
);

CREATE TABLE deployment_logs (
    server_id UUID,
    deployment_id UUID,
    log_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deployment_status VARCHAR(20),
    deployment_duration_seconds INTEGER,
    error_log TEXT,
    PRIMARY KEY (server_id, deployment_id, log_timestamp),
    FOREIGN KEY (server_id) REFERENCES servers(id),
    FOREIGN KEY (deployment_id) REFERENCES deployments(id)
);
```

### 4.3 Migration Strategy

#### Phase 1: Analysis and Planning

```sql
-- 1. Identify tables with composite primary keys
SELECT 
    tc.table_name,
    array_agg(kcu.column_name ORDER BY kcu.ordinal_position) as pk_columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu USING (constraint_name, table_schema)
WHERE tc.constraint_type = 'PRIMARY KEY'
    AND tc.table_schema = 'public'
GROUP BY tc.table_name
HAVING COUNT(*) > 1;

-- 2. Document functional dependencies (manual process)
-- Create functional dependency documentation for each table

-- 3. Identify partial dependencies through data analysis
-- Sample queries to detect data patterns
```

#### Phase 2: Schema Design

```sql
-- Design new 2NF-compliant schema
-- Document all new tables and relationships
-- Plan foreign key constraints and indexes
```

#### Phase 3: Data Migration

```sql
-- Example migration script
BEGIN;

-- Step 1: Create new normalized tables
CREATE TABLE courses (
    course_id VARCHAR(20) PRIMARY KEY,
    course_name VARCHAR(200),
    course_credits INTEGER
);

CREATE TABLE students (
    student_id UUID PRIMARY KEY,
    student_name VARCHAR(200),
    student_major VARCHAR(100)
);

-- Step 2: Migrate data from old table
INSERT INTO courses (course_id, course_name, course_credits)
SELECT DISTINCT course_id, course_name, course_credits
FROM course_enrollments_old;

INSERT INTO students (student_id, student_name, student_major)
SELECT DISTINCT student_id, student_name, student_major
FROM course_enrollments_old;

-- Step 3: Create new enrollment table
CREATE TABLE course_enrollments_new (
    course_id VARCHAR(20) REFERENCES courses(course_id),
    student_id UUID REFERENCES students(student_id),
    enrollment_date DATE,
    grade VARCHAR(2),
    PRIMARY KEY (course_id, student_id)
);

-- Step 4: Migrate enrollment data
INSERT INTO course_enrollments_new (course_id, student_id, enrollment_date, grade)
SELECT course_id, student_id, enrollment_date, grade
FROM course_enrollments_old;

-- Step 5: Verify data integrity
DO $$
BEGIN
    ASSERT (SELECT COUNT(*) FROM course_enrollments_old) = 
           (SELECT COUNT(*) FROM course_enrollments_new),
           'Row count mismatch after migration';
END $$;

-- Step 6: Drop old table and rename new (after verification)
DROP TABLE course_enrollments_old;
ALTER TABLE course_enrollments_new RENAME TO course_enrollments;

COMMIT;
```

## 5. Monitoring and Compliance Verification

### 5.1 Continuous Monitoring

```typescript
/**
 * 2NF Compliance Monitor
 * Periodic validation of database schema compliance
 */

interface ComplianceViolation {
  table: string;
  violationType: '1NF' | '2NF' | '3NF';
  severity: 'CRITICAL' | 'HIGH' | 'MEDIUM' | 'LOW';
  description: string;
  recommendation: string;
}

export class SecondNFComplianceMonitor {
  
  async runComplianceCheck(db: Database): Promise<{
    compliant: boolean;
    violations: ComplianceViolation[];
    summary: {
      totalTables: number;
      tablesWithCompositeKeys: number;
      violations: number;
      requiresReview: number;
    };
  }> {
    const violations: ComplianceViolation[] = [];

    // Step 1: Get tables with composite primary keys
    const compositeTables = await this.getCompositeKeyTables(db);

    // Step 2: For each composite table, check for potential partial dependencies
    for (const table of compositeTables) {
      const potentialViolations = await this.analyzeTableForPartialDependencies(
        db,
        table
      );
      violations.push(...potentialViolations);
    }

    return {
      compliant: violations.length === 0,
      violations,
      summary: {
        totalTables: await this.getTotalTableCount(db),
        tablesWithCompositeKeys: compositeTables.length,
        violations: violations.filter(v => v.severity === 'CRITICAL').length,
        requiresReview: violations.filter(v => v.severity !== 'CRITICAL').length
      }
    };
  }

  private async getCompositeKeyTables(db: Database): Promise<string[]> {
    const result = await db.query(`
      SELECT tc.table_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage kcu 
        ON tc.constraint_name = kcu.constraint_name
      WHERE tc.constraint_type = 'PRIMARY KEY'
        AND tc.table_schema = 'public'
      GROUP BY tc.table_name
      HAVING COUNT(kcu.column_name) > 1
    `);
    return result.rows.map(r => r.table_name);
  }

  private async analyzeTableForPartialDependencies(
    db: Database,
    tableName: string
  ): Promise<ComplianceViolation[]> {
    // Heuristic-based analysis for potential partial dependencies
    // This would be enhanced with actual functional dependency analysis
    const violations: ComplianceViolation[] = [];

    // Example: Check for naming patterns suggesting partial dependencies
    const schema = await this.getTableSchema(db, tableName);
    const warnings = SecondNormalFormValidator.detectPotentialPartialDependencies(schema);

    warnings.forEach(warning => {
      violations.push({
        table: tableName,
        violationType: '2NF',
        severity: 'HIGH',
        description: warning,
        recommendation: 'Review functional dependencies and consider decomposition'
      });
    });

    return violations;
  }

  private async getTableSchema(db: Database, tableName: string): Promise<TableSchema> {
    // Implementation to retrieve table schema from database
    // Returns TableSchema object
    return {
      name: tableName,
      primaryKey: [],
      columns: []
    };
  }

  private async getTotalTableCount(db: Database): Promise<number> {
    const result = await db.query(`
      SELECT COUNT(*) as count
      FROM information_schema.tables
      WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
    `);
    return parseInt(result.rows[0].count);
  }
}
```

### 5.2 CI/CD Integration

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

on:
  pull_request:
    paths:
      - 'database/migrations/**'
      - 'database/schemas/**'

jobs:
  compliance-check:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Database
        run: |
          docker-compose up -d postgres
          npm run db:migrate
      
      - name: Run 1NF Compliance Check (Prerequisite)
        run: npm run db:validate:1nf
      
      - name: Run 2NF Compliance Check
        run: npm run db:validate:2nf
      
      - name: Analyze Functional Dependencies
        run: npm run db:analyze:dependencies
      
      - name: Generate Compliance Report
        run: |
          npm run compliance:report -- --norm=2NF --format=markdown > 2nf-report.md
      
      - name: Upload Report
        uses: actions/upload-artifact@v3
        with:
          name: 2nf-compliance-report
          path: 2nf-report.md
      
      - name: Comment on PR
        if: always()
        uses: actions/github-script@v6
        with:
          script: |
            const fs = require('fs');
            const report = fs.readFileSync('2nf-report.md', 'utf8');
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `## 2NF Compliance Report\n\n${report}`
            });
```

## 6. Training and Awareness

### 6.1 Developer Guidelines

**Quick Reference Card:**

✅ **DO:**
- Ensure 1NF compliance first
- Analyze functional dependencies for tables with composite keys
- Separate entity attributes from relationship attributes
- Create dedicated tables for each entity referenced in composite keys
- Use foreign keys to maintain referential integrity
- Document functional dependencies in schema documentation

❌ **DON'T:**
- Include entity attributes in junction/relationship tables
- Allow non-key attributes to depend on part of a composite key
- Skip 1NF validation before addressing 2NF
- Create composite keys without analyzing dependencies
- Store descriptive attributes that depend on a single key column

### 6.2 Functional Dependency Analysis Template

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

## Table Schema
- **Primary Key**: (column1, column2, ...)
- **Non-Key Columns**: [list]

## Functional Dependencies

### Full Dependencies (✅ Compliant)
- (column1, column2) → column3, column4
- Description: These attributes require both key columns

### Partial Dependencies (❌ Violation)
- column1 → column5, column6
- Description: These attributes depend only on part of the key

## Recommended Decomposition

### New Table 1: [entity_name]
- **Primary Key**: column1
- **Columns**: column1, column5, column6

### New Table 2: [entity_name]  
- **Primary Key**: column2
- **Columns**: column2, column7, column8

### Updated Original Table: [table_name]
- **Primary Key**: (column1, column2)
- **Columns**: column1, column2, column3, column4
- **Foreign Keys**: 
  - column1 → entity1(column1)
  - column2 → entity2(column2)
```

### 6.3 Code Review Checklist

For database schema changes involving composite keys:

- [ ] 1NF compliance verified
- [ ] Functional dependencies documented
- [ ] All partial dependencies identified
- [ ] Decomposition strategy planned (if violations exist)
- [ ] New entity tables created for partial dependencies
- [ ] Foreign key relationships established
- [ ] Data migration script tested
- [ ] No data loss in decomposition (lossless join)
- [ ] Query patterns updated for new schema
- [ ] Performance impact assessed
- [ ] 2NF compliance validation passes

## 7. Exceptions and Exemptions

### 7.1 Approved Exceptions

**EXC-2NF-001: Denormalization for Performance**
- **Justification**: Critical read-heavy queries require denormalized data
- **Scope**: Specific reporting/analytics tables only
- **Conditions**: 
  - Must document performance benefit
  - Must maintain source of truth in normalized tables
  - Must have automated synchronization mechanism
- **Examples**: Materialized views, read replicas, cache tables
- **Review**: Quarterly

**EXC-2NF-002: Historical Snapshots**
- **Justification**: Point-in-time data capture may include redundant information
- **Scope**: Audit logs, historical snapshots, event sourcing
- **Conditions**: 
  - Clearly marked as historical/immutable
  - Not used for operational queries
  - Normalized tables exist for current state
- **Review**: Annual

### 7.2 Denormalization Guidelines

When performance requirements justify denormalization:

```sql
-- Maintain normalized source of truth
CREATE TABLE orders_normalized (
    order_id UUID PRIMARY KEY,
    customer_id UUID REFERENCES customers(id),
    order_date DATE
);

CREATE TABLE order_items_normalized (
    order_id UUID,
    product_id UUID,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders_normalized(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Denormalized view for reporting (materialized for performance)
CREATE MATERIALIZED VIEW order_summary_denormalized AS
SELECT 
    o.order_id,
    o.customer_id,
    c.customer_name,           -- Denormalized
    o.order_date,
    oi.product_id,
    p.product_name,            -- Denormalized
    p.product_category,        -- Denormalized
    oi.quantity,
    oi.unit_price
FROM orders_normalized o
JOIN customers c ON o.customer_id = c.id
JOIN order_items_normalized oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id;

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

-- Automated refresh (example: every hour)
SELECT cron.schedule('refresh-order-summary', '0 * * * *', 
  'REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_denormalized'
);
```

## 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"
- **Date, C.J.** (2019). "Database Design and Relational Theory: Normal Forms and All That Jazz"
- **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) (Prerequisite)
- [3NF Compliance Guide](./3NF.md) (Next level)
- [Database Design Standards](../technical/database-design-standards.md)
- [Functional Dependency Analysis Guide](../technical/functional-dependency-analysis.md)
- [Agent Data Engineer Guidelines](../../.cursor/rules/agent-data-engineer.mdc)

### 8.3 Tools and Automation

- **2NF Validator**: `/scripts/validate-2nf.ts`
- **Functional Dependency Analyzer**: `/tools/fd-analyzer`
- **Schema Decomposer**: `/tools/decompose-schema`
- **Compliance Dashboard**: `/web/compliance/2nf-dashboard`

## 9. Compliance Metrics and KPIs

### 9.1 Key Performance Indicators

| Metric | Target | Current | Status |
|--------|--------|---------|--------|
| Tables in 2NF | 100% | TBD | 🔄 |
| Partial Dependencies Identified | 0 | TBD | 🔄 |
| Functional Dependencies Documented | 100% | TBD | 🔄 |
| 2NF Compliance Score | ≥95% | TBD | 🔄 |
| Composite Key Tables Analyzed | 100% | TBD | 🔄 |

### 9.2 Reporting

**Monthly Compliance Report includes:**
- Overall 2NF compliance percentage
- Number of tables with composite keys
- Partial dependencies detected and remediated
- Functional dependency documentation coverage
- Exception requests processed
- Training completion rates

## 10. Revision History

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

---

## Appendix A: Functional Dependency Examples

### A.1 Full Functional Dependency

```
Table: order_items
Primary Key: (order_id, product_id)

Full Dependencies (Compliant):
- (order_id, product_id) → quantity
- (order_id, product_id) → unit_price_at_order
- (order_id, product_id) → line_discount

Explanation: These attributes make sense only in the context of 
a specific product in a specific order.
```

### A.2 Partial Functional Dependency (Violation)

```
Table: order_items (Non-compliant)
Primary Key: (order_id, product_id)

Partial Dependencies (Violations):
- order_id → customer_name          (depends only on order_id)
- order_id → order_date             (depends only on order_id)
- product_id → product_name         (depends only on product_id)
- product_id → product_category     (depends only on product_id)

Solution: Extract to separate tables (orders, products)
```

## Appendix B: Decomposition Algorithms

### B.1 Lossless-Join Decomposition

**Algorithm:**
1. Identify all functional dependencies
2. Separate partial dependencies into new tables
3. Maintain foreign key relationships
4. Verify no information loss through joins

**Example:**
```sql
-- Original table with violations
R(A, B, C, D, E)
PK = (A, B)
FDs: A → C, D
     (A, B) → E

-- Decomposition
R1(A, C, D)      -- Attributes dependent on A
PK = A

R2(A, B, E)      -- Attributes fully dependent on (A, B)
PK = (A, B)
FK = A → R1(A)

-- Verification: R = R1 ⋈ R2 (lossless join)
```

### B.2 Dependency Preservation

Ensure all functional dependencies can still be verified after decomposition:

```sql
-- Before decomposition: Can verify A → C directly in R

-- After decomposition: Can still verify A → C in R1
-- All original FDs are preserved in decomposed tables
```

## Appendix C: Real-World Case Studies

### C.1 E-Commerce Order System

**Challenge**: Order management system with partial dependencies

**Original Schema (Violated 2NF)**:
- Composite key included redundant product and customer information
- Update anomalies when product details changed
- Data redundancy for frequently ordered products

**Solution**:
- Separated entities: Customers, Products, Orders, OrderItems
- Achieved 2NF compliance
- Reduced storage by 40%
- Eliminated update anomalies

**Metrics**:
- Tables: 1 → 4
- Storage: 500MB → 300MB  
- Update performance: 3x improvement
- Data consistency: 100% (eliminated anomalies)

### C.2 AI Workflow Execution Tracking

**Challenge**: Workflow execution logs with embedded configuration

**Original Schema (Violated 2NF)**:
- Composite key (workflow_id, step_id, execution_timestamp)
- Workflow and step metadata repeated in every execution record
- High redundancy in frequently executed workflows

**Solution**:
- Separated: Workflows, WorkflowSteps, StepExecutions
- Normalized configuration storage
- Improved query performance through better indexing

**Metrics**:
- Storage reduction: 65%
- Query performance: 5x faster for execution history
- Configuration update time: Instant (single row vs. thousands)

---

**Document End**

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