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 4: The Development team of InstantStay wants to ensure that all email data of the users are lowercase. Otherwise, their login system could have problems finding the users. Therefore, they want you to ensure that each email in the table will always be lowercase after a new user is added. Create a TRIGGER called email_insert that will SET new email addresses to lowercase and run before new emails are added to the USERS table. Task Create a TRIGGER to ensure all new email address are lowercase 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 4: The Development team of InstantStay wants to ensure that all email data of the users are lowercase. Otherwise, their login system could have problems finding the users. Therefore, they want you to ensure that each email in the table will always be lowercase after a new user is added. Create a TRIGGER called email_insert that will SET new email addresses to lowercase and run before new emails are added to the USERS table. Task Create a TRIGGER to ensure all new email address are lowercase
Expert Answer:
Answer rating: 100% (QA)
Task IV The Development team of InstantStay wants to ... 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...
-
Knowledge Check 01 A company is in its first month of operations. Supplies worth $4,000 were purchased on January 5. At the end of the month supplies worth $3,000 were in hand. What adjusting entry...
-
Penny Company offers two products. At present, the following represents the usual results of a month's operations: Required: a. Find the breakeven point in dollars. b. Find the margin of safety...
-
HOW MUCH DO YOU GET PAID? Joan had been working as a reporter for a large television network for seven years. She was an experienced and hardworking reporter who had won many awards over the years...
-
Based upon PCAOB Release 2004-001, what are some strong indicators of material weakness in internal control over financial reporting?
-
Kimm Company has gathered the following information about its product. Direct materials. Each unit of product contains 4.5 pounds of materials. The average waste and spoilage per unit produced under...
-
Stens Kenlin Hospitality Corp. has 6.17% annual coupon paying bond that matures in fifteen years. What is the value of a $1,000 par value Stens Kenlin Hospitality Corporation bond if the required...
-
A centrifugal pump delivers water through a 40-cm-diameter, 1,000-m-long commercial steel pipe from reservoir A to B with EA = 920.5 m and 8 = 935.5 m. Friction losses vary according to the Moody...
-
Tom's Year 2 taxable income was $275,000 with a corresponding tax liability of $42,000. His Year 2 adjusted gross income was $280,000. For Year 3, Tom expects taxable income of $250,000 and a tax...
-
Arrange a tour of the chemical engineering unit operations laboratory to observe the different types of separation equipment. Although this equipment is often much larger than the equipment in...
-
The fourth standard of reporting requires the auditors report to contain either an expression of opinion regarding the financial statements taken as a whole or an assertion to the effect that an...
-
ABC Company has a taxable loss of \(\$ 100,000\). The tax rate is \(40 \%\). What is the potential benefit of the tax loss?
-
Soon after Boyds audit report was issued, Boyd learned of certain related party transactions that occurred during the year under audit. These transactions were not disclosed in the notes to the...
-
Refer again to the data in Question 16-8. Assume instead that the company wishes to minimize its taxable loss/maximize taxable income. How much is the taxable income (loss)? Explain.
-
3 Return to question 14.28 points Stuart Company sells lamps and other lighting fixtures. The purchasing department manager prepared the following inventory purchases budget. Stuart's policy is to...
-
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...
-
Patti Smith looked up at the bright blue Carolina sky before she entered the offices of Horizon Consulting. It was Friday, which meant she needed to prepare for the weekly status report meeting....
-
Calculate the direct cost of labor for a project team member using the following data: Hourly rate: $50/hr Hours needed: 120 Overhead rate: 40%
-
Use the cultural dimensions listed in Figure 3.5 to assess the culture of your school. Instead of employees, consider students, and instead of management, use faculty. For example, member identity...
-
Find the Fourier series of the following: a. \(f(x)=x, x \in[0,2 \pi]\). b. \(f(x)=\frac{x^{2}}{4},|x|
-
Find the Fourier series of each function \(f(x)\) of period \(2 \pi\). For each series, plot the Nth partial sum, \[S_{N}=\frac{a_{0}}{2}+\sum_{n=1}^{N}\left[a_{n} \cos n x+b_{n} \sin n x ight]\] for...
-
The result in Problem 12b, above gives a Fourier series representation of \(\frac{x^{2}}{4}\). By picking the right value for \(x\) and a little arrangement of the series, show that a....
Study smarter with the SolutionInn App