Write SQLs to create three tables based on given relational schemas and directio

Question

Write SQLs to create three tables based on given relational schemas and directions below.

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 of {1, 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)

AIRPORT FLIGHTLEG FLIGHT PK Airportcode CHAR(3) Legno FlightNo NUMBER EEEEEE NUMBER NUMBER PK FK FlightNo City Airline VARCHAR2(30) CHAR(2) VARCHAR2(30) VARCHAR2(30) NUMBER State AirportName Weekdays DepTime ArrTime DepAirportCode ArrAirportCode DATE DATE CHAR(3) JCHAR(3)

Explanation / Answer

--=====================================
-- Creation of table SCHEMA_NAME.AIRPORT
--=====================================

CREATE TABEL SCHEMA_NAME.AIRPORT(AIRPORTCODE CHAR(3) NOT NULL UNIQUE,CITY VARCHAR2(30),STATE CHAR(2),AIRPORT_NAME VARCHAR2(30));
ALTER TABEL SCHEMA_NAME.AIRPORT ADD CONSTRAINT PK_AIRPORT_CODE PRIMARY KEY(AIRPORTCODE);

--=====================================
-- Creation of table SCHEMA_NAME.FLIGHT
--=====================================

CREATE TABEL SCHEMA_NAME.FLIGHT(FLIGHT_NO INTEGER NOT NULL,AIRLINE VARCHAR2(30),WEEKDAYS INTEGER CHECK(WEEKDAYS>=1 AND WEEKDAYS<=5));
ALTER TABEL SCHEMA_NAME.FLIGHT ADD CONSTRAINT PK_FLIGHT_NO PRIMARY KEY(FLIGHT_NO);

--=====================================
-- Creation of table SCHEMA_NAME.FLIGHTLEG
--=====================================

CREATE TABEL SCHEMA_NAME.FLIGHTLEG(LEGNO INTEGER NOT NULL ,FLIGHT_NO INTEGER NOT NULL,DEPTIME DATE,ARRTIME DATE,DEPAIRPORTCODE CHAR(3) NULL, ARRAIRPORTCODE CHAR(3));
ALTER TABEL SCHEMA_NAME.FLIGHTLEG ADD CONSTRAINT PK_AIRPORT_CODE PRIMARY KEY(LEGNO,FLIGHT_NO);
ALTER TABEL SCHEMA_NAME.FLIGHTLEG ADD CONSTRAINT FK1_DEPAIRPORTCODE FOREIGN KEY(DEPAIRPORTCODE) REFERENCES SCHEMA_NAME.AIRPORT(AIRPORTCODE) ON DELETE SET NULL;
ALTER TABEL SCHEMA_NAME.FLIGHTLEG ADD CONSTRAINT FK2_ARRAIRPORTCODE FOREIGN KEY(ARRAIRPORTCODE) REFERENCES SCHEMA_NAME.AIRPORT(AIRPORTCODE) ON DELETE SET NULL;
ALTER TABEL SCHEMA_NAME.FLIGHTLEG ADD CONSTRAINT FK3_FLIGHT_NO FOREIGN KEY(FLIGHT_NO) REFERENCES SCHEMA_NAME.FLIGHT(FLIGHT_NO) ON DELETE CASCADE;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.