the folllowing simplified relational schema for an airiine ticket reservation system. Airline Reservation Schema customer (customer_id, first_name, last_name, customer_email, customer_address, city, customer_phone) airline (airline_code, airline_name, airline_country) city( city_id, city_name, post_code, country, province) flight ( flight_no, airline_code, origin_city, destination_city) flight_schedule (schedule_id, flight_no, departure_date, departure_time, arrival_date, arrival_time) booking ( booking _number, customer_id, schedule_id, booking_class, booking_status) • The customer table contains information about customers and customer_id is the primary key. The field city is a foreign key and refers to the city relation. • The airline table contains information about airlines and airline_code is the primary key. • The city table contains information about cities and city_id is the primary key. Also, the post_code is unique for each city. • The flight table contains information about flights and flight_no is the primary key. The fields airline_code, origin_city, and destination_city are foreign keys and refer to airline, city, and city relations respectively. • The flight_schedule table contains information about scheduled trips for the flight and schedule_id is the primary key. The field flight_no is a foreign key and refers to the flight relation. • The booking table contains information about bookings and booking_number is the primary key. A booking will be for a customer for a given flight schedule. The fields customer_id and schedule_id are foreign keys and refer to the customer and flight_schedule relations respectively. Write SQL statements to a. Create the customer and city tables. Your statements should include the primary key and foreign key constraints as well. If required, assume other tables are already created with proper primary key and foreign key constraints. b. Insert 5 records each for the above two tables. If required, assume other tables are already created and contain any data item that you require for referential integrity constraints.

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
icon
Related questions
Question

SQL Question

Consider the following simplified relational schema for an airline ticket reservation system.
Airline Reservation Schema
customer (customer_id, first_name, last_name, customer_email, customer_address, city, customer_phone)
airline (airline_code, airline_name, airline_country)
city( city_id, city_name, post_code, country, province)
flight ( flight_no, airline_code, origin_city, destination_city)
flight_schedule (schedule_id, flight_no, departure_date, departure_time, arrival_date, arrival_time)
booking ( booking _number, customer_id, schedule_id, booking_class, booking_status)
• The customer table contains information about customers and customer_id is the primary key. The field city is a foreign key and refers
to the city relation.
• The airline table contains information about airlines and airline_code is the primary key.
• The city table contains information about cities and city_id is the primary key. Also, the post_code is unique for each city.
• The flight table contains information about flights and flight_no is the primary key. The fields airline_code, origin_city, and
destination_city are foreign keys and refer to airline, city, and city relations respectively.
• The flight_schedule table contains information about scheduled trips for the flight and schedule_id is the primary key. The field
flight_no is a foreign key and refers to the flight relation.
• The booking table contains information about bookings and booking_number is the primary key. A booking will be for a customer for a
given flight schedule. The fields customer_id and schedule_id are foreign keys and refer to the customer and flight_schedule relations
respectively.
Write SQL statements to
a. Create the customer and city tables. Your statements should include the primary key and foreign key constraints as well. If required,
assume other tables are already created with proper primary key and foreign key constraints.
b. Insert 5 records each for the above two tables. If required, assume other tables are already created and contain any data item that you
require for referential integrity constraints.
Transcribed Image Text:Consider the following simplified relational schema for an airline ticket reservation system. Airline Reservation Schema customer (customer_id, first_name, last_name, customer_email, customer_address, city, customer_phone) airline (airline_code, airline_name, airline_country) city( city_id, city_name, post_code, country, province) flight ( flight_no, airline_code, origin_city, destination_city) flight_schedule (schedule_id, flight_no, departure_date, departure_time, arrival_date, arrival_time) booking ( booking _number, customer_id, schedule_id, booking_class, booking_status) • The customer table contains information about customers and customer_id is the primary key. The field city is a foreign key and refers to the city relation. • The airline table contains information about airlines and airline_code is the primary key. • The city table contains information about cities and city_id is the primary key. Also, the post_code is unique for each city. • The flight table contains information about flights and flight_no is the primary key. The fields airline_code, origin_city, and destination_city are foreign keys and refer to airline, city, and city relations respectively. • The flight_schedule table contains information about scheduled trips for the flight and schedule_id is the primary key. The field flight_no is a foreign key and refers to the flight relation. • The booking table contains information about bookings and booking_number is the primary key. A booking will be for a customer for a given flight schedule. The fields customer_id and schedule_id are foreign keys and refer to the customer and flight_schedule relations respectively. Write SQL statements to a. Create the customer and city tables. Your statements should include the primary key and foreign key constraints as well. If required, assume other tables are already created with proper primary key and foreign key constraints. b. Insert 5 records each for the above two tables. If required, assume other tables are already created and contain any data item that you require for referential integrity constraints.
Expert Solution
steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Software Development Approaches
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
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)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education