Getting started

exercise No. 1

Airlines, airports and flights

Q:

Implement a relational schema describing airlines, flights, airports and their respective relationships:

  • Airline:

  • Destination

    • Full name like “Frankfurt am Main International”

    • World airport code like “FRA”.

  • Flight

    • A unique flight number e.g. LH 4234

    • The “owning” airline.

    • originating airport

    • destination airport

    • Constraint: origin and destination must differ.

Provide surrogate keys for all entities and provide names for all constraints (e.g. defining CONSTRAINT _PK_XYZ PRIMARY KEY(...) etc. ).

A:

Extended version providing user defined constraint names using fully qualified foreign key reference column names: 

DROP  TABLE IF EXISTS Flight;
DROP  TABLE IF EXISTS Destination;
DROP  TABLE IF EXISTS Airline;

CREATE Table Airline (
    id INT NOT NULL
    ,name CHAR(20) NOT NULL
    ,airlineCode CHAR(5) NOT NULL

    ,CONSTRAINT _PK_Airline_id PRIMARY KEY(id)
    ,CONSTRAINT _UN_Airline_name UNIQUE(name)
    ,CONSTRAINT _UN_Airline_airlineCode UNIQUE(airlineCode)
);

CREATE TABLE Destination (
    id INT NOT NULL
    ,fullName CHAR(20) NOT NULL
    ,airportCode CHAR(5)

    ,CONSTRAINT _PK_Destination_id PRIMARY KEY(id)
    ,CONSTRAINT _UN_Destination_airportCode UNIQUE(airportCode)
);

CREATE TABLE Flight (
    id INT NOT NULL
    ,flightNumber CHAR(10) NOT NULL
    ,airline INT NOT NULL
    ,origin int NOT NULL
    ,destination int NOT NULL

    ,CONSTRAINT _PK_Flight_id UNIQUE(id)
    ,CONSTRAINT _UN_Flight_flightNumber UNIQUE(flightNumber)

    ,CONSTRAINT _PK_Flight_ref_airline FOREIGN KEY (origin) REFERENCES Airline(id)

    ,CONSTRAINT _PK_Flight_ref_origin FOREIGN KEY (origin) REFERENCES Destination(id)
    ,CONSTRAINT _PK_Flight_ref_destination FOREIGN KEY (destination) REFERENCES Destination(id)

    ,CONSTRAINT _CK_Flight_origin_destination CHECK(NOT(origin = destination))
);

Compact version using auto generated constraint names: 

DROP  TABLE IF EXISTS Flight;
DROP  TABLE IF EXISTS Destination;
DROP  TABLE IF EXISTS Airline;

CREATE Table Airline (
   id INT NOT NULL PRIMARY KEY
  ,name CHAR(20) NOT NULL UNIQUE
  ,airlineCode CHAR(5) NOT NULL UNIQUE
);

CREATE TABLE Destination (
   id INT NOT NULL PRIMARY KEY
  ,fullName CHAR(20) NOT NULL
  ,airportCode CHAR(5) UNIQUE
);

CREATE TABLE Flight (
   id INT NOT NULL UNIQUE
  ,flightNumber CHAR(10) NOT NULL UNIQUE
  ,airline INT NOT NULL REFERENCES Airline
  ,origin int NOT NULL REFERENCES Destination
  ,destination int NOT NULL REFERENCES Destination

  ,CONSTRAINT _CK_Flight_origin_destination CHECK(NOT(origin = destination))
);
Prev  Up  Next
Recommended reading  Home  Creating a Hibernate JPA project