PROMPT Creating table FLIGHT_INFO

CREATE TABLE flight_info (
   flight_no                     CHAR(8) NOT NULL,
   flight_date                   CHAR(5) NOT NULL,
   trip_date                     DATE    NOT NULL,
   trip_number                   NUMBER  DEFAULT 0 NOT NULL,
   airport_code                  CHAR(3) NOT NULL,
   gate                          CHAR(5),
   apron                         CHAR(5),
   std                           CHAR(4),
   block_off                     CHAR(1),
   direction                     CHAR(1),
   interface_id                  NUMBER NOT NULL,
   raw_data_id                   NUMBER,
   departure_time                DATE,
   schd_flight_time              DATE,
   est_flight_time               DATE,
   act_flight_time               DATE,
   terminal                      VARCHAR2 (10),
   aircraft_type                 VARCHAR2 (10),
   aircraft_registration         VARCHAR2 (20),
   row_create_time               date,
   row_date_time                 date,
   cuser                         varchar2(30),
   muser                         varchar2(30),
   mterm                         varchar2(64),
   source                        varchar2(8),
   to_be_archived                varchar2(1),
   appn_row_id                   number(18) not null,
   system_id                     number(4) default 0 not null
);

alter table flight_info add ( constraint flight_info_pk primary key (appn_row_id) using index tablespace &1._indexes);

ALTER TABLE flight_info ADD ( CONSTRAINT flight_info_uk1 unique (flight_no,flight_date,airport_code) USING INDEX TABLESPACE &1._indexes );

ALTER TABLE flight_info ADD ( CONSTRAINT flight_info_uk2 unique (airport_code,flight_no,trip_date,trip_number) USING INDEX TABLESPACE &1._indexes );

ALTER TABLE flight_info ADD CONSTRAINT flight_info_fk_sys_interface FOREIGN KEY (interface_id) REFERENCES sys_interface(id);

CREATE INDEX flight_info_sk1 ON flight_info(interface_id) TABLESPACE &1._indexes;

COMMENT ON TABLE FLIGHT_INFO IS 'This table contains flight details from external systems';

COMMENT ON COLUMN FLIGHT_INFO.ACT_FLIGHT_TIME IS  'Actual flight time';
COMMENT ON COLUMN FLIGHT_INFO.AIRCRAFT_REGISTRATION IS  'Aircraft registration';
COMMENT ON COLUMN FLIGHT_INFO.AIRCRAFT_TYPE IS  'Aircraft type';
COMMENT ON COLUMN FLIGHT_INFO.AIRPORT_CODE IS  'Airport code';
COMMENT ON COLUMN FLIGHT_INFO.APRON IS  'Apron';
COMMENT ON COLUMN FLIGHT_INFO.BLOCK_OFF IS  'Off blocks time';
COMMENT ON COLUMN FLIGHT_INFO.DEPARTURE_TIME IS  'Departure time';
COMMENT ON COLUMN FLIGHT_INFO.DIRECTION IS  'Flight direction (Inbound or outbound)';
COMMENT ON COLUMN FLIGHT_INFO.EST_FLIGHT_TIME IS  'Estimated flight time';
COMMENT ON COLUMN FLIGHT_INFO.FLIGHT_DATE IS  'Flight date';
COMMENT ON COLUMN FLIGHT_INFO.FLIGHT_NO IS  'Flight number';
COMMENT ON COLUMN FLIGHT_INFO.GATE IS  'Gate number';
COMMENT ON COLUMN FLIGHT_INFO.INTERFACE_ID IS  'Interface identifer';
COMMENT ON COLUMN FLIGHT_INFO.RAW_DATA_ID IS  'Raw data identifier';
COMMENT ON COLUMN FLIGHT_INFO.SCHD_FLIGHT_TIME IS  'Scheduled flight time';
COMMENT ON COLUMN FLIGHT_INFO.STD IS  'Scheduled time of departure';
COMMENT ON COLUMN FLIGHT_INFO.TERMINAL IS  'Terminal';
COMMENT ON COLUMN FLIGHT_INFO.TRIP_DATE IS  'Trip date';
COMMENT ON COLUMN FLIGHT_INFO.TRIP_NUMBER IS  'Trip number';
COMMENT ON COLUMN FLIGHT_INFO.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN FLIGHT_INFO.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN FLIGHT_INFO.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN FLIGHT_INFO.CUSER IS 'The user who created the row';
COMMENT ON COLUMN FLIGHT_INFO.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN FLIGHT_INFO.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN FLIGHT_INFO.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN FLIGHT_INFO.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN FLIGHT_INFO.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
