TASK
TASK 1
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
Task: Make 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. Make 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: Make a new table to store maintenance operations.
Task 3:
The Driver Relationship team wants to make 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. Make 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 FLOAT
Task: Make a new table to store the information of active drivers.
1
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. Make an index called NameSearch on the ACTIVE_DRIVERS table make in task 3.
Task: Make 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 make a constraint in the ACTIVE_DRIVERS table, called DuplicateCheck, to ensure the first name, last name, and the driving license ID are unique.
Task: Make a constraint on the ACTIVE_DRIVERS table
1
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 FLOAT.
Task: Alter the MAINTENANCE_TYPES table to include pricing information.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
