૮ ˶ᵔ ᵕ ᵔ˶ ა

Project 1 - USCSS Nostromo Black Box Recovery

5 min read

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Executive Summary

Project Overview

  • This report documents the database system made to ingest, store, and analyze data from the USCSS Nostromo mission’s final 3 weeks. The primary goal is to support the corporate investigation into the vessel’s failure by enabling timeline reconstruction, crew performance assessment, system failure analysis, and corporate compliance evaluation.

Key Findings

  • Critical events, including departure, signal reception, crew medical emergencies, to system outages, were mapped using multi-table CTE queries
  • Window functions ranked survival and effectiveness of the crew, and it revealed top performers and the crew that needed retraining.

Recommendations for Corporate Policy

  • There should be stricter override controls with authentication and audit trails in ACCESS_LOGS.
  • There should be automated alert escalation triggers to reduce lag from manual alerts
  • There should be predictive maintenance analytics based on system log trends
  • There should be required routine emergency drill data logging, with structured crew response agreements tracked in the database.

Database Architecture Analysis

Schema Design Justification

  • The schema includes 13 (15 including the triggers) normalized tables covering crew, systems, logs, incidents, communications, and medical data. Referential integrity via PK/FK constraints guarantees consistency within the database, and CHECK constraints enforce validity. Temporal tracking is standardized via DATETIME across logs which enables precise chronological analysis.

EER Inheritance Design

  • CREW_MEMBERS ⊆ MEDICAL_OFFICERS → role specialization via shared primary key
  • VESSEL_SYSTEMS ⊆ SYSTEM_LOGS/SYSTEM_ALERTS → system categorization

Normalization & Trade-Offs

  • All tables adhere to 3NF, so there’s no partial or transitive dependencies.
  • The trade-offs were that lookup fields (CLEARANCE, SYSTEM_CATEGORY) were kept as domain strings rather than separate tables to balance normalization against query performance and simplicity.

Performance Considerations

  • Composite indexes on (ALERT_TIMESTAMP, SYSTEM_ID) and (ACCESS_TIMESTAMP, CREW_ID) optimize range queries.
  • Critical recursive CTEs and multi-table joins were tested with EXPLAIN plans and nested subqueries were refactored into CTEs
  • Log tables are partition-ready by date the improve archive and query performance as mission data grows

Programming Implementation Review

Stored Procedure Design

  • sp_GenerateCrewReport
    • Inputs → @crew_member_id, @start_date, @end_date
    • Outputs → aggregated logs, assignments, incident reports, access history
    • Validates parameters and wraps queries in transactions with error handling
  • sp_SystemStatusAnalysis
    • Inputs → @system_name, @start_date, @end_date
    • Outputs → time-stamped statuses, uptime percentage (via fn_SystemUptimePercent), alert counts.
  • sp_EmergencyTimelineReport
    • Optional inputs to filter date range.
    • Combines emergency logs, incident reports, and communications into chronological output.
    • Error handling ensures incomplete filters do not cause failures.

User-Defined Functions

  • fn_CrewEfficiencyRating
    • Returns a numeric score based on incident reports handled, response time, and protocol engagement.
  • fn_SystemUptimePercent
    • Calculates uptime using duration of normal vs. total operational time from logs.
  • fn_ResponseTimeMinutes
    • Computes elapsed minutes between alert creation and crew member response.
  • All functions are deterministic, performance-tested, and reusable across queries and procedures.

Trigger Implementation

  • trg_LogSystemStatusChange (on VESSEL_SYSTEMS)
    • Automatically populates SYSTEM_LOGS when VESSEL_SYSTEMS.STATUS updates
  • trg_LogEmergencyProtocolActivation (on EMERGENCY_PROTOCOLS)
    • Automatically logs protocol activations and notifies investigators by updating EMERGENCY_RESPONSE_LOG

Error Handling Strategy

  • All procedures and triggers use TRY/CATCH blocks
  • Transactions are rolled back on exceptions
  • Validation of parameters prevent invalid IDs or date ranges

Investigation Insights

Timeline Reconstruction

  • June 3 → departure log entry from departure dock SYSTEM_LOGS
  • June 21 → received signal logged in COMMUNICATION_LOGS, prompting MISSION_EVENTS entry
  • June 22 → Kane medical emergency recorded in MEDICAL_LOGS
  • June 23-24 → escalating failures in life support and communications
  • June 25 → final system failures, port overrides, and destruction protocols logged

Crew Performance Analysis

  • The top performers were Dallas and Ripley since they had high efficiency scores
  • The candidates that could need improvement are Parker and Brett, which responded slower than peers in 20% of the critical system alerts.
  • Ash logged and exceptionally detailed incident report, ranking in the top 10%

System Failure Patterns

  • Analysis revealed a recurring warning → critical progression in life support and communications, with investigate-worthy lag in response. These failures often preceded medical incidents or protocol activations, signaling predictive maintenance opportunities.

Corporate Compliance Assessment

  • Unauthorized entries flagged where Parker accessed systems without override authorization
  • Activations were sometimes delayed by >15 minutes post-alert
  • Kane submitted a high-severity event but failed to escalate the alert

Technical Recommendations

Database Improvements

  • Implement time-based partitioning on large log tables
  • Create materialized views for key CTE-based reports (such as timeline or hierarchy)
  • Enhance normalization by adding lookup tables for roles, clearance, and access types

Automation Opportunities

  • Extend triggers to auto-escalate on multiple warning statuses
  • Schedule automated reports via sp_GenerateCrewReport for mission oversight
  • Add stored procedures to inject emergency drills for crew training

Risk Mitigation

  • Deploy anomaly detection queries on ACCESS_LOGS via scheduled stored procedures
  • Enforce stricter schema constraints on overrides using CHECK and FK mechanisms
  • Archive mission data with redundancy to prevent investigation data loss

Corporate Intelligence

  • Use uptime and crew scoring outputs for predictive personnel planning and training
  • Integrate alert and incident data into dashboards for group-level intelligence reporting
  • Aggregate system and medical logs to refine risk models for future vessels

Collaboration Reflection

  • My partner for this assignment was Riker. During our first weekly meeting, we worked on phase 1, which we made the tables and normalized them properly. During our second meeting, we took a bit of time to do both phases 2 and 3.
  • We also collaborated with AI to help generate the data, and I used it to help optimize my SQL slightly (such as removing unnecessary logic, such as RETURN after raising errors) and to fix up some of my grammar in this document.

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Graph View