Database Homework Help | Creating Ridesharing Database Schema



We refer to a ridesharing business and star schema.


The business works as follows.

  • Passengers use a mobile app to put in the destination they need a ride to.

  • The application connects them with drivers who are willing pick them up and drive them to their destination.

  • Passengers know what fare they will be paying for the trip ahead of time.

  • After the ride is complete, the business pays the driver some of the fare for the trip.

  • Passengers can choose what kind of car they want to ride in, such as spacious, luxurious, or standard.


For analysis purposes, the business uses the below star schema.
















The DDL and DML to create and populate the schema are below.


CREATE TABLE Driver ( 
driver_id DECIMAL(12) NOT NULL PRIMARY KEY, 
first_name VARCHAR(64) NOT NULL, 
last_name VARCHAR(64) NOT NULL); 

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); 


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); 

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); 

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);

Insert Value into "Driver" Table

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 Value into "Passenger" Table

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 Value into "Ride_date" Table

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 Value into "Destination" Table


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 Value into "Ride" Table

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); 

Queries And Their Solution


Query 1:

Amount Paid to Each Driver in Each State

SELECT Driver.first_name, Driver.last_name, Destination.state,  
SUM(Ride.paid_to_driver) AS paid_to_driver  
FROM Ride  JOIN Driver ON Driver.driver_id = Ride.driver_id  
JOIN Destination ON Destination.destination_id = Ride.destination_id  
GROUP BY Destination.state, Driver.driver_id, Driver.first_name, Driver.last_name  ORDER BY first_name, last_name, state; 

Output:











Query 2:

Total Amount Paid to Each Driver, Also by State

SELECT Driver.first_name, Driver.last_name, Destination.state,  
SUM(Ride.paid_to_driver) AS paid_to_driver  
FROM Ride  
JOIN Driver ON Driver.driver_id = Ride.driver_id 
JOIN Destination ON Destination.destination_id = Ride.destination_id  
GROUP BY ROLLUP(Destination.state), Driver.driver_id, Driver.first_name,  Driver.last_name  
ORDER BY first_name, last_name, state; 

Output:
















You might Get Help In

  • Database Designing Help

  • Database ER Diagram Help

  • Database Implementation Help

  • Database Schema Diagram Help

  • Database Querying Help

  • Database homework help

  • Database Project Help

  • Database Coursework Help

  • Database Assignment Help


Get help in any other Database related help or Hire Database expert to do your project or any coursework, we are focusing to deliver code without any plagiarism.


Contact Us!


realcode4you@gmail.com

6 views0 comments