Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need this as fast as possible 1. Consider a simple relational database for a taxi company consisting of the following relations Customer (CId, CName,
I need this as fast as possible
1. Consider a simple relational database for a taxi company consisting of the following relations Customer (CId, 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, Did 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 Start Time 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. I. (10)Create an ER Diagram for the above Business Rules. Label all entities and relationships. Show cardinality and optionality. II. (10)In the Trip table, (Cld, DId, LicNo, CallTime) is the primary key. Could (Cld, Dld, LicNo, Startloc) be a primary key? Could (Did, StartTime) be a primary key? Explain. IlII. (10)ldentify appropriate foreign key relationships between the tables IV. (20)Write statements in SQL for the following queries: List the driver, license numbers and model names of all taxis that the driver drove which are model year 2005. b. List the driver and customer names for all trips with a fare less than $25. c. List the average tip amount of all trips that ended at location '199 Chambers St. Manhattan'. d. List the names of all drivers who have driven no taxis in 2014. f Prnyide the name of the driver(s) who received the lowest (single-trip) tip everStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started