Problem 5: The Acme limo service company needs a simple database to track their
ID: 3823243 • Letter: P
Question
Problem 5:
The Acme limo service company needs a simple database to track their cars and drivers. They also want to track driver assignments. Write the SQL statements to create tables to store driver, car and assignment information. Assume each driver is assigned only one car. Each car can be assigned to multiple drivers. The tables must capture the following information for each car and driver:
Driver: Name, SSN, DOB
Car: VIN, Make, Model, Color
Be sure to include the appropriate primary and foreign keys
Problem 6:
Write the SQL statement to insert data for the following cars, drivers and their assignments:
John Doe
123121234
11-11-2000
Jill Doe
321434532
07-03-1999
Jane Smith
432121234
03-08-1983
Mike Smith
321125634
04-06-1995
VIN1234
Porsche
Cayene
Black
VIN2134
Porsche
Panamera
Red
VIN2134
Hummer
H2
Black
VIN5432
Mercedes Benz
S500
Red
VIN6543
Mercedes Benz
E350
Blue
VIN7654
Audi
A8
White
Driver assignments
John is assigned the Hummer
Jill is assigned the Audi
Mike is assigned the Panamera
Problem 7:
Write the SQL statements (update statements) to change the driver assignments as follows
John is assigned to drive the Audi
Jill is assigned to drive the Hummer
John Doe
123121234
11-11-2000
Jill Doe
321434532
07-03-1999
Jane Smith
432121234
03-08-1983
Mike Smith
321125634
04-06-1995
Explanation / Answer
create table Driver(
Driver_ID number(3) primary key,
Name VARCHAR2(20),
ssn number(9),
DOB date);
insert into Driver values(100,'John Doe',123121234,TO_DATE('1-11-2000','dd-Mm-yy'));
insert into Driver values(200,'Jill Deo',321434532,TO_DATE('7-3-1999','dd-Mm-yy'));
insert into Driver values(300,'Jane Smith',432121234,TO_DATE('3-8-1983','dd-mm-yy'));
insert into Driver values(400,'Mike Smith',321125634,TO_DATE('4-6-1995','dd-Mm-yy'));
select * from driver;
create table CAR(
car_id NUMBER(3),
VIN VARCHAR2(8),
Make varchar(20),
Model varchar2(20),
color varchar(10),
foreign key(car_id) REFERENCES Driver(driver_id)
);
insert into car values(400,'VIN1234','Porsche', 'Cayene','Black');
insert into car values(400,'VIN2134','Porsche', 'Panamera','Red');
insert into car values(100,'VIN2134','Hummer', 'H2','Black');
insert into car values(300,'VIN5432','Mercedes Benz','S500','Red');
insert into car values(300,'VIN6543','Mercedes Benz', 'E350','Blue');
insert into car values(200,'VIN7654','Audi', 'A8','white');
select * from car;
--John is assigned the Hummer
select d.name,c.make from driver d,car c where d.DRIVER_ID = c.CAR_ID and c.MAKE='Hummer';
--Jill is assigned the Audi
select d.name,c.make from driver d,car c where d.DRIVER_ID = c.CAR_ID and c.MAKE='Audi';
--Mike is assigned the Panamera
select d.name,c.make from driver d,car c where d.DRIVER_ID = c.CAR_ID and c.model='Panamera';
-- (update statements) to change the driver assignments
--John is assigned to drive the Audi
UPDATE car set make='Audi',model='A8' WHERE car_ID = 200 and vin='VIN2134';
--Jill is assigned to drive the Hummer
UPDATE car set make='Hummer', model='H2' where car_ID = 200 and VIN='VIN7654';