CREATE TABLE Ports (
port_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
location_city TEXT,
location_country TEXT,
coordinates TEXT, -- Stored as 'latitude, longitude'
capacity INTEGER,
type TEXT, -- e.g., 'Seaport', 'Riverport'
currently_open INTEGER -- 0 for false, 1 for true
);
CREATE TABLE ShippingCompanies (
company_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
headquarters TEXT
);
CREATE TABLE Ships (
ship_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
shipping_company_id INTEGER,
capacity INTEGER
);
CREATE TABLE Routes (
route_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
origin_port_id INTEGER,
destination_port_id INTEGER,
distance INTEGER, -- in nautical miles
transit_time INTEGER, -- in hours
FOREIGN KEY (origin_port_id) REFERENCES Ports(port_id),
FOREIGN KEY (destination_port_id) REFERENCES Ports(port_id)
);
CREATE TABLE RouteStops (
route_id INTEGER,
port_id INTEGER,
sequence INTEGER, -- sequence of route stops along the way between ports, in order starting from 1
duration_of_stop INTEGER, -- in hours
PRIMARY KEY (route_id, port_id),
FOREIGN KEY (route_id) REFERENCES Routes(route_id),
FOREIGN KEY (port_id) REFERENCES Ports(port_id)
);
CREATE TABLE Containers (
container_id INTEGER PRIMARY KEY AUTOINCREMENT,
ship_id INTEGER,
status TEXT, -- e.g., 'Loaded', 'Empty', 'In Maintenance'
type TEXT, -- e.g., 'Standard', 'Refrigerated', 'Tank'
owner TEXT,
FOREIGN KEY (ship_id) REFERENCES Ships(ship_id)
);
CREATE TABLE ContainerHistory (
container_id INTEGER,
date TEXT,
location TEXT, -- Either port_id or 'At Sea'
event TEXT, -- e.g., 'Loaded', 'Unloaded', 'Inspected'
PRIMARY KEY (container_id, date),
FOREIGN KEY (container_id) REFERENCES Containers(container_id)
);
CREATE TABLE FinancialTransactions (
transaction_id INTEGER PRIMARY KEY AUTOINCREMENT,
shipping_company_id INTEGER,
amount REAL,
transaction_date TEXT,
description TEXT,
FOREIGN KEY (shipping_company_id) REFERENCES ShippingCompanies(company_id)
);
CREATE TABLE TradeAgreements (
agreement_id INTEGER PRIMARY KEY AUTOINCREMENT,
countries_involved TEXT,
description TEXT,
start_date TEXT,
end_date TEXT
);
CREATE TABLE ShipMovements (
ship_id INTEGER,
departure_port_id INTEGER,
arrival_port_id INTEGER,
departure_date TEXT,
arrival_date TEXT,
PRIMARY KEY (ship_id, departure_date),
FOREIGN KEY (ship_id) REFERENCES Ships(ship_id),
FOREIGN KEY (departure_port_id) REFERENCES Ports(port_id),
FOREIGN KEY (arrival_port_id) REFERENCES Ports(port_id)
);
INSERT INTO Ports (name, location_city, location_country, coordinates, capacity, type, currently_open)
VALUES
('Coral Bay', 'Aquamarine City', 'Atlantis', '12.5678, -34.9876', 4500000, 'Seaport', 1),
('Sunset Harbor', 'Orangeport', 'Coralia', '56.1234, 108.5432', 3200000, 'Riverport', 1),
('Mystic Port', 'Dreamshore', 'Mystica', '-23.4567, 89.1234', 5400000, 'Seaport', 1),
('Azure Wharf', 'Sapphire Town', 'Azuria', '67.9876, -15.3456', 2800000, 'Inland Port', 0),
('Emerald Gateway', 'Greenvale', 'Veridis', '-87.6543, 43.2109', 4000000, 'Seaport', 1),
('Starlight Dock', 'Luna Landing', 'Lunaria', '38.0987, 76.5432', 3600000, 'Riverport', 1),
('Neptune Anchorage', 'Bluecrest', 'Marinea', '-12.3456, 98.7654', 5100000, 'Seaport', 1),
('Oasis Berth', 'Sandstone City', 'Aridalis', '45.6789, -67.8901', 2200000, 'Inland Port', 1),
('Celestial Docklands', 'Stargaze Harbor', 'Celestia', '89.0123, -20.3456', 3900000, 'Seaport', 0),
('Harmony Harbor', 'Serenity Shores', 'Symphonia', '10.2345, 145.6789', 4800000, 'Riverport', 1),
('Breeze Wharf', 'Windhaven', 'Aeris', '-56.7890, 123.4567', 2600000, 'Seaport', 1),
('Mirage Port', 'Illusion City', 'Miragica', '78.9012, 32.1098', 3300000, 'Inland Port', 1),
('Twilight Docklands', 'Dusk Harbor', 'Twilightia', '-43.2109, 58.9012', 4100000, 'Seaport', 0),
('Aurora Anchorage', 'Polar Reach', 'Aurorae', '91.8273, -82.6543', 2900000, 'Seaport', 1),
('Zenith Wharf', 'Summit Town', 'Altus', '24.6801, 176.3456', 3500000, 'Mountain Port', 1),
('Abyssal Harbor', 'Deepwater City', 'Abyssia', '-76.5432, 189.0123', 5200000, 'Seaport', 1),
('Solar Port', 'Sunrise Landing', 'Solaria', '123.4567, -45.6789', 3000000, 'Riverport', 1),
('Lunar Dock', 'Moonshadow Bay', 'Lunaria', '65.4321, -167.8901', 2400000, 'Seaport', 0),
('Stardust Wharf', 'Comet City', 'Cosmia', '-32.1098, 90.1234', 4900000, 'Inland Port', 1),
('Serenity Anchorage', 'Harmony Isle', 'Pacifica', '19.0123, 50.4321', 3700000, 'Seaport', 1),
('Gale Port', 'Windcrest', 'Ventus', '-10.9876, 167.5309', 2100000, 'Riverport', 1)
;
INSERT INTO ShippingCompanies (name, headquarters)
VALUES
('GlobalSea Logistics', 'Oceanview Tower, Maritime City'),
('RapidFreight Express', 'Speedway Plaza, Sprint Town'),
('CargoMaster International', 'Trade Central, Commerce Port'),
('BlueOcean Carriers', 'Seabreeze Center, Nautica'),
('SkyHigh Shipping', 'Cloudcrest Building, Skyview City'),
('UniversalCargo Corp.', 'World Trade Hub, Globalia'),
('RedStar Transport', 'Starcrest Plaza, Crimsonville'),
('GreenWay Logistics', 'Ecopark Tower, Verdant Valley'),
('Sunrise Shipping Lines', 'Sunstar Building, Aurora City'),
('Nightfall Freighters', 'Moonshadow Plaza, Duskburg'),
('PolarExpress Carriers', 'Snowcrest Tower, Frostbite City'),
('DesertWind Logistics', 'Sandscape Center, Oasis Town'),
('NeptuneMaritime Inc.', 'Mariners Hub, Aquamarine Bay'),
('StellarFreight Systems', 'Cosmic Tower, Stellar City'),
('LightningCargo Dash', 'Sprintcom Plaza, Lightningburg'),
('EcoGreen Transport', 'Naturepark Building, Greenery City'),
('TitaniumCargo Corp.', 'Metropolis Trade Hub, Steelville'),
('RainbowFreight Alliance', 'Spectrum Tower, Rainbow City'),
('ShadowFox Shipping', 'Nightcrest Plaza, Umbraland'),
('SolarisShipping Sunliners', 'Solstice Hub, Solaris City')
;
INSERT INTO Ships (name, shipping_company_id, capacity)
VALUES
('SS Coral Voyager', 1, 35000),
('RapidFreight Express Runner', 2, 28000),
('CargoMaster Titan', 3, 42000),
('BlueOcean Pride', 4, 30000),
('SkyHigh Skylander', 5, 38000),
('Universal Carrier', 6, 45000),
('RedStar Crimson Star', 7, 25000),
('GreenWay EcoFreighter', 8, 32000),
('Sunrise Aurora', 9, 22000),
('Nightfall Shadow', 10, 29000),
('PolarExpress Frostbite', 11, 18000),
('DesertWind Sandstorm', 12, 24000),
('Neptune Explorer', 13, 36000),
('Stellar Stellar Star', 14, 40000),
('Lightning Dash', 15, 19000),
('EcoGreen Natures Grace', 16, 31000),
('Titanium Steel Titan', 17, 43000),
('Rainbow Spectrum', 18, 26000),
('ShadowFox Umbral Stalker', 19, 20000),
('Solaris Solaris Sun', 20, 23000),
('Open Sea', 1, 15000);
INSERT INTO Routes (name, origin_port_id, destination_port_id, distance, transit_time)
VALUES
('Coral Route', 1, 3, 1200, 48),
('Orange Express', 2, 5, 1800, 72),
('Dreamshore Direct', 3, 6, 2500, 100),
('Sapphire Trade Lane', 4, 7, 1400, 56),
('Greenvale Green Route', 5, 8, 900, 36),
('Luna Landing Link', 6, 9, 1600, 64),
('Bluecrest Blue Route', 7, 1, 1100, 44),
('Sandstone City Shuttle', 8, 10, 700, 28),
('Stargaze Harbor Star Route', 9, 11, 2200, 88),
('Harmony Isle Harmony Line', 10, 12, 1300, 52),
('Windhaven Breeze Route', 11, 13, 1900, 76),
('Illusion City Mirage Run', 12, 14, 1500, 60),
('Dusk Harbor Twilight Route', 13, 15, 2300, 92),
('Polar Reach Aurorae Express', 14, 16, 800, 32),
('Summit Town Zenith Ascent', 15, 17, 2100, 84),
('Deepwater City Abyssal Dive', 16, 18, 1700, 68),
('Sunrise Landing Solar Rise', 17, 19, 1400, 56),
('Moonshadow Bay Lunar Path', 18, 20, 900, 36),
('Comet City Stardust Trail', 19, 1, 2400, 96),
('Aquamarine Bay Aqua Route', 20, 3, 1300, 52),
('Sprint City Dash', 1, 6, 2000, 80),
('Mystica Mystic Path', 3, 9, 1200, 48),
('Azuria Azure Trail', 5, 11, 1800, 72),
('Twilightia Twilight Trail', 7, 14, 1500, 60),
('Frostbite City Polar Route', 9, 16, 900, 36),
('Oasis Town Desert Wind', 11, 18, 1600, 64),
('Marinea Marine Way', 13, 1, 2200, 88),
('Stellar City Stellar Route', 15, 4, 1100, 44),
('Lightningburg Lightning Bolt', 17, 7, 1700, 68),
('Greenery City Eco Route', 19, 12, 1400, 56)
;
INSERT INTO RouteStops (route_id, port_id, sequence, duration_of_stop)
VALUES
(1, 1, 1, 6),
(1, 4, 2, 4),
(1, 7, 3, 8),
(1, 3, 4, 12),
(2, 2, 1, 8),
(2, 6, 2, 10),
(2, 9, 3, 6),
(2, 5, 4, 4),
(3, 3, 1, 12),
(3, 8, 2, 6),
(3, 11, 3, 8),
(3, 6, 4, 4),
(4, 4, 1, 4),
(4, 7, 2, 6),
(4, 10, 3, 10),
(4, 5, 4, 8),
(5, 5, 1, 6),
(5, 9, 2, 4),
(5, 12, 3, 12),
(5, 6, 4, 6),
(6, 6, 1, 10),
(6, 10, 2, 8),
(6, 13, 3, 4),
(6, 7, 4, 6),
(7, 7, 1, 8),
(7, 11, 2, 4),
(7, 14, 3, 6),
(7, 8, 4, 12),
(8, 8, 1, 4),
(8, 12, 2, 6),
(8, 15, 3, 10),
(8, 9, 4, 8),
(9, 9, 1, 6),
(9, 13, 2, 4),
(9, 16, 3, 8),
(9, 11, 4, 12),
(10, 10, 1, 12),
(10, 14, 2, 6),
(10, 17, 3, 4),
(10, 12, 4, 8),
(11, 11, 1, 8),
(11, 15, 2, 10),
(11, 18, 3, 6),
(11, 13, 4, 4),
(12, 12, 1, 6),
(12, 16, 2, 4),
(12, 19, 3, 8),
(12, 14, 4, 12),
(13, 13, 1, 4),
(13, 17, 2, 6),
(13, 20, 3, 10),
(13, 15, 4, 8),
(14, 14, 1, 10),
(14, 18, 2, 8),
(14, 1, 3, 4),
(14, 15, 4, 6),
(15, 15, 1, 12),
(15, 19, 2, 6),
(15, 2, 3, 4),
(15, 16, 4, 8),
(16, 16, 1, 6),
(16, 20, 2, 4),
(16, 3, 3, 8),
(16, 18, 4, 12),
(17, 17, 1, 4),
(17, 1, 2, 6),
(17, 4, 3, 10),
(17, 13, 4, 8),
(18, 18, 1, 8),
(18, 2, 2, 10),
(18, 5, 3, 6),
(18, 19, 4, 4),
(19, 19, 1, 6),
(19, 3, 2, 4),
(19, 6, 3, 8),
(19, 11, 4, 12),
(20, 1, 1, 4),
(20, 5, 2, 6),
(20, 9, 3, 10),
(20, 13, 4, 8),
(21, 2, 1, 10),
(21, 6, 2, 8),
(21, 10, 3, 4),
(21, 14, 4, 6),
(22, 3, 1, 6),
(22, 7, 2, 4),
(22, 11, 3, 8),
(22, 15, 4, 12),
(23, 5, 1, 4),
(23, 9, 2, 6),
(23, 13, 3, 10),
(23, 17, 4, 8),
(24, 6, 1, 8),
(24, 10, 2, 12),
(24, 14, 3, 6),
(24, 18, 4, 4),
(25, 7, 1, 10),
(25, 11, 2, 8),
(25, 15, 3, 4),
(25, 19, 4, 6),
(26, 8, 1, 6),
(26, 12, 2, 4),
(26, 16, 3, 8),
(26, 20, 4, 12),
(27, 9, 1, 4),
(27, 13, 2, 6),
(27, 17, 3, 10),
(27, 1, 4, 8),
(28, 10, 1, 8),
(28, 14, 2, 10),
(28, 18, 3, 6),
(28, 2, 4, 4),
(29, 11, 1, 6),
(29, 15, 2, 4),
(29, 19, 3, 8),
(29, 3, 4, 12),
(30, 12, 1, 4),
(30, 16, 2, 6),
(30, 20, 3, 10),
(30, 4, 4, 8)
;
INSERT INTO Containers (ship_id, status, type, owner)
VALUES
(1, 'Loaded', 'Standard', 'GlobalSea Logistics'),
(1, 'Empty', 'Standard', 'RapidFreight Express'),
(2, 'Loaded', 'Refrigerated', 'CargoMaster International'),
(2, 'In Maintenance', 'Standard', 'BlueOcean Carriers'),
(3, 'Loaded', 'Tank', 'SkyHigh Shipping'),
(3, 'Empty', 'Standard', 'UniversalCargo Corp.'),
(4, 'Loaded', 'Standard', 'RedStar Transport'),
(4, 'Empty', 'Refrigerated', 'GreenWay Logistics'),
(5, 'Loaded', 'Standard', 'Sunrise Shipping Lines'),
(5, 'In Maintenance', 'Tank', 'Nightfall Freighters'),
(6, 'Loaded', 'Refrigerated', 'PolarExpress Carriers'),
(6, 'Empty', 'Standard', 'DesertWind Logistics'),
(7, 'Loaded', 'Standard', 'NeptuneMaritime Inc.'),
(7, 'In Maintenance', 'Refrigerated', 'StellarFreight Systems'),
(8, 'Loaded', 'Tank', 'LightningCargo Dash'),
(8, 'Empty', 'Standard', 'EcoGreen Transport'),
(9, 'Loaded', 'Standard', 'TitaniumCargo Corp.'),
(9, 'In Maintenance', 'Refrigerated', 'RainbowFreight Alliance'),
(10, 'Loaded', 'Refrigerated', 'ShadowFox Shipping'),
(10, 'Empty', 'Standard', 'SolarisShipping Sunliners'),
(11, 'Loaded', 'Tank', 'GlobalSea Logistics'),
(11, 'Empty', 'Standard', 'RapidFreight Express'),
(12, 'Loaded', 'Standard', 'CargoMaster International'),
(12, 'In Maintenance', 'Refrigerated', 'BlueOcean Carriers'),
(13, 'Loaded', 'Refrigerated', 'SkyHigh Shipping'),
(13, 'Empty', 'Standard', 'UniversalCargo Corp.'),
(14, 'Loaded', 'Standard', 'RedStar Transport'),
(14, 'In Maintenance', 'Tank', 'GreenWay Logistics'),
(15, 'Loaded', 'Standard', 'Sunrise Shipping Lines'),
(15, 'Empty', 'Refrigerated', 'Nightfall Freighters'),
(16, 'Loaded', 'Tank', 'PolarExpress Carriers'),
(16, 'In Maintenance', 'Standard', 'DesertWind Logistics'),
(17, 'Loaded', 'Standard', 'NeptuneMaritime Inc.'),
(17, 'Empty', 'Refrigerated', 'StellarFreight Systems'),
(18, 'Loaded', 'Refrigerated', 'LightningCargo Dash'),
(18, 'In Maintenance', 'Standard', 'EcoGreen Transport'),
(19, 'Loaded', 'Standard', 'TitaniumCargo Corp.'),
(19, 'Empty', 'Tank', 'RainbowFreight Alliance'),
(20, 'Loaded', 'Standard', 'ShadowFox Shipping'),
(20, 'In Maintenance', 'Refrigerated', 'SolarisShipping Sunliners'),
(1, 'Loaded', 'Tank', 'GlobalSea Logistics'),
(2, 'Empty', 'Standard', 'RapidFreight Express'),
(3, 'Loaded', 'Standard', 'CargoMaster International'),
(4, 'In Maintenance', 'Refrigerated', 'BlueOcean Carriers'),
(5, 'Loaded', 'Refrigerated', 'SkyHigh Shipping'),
(6, 'Empty', 'Standard', 'UniversalCargo Corp.'),
(7, 'Loaded', 'Standard', 'RedStar Transport'),
(8, 'In Maintenance', 'Tank', 'GreenWay Logistics'),
(9, 'Loaded', 'Standard', 'Sunrise Shipping Lines'),
(10, 'Empty', 'Refrigerated', 'Nightfall Freighters'),
(11, 'Loaded', 'Tank', 'PolarExpress Carriers'),
(12, 'In Maintenance', 'Standard', 'DesertWind Logistics'),
(13, 'Loaded', 'Standard', 'NeptuneMaritime Inc.'),
(14, 'Empty', 'Refrigerated', 'StellarFreight Systems'),
(15, 'Loaded', 'Standard', 'LightningCargo Dash'),
(16, 'In Maintenance', 'Tank', 'EcoGreen Transport'),
(17, 'Loaded', 'Refrigerated', 'TitaniumCargo Corp.'),
(18, 'Empty', 'Standard', 'RainbowFreight Alliance'),
(19, 'Loaded', 'Standard', 'ShadowFox Shipping'),
(20, 'In Maintenance', 'Refrigerated', 'SolarisShipping Sunliners')
;
INSERT INTO ContainerHistory (container_id, date, location, event)
VALUES
(1, '2023-08-06', '1', 'Loaded'),
(1, '2023-08-07', 'At Sea', 'In Transit'),
(1, '2023-08-12', '3', 'Unloaded'),
(2, '2023-08-04', '2', 'Loaded'),
(2, '2023-08-05', 'At Sea', 'In Transit'),
(2, '2023-08-09', '5', 'Unloaded'),
(3, '2023-08-08', '3', 'Loaded'),
(3, '2023-08-09', 'At Sea', 'In Transit'),
(3, '2023-08-14', '6', 'Unloaded'),
(4, '2023-08-10', '4', 'Loaded'),
(4, '2023-08-11', 'At Sea', 'In Transit'),
(4, '2023-08-16', '7', 'Unloaded'),
(5, '2023-08-05', '5', 'Loaded'),
(5, '2023-08-06', 'At Sea', 'In Transit'),
(5, '2023-08-11', '8', 'Unloaded'),
(6, '2023-08-12', '6', 'Loaded'),
(6, '2023-08-13', 'At Sea', 'In Transit'),
(6, '2023-08-17', '9', 'Unloaded'),
(7, '2023-08-09', '7', 'Loaded'),
(7, '2023-08-10', 'At Sea', 'In Transit'),
(7, '2023-08-15', '11', 'Unloaded'),
(8, '2023-08-07', '8', 'Loaded'),
(8, '2023-08-08', 'At Sea', 'In Transit'),
(8, '2023-08-13', '12', 'Unloaded'),
(9, '2023-08-11', '9', 'Loaded'),
(9, '2023-08-12', 'At Sea', 'In Transit'),
(9, '2023-08-17', '13', 'Unloaded'),
(10, '2023-08-14', '10', 'Loaded'),
(10, '2023-08-15', 'At Sea', 'In Transit'),
(10, '2023-08-20', '14', 'Unloaded'),
(11, '2023-08-06', '11', 'Loaded'),
(11, '2023-08-07', 'At Sea', 'In Transit'),
(11, '2023-08-12', '15', 'Unloaded'),
(12, '2023-08-04', '12', 'Loaded'),
(12, '2023-08-05', 'At Sea', 'In Transit'),
(12, '2023-08-10', '16', 'Unloaded'),
(13, '2023-08-08', '13', 'Loaded'),
(13, '2023-08-09', 'At Sea', 'In Transit'),
(13, '2023-08-14', '17', 'Unloaded'),
(14, '2023-08-10', '14', 'Loaded'),
(14, '2023-08-11', 'At Sea', 'In Transit'),
(14, '2023-08-16', '18', 'Unloaded'),
(15, '2023-08-07', '15', 'Loaded'),
(15, '2023-08-08', 'At Sea', 'In Transit'),
(15, '2023-08-13', '19', 'Unloaded'),
(16, '2023-08-12', '16', 'Loaded'),
(16, '2023-08-13', 'At Sea', 'In Transit'),
(16, '2023-08-18', '20', 'Unloaded'),
(17, '2023-08-14', '17', 'Loaded'),
(17, '2023-08-15', 'At Sea', 'In Transit'),
(17, '2023-08-20', '1', 'Unloaded'),
(18, '2023-08-09', '18', 'Loaded'),
(18, '2023-08-10', 'At Sea', 'In Transit'),
(18, '2023-08-15', '3', 'Unloaded'),
(19, '2023-08-07', '19', 'Loaded'),
(19, '2023-08-08', 'At Sea', 'In Transit'),
(19, '2023-08-13', '5', 'Unloaded'),
(20, '2023-08-11', '20', 'Loaded'),
(20, '2023-08-12', 'At Sea', 'In Transit'),
(20, '2023-08-17', '7', 'Unloaded'),
(1, '2023-08-04', '1', 'Inspected'),
(4, '2023-08-09', '4', 'Inspected'),
(7, '2023-08-13', '7', 'Inspected'),
(10, '2023-08-18', '10', 'Inspected'),
(13, '2023-08-19', '13', 'Inspected'),
(16, '2023-08-20', '16', 'Inspected'),
(19, '2023-08-05', '19', 'Inspected'),
(2, '2023-08-03', '2', 'Repaired'),
(5, '2023-08-14', '5', 'Repaired'),
(8, '2023-08-16', '8', 'Repaired'),
(11, '2023-08-17', '11', 'Repaired'),
(14, '2023-08-18', '14', 'Repaired'),
(17, '2023-08-19', '17', 'Repaired'),
(20, '2023-08-20', '20', 'Repaired')
;
INSERT INTO FinancialTransactions (shipping_company_id, amount, transaction_date, description)
VALUES
(1, 15000.00, '2023-08-01', 'Freight Charges'),
(2, 22000.00, '2023-08-02', 'Fuel Costs'),
(3, 18500.00, '2023-08-03', 'Maintenance Expenses'),
(4, 12000.00, '2023-08-04', 'Port Fees'),
(5, 25000.00, '2023-08-05', 'Freight Charges'),
(6, 19000.00, '2023-08-06', 'Crew Salaries'),
(7, 16500.00, '2023-08-07', 'Maintenance Expenses'),
(8, 23000.00, '2023-08-08', 'Freight Charges'),
(9, 17000.00, '2023-08-09', 'Fuel Costs'),
(10, 21000.00, '2023-08-10', 'Port Fees'),
(11, 14500.00, '2023-08-11', 'Maintenance Expenses'),
(12, 24000.00, '2023-08-12', 'Freight Charges'),
(13, 19500.00, '2023-08-13', 'Crew Salaries'),
(14, 26000.00, '2023-08-14', 'Fuel Costs'),
(15, 15500.00, '2023-08-15', 'Port Fees'),
(16, 20000.00, '2023-08-16', 'Maintenance Expenses'),
(17, 13000.00, '2023-08-17', 'Freight Charges'),
(18, 21500.00, '2023-08-18', 'Fuel Costs'),
(19, 17500.00, '2023-08-19', 'Crew Salaries'),
(20, 25500.00, '2023-08-20', 'Port Fees'),
(1, 5000.00, '2023-08-01', 'Office Rent'),
(4, 7000.00, '2023-08-02', 'Marketing Expenses'),
(7, 6000.00, '2023-08-03', 'IT Costs'),
(10, 8000.00, '2023-08-04', 'Office Supplies'),
(13, 9000.00, '2023-08-05', 'Legal Fees'),
(16, 10000.00, '2023-08-06', 'Insurance'),
(19, 4000.00, '2023-08-07', 'Utilities'),
(2, 11000.00, '2023-08-08', 'New Ship Deposit'),
(5, 12000.00, '2023-08-09', 'Container Purchase'),
(8, 13000.00, '2023-08-10', 'Office Renovation'),
(11, 14000.00, '2023-08-11', 'IT Upgrade'),
(14, 15000.00, '2023-08-12', 'Marketing Campaign'),
(17, 6000.00, '2023-08-13', 'Software License'),
(20, 7000.00, '2023-08-14', 'Training Expenses'),
(3, 8000.00, '2023-08-15', 'Consulting Fees'),
(6, 9000.00, '2023-08-16', 'Research & Development'),
(9, 10000.00, '2023-08-17', 'Equipment Upgrade'),
(12, 5000.00, '2023-08-18', 'Office Refreshments'),
(15, 11000.00, '2023-08-19', 'New Container Fleet'),
(18, 12000.00, '2023-08-20', 'Green Initiative')
;
INSERT INTO TradeAgreements (countries_involved, description, start_date, end_date)
VALUES
('Atlantis, Coralia, Mystica', 'Free Trade Agreement', '2024-01-01', '2025-12-31'),
('Azuria, Veridis, Lunaria', 'Regional Trade Partnership', '2024-07-01', '2027-06-30'),
('Coralia, Marinea, Aurorae', 'Preferential Trade Agreement', '2021-04-01', '2026-03-31'),
('Mystica, Marinea, Stellar City', 'Economic Integration Agreement', '2019-10-01', '2024-09-30'),
('Azuria, Veridis, Solaris City', 'Trade and Investment Framework', '2023-03-01', '2028-02-28'),
('Atlantis, Coralia, Lunaria', 'Comprehensive Economic Partnership', '2022-11-01', '2027-10-31'),
('Mystica, Veridis, Aurorae', 'Trade Facilitation Agreement', '2021-06-01', '2026-05-31'),
('Coralia, Marinea, Solaris City', 'Bilateral Trade Agreement', '2020-03-01', '2025-02-28'),
('Atlantis, Azuria, Stellar City', 'Trade and Development Agreement', '2019-09-01', '2024-08-31'),
('Mystica, Veridis, Lunaria', 'Mutual Market Access Agreement', '2022-05-01', '2027-04-30'),
('Coralia, Marinea, Solaris City', 'Trade Liberalization Agreement', '2023-01-01', '2028-12-31'),
('Atlantis, Azuria, Aurorae', 'Economic Cooperation Agreement', '2021-11-01', '2026-10-31'),
('Mystica, Veridis, Stellar City', 'Trade and Investment Agreement', '2020-07-01', '2025-06-30'),
('Coralia, Marinea, Lunaria', 'Preferential Trade and Investment Agreement', '2022-03-01', '2027-02-28'),
('Atlantis, Azuria, Solaris City', 'Comprehensive Trade Partnership', '2021-09-01', '2026-08-31'),
('Mystica, Veridis, Aurorae', 'Economic Integration and Development Agreement', '2023-05-01', '2028-04-30'),
('Coralia, Marinea, Lunaria', 'Regional Trade Facilitation Agreement', '2020-01-01', '2025-12-31'),
('Atlantis, Azuria, Solaris City', 'Trade and Investment Facilitation Agreement', '2022-07-01', '2027-06-30'),
('Mystica, Veridis, Stellar City', 'Mutual Economic Cooperation Agreement', '2024-04-01', '2026-03-31'),
('Coralia, Marinea, Aurorae', 'Preferential Trade and Development Agreement', '2023-01-01', '2028-12-31'),
('Atlantis, Azuria, Lunaria', 'Comprehensive Trade and Investment Agreement', '2020-10-01', '2025-09-30'),
('Mystica, Veridis, Solaris City', 'Trade Liberalization and Investment Agreement', '2022-06-01', '2027-05-31'),
('Coralia, Marinea, Stellar City', 'Economic Partnership Agreement', '2021-03-01', '2026-02-28'),
('Atlantis, Azuria, Aurorae', 'Trade Facilitation and Investment Agreement', '2023-01-01', '2028-12-31'),
('Mystica, Veridis, Lunaria', 'Mutual Trade and Development Agreement', '2020-10-01', '2025-09-30'),
('Coralia, Marinea, Solaris City', 'Regional Trade and Investment Agreement', '2022-07-01', '2027-06-30'),
('Atlantis, Azuria, Stellar City', 'Comprehensive Economic Integration Agreement', '2021-04-01', '2026-03-31'),
('Mystica, Veridis, Aurorae', 'Trade and Investment Promotion Agreement', '2023-01-01', '2028-12-31'),
('Coralia, Marinea, Lunaria', 'Preferential Trade Liberalization Agreement', '2020-10-01', '2025-09-30'),
('Atlantis, Azuria, Solaris City', 'Economic and Trade Agreement', '2022-06-01', '2027-05-31'),
('Mystica, Veridis, Stellar City', 'Mutual Trade Facilitation Agreement', '2021-03-01', '2026-02-28'),
('Coralia, Marinea, Aurorae', 'Bilateral Trade and Investment Agreement', '2023-01-01', '2028-12-31'),
('Atlantis, Azuria, Lunaria', 'Comprehensive Trade and Development Agreement', '2020-07-01', '2025-06-30'),
('Mystica, Veridis, Solaris City', 'Economic Integration and Investment Agreement', '2022-04-01', '2027-03-31'),
('Coralia, Marinea, Stellar City', 'Preferential Trade Facilitation Agreement', '2021-01-01', '2025-12-31'),
('Atlantis, Azuria, Aurorae', 'Trade and Investment Partnership', '2022-10-01', '2027-09-30'),
('Mystica, Veridis, Lunaria', 'Mutual Economic Integration Agreement', '2023-06-01', '2028-05-31');
INSERT INTO ShipMovements (ship_id, departure_port_id, arrival_port_id, departure_date, arrival_date)
VALUES
(1, 1, 3, '2023-08-01', '2023-08-03'),
(1, 3, 6, '2023-08-05', '2023-08-07'),
(1, 6, 9, '2023-08-09', '2023-08-11'),
(2, 2, 5, '2023-08-02', '2023-08-04'),
(2, 5, 8, '2023-08-06', '2023-08-08'),
(2, 8, 11, '2023-08-10', '2023-08-12'),
(3, 3, 7, '2023-08-03', '2023-08-05'),
(3, 7, 10, '2023-08-07', '2023-08-09'),
(3, 10, 13, '2023-08-11', '2023-08-13'),
(4, 4, 6, '2023-08-04', '2023-08-06'),
(4, 6, 9, '2023-08-08', '2023-08-10'),
(4, 9, 12, '2023-08-12', '2023-08-14'),
(5, 5, 8, '2023-08-05', '2023-08-07'),
(5, 8, 11, '2023-08-09', '2023-08-11'),
(5, 11, 14, '2023-08-13', '2023-08-15'),
(6, 6, 10, '2023-08-06', '2023-08-08'),
(6, 10, 13, '2023-08-10', '2023-08-12'),
(6, 13, 16, '2023-08-14', '2023-08-16'),
(7, 7, 11, '2023-08-07', '2023-08-09'),
(7, 11, 14, '2023-08-11', '2023-08-13'),
(7, 14, 17, '2023-08-15', '2023-08-17'),
(8, 8, 12, '2023-08-08', '2023-08-10'),
(8, 12, 15, '2023-08-12', '2023-08-14'),
(8, 15, 18, '2023-08-16', '2023-08-18'),
(9, 9, 13, '2023-08-09', '2023-08-11'),
(9, 13, 16, '2023-08-13', '2023-08-15'),
(9, 16, 19, '2023-08-17', '2023-08-19'),
(10, 10, 14, '2023-08-10', '2023-08-12'),
(10, 14, 17, '2023-08-14', '2023-08-16'),
(10, 17, 20, '2023-08-18', '2023-08-20'),
(11, 11, 15, '2023-08-11', '2023-08-13'),
(11, 15, 18, '2023-08-15', '2023-08-17'),
(11, 18, 1, '2023-08-19', '2023-08-21'),
(12, 12, 16, '2023-08-12', '2023-08-14'),
(12, 16, 19, '2023-08-16', '2023-08-18'),
(12, 19, 2, '2023-08-20', '2023-08-22'),
(13, 13, 17, '2023-08-13', '2023-08-15'),
(13, 17, 20, '2023-08-17', '2023-08-19'),
(13, 20, 4, '2023-08-21', '2023-08-23'),
(14, 14, 18, '2023-08-14', '2023-08-16'),
(14, 18, 1, '2023-08-18', '2023-08-20'),
(14, 1, 5, '2023-08-22', '2023-08-24'),
(15, 15, 19, '2023-08-15', '2023-08-17'),
(15, 19, 2, '2023-08-19', '2023-08-21'),
(15, 2, 6, '2023-08-23', '2023-08-25'),
(16, 16, 20, '2023-08-16', '2023-08-18'),
(16, 20, 4, '2023-08-20', '2023-08-22'),
(16, 4, 8, '2023-08-24', '2023-08-26'),
(17, 17, 1, '2023-08-17', '2023-08-19'),
(17, 1, 5, '2023-08-21', '2023-08-23'),
(17, 5, 9, '2023-08-25', '2023-08-27'),
(18, 18, 2, '2023-08-18', '2023-08-20'),
(18, 2, 6, '2023-08-22', '2023-08-24'),
(18, 6, 10, '2023-08-26', '2023-08-28'),
(19, 19, 3, '2023-08-19', '2023-08-21'),
(19, 3, 7, '2023-08-23', '2023-08-25'),
(19, 7, 11, '2023-08-27', '2023-08-29'),
(20, 20, 4, '2023-08-20', '2023-08-22'),
(20, 4, 8, '2023-08-24', '2023-08-26'),
(20, 8, 12, '2023-08-28', '2023-08-30')
;
-- WITH RouteEfficiency AS (
-- SELECT
-- r.route_id,
-- r.name AS route_name,
-- r.distance,
-- r.transit_time,
-- COUNT(rs.port_id) AS total_stops,
-- (CAST(r.transit_time AS FLOAT) / r.distance) AS time_per_mile
-- FROM
-- Routes r
-- JOIN
-- RouteStops rs ON r.route_id = rs.route_id
-- GROUP BY
-- r.route_id, r.name, r.distance, r.transit_time
-- ),
-- LongestRoutes AS (
-- SELECT
-- *,
-- RANK() OVER (ORDER BY distance DESC) AS rank
-- FROM
-- RouteEfficiency
-- )
-- SELECT
-- route_name,
-- distance,
-- transit_time,
-- total_stops,
-- time_per_mile
-- FROM
-- LongestRoutes
-- WHERE
-- rank <= 3;
-- WITH PortActivity AS (
-- SELECT
-- departure_port_id AS port_id,
-- ship_id,
-- departure_date AS date,
-- 'Departure' AS activity_type
-- FROM
-- ShipMovements
-- UNION ALL
-- SELECT
-- arrival_port_id AS port_id,
-- ship_id,
-- arrival_date AS date,
-- 'Arrival' AS activity_type
-- FROM
-- ShipMovements
-- ),
-- PortTraffic AS (
-- SELECT
-- port_id,
-- COUNT(CASE WHEN activity_type = 'Arrival' THEN 1 END) AS arrivals,
-- COUNT(CASE WHEN activity_type = 'Departure' THEN 1 END) AS departures
-- FROM
-- PortActivity
-- GROUP BY
-- port_id
-- ),
-- ShipTurnaround AS (
-- SELECT
-- A.port_id,
-- A.ship_id,
-- A.date AS arrival_time,
-- B.date AS departure_time
-- FROM
-- PortActivity A
-- JOIN
-- PortActivity B ON A.ship_id = B.ship_id AND A.port_id = B.port_id AND A.activity_type = 'Arrival' AND B.activity_type = 'Departure' AND A.date < B.date
-- ),
-- MinTurnaround AS (
-- SELECT
-- port_id,
-- ship_id,
-- MIN(JULIANDAY(departure_time) - JULIANDAY(arrival_time)) * 24 AS turnaround_hours
-- FROM
-- ShipTurnaround
-- GROUP BY
-- port_id, ship_id
-- ),
-- AverageTurnaround AS (
-- SELECT
-- port_id,
-- AVG(turnaround_hours) AS average_turnaround_hours
-- FROM
-- MinTurnaround
-- GROUP BY
-- port_id
-- )
-- SELECT
-- pt.port_id,
-- p.name AS port_name,
-- pt.arrivals,
-- pt.departures,
-- COALESCE(at.average_turnaround_hours, 0) AS average_turnaround_hours
-- FROM
-- PortTraffic pt
-- JOIN
-- Ports p ON pt.port_id = p.port_id
-- LEFT JOIN
-- AverageTurnaround at ON pt.port_id = at.port_id
-- ORDER BY
-- (pt.arrivals + pt.departures) DESC;
-- WITH ShipFuelCosts AS (
-- SELECT
-- sm.ship_id,
-- SUM(ft.amount) AS total_fuel_cost
-- FROM
-- ShipMovements sm
-- JOIN FinancialTransactions ft ON sm.ship_id = ft.shipping_company_id AND ft.description LIKE '%Fuel%'
-- GROUP BY
-- sm.ship_id
-- ),
-- ShipRoutes AS (
-- SELECT
-- sm.ship_id,
-- r.distance
-- FROM
-- ShipMovements sm
-- JOIN Routes r ON r.origin_port_id = sm.departure_port_id AND r.destination_port_id = sm.arrival_port_id
-- ),
-- ShipDistances AS (
-- SELECT
-- ship_id,
-- SUM(distance) AS total_distance
-- FROM
-- ShipRoutes
-- GROUP BY
-- ship_id
-- ),
-- ShipTrips AS (
-- SELECT
-- ship_id,
-- COUNT(*) AS trip_count
-- FROM
-- ShipMovements
-- GROUP BY
-- ship_id
-- ),
-- ShipEfficiency AS (
-- SELECT
-- sd.ship_id,
-- s.name AS ship_name,
-- sd.total_distance,
-- sfc.total_fuel_cost,
-- (sd.total_distance / NULLIF(sfc.total_fuel_cost, 0)) AS efficiency_score, -- Avoid division by zero
-- st.trip_count
-- FROM
-- ShipDistances sd
-- JOIN ShipFuelCosts sfc ON sd.ship_id = sfc.ship_id
-- JOIN Ships s ON sd.ship_id = s.ship_id
-- JOIN ShipTrips st ON sd.ship_id = st.ship_id
-- WHERE
-- st.trip_count >= 3
-- )
-- SELECT
-- ship_id,
-- ship_name,
-- efficiency_score,
-- RANK() OVER (ORDER BY efficiency_score DESC) AS rank
-- FROM
-- ShipEfficiency
-- ORDER BY
-- rank;
-- Port traffic
-- WITH DateRanges AS (
-- SELECT
-- arrival_port_id AS port_id,
-- arrival_date AS dock_date,
-- departure_date
-- FROM
-- ShipMovements
-- WHERE
-- arrival_date BETWEEN DATE('now', '-1 year') AND DATE('now')
-- OR departure_date BETWEEN DATE('now', '-1 year') AND DATE('now')
-- ),
-- DockedShipsPerDay AS (
-- SELECT
-- port_id,
-- dock_date,
-- COUNT(*) AS ships_docked
-- FROM
-- DateRanges
-- WHERE
-- dock_date <= DATE('now')
-- GROUP BY
-- port_id, dock_date
-- ),
-- AverageDockedPerPort AS (
-- SELECT
-- port_id,
-- AVG(ships_docked) AS avg_ships_docked_per_day
-- FROM
-- DockedShipsPerDay
-- GROUP BY
-- port_id
-- ),
-- PeakCongestion AS (
-- SELECT
-- port_id,
-- dock_date,
-- ships_docked,
-- RANK() OVER (PARTITION BY port_id ORDER BY ships_docked DESC) AS congestion_rank
-- FROM
-- DockedShipsPerDay
-- )
-- SELECT
-- ad.port_id,
-- p.name AS port_name,
-- ad.avg_ships_docked_per_day,
-- pc.dock_date AS peak_congestion_date,
-- pc.ships_docked AS peak_ships_docked
-- FROM
-- AverageDockedPerPort ad
-- JOIN
-- Ports p ON ad.port_id = p.port_id
-- JOIN
-- PeakCongestion pc ON ad.port_id = pc.port_id
-- WHERE
-- pc.congestion_rank = 1
-- ORDER BY
-- ad.avg_ships_docked_per_day DESC
-- LIMIT 2;
-- WITH ContainerMovements AS (
-- SELECT
-- c.container_id,
-- c.ship_id,
-- s.name AS ship_name,
-- ch.date AS event_date,
-- ch.location AS event_location,
-- ch.event,
-- p.name AS port_name
-- FROM
-- Containers c
-- JOIN
-- ContainerHistory ch ON c.container_id = ch.container_id
-- LEFT JOIN
-- Ships s ON c.ship_id = s.ship_id
-- LEFT JOIN
-- Ports p ON ch.location = p.port_id
-- WHERE
-- c.container_id = 1
-- ),
-- ShipTransfers AS (
-- SELECT
-- sm.ship_id,
-- sm.departure_port_id,
-- sm.arrival_port_id,
-- sm.departure_date,
-- sm.arrival_date,
-- dp.name AS departure_port_name,
-- ap.name AS arrival_port_name
-- FROM
-- ShipMovements sm
-- JOIN
-- Ports dp ON sm.departure_port_id = dp.port_id
-- JOIN
-- Ports ap ON sm.arrival_port_id = ap.port_id
-- WHERE
-- EXISTS (
-- SELECT 1
-- FROM ContainerMovements cm
-- WHERE cm.ship_id = sm.ship_id
-- AND cm.container_id = 1
-- )
-- )
-- SELECT
-- cm.container_id,
-- cm.ship_name,
-- cm.event_date,
-- cm.event
-- FROM
-- ContainerMovements cm
-- LEFT JOIN
-- ShipTransfers st ON cm.ship_id = st.ship_id AND cm.event_date BETWEEN st.departure_date AND st.arrival_date
-- ORDER BY
-- cm.event_date;
-- WITH AgreementDetails AS (
-- SELECT
-- agreement_id,
-- description,
-- countries_involved,
-- start_date
-- FROM
-- TradeAgreements
-- ),
-- ContainerMovements AS (
-- SELECT
-- c.type AS goods_type,
-- c.container_id,
-- sm.ship_id,
-- sm.departure_port_id,
-- sm.arrival_port_id,
-- sm.departure_date,
-- sm.arrival_date,
-- p.location_country
-- FROM
-- Containers c
-- JOIN ShipMovements sm ON c.ship_id = sm.ship_id
-- JOIN Ports p ON sm.departure_port_id = p.port_id
-- ),
-- VolumePrePostAgreement AS (
-- SELECT
-- ad.agreement_id,
-- ad.description,
-- cm.goods_type,
-- cm.location_country,
-- CASE
-- WHEN cm.departure_date < ad.start_date THEN 'Pre-Agreement'
-- ELSE 'Post-Agreement'
-- END AS period,
-- COUNT(cm.container_id) AS total_containers
-- FROM
-- ContainerMovements cm
-- JOIN AgreementDetails ad ON INSTR(ad.countries_involved, cm.location_country) > 0
-- GROUP BY
-- ad.agreement_id, cm.goods_type, cm.location_country, period
-- )
-- SELECT
-- agreement_id,
-- description,
-- goods_type,
-- location_country,
-- period,
-- total_containers
-- FROM
-- VolumePrePostAgreement
-- WHERE agreement_id IN (1, 3)
-- ORDER BY
-- agreement_id, goods_type, location_country, period;
-- WITH RevenueData AS (
-- SELECT
-- sm.ship_id,
-- sm.route_id,
-- SUM(ft.amount) AS total_revenue
-- FROM
-- ShipMovements sm
-- JOIN FinancialTransactions ft ON sm.ship_id = ft.shipping_company_id
-- WHERE
-- ft.description LIKE '%Freight%' AND
-- ft.transaction_date BETWEEN DATE('now', '-1 year') AND DATE('now')
-- GROUP BY
-- sm.ship_id, sm.route_id
-- )
-- SELECT
-- s.name AS ship_name,
-- r.name AS route_name,
-- rd.total_revenue
-- FROM
-- RevenueData rd
-- JOIN Ships s ON rd.ship_id = s.ship_id
-- JOIN Routes r ON rd.route_id = r.route_id
-- ORDER BY
-- rd.total_revenue DESC;
-- WITH MonthlyVisits AS (
-- SELECT
-- arrival_port_id AS port_id,
-- strftime('%Y-%m', arrival_date) AS month,
-- COUNT(DISTINCT ship_id) AS ship_visits
-- FROM
-- ShipMovements
-- WHERE
-- arrival_date IS NOT NULL
-- GROUP BY
-- arrival_port_id, month
-- ),
-- AverageVisits AS (
-- SELECT
-- port_id,
-- AVG(ship_visits) AS avg_monthly_visits
-- FROM
-- MonthlyVisits
-- GROUP BY
-- port_id
-- ),
-- CongestionLevel AS (
-- SELECT
-- av.port_id,
-- p.name AS port_name,
-- av.avg_monthly_visits,
-- CASE
-- WHEN av.avg_monthly_visits > (SELECT AVG(avg_monthly_visits) FROM AverageVisits) THEN 'High'
-- ELSE 'Low'
-- END AS congestion_status
-- FROM
-- AverageVisits av
-- JOIN
-- Ports p ON av.port_id = p.port_id
-- ),
-- LeastCongestedPorts AS (
-- SELECT
-- port_id,
-- port_name,
-- avg_monthly_visits
-- FROM
-- CongestionLevel
-- WHERE
-- congestion_status = 'Low'
-- ORDER BY
-- avg_monthly_visits ASC
-- LIMIT 1
-- )
-- SELECT
-- cl.port_id,
-- cl.port_name,
-- cl.avg_monthly_visits,
-- cl.congestion_status,
-- lcp.port_name AS recommended_alternative_port,
-- lcp.avg_monthly_visits AS alternative_port_visits
-- FROM
-- CongestionLevel cl
-- CROSS JOIN
-- LeastCongestedPorts lcp
-- WHERE
-- cl.congestion_status = 'High'
-- ORDER BY
-- cl.avg_monthly_visits DESC;
WITH ShipCapacity AS (
SELECT
ship_id,
name,
capacity
FROM
Ships
),
HistoricalLoad AS (
SELECT
sm.ship_id,
COUNT(*) AS containers_loaded,
COUNT(*) * 150 AS total_size_loaded -- Each container is 150 TEUs
FROM
ShipMovements sm
GROUP BY
sm.ship_id
),
LoadEfficiency AS (
SELECT
sc.ship_id,
sc.name AS ship_name,
sc.capacity,
hl.containers_loaded,
hl.total_size_loaded,
FLOOR(sc.capacity / 150) AS theoretical_max_containers, -- Floor to ensure no partial container is counted
FLOOR((sc.capacity / 150) * 0.9) AS recommended_load -- Assumes 90% of theoretical capacity for operational flexibility
FROM
ShipCapacity sc
JOIN
HistoricalLoad hl ON sc.ship_id = hl.ship_id
)
SELECT
ship_id,
ship_name,
theoretical_max_containers,
recommended_load
FROM
LoadEfficiency
ORDER BY
ship_id;
To embed this program on your website, copy the following code and paste it into your website's HTML: