Operation 01 - Hadleys Hope Database Design

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

Executive Summary

• This database design models the operations of the Hadley’s Hope colony—including personnel management, supply inventory, facility zones, equipment maintenance, etc. The schemas enforce data integrity and allows the resources to be tracked efficiently as well as operational readiness across all departments.

Design Implementation Notes

Entity Overview

• COLONISTS → stores personal details of colony personnel, including name, age, security clearance level
• EMERGENCY_CONTACTS → holds emergency contact info linked to each colonist
• HOUSING_ASSIGNMENTS → tracks living quarters assigned to colonists with occupancy types
• POSITION_ASSIGNMENTS → records positions held by colonists within specific departments
• SUPPLY_CATEGORIES and SUPPLY_ITEMS → categorize and store supply inventory items by type (food, medical, equipment, etc).
• DAILY_CONSUMPTION → logs daily usage of supplies by department to monitor consumption rates and adjust supply needs
• SUPPLY_REQUESTS → manages supply requests from departments with approval tracking
• FACILITY_ZONES → physical areas within the colony and assigns responsible colonists
• FACILITY_EQUIPMENT → registry for equipment installed in facility zones
• FACILITY_MAINTENANCE_SCHEDULES and FACILITY_MAINTENANCE_LOGS → tracks planned and completed maintenance for equipment

Relationship Summary

• Colonists are linked to their emergency contacts, housing, and position assignments
• Position assignments link colonists to positions and department tables
• Supplies are categorized with SUPPLY_CATEGORIES → SUPPLY_ITEMS and consumption/requests are tracked by department
• Facility zones are associated with responsible colonists and contain many facility equipment
• Equipment maintenance schedules and logs reference both equipment and the technicians assigned

Normalization Verification

• Each table has a well-defined primary key ensuring data integrity → 1NF
• Attributes depend solely on their primary key (no partial dependencies) → 2NF
• No transitive dependencies → 3NF

Business Rules

• Security levels are constrained to 1-10
• Supply stock level and reorder points are constrained to positive numbers
• Status fields are enforced such as equipment status, maintenance schedule status, or supply request approval status

Validation Summary

Sample Data Overview

• COLONISTS → diverse ages and security levels 1-10
• SUPPLY_ITEMS → includes emergency reserve indicators and varying stock levels
• FACILITY_EQUIPMENT → contains a mix of operational and out-of-service equipment
• DAILY_CONSUMPTION and SUPPLY_REQUESTS → realistic departmental usage

Query Examples

• There are a few queries to test joins between colonists, their housing, emergency contacts, etc.
• There is a query to test stock levels being below the reorder point
• Query to check pending supply requests, etc.

Design Validation

• Personnel, housing, and emergency contact management are supported with relationships
• Supply chain tracking from category to consumption is normalized and traceable
• Facility zoning and maintenance schedules are integrated
• Normalization has been verified to 3NF

Collaboration Reflection

• The only other collaboration I had was with ChatGPT, but I’m not too sure if that counts. I mainly just used it to generate data.

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