1. (35 points) Write SQLs to create three tables based on given relational schem
ID: 3735896 • Letter: 1
Question
1. (35 points) Write SQLs to create three tables based on given relational schemas and directions below AIRPORT FLIGHTLEG FLIGHT PK AirportCodeCHAR(3) PK lichtNo NUMBER NUMBER NUMBER PK,FK3 lshtNo VARCHAR2130) City State AirportName VARCHAR2(50) VARCHAR2130) CHAR(2) Airline DepTime ArrTime DepAirportCode CHAR(3) DATE DATE Weekdays NUMBER FK1 FK2 ArrAirportCode CHAR(3) In addition, include the specifications below when you create the tables 1) In FLIGHTLEG table, include following referential integrities (1-1) When an AIRPORT record is deleted, the DepAirportCode and ArrAiportCode values of FLIGHTLEG records which refer the airport code of the deleted record will be changed to NULL (1-2) When a FLIGHT record is deleted, all FLIGHTLEG records which refer the flight number of the deleted record will be also deleted (2) In the Weekdays column of FLIGHT table, include a CHECK constraint for following. (2-1) The valid value of WEEKDAYS is one of1,2, 3, 4, 5) (3) In the AirportName column of AIRPORT table, include two constraints for following properties (3-1) required (NOT NULL) (3-2) candidate key (UNIQUE)
Explanation / Answer
Answer)
/*AIRPORT TABLE CREATION*/
CREATE TABLE AIRPORT(AirportCode CHAR(3) PRIMARY KEY, City varchar2(30), State CHAR(2), AirportName varchar2(50) NOT NULL UNIQUE);
/*FLIGHTLEG TABLE CREATION */
CREATE TABLE FLIGHTLEG(LegnoNo NUMBER, FlightNo NUMBER, DepTime DATE, ArrTime DATE, DepAirportCode CHAR(3), ArrAirportCode CHAR(3), PRIMARY KEY (LegnoNo,FlightNo), FOREIGN KEY (FlightNo) REFERENCES FLIGHT(FlightNo) ON DELETE CASCADE, FOREIGN KEY (DepAirportCode) REFERENCES AIRPORT(AirportCode) ON DELETE SET NULL, FOREIGN KEY (ArrAirportCode) REFERENCES AIRPORT(AirportCode) ON DELETE SET NULL);
/*FLIGHT TABLE CREATION*/
CREATE TABLE FLIGHT(FlightNo NUMBER PRIMARY KEY, Airline varchar2(30), Weekdays NUMBER, CONSTRAINT CHK_Weekday CHECK (Weekdays BETWEEN 1 and 5));
On the FLIGHTLEG table when an airport record is deleted the ArrAirportCode and DepAirportCode is set to null via commands in table script ON DELETE SET NULL.
On the FLIGHT table when an flight record is deleted the FLIGHTLEG records are also deleted via commands in table script ON DELETE CASCADE.
Constraints for Weekdays of Flight table introduced.
In Airport table AirportName is Not Null and Unique.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.