Write the SQL DDL to create the following 5 tables describing airports, airplane
ID: 3755946 • Letter: W
Question
Write the SQL DDL to create the following 5 tables describing airports, airplanes, passengers, and flights:
1. An Airplane table where each airplane is identified by a field called id that is a string of up to 10 characters. Other attributes include model (string up to 20 characters), and a manufacture date.
2. An Airport table where each airport has an id of exactly 5 characters, a name (up to 30 characters), and is located in a city (up to 40 characters), a province (up to 20 characters), and a country (up to 20 characters).
3. A Flight table where each flight is identified by both a number (exactly 5 characters) and departure date/time (DATETIME). Note that the flight number does not by itself identify a flight as airlines reuse flight numbers. A flight departs from one airport and arrives at another. Besides the departure date/time, there is an expected arrival date/time, and actual departure and arrival date/times. Each flight record also stores the airplane id of the plane. Make all foreign keys set to null on delete and cascade on update.
4. A Passenger table where each passenger is identified by an integer id. Also store a first name and last name (both up to 30 characters) and a birthdate. Other attributes include street (50 chars), city (40 chars), province (20 chars), and country (20 chars).
5. An OnFlight table that stores information about passengers on flights. Each record stores the passenger id, flight number, flight departure date/time, and a seat number (exactly 4 characters). Make all foreign keys set to perform no action (generate error) on delete and cascade on update.
Thank you!
Explanation / Answer
1)
CREATE TABLE Airplane
{
Airplane_ID STRING(10) ,
Model STRING(20),
Manufacture_date DATE,
PRIMARY KEY(Airplane_ID),
};
2) CREATE TABLE Airport
{
Airport_ID VARCHAR(5) PRIMARY KEY,
Name VARCHAR(30),
City VARCHAR(40),
Province VARCHAR(20),
Country VARCHAR(20),
PRIMARY KEY(Airport_ID),
};
3)CREATE TABLE Flight
{
Airplane_ID STRING(10) REFERENCES Airplane(Airplane_ID) ON DELETE SET NULL [ON UPDATE CASCADE],
Flight_Number INT(5),
Departing_From VARCHAR(20),
Departing_To VARCHAR(20),
Expected_Departure DATETIME,
Expected_Arrival DATETIME,
Actual_Departure DATETIME,
Actual__Arrival DATETIME,
PRIMARY KEY(Flight_Number,Actual_Departure),
};
4) CREATE TABLE Passenger
{Passenger_ID INT(20),
First_Name VARCHAR(30),
Last_Name VARCHAR(30),
Birth_Date DATE,
Street VARCHAR(50),
City VARCHAR(40),
Province VARCHAR(20),
Country VARCHAR(20),
PRIMARY KEY(Passenger_ID),
};
5) CREATE TABLE OnFlight
{
Passenger_ID INT(20) REFERENCES Passenger(Passenger_ID) ON DELETE NO ACTION [ON UPDATE CASCADE ,
Flight_Number INT(5) REFERENCES Flight(Flight_Number) ON DELETE NO ACTION [ON UPDATE CASCADE ,
Seat_Number VARCHAR(4),
};
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.