CREATE

SET DATEFORMAT dmy

IF OBJECT_ID('id_of_city', N'FN') IS NOT NULL
DROP FUNCTION id_of_city;
IF OBJECT_ID('id_of_country', N'FN') IS NOT NULL
DROP FUNCTION id_of_country;
IF OBJECT_ID('id_of_airport', N'FN') IS NOT NULL
DROP FUNCTION id_of_airport

IF OBJECT_ID('Flight', 'U') IS NOT NULL
DROP TABLE Flight;
IF OBJECT_ID('[Status]', 'U') IS NOT NULL
DROP TABLE [Status];
IF OBJECT_ID('Airline', 'U') IS NOT NULL
DROP TABLE Airline;
IF OBJECT_ID('Airport', 'U') IS NOT NULL
DROP TABLE Airport;
IF OBJECT_ID('Airplane', 'U') IS NOT NULL
DROP TABLE Airplane;
IF OBJECT_ID('City', 'U') IS NOT NULL
DROP TABLE City;
IF OBJECT_ID('Country', 'U') IS NOT NULL
DROP TABLE Country;
IF OBJECT_ID('Aircraft', 'U') IS NOT NULL
DROP TABLE Aircraft;
IF OBJECT_ID('Configuration', 'U') IS NOT NULL
DROP TABLE Configuration;
IF OBJECT_ID('Types_of_classes', 'U') IS NOT NULL
DROP TABLE Types_of_classes;

CREATE TABLE Country (
    country_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    name       VARCHAR(40) NOT NULL
);

GO
CREATE FUNCTION dbo.id_of_country(@country_name VARCHAR(40))
RETURNS INTEGER
AS
BEGIN
RETURN
(SELECT country_id FROM Country
WHERE   name LIKE @country_name)
END;
GO

CREATE TABLE City (
    city_id                 INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    name                    VARCHAR(40) NOT NULL,
    difference_with_the_UTC SMALLINT    NOT NULL,
    country_id              INT         NOT NULL
        FOREIGN KEY REFERENCES Country(country_id)
        ON UPDATE CASCADE
);

ALTER TABLE City
    ADD CHECK ( (difference_with_the_UTC BETWEEN -12 AND 12) );

GO
CREATE FUNCTION dbo.id_of_city(@city_name VARCHAR(40))
RETURNS INTEGER
AS
BEGIN
RETURN
(SELECT city_id FROM City
WHERE   name LIKE @city_name)
END;
GO

CREATE TABLE Airport (
    airport_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    ICAO_code  CHAR(4)     NOT NULL,
    city_id    INT         NOT NULL
        FOREIGN KEY REFERENCES City(city_id)
        ON UPDATE CASCADE
);

GO
CREATE FUNCTION dbo.id_of_airport(@airport_name VARCHAR(50))
RETURNS INTEGER
AS
BEGIN
RETURN
(SELECT airport_id FROM Airport
WHERE   name LIKE @airport_name)
END;
GO

CREATE TABLE Airline (
    airline_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    name       VARCHAR(40) NOT NULL,
    base_airport_id INT         NOT NULL
        FOREIGN KEY REFERENCES Airport(airport_id)
        ON UPDATE CASCADE
);

CREATE TABLE Types_of_classes (
    [type_id] INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    name      VARCHAR(40) NOT NULL
);

CREATE TABLE Configuration (
    configuration_id INT      NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [type_id]        INT      NOT NULL
        FOREIGN KEY REFERENCES Types_of_classes([type_id])
        ON UPDATE CASCADE,
    number_of_seats  SMALLINT NOT NULL
);

ALTER TABLE Configuration
    ADD CHECK ( (number_of_seats BETWEEN 0 AND 1000) );

CREATE TABLE Aircraft (
    aircarft_model     VARCHAR(20) NOT NULL PRIMARY KEY,
    cruising_speed     SMALLINT    NOT NULL,
    range_of_flight    SMALLINT    NOT NULL,
    max_takeoff_weight INT         NOT NULL
);

ALTER TABLE Aircraft
    ADD CHECK ( (range_of_flight BETWEEN 100 AND 22000) AND
                (cruising_speed BETWEEN 0 AND 3000) );

CREATE TABLE Airplane (
    airplane_id      INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    aircarft_model   VARCHAR(20) NOT NULL
        FOREIGN KEY REFERENCES Aircraft(aircarft_model),
    configuration_id INT         NOT NULL
        FOREIGN KEY REFERENCES Configuration(configuration_id)
        ON UPDATE CASCADE,
);

CREATE TABLE [Status] (
    status_id  INT        NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [type_id]  INT        NOT NULL
        FOREIGN KEY REFERENCES Types_of_classes([type_id])
        ON UPDATE CASCADE,
    left_seats SMALLINT   NOT NULL,
    price      SMALLMONEY NOT NULL
);

ALTER TABLE [Status]
    ADD CHECK ( (left_seats BETWEEN 0 AND 1000) );

CREATE TABLE Flight (
    flight_id               INT      NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    departure_airport       INT      NOT NULL
        FOREIGN KEY REFERENCES Airport(airport_id)
        ON UPDATE CASCADE,
    departure_date_and_time DATETIME NOT NULL,
    arrival_airport         INT      NOT NULL
        FOREIGN KEY REFERENCES Airport(airport_id)
        --ON UPDATE CASCADE,
        ON DELETE NO ACTION,
    arrival_date_and_time   DATETIME NOT NULL,
    airline_id              INT      NOT NULL
        FOREIGN KEY REFERENCES Airline(airline_id)
        --ON UPDATE CASCADE,
        ON DELETE NO ACTION,
    airplane_id             INT      NOT NULL
        FOREIGN KEY REFERENCES Airplane(airplane_id)
        ON UPDATE CASCADE,
    distance                SMALLINT NOT NULL,
    status_id               INT      NOT NULL
        FOREIGN KEY REFERENCES [Status](status_id)
        --ON UPDATE CASCADE
        ON DELETE NO ACTION
);

ALTER TABLE Flight
    ADD CHECK ( (distance BETWEEN 100 AND 100000) );