Hello need to identify referential constrains and correspond

Hello, need to identify referential constrains and corresponding foreing keys. Also, design queries for:

Usage Scenario 1:

A customer intends to book a flight.

The customer’s query is based on the following information:

Destination airport code (ex: ATL, LAX, etc.)

Departure Date

Arrival Date

Usage Scenario 2:

A customer intends to reserve a hotel room.

The customer’s query is based on his/her date of arrival and departure.

Usage Scenario 3:

A customer intends to reserve a rental car.

The customer’s query is based on his/her date of arrival and departure.

Usage Scenario 4:

A vendor needs to upload newly created items (flights, rooms, cars, etc.).

The system should allow vendor to update the appropriate data. Appropriate data includes:

Flight

o Destination city

o Departure Date

o Return Date

Car

o Check-out Date

o Return Date

Hotel

o Check-in Date

o Checkout Date

Following the addition of the flight/hotel/car to the database, the vendor should be given the database ID of the item (s)he just added.

(This is simply the value of the primary key for the flight/car/hotel table for the newly added row.)

Table: Cars Column Name Not Null Column Type CarlD Yes nteger DATETIME Rental StartDate Yes Rental EndDate DATETIME Yes Table: CarReservations Column Type Not Null Column Name Car ReservationID NTEGER Yes INTEGER CarlD Yes CustomerID INTEGER Yes Table: Customers Not Null Column Name Column Type Customer ID Yes INTEGER VARCHAR (45) First Name Yes VARCHAR(45) Last Name Yes VARCHAR (100) Ema Yes Mailing Address VARCHAR (100) Yes Billing Address VARCHAR(100) Yes VARCHAR (15) Phone Yes Table: Flights Not Null Column Name Column Type FlightlD INTEGER Yes VARCHAR(3) Destination Yes DepartureDate DATETIME Yes DATE TIME Return Date Yes Table FlightReservations Column Name Column Type Not Null FlightReservationID NTEGER Yes CustomerID INTEGER Yes FlightlD INTEGER Yes Auto Increment Yes No No Auto Increment Yes No No Auto Increment Yes Auto Increment Yes Auto Increment Yes Primary Key Yes No No Primary Key Yes No No Primary Key Yes Primary Key Yes Primary Key Yes

Solution

In this in carreservations table carid is foreign key referenced to table cars and customerid is foreign key referenced to table customers

similarly in Flightreservations table Flightid is foreign key referenced to table Flights and customerid is foreign key referenced to table customers

similarly in Hotelreservations table Hotelid is foreign key referenced to table Hotels and customerid is foreign key referenced to table customers

--Usage Scenario 1:
--to see all the flights information
select * from flights where destination=\'atl\' and DepartureDate=\'22/03/2017\' and ReturnDate=\'22/03/2017\'

--to book a flight
insert into FlightReservations (CustomerID,FlightID) values(1,1)

--Usage Scenario 2:
--to see all the Hotels information
select * from hotels where DepartureDate=\'22/03/2017\' and ArrivalDate=\'22/03/2017\'

--to book a hotel
insert into HotelReservations (CustomerID,HotelID) values(1,1)

--Usage Scenario 3:
--to see all the cars information
select * from Cars where RentalStartDate=\'22/03/2017\' and RentalEndDate=\'22/03/2017\'

--to book a Car
insert into CarReservations (CustomerID,CarID) values(1,1)

--Usage Scenario 4:
--to upload newly created items

insert into Flights (destination, DepartureDate, ReturnDate) values(\'atx\',\'22/03/2017\',\'22/03/2017\')

insert into Cars (RentalStartDate, RentalEndDate) values(\'22/03/2017\',\'22/03/2017\')

insert into Hotels (DepartureDate, ArrivalDate) values(\'22/03/2017\',\'22/03/2017\')

if you feel struck somewhere please comment