SHARE
TWEET

CREATE

a guest Mar 19th, 2017 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET DATEFORMAT dmy
  2.  
  3. IF OBJECT_ID('id_of_city', N'FN') IS NOT NULL
  4. DROP FUNCTION id_of_city;
  5. IF OBJECT_ID('id_of_country', N'FN') IS NOT NULL
  6. DROP FUNCTION id_of_country;
  7. IF OBJECT_ID('id_of_airport', N'FN') IS NOT NULL
  8. DROP FUNCTION id_of_airport
  9.  
  10. IF OBJECT_ID('Flight', 'U') IS NOT NULL
  11. DROP TABLE Flight;
  12. IF OBJECT_ID('[Status]', 'U') IS NOT NULL
  13. DROP TABLE [Status];
  14. IF OBJECT_ID('Airline', 'U') IS NOT NULL
  15. DROP TABLE Airline;
  16. IF OBJECT_ID('Airport', 'U') IS NOT NULL
  17. DROP TABLE Airport;
  18. IF OBJECT_ID('Airplane', 'U') IS NOT NULL
  19. DROP TABLE Airplane;
  20. IF OBJECT_ID('City', 'U') IS NOT NULL
  21. DROP TABLE City;
  22. IF OBJECT_ID('Country', 'U') IS NOT NULL
  23. DROP TABLE Country;
  24. IF OBJECT_ID('Aircraft', 'U') IS NOT NULL
  25. DROP TABLE Aircraft;
  26. IF OBJECT_ID('Configuration', 'U') IS NOT NULL
  27. DROP TABLE Configuration;
  28. IF OBJECT_ID('Types_of_classes', 'U') IS NOT NULL
  29. DROP TABLE Types_of_classes;
  30.  
  31. CREATE TABLE Country (
  32.     country_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  33.     name       VARCHAR(40) NOT NULL
  34. );
  35.  
  36. GO
  37. CREATE FUNCTION dbo.id_of_country(@country_name VARCHAR(40))
  38. RETURNS INTEGER
  39. AS
  40. BEGIN
  41. RETURN
  42. (SELECT country_id FROM Country
  43. WHERE   name LIKE @country_name)
  44. END;
  45. GO
  46.  
  47. CREATE TABLE City (
  48.     city_id                 INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  49.     name                    VARCHAR(40) NOT NULL,
  50.     difference_with_the_UTC SMALLINT    NOT NULL,
  51.     country_id              INT         NOT NULL
  52.         FOREIGN KEY REFERENCES Country(country_id)
  53.         ON UPDATE CASCADE
  54. );
  55.  
  56. ALTER TABLE City
  57.     ADD CHECK ( (difference_with_the_UTC BETWEEN -12 AND 12) );
  58.  
  59. GO
  60. CREATE FUNCTION dbo.id_of_city(@city_name VARCHAR(40))
  61. RETURNS INTEGER
  62. AS
  63. BEGIN
  64. RETURN
  65. (SELECT city_id FROM City
  66. WHERE   name LIKE @city_name)
  67. END;
  68. GO
  69.  
  70. CREATE TABLE Airport (
  71.     airport_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  72.     name       VARCHAR(50) NOT NULL,
  73.     ICAO_code  CHAR(4)     NOT NULL,
  74.     city_id    INT         NOT NULL
  75.         FOREIGN KEY REFERENCES City(city_id)
  76.         ON UPDATE CASCADE
  77. );
  78.  
  79. GO
  80. CREATE FUNCTION dbo.id_of_airport(@airport_name VARCHAR(50))
  81. RETURNS INTEGER
  82. AS
  83. BEGIN
  84. RETURN
  85. (SELECT airport_id FROM Airport
  86. WHERE   name LIKE @airport_name)
  87. END;
  88. GO
  89.  
  90. CREATE TABLE Airline (
  91.     airline_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  92.     name       VARCHAR(40) NOT NULL,
  93.     base_airport_id INT         NOT NULL
  94.         FOREIGN KEY REFERENCES Airport(airport_id)
  95.         ON UPDATE CASCADE
  96. );
  97.  
  98. CREATE TABLE Types_of_classes (
  99.     [type_id] INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  100.     name      VARCHAR(40) NOT NULL
  101. );
  102.  
  103. CREATE TABLE Configuration (
  104.     configuration_id INT      NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  105.     [type_id]        INT      NOT NULL
  106.         FOREIGN KEY REFERENCES Types_of_classes([type_id])
  107.         ON UPDATE CASCADE,
  108.     number_of_seats  SMALLINT NOT NULL
  109. );
  110.  
  111. ALTER TABLE Configuration
  112.     ADD CHECK ( (number_of_seats BETWEEN 0 AND 1000) );
  113.  
  114. CREATE TABLE Aircraft (
  115.     aircarft_model     VARCHAR(20) NOT NULL PRIMARY KEY,
  116.     cruising_speed     SMALLINT    NOT NULL,
  117.     range_of_flight    SMALLINT    NOT NULL,
  118.     max_takeoff_weight INT         NOT NULL
  119. );
  120.  
  121. ALTER TABLE Aircraft
  122.     ADD CHECK ( (range_of_flight BETWEEN 100 AND 22000) AND
  123.                 (cruising_speed BETWEEN 0 AND 3000) );
  124.  
  125. CREATE TABLE Airplane (
  126.     airplane_id      INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,    
  127.     aircarft_model   VARCHAR(20) NOT NULL
  128.         FOREIGN KEY REFERENCES Aircraft(aircarft_model),
  129.     configuration_id INT         NOT NULL
  130.         FOREIGN KEY REFERENCES Configuration(configuration_id)
  131.         ON UPDATE CASCADE,
  132. );
  133.  
  134. CREATE TABLE [Status] (
  135.     status_id  INT        NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  136.     [type_id]  INT        NOT NULL
  137.         FOREIGN KEY REFERENCES Types_of_classes([type_id])
  138.         ON UPDATE CASCADE,
  139.     left_seats SMALLINT   NOT NULL,
  140.     price      SMALLMONEY NOT NULL
  141. );
  142.  
  143. ALTER TABLE [Status]
  144.     ADD CHECK ( (left_seats BETWEEN 0 AND 1000) );
  145.  
  146. CREATE TABLE Flight (
  147.     flight_id               INT      NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  148.     departure_airport       INT      NOT NULL
  149.         FOREIGN KEY REFERENCES Airport(airport_id)
  150.         ON UPDATE CASCADE,
  151.     departure_date_and_time DATETIME NOT NULL,
  152.     arrival_airport         INT      NOT NULL
  153.         FOREIGN KEY REFERENCES Airport(airport_id)
  154.         --ON UPDATE CASCADE,
  155.         ON DELETE NO ACTION,
  156.     arrival_date_and_time   DATETIME NOT NULL,
  157.     airline_id              INT      NOT NULL
  158.         FOREIGN KEY REFERENCES Airline(airline_id)
  159.         --ON UPDATE CASCADE,
  160.         ON DELETE NO ACTION,
  161.     airplane_id             INT      NOT NULL
  162.         FOREIGN KEY REFERENCES Airplane(airplane_id)
  163.         ON UPDATE CASCADE,
  164.     distance                SMALLINT NOT NULL,
  165.     status_id               INT      NOT NULL
  166.         FOREIGN KEY REFERENCES [Status](status_id)
  167.         --ON UPDATE CASCADE
  168.         ON DELETE NO ACTION
  169. );
  170.  
  171. ALTER TABLE Flight
  172.     ADD CHECK ( (distance BETWEEN 100 AND 100000) );
RAW Paste Data
Top