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...
-
Refrigerant-22 in a piston/cylinder arrangement is initially at 120 F, x = 1. It is then expanded in a process so that P = Cv1 to a pressure of 30 lbf/in.2. Find the final temperature and specific...
-
The fish department of the local grocery store faces unique problems. Fish is extremely perishable and can only be sold on the day it arrives in the store. Fish is delivered at 3 am and can be sold...
-
For pumping molasses, it is preferable to employ (a) reciprocating pump (b) centrifugal pump (c) open impeller pump (d) rotary pump
-
Out Camping is a manufacturer of supplies and has several divisions, one of which is the tent division that produces that deluxe tent Away From Home. Fourteen thousand of these tents are made each...
-
Ice Cool produces two different models of air conditioners. The activities, costs, and cost drivers associated with the production processes follow. Budgeted Activity Process Activity Assembly...
-
Select all that apply Actual output is used for variance calculations because Blank______. Multiple select question. budgeted output although useful for planning is outdated for analyzing performance...
-
A firm has weekly revenue of $1000. a) The firm's total cost is $1450 per week. The firm will shut down if weekly fixed (sunk) cost is what? b) The firm's variable cost is $500 and its fixed cost is...
-
In Details , Using a Dielectric spectroscopy experiment on the Polypropylene glycol polymer with molecular weight 400 to get the following: 1- the relaxation time of the alpha and beta relaxations of...
-
Depreciation is only applied against land. Depreciation is the only expense where money is not paid out. Only facilities are depreciated. Once an asset is fully depreciated, the organization should...
-
You currently have $ 1 0 2 . 0 in your savings account at an annual rate of 9 percent. How many years will it take for this investment to grow to a value of $ 4 5 7 . 9 ? Round the answer to two...
-
Secondary Analysis: This week visit the Keizer Online Library and find a full-text article on secondary analysis in research. Find a full-text article, download it, highlight it, and upload it to the...
-
Thornton Electronics currently produces the shipping containers it uses to deliver the electronics products it sells. The monthly cost of producing 9,300 containers follows. Unit-level materials...
-
10)A conical cup is 4 cm across and 6 cm deep. Water leaks out of the bottom at the rate of 2 cm/sec. How fast is the water
-
Frontland Advertising creates, plans, and handles advertising campaigns in a three-state area. Recently, Frontland had to replace an inexperienced office worker in charge of bookkeeping because of...
-
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...
-
If the displacement of an object is 300 m north, what can you say about the distance traveled by the object?
-
An object traveling at a constant velocity vo experiences a constant acceleration in the same direction for a period of time t. Then an acceleration of equal magnitude is experienced in the opposite...
-
You are told that a person has walked 750 m. What can you safely say about the persons final position relative to the starting point?
Study smarter with the SolutionInn App