125 lines
3.9 KiB
SQL
125 lines
3.9 KiB
SQL
/*
|
|
Create and use database Rebu
|
|
*/
|
|
CREATE DATABASE IF NOT EXISTS Rebu;
|
|
USE Rebu;
|
|
|
|
/*
|
|
Create table for drivers
|
|
*/
|
|
DROP TABLE IF EXISTS drivers;
|
|
CREATE TABLE IF NOT EXISTS drivers(
|
|
driver_id INT NOT NULL PRIMARY KEY,
|
|
salutation VARCHAR(3) NOT NULL,
|
|
fname VARCHAR(30) NOT NULL,
|
|
lname VARCHAR(30) NOT NULL,
|
|
`address` VARCHAR(100) NOT NULL,
|
|
phone VARCHAR(20) NOT NULL UNIQUE,
|
|
email VARCHAR(100) NOT NULL UNIQUE,
|
|
insurance_provider VARCHAR(30) NOT NULL,
|
|
policy_number VARCHAR(15) NOT NULL UNIQUE,
|
|
speeding_tickers INT,
|
|
accidents INT
|
|
);
|
|
|
|
/*
|
|
Create table for vehicles, in case a driver has two or more
|
|
*/
|
|
DROP TABLE IF EXISTS vehicles;
|
|
CREATE TABLE IF NOT EXISTS vehicles(
|
|
vehicle_id INT NOT NULL PRIMARY KEY,
|
|
driver_id INT NOT NULL,
|
|
vehicle_make VARCHAR(30) NOT NULL,
|
|
vehicle_model VARCHAR(30) NOT NULL,
|
|
vehicle_year VARCHAR(4) NOT NULL,
|
|
license_plate VARCHAR(7) NOT NULL,
|
|
FOREIGN KEY (driver_id) REFERENCES drivers(driver_id)
|
|
);
|
|
|
|
/*
|
|
Create table for payment cards, in case rider has multiple
|
|
*/
|
|
DROP TABLE IF EXISTS cards;
|
|
CREATE TABLE IF NOT EXISTS cards(
|
|
card_id INT NOT NULL PRIMARY KEY,
|
|
rider_id INT NOT NULL,
|
|
card_number VARCHAR(12) NOT NULL,
|
|
card_name VARCHAR(100) NOT NULL,
|
|
card_expiry VARCHAR(12) NOT NULL,
|
|
FOREIGN KEY (rider_id) REFERENCES riders(rider_id)
|
|
);
|
|
|
|
/*
|
|
Create table for riders
|
|
*/
|
|
DROP TABLE IF EXISTS riders;
|
|
CREATE TABLE IF NOT EXISTS riders(
|
|
rider_id INT NOT NULL PRIMARY KEY,
|
|
`address` VARCHAR(100) NOT NULL,
|
|
phone VARCHAR(20) NOT NULL UNIQUE
|
|
);
|
|
|
|
/*
|
|
Create table for the actual ride information
|
|
*/
|
|
DROP TABLE IF EXISTS rides;
|
|
CREATE TABLE IF NOT EXISTS rides(
|
|
ride_id INT NOT NULL PRIMARY KEY,
|
|
pickup_location VARCHAR(100) NOT NULL,
|
|
pickup_date DATE NOT NULL,
|
|
pickup_time TIME NOT NULL,
|
|
dropoff_location VARCHAR(100) NOT NULL,
|
|
base_cost DECIMAL(5,2) NOT NULL,
|
|
hst DECIMAL(5,2) GENERATED ALWAYS AS (ROUND(base_cost * 0.13,2)) STORED,
|
|
total_cost DECIMAL(5,2) GENERATED ALWAYS AS (ROUND(base_cost * 1.13,2)) STORED,
|
|
driver_id INT NOT NULL,
|
|
driver_satisfaction INT NOT NULL CHECK (driver_satisfaction BETWEEN 1 AND 5),
|
|
rider_id INT NOT NULL,
|
|
card_id INT NOT NULL,
|
|
FOREIGN KEY (driver_id) REFERENCES drivers(driver_id),
|
|
FOREIGN KEY (card_id) REFERENCES cards(card_id)
|
|
);
|
|
|
|
/*
|
|
Some simple select and join statements for testing and demonstration purposes
|
|
*/
|
|
INSERT INTO drivers VALUES
|
|
(1, 'Mr', 'John', 'Doe', '123 Main St', '111-111-1111', 'john@example.com', 'StateFarm', 'POL123', 1, 0),
|
|
(2, 'Ms', 'Jane', 'Smith', '456 Oak Ave', '222-222-2222', 'jane@example.com', 'Allianz', 'POL456', 0, 1),
|
|
(3, 'Mr', 'Mike', 'Brown', '789 Pine Rd', '333-333-3333', 'mike@example.com', 'Geico', 'POL789', 2, 2);
|
|
|
|
INSERT INTO vehicles VALUES
|
|
(1, 1, 'Toyota', 'Camry', '2020', 'ABC1234'),
|
|
(2, 2, 'Honda', 'Civic', '2019', 'XYZ5678'),
|
|
(3, 3, 'Ford', 'Focus', '2018', 'LMN9101');
|
|
|
|
INSERT INTO riders VALUES
|
|
(1, '12 King St', '444-444-4444'),
|
|
(2, '34 Queen St', '555-555-5555'),
|
|
(3, '56 Prince St', '666-666-6666');
|
|
|
|
INSERT INTO cards VALUES
|
|
(1, 1, '123456789012', 'John Doe', '12/27'),
|
|
(2, 2, '234567890123', 'Jane Smith', '11/26'),
|
|
(3, 3, '345678901234', 'Mike Brown', '10/25');
|
|
|
|
INSERT INTO rides (ride_id, pickup_location, pickup_date, pickup_time, dropoff_location, base_cost, driver_id, driver_satisfaction, card_id, rider_id) VALUES
|
|
(1, 'Downtown', '2026-03-01', '08:00:00', 'Airport', 20.00, 1, 5, 1, 1),
|
|
(2, 'Mall', '2026-03-02', '09:30:00', 'University', 15.50, 2, 4, 2, 2),
|
|
(3, 'Station', '2026-03-03', '11:00:00', 'Hotel', 30.00, 3, 3, 3, 3);
|
|
|
|
SELECT * FROM drivers;
|
|
SELECT * FROM vehicles;
|
|
SELECT * FROM riders;
|
|
SELECT * FROM cards;
|
|
SELECT * FROM rides;
|
|
|
|
SELECT * FROM drivers
|
|
INNER JOIN vehicles ON drivers.driver_id = vehicles.driver_id;
|
|
|
|
SELECT * FROM riders
|
|
INNER JOIN cards ON riders.rider_id = cards.rider_id;
|
|
|
|
SELECT * FROM rides
|
|
INNER JOIN drivers ON rides.driver_id = drivers.driver_id
|
|
INNER JOIN riders ON rides.rider_id = riders.rider_id; |