Lab 3 - Special Projects Division Query Protocols

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

Executive Summary

• This analysis explores hierarchical personnel structures within the SQL database. Queries were developed with self-joins, recursive CTEs, and multi-station relationships to show command chains and oversight across facilities.

Advanced SQL Implementation Analysis

Join Strategy Evaluation

• The most challenging join was connecting employees with their supervisors across multiple levels recursively. By starting with the top level supervisor and building the hierarchy incrementally with an inner join it helps performance as well. Another challenge was making sure outer joins handled missing supervisors correctly in the cross-facility reports.

Subquery Performance

• Nested subqueries were tested to get supervisor chains, but they were less efficient and readable. Recursive CTEs were preferred for maintainability and also performance. However, subqueries are still useful for certain filtering—such as filtering lowest-level employees with a NOT EXISTS clause.

CTE Benefits

• CTEs improve query organization especially in recursive structures. Recursive CTEs allow a clear top-down command tracking and layered hierarchy modeling, which made debugging and expansion easy.

Window Function Applications

• Window functions were used to rank researchers by the number of discoveries, with rolling averages of containment success rates. Also, there were comparative lag/lead analysis of specimen classification changes over time. It was used for ranking researchers (ROW_NUMBER), tracking cumulative budgets (SUM OVER), comparing past and current specimen statuses (LAG).

Set Operation Utility

• Set operations were needed for combining data across separate divisions. Union combined personnel across multiple research stations, intersect was used to isolate individuals in both specimen research and containment protocol development, except was used to highlight specimens present in one database but not another. Also, there were performance tradeoffs in deduping with using union over union all.

Query Optimization Insights

Performance Analysis

• Execution plans were reviewed for complex queries: recursive CTE for personnel chain of command, nested subquery for ranking high-salary researchers by department, windowed LAG function for comparing specimen classifications. The findings from this were that nested loops were costly and that unnecessary full scans should be avoided.

Index Usage

• Queries took advantages of indexes of the IDs on specimen, employees, and facilities. Recursive and join-heavy queries also improved when foreign key columns had proper indexes.

Optimization Techniques

• Refactoring nested subqueries into CTEs and adding where clauses earlier in execution order helps improve performance. Also, using EXISTS over IN reduces execution time for filtering

Best Practices

• Modular queries with CTEs are good
• Avoid deeply nested subqueries when you can use a join or a CTE
• Put filters as early as possible in the query
• Use indexes with join and where clauses
• Use EXISTS over IN for large datasets

Business Intelligence Value

Research Insights

• One of the main insights was that a small group of researchers accounted for the majority of specimen discoveries. Also, there were hardly any supervisors partitions in the database, which was most likely an oversight in the initialization script. However, after discovering this, it was fixed with the updated script to initialize the database.

Security Intelligence

• Window functions show a spike in containment breaches following equipment failure logs, which can prompt investigation into predictive maintenance schedules. Logs also linked clearance violations to specific employees and timeframes.

Operational Recommendations

• Enhance scheduling for preventative equipment maintenance
• Implement stricter enforcement of security clearance auditing
• Prioritize resource allocation to research teams with a high-output

Collaboration Reflection

• This was completed with Riker mainly, we each worked on the tasks and collaborated to see how we could ‘combine’ our queries to make them more efficient and see what we could each improve. I also collaborated with ChatGPT to help me out with some of my grammar in the reflection as well as formatting my SQL a bit to make it more clear.

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