MissionOps

Video Demo: https://youtu.be/vQrAjbHH3-U

Description: This project demonstrates the comprehensive knowledge acquired through Harvard's CS50 Introduction To Databases with SQL Course.

It begins with the first table, aircraft. this table contains a primary id for easy referencing, type, squadron, homebase, and role. It organizes each aircraft with a primary key that allows for easy searching.

CREATE TABLE "aircraft"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "type" TEXT NOT NULL,
  "squadron" TEXT NOT NULL,
  "home_base" TEXT NOT NULL,
  "role" TEXT NOT NULL
);

Next, we have the second table, test_ranges. This table includes the locations where the aviation is tested, for example China Lake, CA. It contains a primary key of id, range_name, region, size_sq_miles, and operating_organization.

CREATE TABLE "test_ranges"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "range_name" TEXT NOT NULL UNIQUE,
  "region" TEXT NOT NULL,
  "size_sq_miles" INTEGER NOT NULL,
  "operating_organization" TEXT NOT NULL
);

The third table operators uses id as a primary key, name, role, organization, and clearance level. The name is the name of the operators and the role is the role they play.

CREATE TABLE "operators"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "role" TEXT NOT NULL,
  "organization" TEXT NOT NULL,
  "clearance_level" TEXT NOT NULL CHECK("clearance_level" IN ('confidential', 'secret', 'top secret'))
);

The fifth table is missions and it also contains a primary key for identification, and it has a mission code which is a text, and it has aircraft id which is a foreign key referencing aircraft id, and it has range_id which is a foreign key referencing test_ranges id, and it has a mission type, mission date, start time, and end time.

CREATE TABLE "missions"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "mission_code" TEXT NOT NULL UNIQUE,
  "aircraft_id" INTEGER NOT NULL,
  "range_id" INTEGER NOT NULL,
  "mission_type" TEXT NOT NULL,
  "mission_date" DATE NOT NULL,
  "start_time" TEXT NOT NULL,
  "end_time" TEXT NOT NULL,
  FOREIGN KEY("range_id") REFERENCES "test_ranges"("id"),
  FOREIGN KEY("aircraft_id") REFERENCES "aircraft"("id")
);

The sixth table, weather_conditions, looks at a primary key of id, a mission code which is a foreign key of type text referencing the mission, the temperature_f which is temperature in farenheit, the wind_speed_knots which is wind speet in knots, and wind_gusts_knots which is the wind gusts in knots, wind_direction_deg which is wind direction, visibility_miles which is the visibility the aircraft has of its surroundings, precipitation_inches which is self explanatory, and humidity_percent the percentage humidity.

CREATE TABLE "weather_conditions"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "mission_code" TEXT NOT NULL UNIQUE,
  "temperature_f" REAL NOT NULL,
  "wind_speed_knots" REAL NOT NULL,
  "wind_gusts_knots" REAL NOT NULL,
  "wind_direction_deg" REAL NOT NULL,
  "visibility_miles" REAL NOT NULL,
  "precipitation_inches" REAL NOT NULL,
  "humidity_percent" REAL NOT NULL,
  FOREIGN KEY("mission_code") REFERENCES "missions"("mission_code")
);

The seventh table, mission_results, contains a primary key of an id, a mission code which is again a foreign key referencing missions table, the status which is either go or no go, the primary_reason of the decision, and notes containing additional information regarding the mission results.

CREATE TABLE "mission_results"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "mission_code" TEXT NOT NULL UNIQUE,
  "status" TEXT NOT NULL CHECK("status" IN ('GO', 'NO_GO')),
  "primary_reason" TEXT NOT NULL,
  "notes" TEXT,
  FOREIGN KEY("mission_code") REFERENCES "missions"("mission_code")
);

The eighth table, mission_risk_assessments, contains a primary key of id, a mission code which is again a foreign key for the missions table, and the warnings_triggered which is an integer showing how many warnings were triggered, and the risk level which is either low, moderate, or high and has a check constraint, decision which is either go or no go, and explanation which serves as an additional notes section explaining why the mission was assessed as it was.

CREATE TABLE "mission_risk_assessments"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "mission_code" TEXT NOT NULL UNIQUE,
  "warnings_triggered" INTEGER NOT NULL,
  "risk_level" TEXT NOT NULL CHECK("risk_level" IN ('LOW', 'MODERATE', 'HIGH')),
  "decision" TEXT NOT NULL CHECK("decision" IN ('GO', 'NO_GO')),
  "explanation" TEXT NOT NULL,
  FOREIGN KEY("mission_code") REFERENCES "missions"("mission_code")
);

The ninth table, mission_logs, contains a primary key id, and mission code again a foreign key referencing missions(mission code), and we have operator id which is essentially another foreign key referencing operators(id). Additionally we have timestamp which has the date, and system notes which is a text.

CREATE TABLE "mission_logs"(
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "mission_code" TEXT NOT NULL,
  "operator_id" INTEGER NOT NULL,
  "timestamp" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "system_notes" TEXT,
  FOREIGN KEY("operator_id") REFERENCES "operators"("id"),
  FOREIGN KEY("mission_code") REFERENCES "missions"("mission_code")
);