Hi everyone, today i introduce to you a Bus System database in which a passenger can ride a bus in specific date.
Driver Table:
CREATE TABLE Driver (
driver_id DECIMAL(12) NOT NULL PRIMARY KEY,
first_name VARCHAR(64) NOT NULL,
last_name VARCHAR(64) NOT NULL);Passenger Table
CREATE TABLE Passenger (
passenger_id DECIMAL(12) NOT NULL PRIMARY KEY,
first_name VARCHAR(64) NOT NULL,
last_name VARCHAR(64) NOT NULL,
street1 VARCHAR(64) NOT NULL,
city VARCHAR(64) NOT NULL,
state VARCHAR(64) NOT NULL,
postal_code VARCHAR(64) NOT NULL);Ride Date Table
CREATE TABLE Ride_date (
ride_date_id DECIMAL(12) NOT NULL PRIMARY KEY,
ride_date DATE NOT NULL,
year DECIMAL(4) NOT NULL,
month DECIMAL(2) NOT NULL,
day_of_month DECIMAL(2) NOT NULL);Destination Table:
CREATE TABLE Destination (
destination_id DECIMAL(12) NOT NULL PRIMARY KEY,
street1 VARCHAR(64) NOT NULL,
city VARCHAR(64) NOT NULL,
state VARCHAR(64) NOT NULL,
postal_code VARCHAR(64) NOT NULL);ride table:
CREATE TABLE Ride (
passenger_id DECIMAL(12) NOT NULL,
ride_date_id DECIMAL(12) NOT NULL,
destination_id DECIMAL(12) NOT NULL,
driver_id DECIMAL(12) NOT NULL,
miles_driven DECIMAL(5) NOT NULL,
fare_charged DECIMAL(8,2) NOT NULL,
paid_to_driver DECIMAL(8,2) NOT NULL);Inserting data into tables
INSERT INTO Driver(driver_id, first_name, last_name)
VALUES(1, 'Owen', 'Mann');
INSERT INTO Driver(driver_id, first_name, last_name)
VALUES(2, 'Leah', 'Webb');
INSERT INTO Driver(driver_id, first_name, last_name)
VALUES(3, 'Curtis', 'Boone');
INSERT INTO Driver(driver_id, first_name, last_name)
VALUES(4, 'Antoinette', 'Ruiz');
INSERT INTO Driver(driver_id, first_name, last_name)
VALUES(5, 'Lillian', 'Chambers');
INSERT INTO Passenger(passenger_id, first_name, last_name, street1, city, state, postal_code)
VALUES(1, 'Tracy', 'Hall', '7640 East Redwood Rd.', 'Onalaska', 'WI', '54650');
INSERT INTO Passenger(passenger_id, first_name, last_name, street1, city, state, postal_code)
VALUES(2, 'Max', 'Potter', '42 Pawnee Drive', 'Saint Cloud', 'MN', '56301');
INSERT INTO Passenger(passenger_id, first_name, last_name, street1, city, state, postal_code)
VALUES(3, 'Bobby', 'Pierce', '54 Marsh St.', 'Saint Louis', 'MO', '63109');
INSERT INTO Passenger(passenger_id, first_name, last_name, street1, city, state, postal_code)
VALUES(4, 'Clark', 'Kim', '410 Edgewood St.', 'Ann Arbor', 'MI', '48103');
INSERT INTO Passenger(passenger_id, first_name, last_name, street1, city, state, postal_code)
VALUES(5, 'Meredith', 'Manning', '744 Glenwood Street', 'Crystal Lake', 'IL', '60014');
INSERT INTO Ride_date(ride_date_id, ride_date, year, month, day_of_month)
VALUES(1, '01‐APR‐2021', 2021, 4, 1);
INSERT INTO Ride_date(ride_date_id, ride_date, year, month, day_of_month)
VALUES(2, '02‐APR‐2021', 2021, 4, 2);
INSERT INTO Ride_date(ride_date_id, ride_date, year, month, day_of_month)
VALUES(3, '03‐APR‐2021', 2021, 4, 3);
INSERT INTO Ride_date(ride_date_id, ride_date, year, month, day_of_month)
VALUES(4, '04‐APR‐2021', 2021, 4, 4);
INSERT INTO Ride_date(ride_date_id, ride_date, year, month, day_of_month)
VALUES(5, '05‐APR‐2021', 2021, 4, 5);
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(1, '8959 W. King Drive', 'Onalaska', 'WI', '54650');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(2, '635 Beech St.', 'Onalaska', 'WI', '54650');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(3, '7041 Lake Forest Drive', 'Saint Cloud', 'MN', '56301');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(4, '26 Gregory St.', 'Saint Cloud', 'MN', '56301');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(5, '85 Depot Circle', 'Saint Louis', 'MO', '63109');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(6, '972 Princeton Lane', 'Saint Louis', 'MO', '63109');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(7, '3 Pennsylvania Court', 'Ann Arbor', 'MI', '48103');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(8, '7469 Tunnel Ave.', 'Ann Arbor', 'MI', '48103');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(9, '9566 Harvard Court', 'Crystal Lake', 'IL', '60014');
INSERT INTO Destination(destination_id, street1, city, state, postal_code)
VALUES(10, '3 Wakehurst St.', 'Crystal Lake', 'IL', '60014');
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(1, 1, 1, 1, 35, 43.05, 25.83);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(1, 1, 2, 3, 20, 24.6, 14.76);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(3, 2, 3, 1, 45, 55.35, 33.21);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(4, 2, 4, 1, 30, 36.9, 22.14);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(5, 3, 5, 1, 22, 27.06, 16.24);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(1, 3, 6, 1, 10, 12.3, 7.38);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(2, 4, 7, 1, 5, 6.15, 3.69);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(3, 4, 8, 1, 18, 22.14, 13.28);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(4, 5, 9, 1, 98, 120.54, 72.32);
INSERT INTO Ride(driver_id, passenger_id, destination_id, ride_date_id, miles_driven, fare_charged,
paid_to_driver)
VALUES(5, 5, 10, 1, 27, 33.21, 19.93)