Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: USER ID 3001 3002 3003 3004 3005 3006 3007 3008 USER FIRST NAME Jack Ryan Nursin Sarah Bobby Randy Jose Nursin USERS Table USER LAST NAME Cons VIMA Frie Grmn Clark Troms USER EMAIL @mal.com calgama.com nata@gmail.com spricemal.com bprmn@mail.com cork@mal.com thomas@mail.com nyman@mail.com In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: DRIVER ID 2001 2002 2003 2004 CAR ID 1001 1002 DRIVER FIRST NAME DRIVERS Table 1003 Willie 1004 Justin Anthony Ece DRIVER_LAST_NAME DRIVER DRIVING LICENSE_ID CARS Table Butler Howard Walker Yilmaz CAR PLATE BB-542-AB BB-883-EE B8-451-ZN 1874501 BB-189-MM 1953853 1735487 1734747 CAR MODEL In the CARS table, all the cars in the Instant Ride system are kept with the license plate, model and year: TOYOTA PRIUS TESLA MODEL 3 TOYOTA AURIS DRIVER START DATE MERCEDES E200 2022-09-12 2022-09-09 2022-09-15 2022-08-15 DRIVER DRIVING LICENSE CHECKED 1 1 1 1 CAR_YEAR 2020 2021 2021 DRIVER RATING 2021 44 4.8 3.5 0.0 Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and user are listed for each drive. Price and discount information are also available in the database: information are also available in the database: TRAVEL ID 5001 5002 5003 5004 5005 5006 5007 5006 5009 5010 TRAVEL START TIME 2022-10-01 01:04:55 2022-10-01 06:57:33 2022-10-01 13:35:20 2022-10-02 08:44:48 2022-10-02 16:38:54 2022-10-03 19:12:14 2022-10-03 16:06:36 2022-10-03 17:17:12 2022-10-03 21:1608 2022-10-03 23:21:40 TRAVEL_END_TIME 2022-10-01 04:14:19 TRAVELS Table 2022-10-01 06:12:33 2022-10-01 13:45:10 2022-10-02 09:15:28 2022-10-02 1648:10 2022-10-03 19:23:45 2022-10-03 16:08:56 2022-10-03 17:37:42 2022-10-03 21:26:18 2022-10-03 23:39:10 TRAVEL START LOCATION 9614 York Road 47 Church Street 2 Windsor Road 9060 Mill Lane 2 Queensway 50 Main Road 39 Park Road 37 The Drive 77 Mill Road 16 Church Road TRAVEL END LOCATION 84 Church Lane 60 High Street 95 West Street 27 Main Road 24 Mill Lane 93 Broadway 91 West Street 17 Stanley Road 724 Springfield Road 30 North Road TRAVEL PRICE 1544 20.56 12:32 3049 11.15 1461 4.41 25.12 13.55 25.62 DRIVER ID 2001 2001 2002 2003 2001 2003 2002 2001 2001 2000 CAR ID 1003 1003 1001 1002 1003 1002 1004 1003 1003 1002 USER_ID 3005 3006 3002 3001 3007 3007 3003 3001 3005 3000 TRAVEL DISCOUNT NULL NULL NULL 013 NULL 0.10 014 0.25 NULL 0.20 You are assigned as the database administrator to collect and manage transactional data of the Instant Ride operations. Your main task is to create SQL scripts to help other teams to retrieve the requested data. In the following activities, you will create the scripts, run against the database and send the result to the corresponding teams. Grading After you have completed a problem and clicked the Run Query button, mark the task as complete. Checks will run to verify your work. Task 3: The Marketing team wants to collect emails of the users on InstantStay. However, the team needs a SQL statement to execute inside their programming environment. You need to create a statement that they can easily run the EXECUTE command to return a single column table containing the USER_EMAIL addresses. Task Create a prepared statement for use with the EXECUTE command. > Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: USER ID 3001 3002 3003 3004 3005 3006 3007 3008 USER FIRST NAME Jack Ryan Nursin Sarah Bobby Randy Jose Nursin USERS Table USER LAST NAME Cons VIMA Frie Grmn Clark Troms USER EMAIL @mal.com calgama.com nata@gmail.com spricemal.com bprmn@mail.com cork@mal.com thomas@mail.com nyman@mail.com In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: DRIVER ID 2001 2002 2003 2004 CAR ID 1001 1002 DRIVER FIRST NAME DRIVERS Table 1003 Willie 1004 Justin Anthony Ece DRIVER_LAST_NAME DRIVER DRIVING LICENSE_ID CARS Table Butler Howard Walker Yilmaz CAR PLATE BB-542-AB BB-883-EE B8-451-ZN 1874501 BB-189-MM 1953853 1735487 1734747 CAR MODEL In the CARS table, all the cars in the Instant Ride system are kept with the license plate, model and year: TOYOTA PRIUS TESLA MODEL 3 TOYOTA AURIS DRIVER START DATE MERCEDES E200 2022-09-12 2022-09-09 2022-09-15 2022-08-15 DRIVER DRIVING LICENSE CHECKED 1 1 1 1 CAR_YEAR 2020 2021 2021 DRIVER RATING 2021 44 4.8 3.5 0.0 Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and user are listed for each drive. Price and discount information are also available in the database: information are also available in the database: TRAVEL ID 5001 5002 5003 5004 5005 5006 5007 5006 5009 5010 TRAVEL START TIME 2022-10-01 01:04:55 2022-10-01 06:57:33 2022-10-01 13:35:20 2022-10-02 08:44:48 2022-10-02 16:38:54 2022-10-03 19:12:14 2022-10-03 16:06:36 2022-10-03 17:17:12 2022-10-03 21:1608 2022-10-03 23:21:40 TRAVEL_END_TIME 2022-10-01 04:14:19 TRAVELS Table 2022-10-01 06:12:33 2022-10-01 13:45:10 2022-10-02 09:15:28 2022-10-02 1648:10 2022-10-03 19:23:45 2022-10-03 16:08:56 2022-10-03 17:37:42 2022-10-03 21:26:18 2022-10-03 23:39:10 TRAVEL START LOCATION 9614 York Road 47 Church Street 2 Windsor Road 9060 Mill Lane 2 Queensway 50 Main Road 39 Park Road 37 The Drive 77 Mill Road 16 Church Road TRAVEL END LOCATION 84 Church Lane 60 High Street 95 West Street 27 Main Road 24 Mill Lane 93 Broadway 91 West Street 17 Stanley Road 724 Springfield Road 30 North Road TRAVEL PRICE 1544 20.56 12:32 3049 11.15 1461 4.41 25.12 13.55 25.62 DRIVER ID 2001 2001 2002 2003 2001 2003 2002 2001 2001 2000 CAR ID 1003 1003 1001 1002 1003 1002 1004 1003 1003 1002 USER_ID 3005 3006 3002 3001 3007 3007 3003 3001 3005 3000 TRAVEL DISCOUNT NULL NULL NULL 013 NULL 0.10 014 0.25 NULL 0.20 You are assigned as the database administrator to collect and manage transactional data of the Instant Ride operations. Your main task is to create SQL scripts to help other teams to retrieve the requested data. In the following activities, you will create the scripts, run against the database and send the result to the corresponding teams. Grading After you have completed a problem and clicked the Run Query button, mark the task as complete. Checks will run to verify your work. Task 3: The Marketing team wants to collect emails of the users on InstantStay. However, the team needs a SQL statement to execute inside their programming environment. You need to create a statement that they can easily run the EXECUTE command to return a single column table containing the USER_EMAIL addresses. Task Create a prepared statement for use with the EXECUTE command. >
Expert Answer:
Related Book For
Project Management The Managerial Process
ISBN: 9781260570434
8th Edition
Authors: Eric W Larson, Clifford F. Gray
Posted Date:
Students also viewed these databases questions
-
a. Why is it important to have a good credit score? List at least 3 reasons. b. List the 5 factors that influence your credit score. c. What is a credit reporting bureau? (You may need to do some...
-
Consider the job you currently have in criminal justice or would like to have in the future. How might learning various budgetary approaches make you valuable to a criminal justice employer?
-
In the field of industrial organization, economists study the strategic behavior of firms under alternative market structures and regulatory policies. One such market structure is known as an...
-
On January 26, Nova Co. borrowed cash from Conrad Bank by issuing a 30-day note with a face amount of $60,000. Assume a 360-day year. a. Determine the proceeds of the note, assuming the note carries...
-
A substance is at 70 F, 300 lbf/in.2 in a 10 ft3 tank. Estimate the mass from the compressibility chart if the substance is a) air, b) butane or c) propane.
-
Assume the Hong Kong dollar (HK$) value is tied to the U.S. dollar and will remain tied to the U.S. dollar. Last month, a HK$ = 0.25 Singapore dollars. Today, a HK$=0.30 Singapore dollars. Assume...
-
For pumping mustard oil from a drum, a suitable pump is (a) reciprocating pump (b) vane pump (c) centrifugal pump (d) gear pump
-
1. What are some advantages of a person-focused pay system at Mitron? 2. What are some disadvantages? 3. What approach would you recommend for Holly to take in designing a person-focused pay system?...
-
After all of the account balances have been extended to the Balance Sheet columns of the work sheet, the totals of the Debit and Credit columns are $28,246 and $21,306, respectively. What is the...
-
Consider the following network representation of a transportation problem: The supplies, demands, and transportation costs per unit are shown on the network. a. Develop a linear programming model for...
-
Assume that there are two factors of production: labor and physical capital. When Japan opened to trade in 1860, we saw that Japan started to import rice, a labor-intensive good. a.According to the...
-
What industry led the push for the incorporation of as little iron as possible in building construction in the interest of keeping costs down?
-
A flat fee associated with a specific, routine event, like $25 for a doctor's visit. The maximum amount of medical expenses you can pay each year. The percentage of your healthcare costs you cover....
-
Why are gains/losses added back to (or deducted from) the cash from operations section of a cash flow statement?
-
Under what conditions would the National Building Code (N.B.C.) be used, as opposed to the Ontario Building Code (O.B.C.)?
-
Combination A, B, C, and F is the fifth most valuable with an NPV of $169,000. how did you came up with it?
-
Mr. Mick moved from Calgary Alb. to Markham On. to start a new job. He earned $66,500 from his Calgary job and $36,500 from his Markham job in the year of the move. Mr. Mick incurred the following...
-
Pappa's Appliances uses the periodic inventory system. Details regarding the inventory of appliances at January 1, purchases invoices during the year, and the inventory count at December 31 are...
-
1 . What are the disadvantages of concurrent engineering (fast tracking)? 2 . What kinds of projects should avoid using concurrent engineering?
-
Do you agree that true innovation can only come from a small group of dedicated professionals?
-
What similarities do you see between the Ford Diesel team and the Mac team at Apple and the Skunk Works at Lockheed?
-
What are the numbers of observations in each of the five categories that rate the respondents level of job satisfaction?
-
Which of the seven store departments had the most customer visits, and what was this number?
-
What proportion of the 26,280 individuals are in each of the five categories?
Study smarter with the SolutionInn App