From Sole Butterfly, 4 Days ago, written in Text.
Embed
  1. SET SCHEMA FN71738;
  2.  
  3. CREATE TABLE AIRLINES
  4. (
  5. NAME VARCHAR(20) PRIMARY KEY NOT NULL,
  6. COUNTRY VARCHAR(50) NOT NULL
  7. );
  8.  
  9. CREATE TABLE AIRPORTS
  10. (
  11. CODE CHAR(3) PRIMARY KEY NOT NULL,
  12. NAME VARCHAR(50) NOT NULL UNIQUE,
  13. CITY VARCHAR(50) NOT NULL,
  14. GMT INTEGER NOT NULL
  15. );
  16.  
  17. CREATE TABLE AIRPLANES
  18. (
  19. CODE CHAR(4) PRIMARY KEY NOT NULL,
  20. NAME VARCHAR(5) NOT NULL,
  21. YEAR INTEGER CHECK(YEAR > 0) NOT NULL,
  22. FCLASS_SEATS INTEGER CHECK(FCLASS_SEATS > 0) NOT NULL,
  23. SCLASS_SEATS INTEGER CHECK(SCLASS_SEATS > 0) NOT NULL
  24. );
  25.  
  26. CREATE TABLE AGENCIES
  27. (
  28. NAME VARCHAR(100) PRIMARY KEY NOT NULL,
  29. CITY VARCHAR(50) NOT NULL,
  30. COUNTRY VARCHAR(50) NOT NULL,
  31. PHONE VARCHAR(50) NOT NULL
  32. );
  33.  
  34. CREATE TABLE CUSTOMERS
  35. (
  36. CODE CHAR(10) PRIMARY KEY NOT NULL,
  37. NAME VARCHAR(100) NOT NULL,
  38. PHONE VARCHAR(50) NOT NULL,
  39. EMAIL VARCHAR(50) NOT NULL
  40. );
  41.  
  42. CREATE TABLE FLIGHTS
  43. (
  44. CODE CHAR(14) PRIMARY KEY NOT NULL,
  45. AIRLINE_OPERATOR VARCHAR(20) NOT NULL
  46. REFERENCES AIRLINES(NAME),
  47. AIRPLANE_CODE CHAR(3) NOT NULL
  48. REFERENCES AIRPLANES(CODE),
  49. DEPT_AIRPORT CHAR(3) NOT NULL
  50. REFERENCES AIRPORTS(CODE),
  51. ARRV_AIRPORT CHAR(3) NOT NULL
  52. REFERENCES AIRPORTS(CODE),
  53. DEPT_TIME TIME NOT NULL,
  54. ARRV_TIME TIME NOT NULL,
  55. FLIGHT_DATE DATE NOT NULL,
  56. FLIGHT_TIME TIME NOT NULL,
  57. FLIGHT_KM INTEGER CHECK(FLIGHT_KM > 0)
  58. );
  59.  
  60. CREATE TABLE BOOKINGS
  61. (
  62. CODE CHAR(10) PRIMARY KEY NOT NULL,
  63. AGENCY_CODE VARCHAR(100) NOT NULL
  64. REFERENCES AGENCIES(NAME),
  65. AIRLINE_CODE VARCHAR(20) NOT NULL
  66. REFERENCES AIRLINES(NAME),
  67. FLIGHT_CODE CHAR(14) NOT NULL
  68. REFERENCES FLIGHTS(CODE),
  69. CUSTOMER_CODE CHAR(10) NOT NULL
  70. REFERENCES CUSTOMERS(CODE),
  71. DATE DATE,
  72. PRICE DOUBLE,
  73. CURRENCY CHAR(3) DEFAULT 'EUR',
  74. STATUS INTEGER
  75. );
  76.  
  77. CREATE TABLE FLIGHTS_INFO
  78. (
  79. FLIGHT_CODE CHAR(14) NOT NULL
  80. REFERENCES FLIGHTS(CODE),
  81. OCC_FCLASS_CAP INTEGER NOT NULL CHECK(OCC_FCLASS_CAP > 0),
  82. MAX_FCLASS_CAP INTEGER NOT NULL CHECK(MAX_FCLASS_CAP > 0),
  83. OCC_SCLASS_CAP INTEGER CHECK(OCC_SCLASS_CAP > 0),
  84. MAX_SCLASS_CAP INTEGER CHECK(MAX_SCLASS_CAP > 0),
  85. PAYMENT_SUM DOUBLE
  86. );
  87.  
  88. CREATE TABLE FLIGHTS_AIRLINES
  89. (
  90. FLIGHT_CODE CHAR(14) NOT NULL
  91. REFERENCES FLIGHTS(CODE),
  92. FLIGHT_NUMBER CHAR(8) NOT NULL,
  93. AIRLINE_NAME VARCHAR(20) NOT NULL
  94. REFERENCES AIRLINES(NAME),
  95. UNIQUE (FLIGHT_CODE, AIRLINE_NAME)
  96. );
  97.