DB Test

## Звездочка

CREATE TABLE "OrderFactTable" (
  "orderId" integer PRIMARY KEY,
  "driverId" integer,
  "vehicleId" integer,
  "gaz" float,
  "distance" bigint,
  "routeId" integer,
  "travel_time" bigint,
  "departure_time" bigint
);

CREATE TABLE "Driver" (
  "driverId" integer PRIMARY KEY,
  "name" string,
  "phoneNumber" text UNIQUE
);

CREATE TABLE "Vehicle" (
  "vehicleId" integer PRIMARY KEY,
  "model" text,
  "year" date
);

CREATE TABLE "MaintenanceFact" (
  "id" integer PRIMARY KEY,
  "vehicleId" integer,
  "maintanceId" integer,
  "routeId" integer,
  "orderId" integer
);

CREATE TABLE "Maintenance" (
  "maintanceId" integer,
  "maintenance_type" text,
  "maintenance_cost" integer,
  "maintenance_date" date
);

CREATE TABLE "Route" (
  "routeId" integer PRIMARY KEY,
  "best_distance" bigint,
  "average_time" bigint
);

ALTER TABLE "Driver" ADD FOREIGN KEY ("driverId") REFERENCES "OrderFactTable" ("driverId");

ALTER TABLE "Vehicle" ADD FOREIGN KEY ("vehicleId") REFERENCES "OrderFactTable" ("vehicleId");

ALTER TABLE "MaintenanceFact" ADD FOREIGN KEY ("vehicleId") REFERENCES "Vehicle" ("vehicleId");

ALTER TABLE "Route" ADD FOREIGN KEY ("routeId") REFERENCES "OrderFactTable" ("routeId");

ALTER TABLE "MaintenanceFact" ADD FOREIGN KEY ("routeId") REFERENCES "Route" ("routeId");

ALTER TABLE "Maintenance" ADD FOREIGN KEY ("maintanceId") REFERENCES "MaintenanceFact" ("maintanceId");

ALTER TABLE "OrderFactTable" ADD FOREIGN KEY ("orderId") REFERENCES "MaintenanceFact" ("orderId");


Запросы

// Fuel Consumption
SELECT AVG(gaz) from OrderFactTable; 

// Drivers Performance
SELECT d.name, sum(oft.travel_time) FROM OrderFactTable as oft JOIN Driver AS d ON d.driverId = oft.driverId 
GROUP BY d.name

// Maintenance Cost
SELECT v.vehicleId, v.model sum(m.maintenance_cost) FROM MaintenanceFact as mf
JOIN Vehicle as v ON mf.vehicleId = v.vehicleId
JOIN Maintenance as m ON m.maintenanceId = mf.maintenanceId
GROUP BY v.vehicleId, v.model 

## Нормальная форма

CREATE TABLE "vehicle" (
  "vehicleId" integer PRIMARY KEY,
  "type" text,
  "make" text,
  "plate_number" text,
  "model" text,
  "year" integer
);

CREATE TABLE "driver" (
  "id" integer PRIMARY KEY,
  "name" text,
  "surname" text,
  "phone_number" text,
  "license_number" bigint
);

CREATE TABLE "route" (
  "routeId" integer PRIMARY KEY,
  "start_location" text,
  "end_location" text,
  "distance" bigint,
  "estimated_time" bigint,
  "gaz" bigint
);

CREATE TABLE "maintenance" (
  "id" integer PRIMARY KEY,
  "vehicle_id" integer,
  "type" text,
  "date" timestamp,
  "cost" integer
);

CREATE TABLE "schedule" (
  "id" integer PRIMARY KEY,
  "vehicle_id" integer,
  "driver_id" integer,
  "route_id" integer,
  "departure" timestamp,
  "arrival" timestamp
);

ALTER TABLE "maintenance" ADD FOREIGN KEY ("vehicle_id") REFERENCES "vehicle" ("vehicleId");

ALTER TABLE "schedule" ADD FOREIGN KEY ("vehicle_id") REFERENCES "vehicle" ("vehicleId");

ALTER TABLE "schedule" ADD FOREIGN KEY ("driver_id") REFERENCES "driver" ("id");

ALTER TABLE "schedule" ADD FOREIGN KEY ("route_id") REFERENCES "route" ("routeId");


// Fuel Consumption
SELECT d.id, d.name, avg(gaz) FROM schedule AS s
JOIN route as r ON s.route_id = r.routeId
JOIN driver as d ON d.id = s.driver_id
GROUP BY d.name, d.id