Lab 4 - MUTHUR 6000 Procedure Implementation

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

Execution Summary

• This SQL implementation establishes a system for managing emergency protocols, crew performance, and maintenance operations. The database includes tables for emergency events, crew status, task management, attendance, and maintenance logs, with stored procedures and functions to handle initiation, updates, and reporting. The design prioritizes modularity, error handling, and performance optimization to support critical field operations.

Procedure Architecture

Design Decisions

• The SQL is organized into sections for each task.
• The primary and foreign key constraints enforce referential integrity. Such as EMERGENCY_EVENTS linking to EMPLOYEES, CREW_EMERGENCY_STATUS references EMERGENCY_EVENTS)
• Check constraints are used to validate inputs like SEVERITY_LEVEL between 1-5 and STATUS values
• Procedures like sp_InitiateEmergencyProtocol and sp_ScheduleMaintenanceOperation use pseudo-random ID generation to create unique identifiers which helps scale without collisions
• Default values like CURRENT_TIMESTAMP and GETDATE() ensure consistent time tracking across tables

Integration Approach

• Procedures like sp_InitiateEmergencyProtocol, sp_UpdateCrewEmergencyStatus, and sp_GenerateMaintenanceReport encapsulate business logic, which reduces client-side processing and ensures consistent data manipulation
• Scalar functions like fn_CalculateCrewEfficiency and fn_AnalyzeDepartmentWorkload provide reusable calculations for performance metric which are also integrated into reporting workflows
• Foreign key constraints and validation checks prevent invalid data entries—such as ensuring active employee status in sp_InitiateEmergencyProtocol prevent invalid data entries, ensuring system reliability
• The schema supports future additions, such as emergency types or maintenance categories by using flexible VARCHAR fields and extensible check constraints

Function Library

Calculation Methods

• fn_CalculateCrewEfficiency → computes a weighted efficiency score based on task completion (50%), quality score (30%), and attendance rate (20%). Uses subqueries to aggregate data within a specified time window, handling nulls with ISNULL for robustness
• fn_AnalyzeDepartmentWorkload → calculates workload as the ratio of tasks to employees for a given department and date, providing a simple yet effective metric for resource allocation
• fn_EstimateResponseTime → estimates emergency response time by combining travel time (LOCATION_DISTANCE), priority multipliers (such as 0.6 for intruder), and crew availability, which returns a ceiling value to be conservative
• fn_AssessCrewReadiness → evaluates crew readiness by weighting training (40%), health (30%), and performance (30%) scores, factoring in recent incidents and mission completions

Business Logic Implementation

• sp_InitiateEmergencyProtocol → validates inputs (severity, employee status) and logs events, while sp_ResolveEmergencyProtocol updates statuses and records resolutions, ensuring a complete audit trail
• sp_ScheduleMaintenanceOperation → assigns technicians based on facility and clearance, with estimated durations varying by maintenance type (such as 2 hours for inspection)
• sp_GenerateMaintenanceReport → provides detailed metrics and risk assessments, using dynamic prioritization for critical systems like Life Support

Error Handling Strategy

Validation Framework

• Procedures validate inputs (such as SEVERITY_LEVEL between 1-5, valid STATUS values) using IF conditions and THROW statements to return descriptive errors
• Foreign key constraints prevent orphaned records, and checks ensure valid data states
• sp_InitiateEmergencyProtocol checks for duplicate active emergencies, and sp_ScheduleMaintenanceOperation prevents scheduling conflicts on the same equipment and date

Security Measures

• Procedures verify active employee status and security clearance (such as how sp_ScheduleMaintenanceOperaiton selects technicians with the highest clearance)
• sp_GenerateMaintenanceReport uses TRY/CATCH blocks to capture and report errors (message, severity, state) without disrupting execution
• Joins with EMPLOYEES and FACILITIES ensure only active records are processed, reducing exposure to unauthorized data

Testing Results

Test Case Outcomes

Emergency Scenario Testing

• Test 1 → successfully initiated and resolved a fire emergency, with correct ID generation and status updates.
• Test 2 → correctly rejected a severity level of 6, returning an error message.
• Test 3 → handled multiple emergencies (Intruder, Fire) without conflicts, validating unique ID generation.
• Test 4 → blocked unauthorized crew members, ensuring security compliance.
• Test 5 → successfully updated crew status and resolved an emergency, with accurate completion percentage calculation.

Crew Function Validation

• Test 1 → calculated efficiency for two crew members, handling edge cases like zero tasks gracefully
• Test 2 → returned workload score for Engineering department, confirming task-to-employee ratio accuracy
• Test 3 → provided realistic response times for Fire and Medical emergencies, factoring in location distances and multipliers
• Test 4 → assessed vessel readiness, correctly weighting training, health, and performance metrics

Maintenance Integration Testing

• Test 1 → successfully scheduled routine maintenance with crew assignment and duration estimation
• Test 2 → detected and rejected overlapping maintenance schedules for the same equipment
• Test 3 → generated a report with metrics like completion rate and overdue count, handling null filters correctly

Performance Analysis

• Joins on indexed columns (EMPLOYEE_ID, EQUIPMENT_ID) minimized full table scans.
• sp_GenerateMaintenanceReport showed higher costs due to multiple joins and aggregations
• Early filtering in WHERE clauses and use of EXISTS over IN improved query performance, especially in sp_UpdateCrewEmergencyStatus

Collaboration Reflection

• I worked with Riker on this lab—we each worked on the tasks and collaborated to work on the SQL for the lab. Also, we used ChatGPT to help optimize our SQL and to make the formatting better, and I used it personally to fix up some of my grammar and formatting for this documentation.

Deployment Recommendations

Integration Guidance

• Deploy tables first (EMERGENCY_EVENTS, TASK) followed by functions and procedures to avoid dependency errors. Ensure referenced tables exist in the database.
• Create indexes on EMPLOYEE_ID, EQUIPMENT_ID, and MAINTENANCE_DATE to optimize join and filter performance
• Configure database roles to restrict procedure execution to authorized users, aligning with employee clearance levels
• Run provided test cases in a staging environment to validate functionality before production deployment

Operational Impact

• Emergency management enhances rapid response capabilities with automated logging and status tracking, reducing manual oversight
• Crew performance provides actionable metrics for workloads and readiness, enabling better resource allocation and training prioritization
• Maintenance scheduling automates scheduling and reporting, reducing downtime and improving equipment reliability
• Regular execution of sp_GenerateMaintenanceReport can identify overdue maintenance and can prompt proactive scheduling to prevent failures

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