Name: Sayan M CIS 495 MIDTERM KOK 1. Consider a simple relational database for a

Question

Name: Sayan M CIS 495 MIDTERM KOK 1. Consider a simple relational database for a taxi company consisting of the following relations: Customer (Cld, CName, CAddress, CPhone) Driver (DId, DName, DAddress, DPhone) Trip (Cld, DId, LicNo, CallTime, StartLoc, EndLoc, StartTime, EndTime, Fare, Tip, TripRating) Taxi (LicNo, ModelName, ModelYear) TaxiDriver (LicNo, Dld) In this scenario Cld is the ID of a customer, Dld is the ID of a driver, and LicNo is the license number of the taxi, CallTime is the time when the customer called for a taxi, and StartTime is the actual time when the trip started. Startloc and EndLoc are the start and end addresses of the trip. TripRating is a rating between 1 and 5 given by the customer for the particular trip. A driver can drive several different taxis and a taxi can have several drivers, and table TaxiDriver stores which driver is allowed to drive which taxi. A taxi can only be called via phone for immediate pickup, and cannot be booked in advance for future use. The primary key attributes for each relation have been underlined.

Explanation / Answer

a)//select the driver name from drive table , LicNo and ModelName from the Taxi table, if the model year is 2005
SELECT Driver.DNAME,Taxi.LicNo, Taxi.ModelName
FROM ((Trip
INNER JOIN Diver ON Trip.DId=Driver.DId)
INNER JOIN Taxi ON Trip.LicNo=Taxi.LicNo)
WHERE Taxi.ModelYear='2005';

b)//selects the driver and customer name, if the fare is less than $25

SELECT Driver.DName, Customer.CName
FROM ((Trip
INNER JOIN Diver ON Trip.DId=Driver.DId)
INNER JOIN Customer ON Trip.CId=Customer.CId)
WHERE Trip.Fare<$25;

c)//selects the average trip tip from trip table
SELECT AVG(Trip.Tip)
FROM Trip
WHERE Trip.EndLoc='199 Chambers St.Manhattan';

d) //selects the driver name , where taxi not driven in 2014
SELECT Driver.DName
FROM ((Trip
INNER JOIN Diver ON Trip.DId=Driver.DId)
INNER JOIN Diver ON Trip.LicNo=Taxi.LicNo)
WHERE NOT Taxi.ModelYear='2014';

e)//selects driver name, with minimum tip values

SELECT Driver.DName
FROM (Trip
INNER JOIN Diver ON Trip.DId=Driver.DId)
WHERE Trip.TripRating =MIN(Trip.Tip);

//for any clarification, please do comments

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.