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 USER_ID TRAVEL_DISCOUNT 1003 1003 1001 1002 1003 1002 1004 1003 1003 1002 3005 3006 3002 3001 3007 3007 3003 3001 3005 3000 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 6: The Marketing team of InstantStay wants to create mailing lists for the users. However, they need a single string which consists of all emails of the users combined (delimited) with a ";". You need to create a procedure called EmailList which uses a CURSOR that iteratively collects the email addresses in the USERS table and appends them to the string value. In addition, run the procedure and return back them the email list. Task Create a procedure to collect emails formatted as a mailing list. > 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 USER_ID TRAVEL_DISCOUNT 1003 1003 1001 1002 1003 1002 1004 1003 1003 1002 3005 3006 3002 3001 3007 3007 3003 3001 3005 3000 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 6: The Marketing team of InstantStay wants to create mailing lists for the users. However, they need a single string which consists of all emails of the users combined (delimited) with a ";". You need to create a procedure called EmailList which uses a CURSOR that iteratively collects the email addresses in the USERS table and appends them to the string value. In addition, run the procedure and return back them the email list. Task Create a procedure to collect emails formatted as a mailing list. >
Expert Answer:
Answer rating: 100% (QA)
Task VI The Marketing team of InstantStay wants to create mailing lists for the users H... View the full 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...
-
Table illustrates the quantities, marginal costs, average variable costs, and average costs of a competitive firm. Refer to table 4. How much is the unit profit at price =$30? [the problem is based...
-
Give the phase and the missing properties of P, T, v and x. These may be a little more difficult if the appendix tables are used instead of the software. Discuss.
-
Reese Co. will pay 1 million British pounds for materials imported from the U.K. in one month. Reese Co. sells some goods to Poland, and will receive 3 million zloty (the Polish currency) for those...
-
Which one of the following is not a rotary pump? (a) gear pump (b) vane pump (c) screw pump (d) axial pump
-
During your audit of Carla Pang Inc. you prepared the following bank transfer schedule. Fill out the table below indicating the most likely situation as it relates to cash at year-end. Indicate the...
-
Piedmont Company segments its business into two regions-North and South. The company prepared the contribution format segmented income statement as shown: Sales Variable expenses Contribution margin...
-
Huey and Dunham (1987) measured the running speed of fence lizards, Sceloporus merriami, in Big Bend National Park in Texas. Individual lizards were captured and placed in a 2.3-meter raceway, where...
-
Figure out the fix cost with the information given below. 1 2 B Wedgewood Candle Co. Soy Candle Division Operating Results for Quarter 1, 2022 % % 4 5 Revenues Variable Fixed $11,382,084 5 Cost of...
-
Explain Super's life roles, career maturity, life space, and work-life-balance in career development.
-
Assignment 3 CLO 4 Pee Company acquired 80% in See Company for $800,000 on Jan 1, 2019, when See had $700,000 capital stock and $200,000 retained earnings. The excess of fa ir over book value relates...
-
Whitman Company Income Statement Sales (42,000 units x $41.60 per unit) Cost of goods sold (42,000 units x $21 per unit) Gross margin Selling and administrative expenses Net operating income $...
-
Mini-Case: Tax Deductions and Tax Credits Ted Burns Your investment client, Ted Burns, is a 55-year-old service manager for a car dealership. Ted has asked you for advice on preparing his 2017 tax...
-
Valence Electronics has 231 million shares outstanding. It expects earnings at the end of the year of $810 million. Valence pays out 40% of its earnings in total - 15% paid out as dividends and 25%...
-
Question on Linear Algebra : Prove the area of a parallelogam made by the vectors a and b starting at the origin 'O' is || a X b || .
-
Why do CPA firms sometimes use a combination of positive and negative confirmations on the same audit?
-
Calculate the direct and total direct costs of labor for a project team member using the following data: Hourly rate: $50/hr Hours needed: 100 Overhead rate: 30%
-
What do you believe is more important for successfully completing a projectthe formal project management structure or the culture of the parent organization?
-
1 . Why would IBM want to move from computer hardware to service products? 2 . What impact will artificial intelligence (AI) have on the field of project management?
-
As part of a study of the relationship between brain size and IQ, a random sample of 250 adult males is obtained and their brain volumes are measured and found to be normally distributed. Decide...
-
A sample of 2,000 women is randomly selected, and it is found that the heights of the women are normally distributed with a mean of 63.6 in. Decide whether the statement makes sense (or is clearly...
-
The waiting times at a bus stop if the bus comes once every 10 minutes and you arrive at random times. State whether you would expect it to be normally distributed. Explain your reasoning.
Study smarter with the SolutionInn App