🪴 Notes

Lab 1

3 min read

Course: CSCI 2020

Part 1: System Verification

Primary Keys

  • PLANETS → planet_id
  • DEPARTMENTS → department_id
  • PERSONNEL → personnel_id
  • STARSHIPS → starship_id
  • ASSIGNMENTS → assignment_id
  • PROJECTS → project_id

Foreign Keys

  • DEPARTMENTS
    • planet_id → PLANETS(planet_id)
  • PERSONNEL
    • department_id → DEPARTMENTS(department_id)
    • superior_id → PERSONNEL(personal_id)
  • STARSHIPS
    • department_id → DEPARTMENTS(department_id)
  • ASSIGNMENTS
    • personnel_id → PERSONNEL(personnel_id)
    • starship_id → STARSHIPS(starship_id)
    • planet_id → PLANETS(planet_id)
  • PROJECTS
    • department_id → DEPARTMENTS(department_id)
    • lead_id → PERSONNEL(personnel_id)
    • planet_id → PLANETS(planet_id)

Part 2: Basic Intelligence Gathering

Planetary Systems Analysis

Personnel Records Investigation

Fleet Asset Inventory

Part 3: Basic Intelligence Operations

Personnel Analysis

SELECT department_id, COUNT(*) AS employees  
FROM PERSONNEL  
WHERE department_id IS NOT NULL  
GROUP BY department_id;

SELECT *  
FROM PERSONNEL  
WHERE salary > 400000;

SELECT AVG(salary) as average_salary  
FROM PERSONNEL;

Starship Investigation

SELECT *  
FROM STARSHIPS  
WHERE class = 'Imperial Star Destroyer';

SELECT status, COUNT(*) AS count  
FROM STARSHIPS  
GROUP BY status

SELECT *  
FROM STARSHIPS  
WHERE crew_capacity = (SELECT MAX(crew_capacity) FROM STARSHIPS);

Project Status Report

SELECT *  
FROM PROJECTS  
where status = 'Active'

SELECT status, COUNT(*) AS count  
FROM PROJECTS  
GROUP BY status

SELECT priority, *  
FROM PROJECTS  
WHERE priority = 1

Create Your Own Queries

SELECT d.name        AS department_name,  
       AVG(p.salary) AS average_salary  
FROM PERSONNEL p  
         INNER JOIN DEPARTMENTS d ON p.department_id = d.department_id  
WHERE p.status = 'Active'  
GROUP BY d.name  
ORDER BY average_salary DESC;

This reveals that the department with the highest salary is the “Imperial Naval Command”, which I suppose makes sense since it would likely be the people up the chain of command.

SELECT d.name AS department_name,  
       s.name AS starship_name,  
       s.maintenance_cost  
FROM STARSHIPS s  
         INNER JOIN DEPARTMENTS d ON s.department_id = d.department_id  
WHERE s.maintenance_cost > 100000  
ORDER BY d.name, s.maintenance_cost DESC;

This reveals that the “Imperial Naval Command” department has multiple starships, whereas the “Military Operations” department only has one—which is interesting since I’m assume the military would have more.

(old) Part 3: Advanced Intelligence Operations

I’ll keep this in the document (I did it before it changed to the new part 3)

Command Structure Analysis

SELECT p1.name                          AS personnel_name,  
       p1.rank                          AS personnel_rank,  
       d.name                           AS department_name,  
       COALESCE(p2.name, 'No Superior') AS superior_name,  
       COALESCE(p2.rank, 'N/A')         AS superior_rank  
FROM PERSONNEL p1  
         LEFT JOIN PERSONNEL p2 ON p1.superior_id = p2.personnel_id  
         LEFT JOIN DEPARTMENTS d ON p1.department_id = d.department_id  
ORDER BY d.name, p1.rank;

Mission Activity Report

SELECT a.start_date                    AS start_date,  
       p.name                          AS personnel_name,  
       p.rank                          AS personnel_rank,  
       COALESCE(s.name, 'No Starship') AS starship_name,  
       pl.name                         AS planet_name  
FROM ASSIGNMENTS a  
         INNER JOIN PERSONNEL p ON a.personnel_id = p.personnel_id  
         LEFT JOIN STARSHIPS s ON a.starship_id = s.starship_id  
         LEFT JOIN PLANETS pl ON a.planet_id = pl.planet_id  
-- all the assignments are active so this isn't really needed, but good to have  
WHERE a.status = 'Active'  
ORDER BY a.start_date;

Strategic Projects Overview

SELECT d.name                                   AS department_name,  
       pl.name                                  AS project_lead,  
       p.status                                 AS project_status,  
       p.budget                                 AS project_budget,  
       p.priority                               AS project_priority,  
       SUM(p.budget) OVER (PARTITION BY d.name) AS department_budget  
FROM PROJECTS p  
         INNER JOIN DEPARTMENTS d ON p.department_id = d.department_id  
         INNER JOIN PERSONNEL pl ON p.lead_id = pl.personnel_id  
ORDER BY d.name, p.status;

Graph View