Skip to content

DATABASE_SCHEMA.md

Latest commit

 

History

History
418 lines (334 loc) · 15.3 KB

DATABASE_SCHEMA.md

File metadata and controls

418 lines (334 loc) · 15.3 KB

Database Schema Documentation

Overview

The Market Risk Management System database consists of 14 core tables organized into 4 main functional areas:

  1. Reference Data - Master data for currencies, asset classes, counterparties, and instruments
  2. Market Data - Price data, historical data, and risk-free rates
  3. Trading Data - Trades and positions
  4. Risk Management - Risk metrics, limits, and breaches

Entity Relationship Diagram (Text Format)

┌─────────────────┐
│   Currencies    │
│─────────────────│
│ CurrencyID (PK) │
│ CurrencyCode    │
│ CurrencyName    │
└────────┬────────┘
         │
         │ 1:N
         │
┌────────▼────────────┐        ┌──────────────────┐
│   Instruments       │        │  AssetClasses    │
│─────────────────────│        │──────────────────│
│ InstrumentID (PK)   │◄──N:1──│ AssetClassID(PK) │
│ InstrumentCode      │        │ AssetClassName   │
│ InstrumentName      │        │ AssetClassCode   │
│ InstrumentType      │        └──────────────────┘
│ AssetClassID (FK)   │
│ CurrencyID (FK)     │        ┌───────────────────┐
│ IssuerCPID (FK)     │◄──N:1──│  Counterparties   │
│ UnderlyingInstID(FK)│        │───────────────────│
└──────┬──────────────┘        │ CounterpartyID(PK)│
       │                       │ CounterpartyCode  │
       │ 1:N                   │ CounterpartyName  │
       │                       │ CounterpartyType  │
       │                       │ CreditRating      │
┌──────▼──────────┐            │ ExposureLimit     │
│   MarketData    │            └─────────┬─────────┘
│─────────────────│                      │
│ MarketDataID(PK)│                      │ 1:N
│ InstrumentID(FK)│                      │
│ PriceDate       │            ┌─────────▼─────────┐
│ ClosePrice      │            │      Trades       │
│ OpenPrice       │      ┌─────│───────────────────│
│ HighPrice       │      │     │ TradeID (PK)      │
│ LowPrice        │      │     │ TradeRefNumber    │
│ Volume          │      │     │ InstrumentID (FK) │
│ Volatility      │      │     │ CounterpartyID(FK)│
└─────────────────┘      │     │ TradeDate         │
                         │     │ SettlementDate    │
┌─────────────────┐      │     │ TradeType         │
│  PriceHistory   │      │     │ Quantity          │
│─────────────────│      │     │ Price             │
│ PriceHistoryID  │      │     │ NotionalAmount    │
│ InstrumentID(FK)│◄─────┤     │ TradeStatus       │
│ PriceDate       │      │     │ BookName          │
│ ClosePrice      │      │     └───────────────────┘
│ DailyReturn     │      │
│ LogReturn       │      │
└─────────────────┘      │     ┌───────────────────┐
                         │     │    Positions      │
                         └─────│───────────────────│
                               │ PositionID (PK)   │
                               │ InstrumentID (FK) │
                               │ CounterpartyID(FK)│
                               │ BookName          │
                               │ PositionDate      │
                               │ Quantity          │
                               │ AveragePrice      │
                               │ MarketPrice       │
                               │ MarketValue       │
                               │ UnrealizedPnL     │
                               │ RealizedPnL       │
                               └─────────┬─────────┘
                                         │
                                         │ 1:N
                                         │
                               ┌─────────▼─────────┐
                               │   RiskMetrics     │
                               │───────────────────│
                               │ RiskMetricID (PK) │
                               │ PositionID (FK)   │
                               │ CalculationDate   │
                               │ VaR_95            │
                               │ VaR_99            │
                               │ ExpectedShortfall │
                               │ DeltaExposure     │
                               │ GammaExposure     │
                               │ VegaExposure      │
                               └───────────────────┘

┌──────────────────┐          ┌───────────────────────┐
│   RiskLimits     │          │ RiskLimitBreaches     │
│──────────────────│          │───────────────────────│
│ LimitID (PK)     │◄────1:N──│ BreachID (PK)         │
│ LimitType        │          │ LimitID (FK)          │
│ EntityType       │          │ BreachDate            │
│ EntityID         │          │ ActualValue           │
│ LimitAmount      │          │ LimitValue            │
│ WarningThreshold │          │ ExcessAmount          │
│ EffectiveDate    │          │ BreachSeverity        │
│ ExpiryDate       │          │ ResolutionStatus      │
└──────────────────┘          └───────────────────────┘

┌──────────────────┐          ┌───────────────────┐
│  RiskFreeRates   │          │    AuditLog       │
│──────────────────│          │───────────────────│
│ RateID (PK)      │          │ AuditID (PK)      │
│ CurrencyID (FK)  │          │ TableName         │
│ RateDate         │          │ RecordID          │
│ TenorDays        │          │ Action            │
│ Rate             │          │ UserName          │
└──────────────────┘          │ ActionDate        │
                              │ OldValues         │
                              │ NewValues         │
                              └───────────────────┘

Table Descriptions

Reference Data Tables

1. Currencies

Stores currency master data.

Key Columns:

  • CurrencyID - Primary key
  • CurrencyCode - 3-character ISO code (USD, EUR, etc.)
  • CurrencyName - Full currency name

2. AssetClasses

Defines asset class categories for risk classification.

Key Columns:

  • AssetClassID - Primary key
  • AssetClassName - Name (Equities, Fixed Income, etc.)
  • RiskWeighting - Risk weighting factor for calculations

3. Counterparties

Master data for trading counterparties.

Key Columns:

  • CounterpartyID - Primary key
  • CounterpartyCode - Unique identifier
  • CreditRating - Credit rating (AAA, A, BBB, etc.)
  • ExposureLimit - Maximum allowed exposure

4. Instruments

Financial instruments catalog.

Key Columns:

  • InstrumentID - Primary key
  • InstrumentCode - Ticker/ISIN
  • InstrumentType - Stock, Bond, Option, Future, FX, Swap
  • AssetClassID - Foreign key to AssetClasses
  • UnderlyingInstrumentID - Self-reference for derivatives

Market Data Tables

5. MarketData

Daily market prices and volatility.

Key Columns:

  • InstrumentID - Foreign key to Instruments
  • PriceDate - Trading date
  • ClosePrice - Closing price
  • Volatility - Implied or historical volatility

6. PriceHistory

Historical prices with calculated returns for VaR.

Key Columns:

  • DailyReturn - Simple daily return
  • LogReturn - Logarithmic return (for VaR calculations)

7. RiskFreeRates

Risk-free interest rates by currency and tenor.

Key Columns:

  • TenorDays - Maturity in days (1, 30, 90, 365, etc.)
  • Rate - Interest rate (decimal)

Trading Tables

8. Trades

All trading transactions.

Key Columns:

  • TradeID - Primary key
  • TradeRefNumber - External reference number
  • TradeType - Buy or Sell
  • Quantity - Number of units
  • NotionalAmount - Total transaction value
  • TradeStatus - Active, Closed, Cancelled

9. Positions

Aggregated current positions.

Key Columns:

  • PositionID - Primary key
  • Quantity - Net quantity (positive = long, negative = short)
  • AveragePrice - Weighted average acquisition price
  • MarketValue - Current market value
  • UnrealizedPnL - Mark-to-market profit/loss
  • BookName - Portfolio/book identifier

Risk Management Tables

10. RiskMetrics

Calculated risk measures for positions.

Key Columns:

  • VaR_95 - Value at Risk at 95% confidence
  • VaR_99 - Value at Risk at 99% confidence
  • ExpectedShortfall - Expected loss beyond VaR
  • DeltaExposure - First-order price sensitivity
  • GammaExposure - Second-order price sensitivity
  • VegaExposure - Volatility sensitivity

11. RiskLimits

Defined risk limits by entity.

Key Columns:

  • LimitType - VaR, Exposure, Concentration
  • EntityType - Counterparty, Instrument, AssetClass, Portfolio
  • LimitAmount - Maximum allowed value
  • WarningThreshold - Warning level (e.g., 0.80 = 80%)

12. RiskLimitBreaches

Historical record of limit breaches.

Key Columns:

  • BreachSeverity - Warning, Breach, Critical
  • ResolutionStatus - Open, Acknowledged, Resolved
  • ExcessAmount - Amount over limit

Supporting Tables

13. AuditLog

Audit trail for all data changes.

Key Columns:

  • TableName - Table that was modified
  • Action - INSERT, UPDATE, DELETE
  • OldValues - Previous values (JSON)
  • NewValues - New values (JSON)

Key Relationships

Primary Relationships

  1. Instruments → AssetClasses (N:1)

    • Each instrument belongs to one asset class
    • Asset classes contain multiple instruments

  2. Instruments → Currencies (N:1)

    • Each instrument is denominated in one currency

  3. Instruments → Counterparties (N:1)

    • Optional: For bonds/notes, links to issuer

  4. Instruments → Instruments (N:1)

    • Self-reference for derivatives linking to underlying

  5. Trades → Instruments (N:1)

    • Each trade is for one instrument

  6. Trades → Counterparties (N:1)

    • Each trade is with one counterparty

  7. Positions → Instruments (N:1)

    • Each position is for one instrument

  8. Positions → Counterparties (N:1)

    • Each position is with one counterparty

  9. RiskMetrics → Positions (N:1)

    • Risk metrics calculated for each position

  10. RiskLimitBreaches → RiskLimits (N:1)

    • Each breach references a specific limit

Indexes

Performance-Critical Indexes

Positions Table:

  • IX_Positions_Date_NonZero - Date-based queries for active positions
  • IX_Positions_Instrument_Date - Instrument position history
  • IX_Positions_Counterparty_Date - Counterparty exposure

MarketData Table:

  • IX_MarketData_Instrument_Date - Price lookups
  • IX_MarketData_Date_Instrument - Date range queries

RiskMetrics Table:

  • IX_RiskMetrics_Position_Date - Latest metrics by position
  • IX_RiskMetrics_Date - Portfolio-wide risk reporting

See 07-Indexes/CreateIndexes.sql for complete index definitions.

Data Flow

1. TRADE ENTRY
   ┌─────────┐
   │  Trades │
   └────┬────┘
        │
        ▼
   ┌─────────────┐
   │  Positions  │ ◄─── Updated via sp_ProcessTrade
   └─────┬───────┘
         │
         ▼
   ┌──────────────┐
   │ MarketData   │ ◄─── Daily price feed
   └──────┬───────┘
          │
          ▼
   ┌──────────────┐
   │ RiskMetrics  │ ◄─── Calculated via sp_CalculatePortfolioRisk
   └──────┬───────┘
          │
          ▼
   ┌────────────────────┐
   │ RiskLimitBreaches  │ ◄─── Checked via sp_CheckRiskLimits
   └────────────────────┘

Views

1. vw_ActivePositions

Comprehensive view of all active positions with instrument and counterparty details.

2. vw_RiskMetrics

Risk metrics joined with position and instrument information.

3. vw_CounterpartyExposure

Aggregated exposure analysis by counterparty with limit utilization.

4. vw_PortfolioSummary

Portfolio summary aggregated by book and asset class with ROLLUP.

Functions

1. fn_CalculatePnL

Calculates P&L based on quantity, average price, and market price.

2. fn_GetExposure

Calculates total exposure including contract size.

3. fn_CalculateVolatility

Calculates annualized volatility from historical returns.

4. fn_CalculateSimpleVaR

Parametric VaR calculation using normal distribution.

5. fn_GetBusinessDays

Calculates business days between two dates.

Stored Procedures

1. sp_ProcessTrade

Processes new trades and updates positions automatically.

Parameters:

  • Trade details (instrument, counterparty, quantity, price, etc.)

Actions:

  • Inserts trade record
  • Updates or creates position
  • Recalculates average price
  • Handles long/short position changes

2. sp_CalculatePortfolioRisk

Calculates risk metrics for all positions as of a date.

Parameters:

  • @CalculationDate - Date for calculation
  • @BookName - Optional: specific book

Actions:

  • Updates market values
  • Calculates VaR (95%, 99%)
  • Calculates Expected Shortfall
  • Computes Greeks

3. sp_CheckRiskLimits

Checks current exposures against limits and logs breaches.

Parameters:

  • @CheckDate - Date for limit check

Actions:

  • Calculates current exposures
  • Compares against limits
  • Logs new breaches
  • Returns breach details

Sample Queries

See 06-Queries/AnalyticalQueries.sql for 10 advanced analytical queries demonstrating:

  • Window functions
  • CTEs
  • Pivots
  • Statistical analysis
  • Time-series analysis
  • Correlation analysis
  • Stress testing

For installation instructions, see INSTALLATION.md