SHARE
TWEET

Create_db_Airport

a guest Mar 20th, 2017 65 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. IF OBJECT_ID('id_of_type', N'FN') IS NOT NULL
  10. DROP FUNCTION id_of_type
  11. IF OBJECT_ID('id_of_configuration_name', N'FN') IS NOT NULL
  12. DROP FUNCTION id_of_configuration_name
  13. IF OBJECT_ID('id_of_airline', N'FN') IS NOT NULL
  14. DROP FUNCTION id_of_airline
  15.  
  16. IF OBJECT_ID('Flight', 'U') IS NOT NULL
  17. DROP TABLE Flight;
  18. IF OBJECT_ID('[Status]', 'U') IS NOT NULL
  19. DROP TABLE [Status];
  20. IF OBJECT_ID('Airline', 'U') IS NOT NULL
  21. DROP TABLE Airline;
  22. IF OBJECT_ID('Airport', 'U') IS NOT NULL
  23. DROP TABLE Airport;
  24. IF OBJECT_ID('Airplane', 'U') IS NOT NULL
  25. DROP TABLE Airplane;
  26. IF OBJECT_ID('City', 'U') IS NOT NULL
  27. DROP TABLE City;
  28. IF OBJECT_ID('Country', 'U') IS NOT NULL
  29. DROP TABLE Country;
  30. IF OBJECT_ID('Aircraft', 'U') IS NOT NULL
  31. DROP TABLE Aircraft;
  32. IF OBJECT_ID('Configuration', 'U') IS NOT NULL
  33. DROP TABLE Configuration;
  34. IF OBJECT_ID('Types_of_classes', 'U') IS NOT NULL
  35. DROP TABLE Types_of_classes;
  36. IF OBJECT_ID('Configuration_names', 'U') IS NOT NULL
  37. DROP TABLE Configuration_names;
  38. IF OBJECT_ID('Status_characteristics', 'U') IS NOT NULL
  39. DROP TABLE Status_characteristics;
  40.  
  41. CREATE TABLE Country (
  42.     country_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  43.     name       VARCHAR(40) NOT NULL
  44. );
  45.  
  46. GO
  47. CREATE FUNCTION dbo.id_of_country(@country_name VARCHAR(40))
  48. RETURNS INTEGER
  49. AS
  50. BEGIN
  51. RETURN
  52. (SELECT country_id FROM Country
  53. WHERE   name LIKE @country_name)
  54. END;
  55. GO
  56.  
  57. CREATE TABLE City (
  58.     city_id                 INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  59.     name                    VARCHAR(40) NOT NULL,
  60.     difference_with_the_UTC SMALLINT    NOT NULL,
  61.     country_id              INT         NOT NULL
  62.         FOREIGN KEY REFERENCES Country(country_id)
  63.         ON UPDATE CASCADE
  64. );
  65.  
  66. ALTER TABLE City
  67.     ADD CHECK ( (difference_with_the_UTC BETWEEN -12 AND 12) );
  68.  
  69. GO
  70. CREATE FUNCTION dbo.id_of_city(@city_name VARCHAR(40))
  71. RETURNS INTEGER
  72. AS
  73. BEGIN
  74. RETURN
  75. (SELECT city_id FROM City
  76. WHERE   name LIKE @city_name)
  77. END;
  78. GO
  79.  
  80. CREATE TABLE Airport (
  81.     airport_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  82.     name       VARCHAR(50) NOT NULL,
  83.     ICAO_code  CHAR(4)     NOT NULL,
  84.     city_id    INT         NOT NULL
  85.         FOREIGN KEY REFERENCES City(city_id)
  86.         ON UPDATE CASCADE
  87. );
  88.  
  89. GO
  90. CREATE FUNCTION dbo.id_of_airport(@airport_name VARCHAR(50))
  91. RETURNS INTEGER
  92. AS
  93. BEGIN
  94. RETURN
  95. (SELECT airport_id FROM Airport
  96. WHERE   name LIKE @airport_name)
  97. END;
  98. GO
  99.  
  100. CREATE TABLE Airline (
  101.     airline_id INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  102.     name       VARCHAR(40) NOT NULL,
  103.     base_airport_id INT    NOT NULL
  104.         FOREIGN KEY REFERENCES Airport(airport_id)
  105.         ON UPDATE CASCADE
  106. );
  107.  
  108. GO
  109. CREATE FUNCTION dbo.id_of_airline(@airline_name VARCHAR(40))
  110. RETURNS INTEGER
  111. AS
  112. BEGIN
  113. RETURN
  114. (SELECT airline_id FROM Airline
  115. WHERE   name LIKE @airline_name)
  116. END;
  117. GO
  118.  
  119. CREATE TABLE Types_of_classes (
  120.     [type_id] INT         NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  121.     name      VARCHAR(40) NOT NULL
  122. );
  123.  
  124. GO
  125. CREATE FUNCTION dbo.id_of_type(@type_name VARCHAR(40))
  126. RETURNS INTEGER
  127. AS
  128. BEGIN
  129. RETURN
  130. (SELECT [type_id] FROM Types_of_classes
  131. WHERE   name LIKE @type_name)
  132. END;
  133. GO
  134.  
  135. CREATE TABLE Configuration_names (
  136.     configuration_id      INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
  137.     name_of_configuration NVARCHAR(40) NOT NULL
  138. );
  139.  
  140. GO
  141. CREATE FUNCTION dbo.id_of_configuration_name(@configuration_name NVARCHAR(40))
  142. RETURNS INTEGER
  143. AS
  144. BEGIN
  145. RETURN
  146. (SELECT configuration_id FROM Configuration_names
  147. WHERE   name_of_configuration LIKE @configuration_name)
  148. END;
  149. GO
  150.  
  151. CREATE TABLE Configuration (
  152.     configuration_id INT      NOT NULL
  153.         FOREIGN KEY (configuration_id) REFERENCES Configuration_names(configuration_id)
  154.         ON UPDATE CASCADE,
  155.     [type_id]        INT      NOT NULL
  156.         FOREIGN KEY REFERENCES Types_of_classes([type_id])
  157.         ON UPDATE CASCADE,
  158.     number_of_seats  SMALLINT NOT NULL,
  159.     CONSTRAINT type_in_configuration PRIMARY KEY (configuration_id, [type_id])
  160. );
  161.  
  162. ALTER TABLE Configuration
  163.     ADD CHECK ( (number_of_seats BETWEEN 0 AND 1000) );
  164.  
  165. CREATE TABLE Aircraft (
  166.     aircarft_model     NVARCHAR(40) NOT NULL PRIMARY KEY,
  167.     cruising_speed     SMALLINT     NOT NULL,
  168.     range_of_flight    SMALLINT     NOT NULL,
  169.     max_takeoff_weight INT          NOT NULL
  170. );
  171.  
  172. ALTER TABLE Aircraft
  173.     ADD CHECK ( (range_of_flight BETWEEN 100 AND 22000) AND
  174.                 (cruising_speed BETWEEN 0 AND 3000) );
  175.  
  176. CREATE TABLE Airplane (
  177.     airplane_id      NVARCHAR(20) NOT NULL PRIMARY KEY,    
  178.     aircarft_model   NVARCHAR(40) NOT NULL
  179.         FOREIGN KEY REFERENCES Aircraft(aircarft_model),
  180.     configuration_id INT         NOT NULL
  181.         FOREIGN KEY REFERENCES Configuration_names(configuration_id)
  182.         ON UPDATE CASCADE,
  183. );
  184.  
  185. CREATE TABLE Status_characteristics (
  186.     status_id               INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
  187.     сompleteness_of_filling NVARCHAR(20) NOT NULL
  188. );
  189.  
  190. CREATE TABLE [Status] (
  191.     status_id  INT        NOT NULL
  192.         FOREIGN KEY (status_id) REFERENCES Status_characteristics(status_id),
  193.     [type_id]  INT        NOT NULL
  194.         FOREIGN KEY REFERENCES Types_of_classes([type_id])
  195.         ON UPDATE CASCADE,
  196.     left_seats SMALLINT   NOT NULL,
  197.     price      SMALLMONEY NOT NULL,
  198.     CONSTRAINT type_in_status PRIMARY KEY (status_id, [type_id])
  199. );
  200.  
  201. ALTER TABLE [Status]
  202.     ADD CHECK ( (left_seats BETWEEN 0 AND 1000) );
  203.  
  204. CREATE TABLE Flight (
  205.     flight_id               INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
  206.     flight_number           INT          NOT NULL,
  207.     departure_airport       INT          NOT NULL
  208.         FOREIGN KEY REFERENCES Airport(airport_id)
  209.         ON UPDATE CASCADE,
  210.     departure_date_and_time DATETIME     NOT NULL,
  211.     arrival_airport         INT          NOT NULL
  212.         FOREIGN KEY REFERENCES Airport(airport_id)
  213.         --ON UPDATE CASCADE,
  214.         ON DELETE NO ACTION,
  215.     arrival_date_and_time   DATETIME     NOT NULL,
  216.     airline_id              INT          NOT NULL
  217.         FOREIGN KEY REFERENCES Airline(airline_id)
  218.         --ON UPDATE CASCADE,
  219.         ON DELETE NO ACTION,
  220.     airplane_id             NVARCHAR(20) NOT NULL
  221.         FOREIGN KEY REFERENCES Airplane(airplane_id)
  222.         ON UPDATE CASCADE,
  223.     distance                SMALLINT     NOT NULL,
  224.     status_id               INT          NOT NULL
  225.         FOREIGN KEY REFERENCES Status_characteristics(status_id)
  226.         --ON UPDATE CASCADE
  227.         ON DELETE NO ACTION
  228. );
  229.  
  230. ALTER TABLE Flight
  231.     ADD CHECK ( (distance BETWEEN 100 AND 100000) );
RAW Paste Data
Top