Database Schema Overview¶

Architecture Overview¶

graph TB
    subgraph "Core Data Layer"
        CVE[CVE Main Database<br/>~280K Records]
        CWE[CWE Reference<br/>Weakness Catalog]
        CAPEC[CAPEC Reference<br/>Attack Patterns]
    end

    subgraph "Exploit Intelligence"
        EDB[ExploitDB<br/>~51K Exploits]
        KEV[CISA KEV<br/>Known Exploited]
    end

    subgraph "Commercial Patches"
        MSRC[Microsoft MSRC<br/>Security Updates]
        CISCO[Cisco PSIRT<br/>Advisories]
        REDHAT[Red Hat<br/>Security Advisories]
    end

    subgraph "Open Source Intelligence"
        GITHUB[GitHub Advisories<br/>Package Ecosystem]
        MOREFIXES[MoreFixes Dataset<br/>Academic Research]
    end

    CVE --> EDB
    CVE --> MSRC
    CVE --> CISCO
    CVE --> REDHAT
    CVE --> GITHUB
    CVE --> MOREFIXES
    CVE --> CWE
    CVE --> CAPEC

    style CVE fill:#e1f5fe
    style EDB fill:#fff3e0
    style MSRC fill:#e8f5e8
    style CISCO fill:#e8f5e8
    style REDHAT fill:#e8f5e8
    style GITHUB fill:#f3e5f5
    style MOREFIXES fill:#f3e5f5

Core Schema Design¶

erDiagram
    cve_main ||--o{ exploits : "has_exploits"
    cve_main ||--o{ msrc_patches : "patched_by"
    cve_main ||--o{ redhat_patches : "patched_by"
    cve_main ||--o{ cisco_patches : "patched_by"
    cve_main ||--o{ github_advisories : "advised_by"
    cve_main ||--o{ morefixes_fixes : "fixed_by"
    cve_main ||--o{ cwe_ref : "classified_by"
    cve_main ||--o{ capec_ref : "attacked_via"

    cve_main {
        string cve_id PK "Primary identifier"
        date date_published "Publication timestamp"
        float cvss_v3_score "CVSS v3 base score"
        string cvss_v3_severity "Severity rating"
        text description "Vulnerability description"
        string cwe_ids "Associated weakness types"
        string cpes "Common Platform Enumeration"
        boolean has_exploit "Exploit availability flag"
        boolean kev_known_exploited "CISA KEV status"
        float epss_score "Exploit Prediction Score"
    }

    exploits {
        int id PK "Exploit identifier"
        string cve_id FK "CVE reference"
        date date_published "Publication date"
        string author "Exploit author"
        string type "Exploit category"
        string platform "Target platform"
        boolean verified "Verification status"
    }

    msrc_patches {
        string cve_id FK "CVE reference"
        date release_date "Patch release date"
        string product_name "Affected product"
        boolean exploited_status "Exploitation indicator"
        string cvss_vector "CVSS vector string"
    }

    cwe_ref {
        string cwe_id PK "CWE identifier"
        string name "Weakness name"
        string weakness_abstraction "Abstraction level"
        text description "Detailed description"
    }

    capec_ref {
        string capec_id PK "CAPEC identifier"
        string name "Attack pattern name"
        string abstraction "Pattern abstraction"
        text description "Attack methodology"
    }

Database Statistics¶

timeline
    title Database Temporal Coverage
    section Historical
        1999-2009 : Early CVE Records
                  : Foundation Data
    section Growth Period  
        2010-2019 : Expanded Coverage
                  : Multi-vendor Integration
    section Modern Era
        2020-2025 : Comprehensive Dataset
                  : Real-time Updates
                  : Advanced Analytics

Table Categories¶

1. Core Vulnerability Tables¶

2. Exploit Intelligence Tables¶

3. Commercial Vendor Patches¶

4. Open Source Intelligence¶

5. Reference and Classification¶

Data Relationships & Analytics¶

Primary Relationship Patterns¶

graph LR
    subgraph "Vulnerability Lifecycle"
        A[CVE Published] --> B[Exploit Developed]
        B --> C[Patch Released]
        C --> D[Advisory Issued]
    end

    subgraph "Multi-Vendor Response"
        E[Single CVE] --> F[Microsoft Patch]
        E --> G[Cisco Advisory]
        E --> H[Red Hat Update]
        E --> I[GitHub Advisory]
    end

    subgraph "Intelligence Correlation"
        J[CVE] --> K[CWE Classification]
        K --> L[CAPEC Patterns]
        J --> M[EPSS Score]
        J --> N[KEV Status]
    end

    style A fill:#ffebee
    style B fill:#fff3e0
    style C fill:#e8f5e8
    style D fill:#e3f2fd

Advanced Query Patterns¶

-- CVE-to-Exploit-to-Patch timeline analysis
WITH lifecycle_analysis AS (
    SELECT 
        cm.cve_id,
        cm.date_published as cve_date,
        e.date_published as exploit_date,
        mp.release_date as patch_date,
        cm.cvss_v3_score,
        cm.epss_score,
        DATE_DIFF('day', cm.date_published, e.date_published) as days_to_exploit,
        DATE_DIFF('day', e.date_published, mp.release_date) as exploit_to_patch_days
    FROM cve_main cm
    LEFT JOIN exploits e ON cm.cve_id = e.cve_id
    LEFT JOIN msrc_patches mp ON cm.cve_id = mp.cve_id
    WHERE cm.date_published >= '2020-01-01'
      AND cm.cvss_v3_score >= 7.0
)
SELECT 
    CASE 
        WHEN days_to_exploit <= 7 THEN 'Rapid Exploitation'
        WHEN days_to_exploit <= 30 THEN 'Standard Timeline'
        ELSE 'Delayed Exploitation'
    END as exploitation_pattern,
    COUNT(*) as vulnerability_count,
    AVG(exploit_to_patch_days) as avg_patch_response_time,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY epss_score) as median_epss
FROM lifecycle_analysis
WHERE days_to_exploit IS NOT NULL
GROUP BY 1
ORDER BY avg_patch_response_time;

-- Vendor response time comparison with statistical analysis
WITH unified_patches AS (
    SELECT 
        cve_id, 
        release_date as patch_date, 
        'Microsoft' as vendor,
        'Commercial' as ecosystem
    FROM msrc_patches

    UNION ALL

    SELECT 
        cve_id, 
        current_release_date, 
        'RedHat',
        'Open Source'
    FROM redhat_patches

    UNION ALL

    SELECT 
        cve_id, 
        current_release_date, 
        'Cisco',
        'Commercial'
    FROM cisco_patches
),
response_metrics AS (
    SELECT 
        up.vendor,
        up.ecosystem,
        DATE_DIFF('day', cm.date_published, up.patch_date) as response_days,
        cm.cvss_v3_score,
        cm.kev_known_exploited
    FROM unified_patches up
    JOIN cve_main cm ON up.cve_id = cm.cve_id
    WHERE up.patch_date IS NOT NULL
      AND cm.date_published IS NOT NULL
      AND DATE_DIFF('day', cm.date_published, up.patch_date) BETWEEN 0 AND 365
)
SELECT 
    vendor,
    ecosystem,
    COUNT(*) as total_patches,
    ROUND(AVG(response_days), 1) as avg_response_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY response_days), 1) as median_response_days,
    ROUND(STDDEV(response_days), 1) as response_std_dev,
    COUNT(CASE WHEN kev_known_exploited = true THEN 1 END) as kev_vulnerabilities,
    ROUND(AVG(CASE WHEN kev_known_exploited = true THEN response_days END), 1) as avg_kev_response
FROM response_metrics
GROUP BY vendor, ecosystem
ORDER BY median_response_days;

Performance & Scalability¶

Database Technology Stack¶

ETL & Data Pipeline¶

flowchart LR
    subgraph "Data Sources"
        A[NVD API]
        B[ExploitDB]
        C[Vendor APIs]
        D[GitHub API]
    end

    subgraph "ETL Pipeline"
        E[Data Extraction]
        F[Validation & Cleaning]
        G[Transformation]
        H[Quality Assurance]
    end

    subgraph "Database Layer"
        I[Staging Tables]
        J[Production Schema]
        K[Analytical Views]
    end

    A --> E
    B --> E
    C --> E
    D --> E

    E --> F
    F --> G
    G --> H

    H --> I
    I --> J
    J --> K

    style E fill:#e3f2fd
    style F fill:#f3e5f5
    style G fill:#e8f5e8
    style H fill:#fff3e0

Research Applications¶

Supported Analysis Frameworks¶

Research Output Capabilities¶

mindmap
  root((Database Applications))
    Academic Research
      Lifecycle Analysis
      Vendor Comparison
      Predictive Modeling
      Economic Analysis
    Industry Applications
      Risk Assessment
      Patch Prioritization
      Threat Intelligence
      Resource Planning
    Policy Research
      Coordination Analysis
      Disclosure Policies
      Regulatory Impact
      Best Practices
    Tool Development
      Dashboard Analytics
      API Development
      ML Model Training
      Visualization Tools