Travel Agencies Pk travel agency id travel agency detas Ref Booking Outcomes PK
ID: 3907047 • Letter: T
Question
Travel Agencies Pk travel agency id travel agency detas Ref Booking Outcomes PK outcome_code Bookine Customers Addresses outcome,descriptionPK booking id FK customer id PK trJPK address ja eg Canceled, OK customer details address_detais FK outcomecode FK status code Ref Booking Status Pk status code FK travel agercy id date of bookng status description eg Provisional Services Service Bookings PF service id PF booking id Payments Pk payment id FK booking id PK service id PK customer addressd service details eg Airines, Cars Hotel Resran booking end date other_detab payment_amount Feddress id payment date other detais date from date to Airlines PF airline id Restaurants PF restaurant id arine detais Cars Hotels PF car id PF hotel id car detais hotel detaisExplanation / Answer
If you have any doubts, please give me comment...
CREATE TABLE Travel_Agences(
travel_agency_id INT NOT NULL PRIMARY KEY,
travel_agency_details TEXT
);
CREATE TABLE Ref_Booking_Outcomes(
outcome_code INT NOT NULL PRIMARY KEY,
outcome_description VARCHAR(50)
);
CREATE TABLE Ref_Booking_Status(
status_code INT NOT NULL PRIMARY KEY,
status_description VARCHAR(50)
);
CREATE TABLE Customers(
customer_id INT NOT NULL PRIMARY KEY,
customer_details VARCHAR(255)
);
CREATE TABLE Bookings(
booking_id INT NOT NULL PRIMARY KEY,
customer_id INT,
outcome_code INT,
status_code INT,
travel_agency_id INT,
date_of_booking DATE,
booking_details VARCHAR(255),
FOREIGN KEY(customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY(outcome_code) REFERENCES Ref_Booking_Outcomes(outcome_code),
FOREIGN KEY(status_code) REFERENCES Ref_Booking_Status(status_code),
FOREIGN KEY(travel_agency_id) REFERENCES Travel_Agences(travel_agency_id)
);
CREATE TABLE Services(
service_id INT NOT NULL PRIMARY KEY,
service_details VARACHAR(50)
);
CREATE TABLE Service_Bookings(
service_id INT,
booking_id INT,
booking_start_date DATE,
booking_end_date DATE,
other_details VARCHAR(100)
PRIMARY KEY(service_id, booking_id),
FOREIGN KEY(service_id) REFERENCES Services(service_id),
FOREIGN KEY(booking_id) REFERENCES Bookings(booking_id)
);
CREATE TABLE Payments(
payments_id INT NOT NULL PRIMARY KEY,
booking_id INT,
payment_amount REAL(10,2),
payment_date DATE,
other_details TEXT,
FOREIGN KEY(booking_id) REFERENCES Bookings(booking_id)
);
CREATE TABLE Addresses(
address_id INT NOT NULL PRIMARY KEY,
address_details VARCHAR(255)
);
CREATE TABLE Customers_Addresses(
customer_address_id INT NOT NULL PRIMARY KEY,
customer_id INT,
address_id INT,
date_from DATE,
date_to DATE,
FOREIGN KEY(customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY(address_id) REFERENCES Addresses(address_id)
);
CREATE TABLE Airlines(
airline_id INT NOT NULL PRIMARY KEY,
airline_details VARCHAR(255)
);
CREATE TABLE Cars(
car_id INT NOT NULL PRIMARY KEY,
car_details VARCHAR(255)
);
CREATE TABLE Hotels(
hotel_id INT NOT NULL PRIMARY KEY,
hotel_details VARCHAR(255)
);
CREATE TABLE Restaurants(
restaurant_id INT NOT NULL PRIMARY KEY,
restaurant_details VARCHAR(255)
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.