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:

USERS Table

USERS Table

In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information:

DRIVERS Table

DRIVERS Table

In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year:

CARS Table

CARS Table

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:

TRAVELS Table

TRAVELS Table

You are assigned as the database administrator to collect and manage transactional data of the InstantRide 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.

Task $1$:

The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENANCE$\_$TYPE$\_$ID char$(5))$ and a MAINTENANCE$\_$TYPE$\_$DESCRIPTION varchar$(30))$ in the database. Using MAINTENANCE$\_$TYPE$\_$ID as the PRIMARY KEY, create a new table, MAINTENANCE$\_$TYPES and send the table description with the column names and types to the Car Maintenance team.

Task $1$: Create a new table to store car maintenance types.

Task $2$:

The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR$\_$ID CHAR$(5),$ MAINTENANCE$\_$TYPE$\_$ID CHAR$(5)$ and MAINTENANCE$\_$DUE DATE date for the operation. Create a new table named MAINTENANCES. The PRIMARY$\_$KEY should be the combination of the three fields. The CAR$\_$ID and MAINTENANCE$\_$TYPE$\_$ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.

Task $2$: Create a new table to store maintenance operations.

Task $3$:

The Driver Relationship team wants to create some workshops and increase communication with the active drivers in InstantRide. Therefore, they requested a new database table to store the driver details of the drivers that have had at least one ride in the system. Create a new table, ACTIVE$\_$DRIVERS##from the DRIVERS and TRAVELS tables which contains the following fields:

DRIVER$\_$ID CHAR$(5)($Primary key$)$

DRIVER$\_$FIRST$\_$NAME VARCHAR$(20)$

DRIVER$\_$LAST$\_$NAME VARCHAR$(20)$

DRIVER$\_$DRIVING$\_$LICENSE$\_$ID VARCHAR$(10)$

DRIVER$\_$DRIVING$\_$LICENSE$\_$CHECKED BOOL

DRIVER$\_$RATING DECIMAL$(2,1)$

Task $3$: Create a new table to store the information of active drivers.

Task $4$:

The Driver Relationship team wants to have quick search options for the active drivers. The team specifically mentioned that they are using first name, last name and driving license ID to search the drivers. Create an index called NameSearch on the ACTIVE$\_$DRIVERS table created in task $3.$

Task $4$: Create an INDEX to search for driver information.

Task $5$:

The Driver Relationship team requested to ensure that there will be no duplicates in the active drivers tables in terms of first name, last name and driving license ID$.$ You need to create a constraint in the ACTIVE$\_$DRIVERS table, called DuplicateCheck to ensure the first name, last name, and the driving license ID are unique.

Task $5$: Create a constraint on the ACTIVE$\_$DRIVERS table

Task $6$:

The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE$\_$TYPES table to include a new column named MAINTENANCE$\_$PRICE of type DECIMAL$(5,2).$

Task $6$: Alter the MAINTENANCE$\_$TYPES table to include pricing information.