A relational recap exercise

exercise No. 1

Airlines, airports and flights Create comment in forum

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. Hint: Mysql provides a syntactical means to implement this constraint. It will however not be enforced at runtime. Database vendors like Oracle, IBM/DB2, Sybase, Informix etc. support this type of runtime integrity constraint enforcement.

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_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))
);