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 MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.
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 MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
The Car Maintenance team also wants to store the actual maintenance operations in the
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
USER FIRST_NAME
USER LAST NAME
USER EMAIL
3001
Jack
Hill
Jhillexmail.com
3002
Ryan
Collins
r.collins@xmail.com
3003
Nursin
Yilmaz
n.atak@gmail.com
3004
Sarah
Price
s.price@xmail.com
3005
Bobby
Griffin
bgriffinexmal.com
3006
Randy
Clark
r.clark@xmail.com
3007
Jose
Thomas
jthomas@xmail.com
3008
Nursin
Yilmaz
nyilmazexmail.com
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:
DRIVER ID DRIVER FIRST NAME DRIVER LAST NAME DRIVER DRIVING LICENSE ID DRIVER START DATE DRIVER DRIVING LICENSE CHECKED DRIVER RATING
2001
willie
Butler
1874501
2019-09-12
4.4
2002
Justin
Howard
1953853
2019-09-09
4.8
2003
Anthony
Walker
1735487
2019-09-15
3.5
2004
Ece
Yilmaz
1734747
2019-08-15
DRIVERS Table
Transcribed Image Text:In the CARS table, all the cars in the InstantRide system are kept with the license plate, model
and year:
CAR MODEL
CAR ID
CAR PLATE
CAR YEAR
1001
BB-542-AB
ΤOYOTA PRIUS
2018
1002
BB-883-EE
TESLA MODEL 3
2019
1003
BB-451-ZN
ΤΟΥOTA AURIS
2019
1004
BB-189-MM
MERCEDES E200
2019
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:
TRAVELD
TRAVEL START TIME
TRAVEL END TIMe
TRAVEL START LOCATION
TRAVEL END LOCATION
TRAVEL PRICE
DRIVER ID
CARD
USER D
TRAVEL DISCOUNT
2010.10ar aete
A Ver tead
Churen Lane
2001
47 Churen seet
gh eet
20.
2001
scos
NULL
2011a 13::to
Wineear ear
20
201-104 a1:s
20so MI Lane
77 Mn tea
201 16RR
20114 16:R:10
11.15
100
200-0 11214
20131001 11:21et
so Mn nat
nreanwy
200n
5007
20160 1606
2019-1003 16056
30 a Anod
91 West or
4.41
200
201s10-03 171712
2019-10-03 17:3742
37 The Drive
17 Staey oed
25.12
2001
1003
3001
0.25
5009
2016-0216
201910-03 2126:18
77 M oad
724 Sprngie Rad
1355
2001
1003
NULL
5010
201510-03 232140
20191003 23310
16 Church ead
30 North Road
2542
2009
3003
0.20
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.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education