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
Comments