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 TRAVELS Table 2022-10-01 04:14:19 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 1: The Finance team wants to calculate a VAT (8%) on the amount of the stay reservations in the database. However, the team wants to calculate the VAT after the discounts are applied on the prices. In addition, it is important to remember if there is no discount/offers, then the value is NULL in the TRAVELS table. Create a procedure called VATCalculator to calculate the VAT amount and return with the corresponding travel ID. In addition, execute the procedure to account for NULL values and send the respective result to the team. The VAT value should be rounded to 2 decimals. Remember to redefine the MYSQL delimiter ( ; ) when creating the procedure. This will prevent the commands from executing separately. Task Create a procedure to calculate the VAT. > 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 TRAVELS Table 2022-10-01 04:14:19 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 1: The Finance team wants to calculate a VAT (8%) on the amount of the stay reservations in the database. However, the team wants to calculate the VAT after the discounts are applied on the prices. In addition, it is important to remember if there is no discount/offers, then the value is NULL in the TRAVELS table. Create a procedure called VATCalculator to calculate the VAT amount and return with the corresponding travel ID. In addition, execute the procedure to account for NULL values and send the respective result to the team. The VAT value should be rounded to 2 decimals. Remember to redefine the MYSQL delimiter ( ; ) when creating the procedure. This will prevent the commands from executing separately. Task Create a procedure to calculate the VAT. >
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
-
What is the driving force or purpose that will keep you motivated for the rest of your life, and how can you align your actions and goals with this deep-seated motivation?"
-
Define yield curve risk and embedded options risk. Explain (with detailed examples) why these factors must be taken into consideration when using the repricing gap analysis model.
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Create a T-Account Transaction Account titles Cash d Common stock Supplies Creditors accounts payable) Cash Fees earned Rent expense Cash Creditors(accounts payable) Cash Accounts receivable Fees...
-
Using Maxwell's equations, show that (a) A time-dependent magnetic field cannot exist without an electric field; (b) A uniform electric field cannot exist in the presence of a time-dependent magnetic...
-
In 2015, the estimated population of Japan was 127 million with a relative growth rate of -0.16%. (A) Write an equation that models the population growth in Japan, letting 2015 be year 0. (B) Based...
-
What are the modifications necessary for using HRIS software applications that are designed for domestic companies in an MNE?
-
For the following project, 3 sections are expected every week. Each week is 5 working days with 8 hours a day. Find the value of X1? Find the value of X2? Find the value of X3? Find the value of X4?...
-
what is the risk premium for a stock when the risk free rate is 5%, the S&P 500 index has an expected return of 22% and the stock has a beta of 2.5?
-
The Golden Oranges Nursery, which provides facilities for pre-school children on a commercial basis, is preparing its cash budget for next year. A profile of the estimated revenues and expenses for...
-
A ball is having diameter of 50 cm with mass 2 kg falls in the swimming pool. Calculate its buoyant force and volume of water displaced?
-
The scheduled time for a flight between Kuwait city and the city of Dubai in the United Arab Emirates is 75 minutes. Assume that the actual flight time is uniformly distributed between 73 and 85...
-
A study of the time spent by visitors to finish the viewing of a marine life aquarium is uniformly distributed between 120 and200 minutes. What is the probability that the viewing time will be a....
-
The time it takes for a plane to be cleaned and ready for the next flight is uniformly distributed between 35 and 45 minutes. What is the probability that the cleaning time will be a. less than...
-
A quality control officer heads a department that manufactures 1,000,000 units per annum. Answer the following: a. Is it feasible for him to check all units produced or to check a sample of 1,000...
-
Comment on the normality of the sampling distribution of mean when the a. sample size is 5 b. sample size is 2 c. sample size is 30 d. sample size is 100 Do so for the following scenarios: population...
-
A flask contains a mixture of compounds A and B. Both compounds decompose by first order kinetics. The half lives are 50.0 minutes for A and 18.0 minutes for B. If the concentrations of A and B are...
-
Give codons for the following amino acids: (a) Th (b) Asp (c) Thr
-
The 2008 Olympic Redeem Team Which of the eight characteristics associated with positive synergy were evident in the case of the Redeem team? Which of the eight characteristics associated with...
-
What are the eight elements of a typical scope statement?
-
Omega 2 Project. Using the complexity weighting scheme shown in Table 5.2 and the following function point complexity weight table, estimate the total function point count. Assume historical data...
-
In Fig. 122, although a Sending may have multiple assessments, they must all be from different reviewers. The current model does not guarantee it. Write an invariant that deals with this issue....
-
Consider Fig. 13-28, try to present a concentration version of this diagram and present the corresponding (smelling) code. Interface : Controller send proposal(a_proposal_id,a_federal_manager_id) a...
-
Considering Fig. 13-26, a Proposal has an association to Sending that is 1 to *{ordered} stack. Considering that this association is bidirectional, show the mutual-friends implementation of its...
Study smarter with the SolutionInn App