CREATE TABLE Stations (
station_id INTEGER PRIMARY KEY,
name TEXT,
location TEXT,
capacity INTEGER,
is_operational BOOLEAN
);
-- charging docks at a station
CREATE TABLE StationDocks (
dock_id INTEGER,
station_id
station_docks INTEGER,
battery_level INTEGER, -- 1-100
battery_gen INTEGER,
is_operational BOOLEAN,
occupied BOOLEAN,
compatible_gens TEXT, -- comma-separated generation ids
PRIMARY KEY (dock_id, station_id),
FOREIGN KEY (station_id) REFERENCES Stations(station_id)
);
CREATE TABLE Bikes (
bike_id INTEGER PRIMARY KEY,
station_id INTEGER,
gen INTEGER,
dock_id INTEGER,
battery_level INTEGER, -- 1-100
status TEXT,
FOREIGN KEY (station_id) REFERENCES Stations(station_id)
FOREIGN KEY (station_id, dock_id) REFERENCES station_docks(station_id, dock_id)
FOREIGN KEY (gen) REFERENCES Generations(generation_id)
);
CREATE TABLE Users (
user_id INTEGER PRIMARY KEY,
name TEXT,
email TEXT UNIQUE,
phone_number TEXT,
join_date TEXT
);
CREATE TABLE Rides (
ride_id INTEGER PRIMARY KEY,
user_id INTEGER,
bike_id INTEGER,
start_time TEXT,
start_station_id INTEGER,
end_time TEXT,
end_station_id INTEGER,
current_location INTEGER,
distance_traveled REAL,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (bike_id) REFERENCES Bikes(bike_id),
FOREIGN KEY (start_station_id) REFERENCES Stations(station_id),
FOREIGN KEY (end_station_id) REFERENCES Stations(station_id)
);
-- a dispatch for station inspections, stray bike pickups, assisting in-ride riders, etc.
-- only one of station/bike/ride is filled per dispatch
CREATE TABLE Dispatch (
dispatch_id INTEGER PRIMARY KEY,
station_id INTEGER,
bike_id INTEGER,
ride_id INTEGER,
reason TEXT,
severity INTEGER, -- Lowest to highest: 1-10
date TEXT,
FOREIGN KEY (station_id) REFERENCES Stations(station_id),
FOREIGN KEY (bike_id) REFERENCES Bikes(bike_id),
FOREIGN KEY (ride_id) REFERENCES Rides(ride_id)
);
CREATE TABLE Generations (
generation_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
description TEXT,
release_year INTEGER,
battery_technology TEXT,
max_speed REAL,
range_miles REAL,
special_features TEXT,
charge_depletion_rate INTEGER
);
INSERT INTO Stations (station_id, name, location, capacity, is_operational)
VALUES
(1, 'Central Park Station', 'New York', 20, 1),
(2, 'Downtown Station', 'Los Angeles', 15, 1),
(3, 'Beachside Station', 'Miami', 10, 1),
(4, 'University Station', 'Boston', 25, 1),
(5, 'Suburban Station', 'Chicago', 18, 1),
(6, 'Mountain View Station', 'Denver', 12, 1);
INSERT INTO StationDocks (dock_id, station_id, battery_level, battery_gen, is_operational, occupied, compatible_gens)
VALUES
(1, 1, 85, 2, 1, 0, '1,2'),
(2, 1, 90, 3, 1, 1, '2,3'),
(3, 1, 70, 2, 1, 0, '1,3'),
(4, 1, 100, 1, 1, 0, '1'),
(5, 2, 60, 2, 1, 1, '2,3'),
(6, 2, 95, 3, 1, 0, '3'),
(7, 2, 80, 1, 1, 0, '1,2'),
(8, 2, 75, 2, 1, 1, '1'),
(9, 3, 50, 1, 1, 0, '1,2'),
(10, 3, 80, 2, 1, 1, '2'),
(11, 3, 90, 3, 1, 0, '3'),
(12, 3, 65, 1, 1, 1, '1'),
(13, 4, 90, 2, 1, 0, '2,3'),
(14, 4, 75, 3, 1, 1, '3'),
(15, 4, 85, 1, 1, 0, '1,2'),
(16, 4, 100, 2, 1, 1, '2'),
(17, 5, 70, 1, 1, 1, '1,3'),
(18, 5, 80, 2, 1, 0, '2'),
(19, 5, 95, 3, 1, 1, '3'),
(20, 5, 60, 2, 1, 0, '1'),
(21, 6, 85, 3, 1, 0, '3'),
(22, 6, 75, 1, 1, 1, '1,2'),
(23, 6, 90, 2, 1, 1, '2'),
(24, 6, 65, 2, 1, 0, '1');
INSERT INTO Bikes (bike_id, station_id, gen, dock_id, battery_level, status)
VALUES
(1, 6, 2, 1, 80, 'Available'),
(2, NULL, 2, NULL, 95, 'In Use'),
(3, 2, 3, 5, 70, 'Available'),
(4, NULL, 2, NULL, 50, 'Under Maintenance'),
(5, 3, 1, 9, 60, 'Available'),
(6, NULL, 2, NULL, 90, 'In Use'),
(7, 4, 3, 13, 85, 'Available'),
(8, NULL, 2, NULL, 75, 'In Use'),
(9, 5, 1, 17, 65, 'Under Maintenance'),
(10, 5, 2, 18, 90, 'Available'),
(11, NULL, 3, NULL, 80, 'In Use'),
(12, 6, 2, 22, 70, 'Available'),
(13, 1, 1, 3, 55, 'Under Maintenance'),
(14, 2, 2, 7, 85, 'Available'),
(15, 6, 3, NULL, 95, 'In Use'),
(16, 4, 1, 15, 70, 'Available'),
(17, 6, 2, NULL, 60, 'Under Maintenance'),
(18, NULL, 3, NULL, 85, 'In Use'),
(19, NULL, 2, NULL, 90, 'In Use'),
(20, 2, 1, 8, 65, 'Available'),
(21, NULL, 3, NULL, 75, 'Under Maintenance'),
(22, 6, 2, NULL, 90, 'In Use'),
(23, 5, 1, 20, 80, 'Available'),
(24, NULL, 2, NULL, 65, 'Under Maintenance');
INSERT INTO Users (user_id, name, email, phone_number, join_date)
VALUES
(1, 'John Doe', 'john@example.com', '+1234567890', '2023-04-12'),
(2, 'Jane Smith', 'jane@example.com', '+9876543210', '2023-03-20'),
(3, 'Michael Johnson', 'michael@example.com', '+5551234567', '2023-02-10'),
(4, 'Emily Brown', 'emily@example.com', '+5559876543', '2023-01-05'),
(5, 'David Lee', 'david@example.com', '+1239876540', '2022-12-25'),
(6, 'Rachel Green', 'rachel@example.com', '+9871234567', '2022-11-10'),
(7, 'Robert Johnson', 'robert@example.com', '+5554567890', '2022-10-01'),
(8, 'Sarah Miller', 'sarah@example.com', '+1236543210', '2022-09-15'),
(9, 'William Anderson', 'william@example.com', '+9879876543', '2022-08-03'),
(10, 'Linda Davis', 'linda@example.com', '+5557894561', '2022-07-20');
INSERT INTO Rides (ride_id, user_id, bike_id, start_time, start_station_id, end_time, end_station_id, current_location, distance_traveled)
VALUES
(1, 1, 1, '2023-04-12 09:00:00', 1, '2023-04-12 09:30:00', 2, 2, 5.0),
(2, 2, 4, '2023-04-12 10:00:00', 2, '2023-04-12 10:45:00', 4, 4, 8.5),
(3, 3, 6, '2023-04-12 11:30:00', 3, NULL, NULL, 3, 3.2),
(4, 4, 8, '2023-04-12 13:00:00', 4, NULL, NULL, 4, 1.5),
(5, 5, 10, '2023-04-12 14:15:00', 5, NULL, NULL, 5, 2.8),
(6, 6, 12, '2023-04-12 15:30:00', 6, NULL, NULL, 6, 4.2),
(7, 1, 3, '2023-04-11 08:00:00', 1, '2023-04-11 08:45:00', 3, 3, 7.2),
(8, 2, 5, '2023-04-11 10:30:00', 2, '2023-04-11 11:15:00', 5, 5, 6.0),
(9, 3, 7, '2023-04-11 12:00:00', 3, NULL, NULL, 3, 2.3),
(10, 4, 9, '2023-04-11 13:45:00', 4, NULL, NULL, 4, 1.8);
INSERT INTO Dispatch (dispatch_id, station_id, bike_id, ride_id, reason, severity, date)
VALUES
(1, 1, NULL, NULL, 'Station Inspection', 5, '2023-04-12'),
(2, NULL, 4, NULL, 'Stray Bike Pickup', 7, '2023-04-12'),
(3, NULL, NULL, 2, 'Assisting In-Ride Rider', 8, '2023-04-12'),
(4, 3, NULL, NULL, 'Battery Replacement', 6, '2023-04-11'),
(5, NULL, 10, NULL, 'Bike Maintenance', 4, '2023-04-11'),
(6, 5, NULL, NULL, 'Station Maintenance', 3, '2023-04-13'),
(7, NULL, 15, NULL, 'Stolen Bike Report', 9, '2023-04-13'),
(8, 2, NULL, NULL, 'Rider Assistance', 2, '2023-04-12'),
(9, NULL, NULL, 5, 'Rider Support', 1, '2023-04-11'),
(10, 4, NULL, NULL, 'Station Clean-up', 7, '2023-04-11'),
(11, NULL, 3, NULL, 'Bike Repair', 5, '2023-04-13'),
(12, 6, NULL, NULL, 'Station Check', 3, '2023-04-12'),
(13, NULL, 8, NULL, 'Stray Bike Recovery', 6, '2023-04-11'),
(14, 1, NULL, NULL, 'Routine Inspection', 4, '2023-04-10'),
(15, NULL, 12, NULL, 'Bike Maintenance', 7, '2023-04-12'),
(16, 3, NULL, NULL, 'Battery Check', 2, '2023-04-11'),
(17, NULL, NULL, 3, 'Rider Assistance', 8, '2023-04-13'),
(18, 5, NULL, NULL, 'Station Clean-up', 6, '2023-04-10'),
(19, NULL, 6, NULL, 'Bike Repair', 4, '2023-04-12'),
(20, 2, NULL, NULL, 'Rider Support', 3, '2023-04-11');
INSERT INTO Generations (name, description, release_year, battery_technology, max_speed, range_miles, special_features)
VALUES
('Gen 1', 'First Generation SoopaBike', 2020, 'Lithium-ion', 20.0, 50.0, 'Regenerative Braking'),
('Gen 2', 'Second Generation SoopaBike', 2022, 'Lithium-ion Polymer', 25.0, 60.0, 'Regenerative Braking, Smart Assist Mode'),
('Gen 3', 'Third Generation SoopaBike', 2024, 'Solid-State Battery', 30.0, 200.0, 'Regenerative Braking, Autopilot');
SELECT COUNT(b.bike_id) AS LithiumIonBikesDocked
FROM Bikes b
JOIN Generations g ON b.gen = g.generation_id
JOIN Stations s ON b.station_id = s.station_id
WHERE g.battery_technology = 'Lithium-ion'
AND s.name = 'Mountain View Station'
;
To embed this program on your website, copy the following code and paste it into your website's HTML: