૮ ˶ᵔ ᵕ ᵔ˶ ა

Project 2 - Project Prometheus

5 min read

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

Execution Summary

  • Project Prometheus establishes a database infrastructure to manage and analyze xenomorph biological data, which is important for advancing Weyland-Yutani’s research into xenomorph genetics and threat assessment. The project integrates a relational SQL database for structured specimen tracking and a MongoDB NoSQL database for flexible, nested genetic data storage. This dual-database approach ensures scalability, security, and analytics precision

Relational Database Enhancement

  • The relational database provides a structured framework for managing xenomorph specimen data, genetic samples, research sessions, and evolutionary stages. The design emphasizes data integrity, security, and efficient querying through advanced schema design and stored procedures

Advanced Schema Design

  • GENETIC_SAMPLES → stores data on biological samples (DNA, RNA, protein, tissue, blood) with constraints ensuring valid sample types, purity percentages (0-100), and classification levels (1-10). References SPECIMENS and EMPLOYEES
  • RESEARCH_SESSIONS → tracks research activities (e.g., Sequencing, Cloning) with constraints on session types and success rates (0–100). Links to GENETIC_SAMPLES, RESEARCH_PROJECTS, and EMPLOYEES
  • SPECIMEN_EVOLUTION → records specimen life stage transitions (e.g., Egg, Facehugger, Adult) with threat change (-5 to 5) and stability status (Stable, Unstable, Critical). References SPECIMENS and EMPLOYEES

Stored Procedures

  • sp_ProcessGeneticSample → automates sample processing, validating inputs (specimen ID, sample type, employee ID) and generating unique sample IDs. Updates specimen status to ‘Study Complete’ for DNA/RNA samples ≥95%
  • sp_GetResearchSummary → generates comprehensive reports for specimens or projects, retrieving specimen details, genetic samples, research sessions, and evolutionary data filtered by classification level. Includes aggregate metrics like average purity and success rates
  • sp_ExportToNoSQL → facilitates data export to MongoDB in JSON format, supporting ETL processes for cross-database integration

Security Measures

  • Roles → Researcher (SELECT, INSERT on key tables) and Administrator (full CRUD and database control)
  • Audit Logging → SPECIMEN_AUDIT_LOG table and trg_SPECIMENS_Audit trigger track changes (INSERT, UPDATE, DELETE) to specimens, logging user, timestamp, and status changes
  • Data Validation → trg_GENETIC_SAMPLES_Validation trigger ensures valid specimen status before sample insertion, preventing unauthorized or invalid data entry

NoSQL Implementation

  • The MongoDB implementation provides a flexible document-based storage solution for xenomorph research data, optimized for complex, nested genetic and behavioral attributes

Alien Data Storage Solution

  • Database and Collection → created xenomorph_research database with alien_research collection to store specimen data, dropped and recreated for iterative development
  • Document Schema → each document includes specimen_code, discovery_site, research_notes, researcher, genetic_info (nested DNA, RNA, mutations), and threat_assessment

Query Operations

  • Insertions → insertMany adds multiple specimen documents with varied genetic and behavioral data (such as XM-001 with acid blood or XM-005 with camouflage)
  • Queries → finds documents by threat level (e.g., 8) or discovery site (e.g., LV-426), supporting rapid data retrieval for high-risk specimens
  • Updates → updates research notes and threat assessments (e.g., XM-001 threat level increased to 10 due to acid blood corrosion)
  • Aggregations → groups documents by threat level and researcher, counting occurrences to identify research focus areas

System Integration

ETL Process

  • The sp_ExportToNoSQL procedure exports SQL data to JSON, enabling seamless transfer to MongoDB. This supports hybrid analysis, combining structured relational data with flexible NoSQL documents
  • Example: Specimen data from SQL Server (e.g., XX121-001) is exported to MongoDB, where nested genetic_info fields enhance analytical flexibility
  • Manual updates, such as the research note for XM-001, are synchronized across systems via targeted SQL updates and MongoDB updateOne operations

Security Implementation

  • SQL Server → role-based access control and audit triggers ensure data integrity and traceability. The trg_GENETIC_SAMPLES_Validation trigger enforces specimen status checks
  • MongoDB → security considerations include integration of role-based access (within the MongoDB website—not directly in the .js submission) and encryption for sensitive genetic data

Technology Comparison

Relational vs NoSQL

Data Structure

  • SQL Server → ideal for structured data with fixed schemas (such as GENETIC_SAMPLES, RESEARCH_SESSIONS). Enforces referential integrity and supports complex joins for cross-table analysis
  • MongoDB → suited for semi-structured, nested data (such as genetic_info with varying attributes). Simplifies storage of diverse specimen types but lacks strict referential integrity

Scalability

  • SQL Server → scales vertically, requiring robust hardware for large datasets. Efficient for transactional operations and standardized reporting
  • MongoDB → scales horizontally, distributing data across servers for high-volume, unstructured data. Better for rapid prototyping and flexible queries

Query Performance

  • SQL Server → optimized for complex joins and aggregations (such as sp_GetResearchSummary). Requires careful indexing for performance
  • MongoDB → excels in nested queries and aggregations (such as genetic_markers.mutation_rate). Performance depends on indexing and pipeline optimization

Use Case

  • SQL Server → best for standardized research processes, audit logging, and regulatory compliance (e.g., specimen containment status)
  • MongoDB → ideal for exploratory genetic analysis, rapid data ingestion, and cross-species comparisons with variable attributes

Recommendations

  • Use SQL Server for operational data (such as specimen tracking, employee assignments) requiring strict consistency and auditing
  • Use MongoDB for research data (such as genetic markers, behavioral notes) needing flexibility and scalability for evolving datasets

Collaboration Reflection

  • I collaborated with Riker on this implementation, and we both worked on different components of the project, I mainly focused on the NoSQL implementation, whereas Riker focused more on the SQL implementation. I also used Claude AI as well as ChatGPT to fix up some of my grammar and help out with my documentation and code styling.

Strategic Recommendations

  • Continue leveraging SQL Server for operational and compliance-driven data, with MongoDB for research and genetic analysis. Implement automated ETL pipelines to streamline data synchronization
  • Extend MongoDB security with role-based access and encryption. Enhance SQL Server audit logging to include research session changes.
  • Invest in MongoDB sharding for large-scale genetic data and SQL Server partitioning for high-volume specimen records
  • Develop advanced MongoDB aggregation pipelines for real-time genetic mutation tracking. Enhance SQL Server reporting with dynamic dashboards for threat assessment
  • Standardize hybrid database templates for other bioengineering projects, ensuring modularity and reusability. Explore machine learning integration for predictive threat analysis using combined SQL/NoSQL data

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

Graph View