Create_db_Airport

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('id_of_type', N'FN') IS NOT NULL
DROP FUNCTION id_of_type
IF OBJECT_ID('id_of_configuration_name', N'FN') IS NOT NULL
DROP FUNCTION id_of_configuration_name
IF OBJECT_ID('id_of_airline', N'FN') IS NOT NULL
DROP FUNCTION id_of_airline

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;
IF OBJECT_ID('Configuration_names', 'U') IS NOT NULL
DROP TABLE Configuration_names;
IF OBJECT_ID('Status_characteristics', 'U') IS NOT NULL
DROP TABLE Status_characteristics;

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

GO
CREATE FUNCTION dbo.id_of_airline(@airline_name VARCHAR(40))
RETURNS INTEGER
AS
BEGIN
RETURN
(SELECT airline_id FROM Airline
WHERE   name LIKE @airline_name)
END;
GO

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

GO
CREATE FUNCTION dbo.id_of_type(@type_name VARCHAR(40))
RETURNS INTEGER
AS
BEGIN
RETURN
(SELECT [type_id] FROM Types_of_classes
WHERE   name LIKE @type_name)
END;
GO

CREATE TABLE Configuration_names (
    configuration_id      INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    name_of_configuration NVARCHAR(40) NOT NULL
);

GO
CREATE FUNCTION dbo.id_of_configuration_name(@configuration_name NVARCHAR(40))
RETURNS INTEGER
AS
BEGIN
RETURN
(SELECT configuration_id FROM Configuration_names
WHERE   name_of_configuration LIKE @configuration_name)
END;
GO

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

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

CREATE TABLE Aircraft (
    aircarft_model     NVARCHAR(40) 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      NVARCHAR(20) NOT NULL PRIMARY KEY,
    aircarft_model   NVARCHAR(40) NOT NULL
        FOREIGN KEY REFERENCES Aircraft(aircarft_model),
    configuration_id INT         NOT NULL
        FOREIGN KEY REFERENCES Configuration_names(configuration_id)
        ON UPDATE CASCADE,
);

CREATE TABLE Status_characteristics (
    status_id               INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    сompleteness_of_filling NVARCHAR(20) NOT NULL
);

CREATE TABLE [Status] (
    status_id  INT        NOT NULL
        FOREIGN KEY (status_id) REFERENCES Status_characteristics(status_id),
    [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,
    CONSTRAINT type_in_status PRIMARY KEY (status_id, [type_id])
);

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

CREATE TABLE Flight (
    flight_id               INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    flight_number           INT          NOT NULL,
    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             NVARCHAR(20) NOT NULL
        FOREIGN KEY REFERENCES Airplane(airplane_id)
        ON UPDATE CASCADE,
    distance                SMALLINT     NOT NULL,
    status_id               INT          NOT NULL
        FOREIGN KEY REFERENCES Status_characteristics(status_id)
        --ON UPDATE CASCADE
        ON DELETE NO ACTION
);

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