MYSQL

1. Suppose you have a database modeling a mobile riding platform somewhat similar to

Uber, which is given by the following schema:

PASSENGER (pid, pname, city, phone)

DRIVER (did, dname, city, phone, vehicletype, licenseplate)

TRIP (pid, did, starttime, endtime, startloc, destinationloc, miles, price)

Note: pid references pid in PASSENGER, and did references did in DRIVER

RATING (pid, did, starttime, ratertype, score, comment)

Note: (pid, did, starttime) references (pid, did, starttime) in TRIP

In this schema, passengers are the people who use this platform to request a ride.

Drivers are the people who provide the ride. For the sake of simplicity, each driver can

only use one vehicle on this platform. In each trip, there is only one driver and one

passenger account involved. The starttime and endtime in the TRIP table show the start

time and end time of this trip, respectively. After each trip, both the passenger and the

driver have the chance to rate this trip and leave their comments. In the RATING table,

ratertype has only two values, passenger or driver. It indicates whether this rating is

from the driver or the passenger. A record will be added to the RATING table only when

there is a rating. If nobody rates the trip, there will be no record in the RATING table.

(a) Write SQL statements for the following queries.

I. Output the passenger name, driver name, and start time of trips in which the

passenger and the driver are from the same city.

II. Output the passenger ID, driver ID, and start time of trips that were completed in

2020. (Meaning: the end time is during 2020)

III. Output the passenger name, driver name, start time, and end time of trips that

were more than 4 hours and where the vehicle type was SUV.

IV. Output the ID and name of passengers who live in Brooklyn and have not taken

any ride ending in 2020.

V. Output the ID and name of the driver(s) who finished the most rides in 2020.

VI. For each driver, output the total number of rides he/she finished and the number

of ratings he/she got from these rides.

VII. Find the most expensive trip, and output its price together with the ID and name

of the driver(s).

VIII. Output the ID of any passenger who has taken at least one trip with every driver

from the city of Hackensack.

(b) Write statements in Relational Algebra for all eight queries. Use basic RA whenever

possible, and extended RA otherwise.

(c) Write statements in (Domain or Tuple) Relational Calculus for query I, II, IV, VI, VII,

and VIII, or explain if it is not possible to do so.

(d) How would you change the schema if a driver can use several vehicles and a

vehicle can have several drivers?

2. In this problem, you will design a relational schema for an online food delivery system

similar to DoorDash. This service allows people to order food online, and then delivery

people will deliver orders to customers. When customers are waiting for their food, they

are able to check the status of their order. Customers can leave a review for an order

that includes a separate rating of each food item in the delivery, a rating for the delivery

itself, as well as optional comments. Each order only includes food from a single

restaurant, but the order may include several food items from that restaurant. Different

restaurants may have dishes with the same name, but these dishes will often have

different descriptions and differ in information such as calories and serving size.

Customers may receive coupons from restaurants. Each such coupon is for a fixed

amount (e.g., $1.50 off), can only be used at the restaurant that issued it, and only by

the customer that received it. Coupons are automatically applied to any future order at

that restaurant, but have an expiration date. To simplify the design, we do not model the

case of the same restaurant having several locations. Ratings for food items and for

delivery are on a scale from 1 to 5.

For this homework, you need to design a suitable relational schema for this scenario.

Thus, you need to store information about customers (e.g., name, age, email, credit

card, phone number), restaurants (e.g., name, zip code, category (e.g., Chinese,

Seafood, Korean, or Italian)), food items (e.g., food name, description, price), the

people doing the delivery (name, phone number), orders, and coupons.

(a) Design a relational database schema for this online application that supports all the

functionality. You should state reasonable assumptions about your schema that you find

necessary. You need to specify the primary keys and foreign keys for each table.

(b) Write SQL statements for the following queries.

I. Output the name of all delivery people who delivered an order that received five

stars for delivery but only one star for at least one of the food items delivered in

the order.

II. For each restaurant, output the name of the food item(s) with the highest average

rating score given by customers.

III. For each restaurant, output the name of the customer(s) with the largest number

of unexpired coupons.

IV. Output the number of customers who ordered hamburger but did not order

fries in the same order.