Oracle-built Table instances

-- --------------------------------------------------
--Generated by Enterprise Architect Version 9.2.921
--Created on: Monday, 66 months, 2016
--Dbms:oracle
-- --------------------------------------------------

--Create Tables
Create TABLE cstl_asm_class
(
cls_id number () not NULL,--primary key
leg_id number (not NU) LL,--associated flight Section ID
DECK VARCHAR2 (1) NOT NULL,--deck number, u/m/l, default L
CLASS VARCHAR2 (1) NOT NULL,--Class code
Sub_class Varc HAR2 (+) NOT NULL,----------------the sub-module code collection
Class_seq number (2) not NULL,--class sort
Aisle_number VARCHAR2 (+) NOT null--Class interior column layout information
)
tablespace seat_cstl_dat
Pctfree
Initrans 1
Maxtrans 255
Storage
(
initial 10M
NE XT 1M
Minextents 1
Maxextents unlimited
)
;

COMMENT on table Cstl_asm_class is ' Flight seat map class information table '
;
COMMENT on COLUMN Cstl_asm_class. CLS_ID is ' primary key '
;
COMMENT on COLUMN Cstl_asm_class. LEG_ID is ' associated navigation section ID '
;
COMMENT on COLUMN Cstl_asm_class. DECK is ' deck number, u/m/l, default L '
;
COMMENT on COLUMN Cstl_asm_class. Class is ' class code '
;
COMMENT on COLUMN Cstl_asm_class. Sub_class is ' sub-module code set '
;
COMMENT on COLUMN Cstl_asm_class. Class_seq is ' cabin sort '
;
COMMENT on COLUMN Cstl_asm_class. Aisle_number is ' cabin internal column layout information '
;

CREATE TABLE Cstl_asm_dollar_leg
(
LEG_ID number not NULL--corresponds to the primary key of the navigation table, is a non-real foreign key, as the primary key of this table
Hash number (6) Not NULL, the hash value of the flight identification, flight identification (CA1234T_10MAR16)
Count number (2) not NULL,--cumulative execution minus $ property
De_time date not NULL,--minus $ time, where time should be UTC time
Cmd_head VARCHAR2 (+) not NULL,--minus $ when the instruction head
SEAT VARCHAR2 (4000),----minus $ for the seat ID to separate each seat with a #.
Check_statusnumber (1) Not NULL--identifies the status of the review after the navigation section is automatically on the $ attribute, 1 identifies no review, and the 0 ID has been reviewed.
)
Tablespace Seat_cstl_dat
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 10M
Next 1M
Minextents 1
Maxextents Unlimited
)
;

COMMENT on TABLE Cstl_asm_dollar_leg is ' has been automatically on the $ property of the flight section information for a time before takeoff minus the $ attribute. ‘
;
COMMENT on COLUMN Cstl_asm_dollar_leg. LEG_ID is ' corresponds to the navigation table primary key, is a non-real foreign key, as the primary key of this table '
;
COMMENT on COLUMN Cstl_asm_dollar_leg. Hash is ' flight identification hash value, flight identification form (CA1234T_10MAR16) '
;
COMMENT on COLUMN Cstl_asm_dollar_leg. Count is ' cumulative execution minus $ attribute count '
;
COMMENT on COLUMN Cstl_asm_dollar_leg.de_time is ' minus $ time, here time should be UTC time '
;
COMMENT on COLUMN Cstl_asm_dollar_leg. Cmd_head is ' minus $ when the instruction head '
;
COMMENT on COLUMN Cstl_asm_dollar_leg. SEAT is ' minus $ for the seat ID, separated by # for each seat. ‘
;
COMMENT on COLUMN Cstl_asm_dollar_leg. Check_status is ' identifies the navigation section automatically on the review status after the $ attribute, 1 identifies no review, and 0 ID is reviewed. ‘
;
CREATE TABLE Cstl_asm_flight
(
FLT_ID number () not NULL,--primary key, uniquely identifying
AIRLINE VARCHAR2 (2) Not NULL,--Airlines
Flight_number VARCHAR2 (5) Not NULL,--flight number, including suffix
Flight_date date not NULL,--departure flight day, accurate to day
Itinerary VARCHAR2 () not NULL,--flight itinerary, a-b-c-d form, up to 16 sailing festivals, 17 terminals
Status number () not NULL,--flight status
Context number (1) is not NULL, which is the state of the flight initialization. 1-Occurrence of if;2-occurs pnl;3-1 and 2 occur; 0-Other
Create_time date not NULL--the first time the record was created
Update_time date not NULL-flight updated time, accurate to seconds, more for status updates
)
Tablespace Seat_cstl_dat
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 10M
Next 1M
Minextents 1
Maxextents Unlimited
)
;

COMMENT on table cstl_asm_flight is ' Flight Information summary table '
;
COMMENT on COLUMN cstl_asm_flight. FLT_ID is ' primary key, uniquely identifies '
;
COMMENT on COLUMN cstl_asm_flight. AIRLINE is ' airline '
;
COMMENT on COLUMN cstl_asm_flight. Flight_number is ' flight number with suffix '
;
COMMENT on COLUMN cstl_asm_flight. Flight_date is ' flight day, accurate to day '
;
COMMENT on COLUMN cstl_asm_flight. Itinerary is ' flight, a-b-c-d form, supports up to 16 sailing festivals, 17 terminals '
;
COMMENT on COLUMN cstl_asm_flight. Status is ' Flight state '
;
COMMENT on COLUMN cstl_asm_flight. Context is ' flight initialization contextual state. 1-Occurrence of if;2-occurs pnl;3-1 and 2 occur; 0-Other '
;
COMMENT on COLUMN cstl_asm_flight. Create_time is ' This record first created time '
;
COMMENT on COLUMN cstl_asm_flight. Update_time is ' flight updated time, accurate to seconds, more for status updates '
;

CREATE TABLE Cstl_asm_leg
(
leg_id number is not NULL,--primary key, navigation information
flt_id number () not NULL,--associated flight table, outer Key function, but not the actual foreign key
Eqt_type VARCHAR2 (3) NOT NULL,--model
Eqt_version VARCHAR2 (4) Not NULL,--version number
Dep_airport VARCHAR2 ( 3) Not NULL,--from Airport
Arr_airport VARCHAR2 (3) NOT NULL,--Arrival Airport
Leg_seq Number (2) not NULL,--order of navigation in voyage
STATUS Number (x) not NULL,--segment status
context number (2) not NULL,--change aircraft context state, Aec,rea,sea,po four bit
Dep_time DATE,--flight segment Actual departure time
Plan_dep_time Date-flight segment scheduled departure
)
Tablespace seat_cstl_dat
Pctfree
Initrans 1
Maxtrans 255
Storage
(
initial 10M
Next 1M
minextents 1
Maxextents unlimited
)
;

COMMENT on table Cstl_asm_leg is ' Flight seating chart '
;
COMMENT on COLUMN Cstl_asm_leg. LEG_ID is ' primary key, air festival information '
;
COMMENT on COLUMN Cstl_asm_leg. FLT_ID is ' associated flight table, foreign key function, but not actual foreign key '
;
COMMENT on COLUMN Cstl_asm_leg. Eqt_type is ' model '
;
COMMENT on COLUMN Cstl_asm_leg. Eqt_version is ' version number '
;
COMMENT on COLUMN Cstl_asm_leg. Dep_airport is ' starting airport '
;
COMMENT on COLUMN Cstl_asm_leg. Arr_airport is ' arrival airport '
;
COMMENT on COLUMN Cstl_asm_leg. The order of Leg_seq is ' festival in flight '
;
COMMENT on COLUMN Cstl_asm_leg. Status is ' segment state '
;
COMMENT on COLUMN Cstl_asm_leg. Context is ' Aec,rea,sea,po for four bits ', respectively.
;
COMMENT on COLUMN Cstl_asm_leg. Dep_time is ' flight segment actual departure time '
;
COMMENT on COLUMN Cstl_asm_leg. Dep_time is ' segment scheduled departure time '
;

CREATE TABLE Cstl_asm_seat
(
SEAT_ID number () not NULL,--seat ID, primary key
LEG_ID number () not NULL,--Navigation table ID
Show CHAR (1) Not NULL,--content when matrix format is displayed
Service_row Number (2),--true implementation
Service_col CHAR (1),--column number
Logic_row Number (3),--Logical line
Logic_col Number (2),--Logical column
X number (3) not NULL,--The x-coordinate of the positive direction to the right as the origin point in the upper-left corner of the matrix graph
Y Number (3) not NULL,--The y-coordinate of the positive direction down to the upper-left corner of the matrix.
Attr_1 Number (10),--Integer record seat attribute 1-32bit, from left to right.
Attr_2 Number (10),--Integer record seat attribute 33-64bit, from left to right.
Attr_3 Number (10),--Integer record seat attribute 65-96bit, from left to right.
Attr_4 Number (10),--Integer record seat attribute 97-128bit, from left to right.
Seg_reserve_range Number (2),--reserved segment seat length for a property
Row_attr Number (10),--row property
SECTION_ID Number (2)--the seat segment ID, which is separated by an aisle in the cabin, from left to right, and increments from 1.
Section_attr Number (10),--seat segment properties
DECK VARCHAR2 (1) Not NULL,--deck code, U/M/L, default to L
Class VARCHAR2 (1) Not NULL,--Class code
Sub_class VARCHAR2 (1),--the sub-cabin code
Price_level VARCHAR2 (1),--Tariff class
Seat_level VARCHAR2 (5)--Seating class
)
Tablespace Seat_cstl_dat
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 10M
Next 1M
Minextents 1
Maxextents Unlimited
)
;

COMMENT on table cstl_asm_seat is ' Flight seat map seating information table '
;
COMMENT on COLUMN cstl_asm_seat. SEAT_ID is ' seat ID number, primary key '
;
COMMENT on COLUMN cstl_asm_seat. LEG_ID is ' air section table ID '
;
COMMENT on COLUMN cstl_asm_seat. Show is ' matrix format when displaying content '
;
COMMENT on COLUMN cstl_asm_seat. Service_row is ' true implementation number '
;
COMMENT on COLUMN cstl_asm_seat. Service_col is ' column number '
;
COMMENT on COLUMN cstl_asm_seat. Logic_row is ' logical line number '
;
COMMENT on COLUMN cstl_asm_seat. Logic_col is ' logical column number '
;
COMMENT on COLUMN cstl_asm_seat. X is ' takes the upper-left corner of the matrix as the origin and right is the x-coordinate of the positive direction '
;
COMMENT on COLUMN cstl_asm_seat. Y is ' takes the upper-left corner of the matrix as the origin and downward is the y-coordinate of the positive direction '
;
COMMENT on COLUMN cstl_asm_seat. Attr_1 is ' integer record seat properties 1-32bit, left-to-right. ‘
;
COMMENT on COLUMN cstl_asm_seat. Attr_2 is ' integer record seat properties 33-64bit, left-to-right. ‘
;
COMMENT on COLUMN cstl_asm_seat. Attr_3 is ' integer record seat properties 65-96bit, left-to-right. ‘
;
COMMENT on COLUMN cstl_asm_seat. Attr_4 is ' integer record seat properties 97-128bit, left-to-right. ‘
;
COMMENT on COLUMN cstl_asm_seat. Seg_reserve_range is ' reserved segment seat length for a property '
;
COMMENT on COLUMN cstl_asm_seat. Row_attr is ' line property '
;
COMMENT on COLUMN cstl_asm_seat. SECTION_ID is ' seat segment ID, which is separated by an aisle in the cabin, from left to right, and increments from 1. ‘
;
COMMENT on COLUMN cstl_asm_seat. Section_attr is ' seat segment properties '
;
COMMENT on COLUMN cstl_asm_seat. DECK is ' deck code, U/M/L, default to L '
;
COMMENT on COLUMN cstl_asm_seat. Class is ' Class code '
;
COMMENT on COLUMN cstl_asm_seat. Sub_class is ' belongs to the sub-cabin code '
;
COMMENT on COLUMN cstl_asm_seat. Price_level is ' tariff class '
;
COMMENT on COLUMN cstl_asm_seat. Seat_level is ' Seat class '
;

CREATE TABLE cstl_asm_share
(
Mc_airline VARCHAR2 (2) NOT NULL,--market side Airlines
Mc_number VARCHAR2 (5) Not NULL,-- Market party Flight number
Flight_date date not NULL,--flight day, accurate to day
Oc_airline VARCHAR2 (2) not NULL, carrier airline
Oc_number VARCHAR2 (5 ) not NULL,--carrier flight number
Dep_airport VARCHAR2 (3) NOT NULL,--departure airport for segment
Arr_airport VARCHAR2 (3) NOT NULL,--flight segment arrival Airport OC_FLT_ID Number (9) Not NULL-the carrier flight ID of the shared information
)
tablespace seat_cstl_dat
Pctfree
Initrans 1
Maxt Rans 255
Storage
(
initial 10M
Next 1M
minextents 1
Maxextents unlimited
)
;

COMMENT on TABLE cstl_asm_share is ' record code sharing relationship '
;
COMMENT on COLUMN cstl_asm_share. Mc_airline is ' Market square airline '
;
COMMENT on COLUMN cstl_asm_share. Mc_number is ' Market Square flight number '
;
COMMENT on COLUMN cstl_asm_share. Flight_date is ' flight day, accurate to day '
;
COMMENT on COLUMN Cstl_asm_share.oc_airline is ' carrier airline '
;
COMMENT on COLUMN Cstl_asm_share.oc_number is ' Carrier flight number '
;
COMMENT on COLUMN cstl_asm_share. Departure airport of Dep_airport is ' flight segment '
;
COMMENT on COLUMN cstl_asm_share. Arr_airport is ' Flight segment arrival Airport '
;
COMMENT on COLUMN cstl_asm_share.oc_flt_id is ' carrier flight ID for this sharing information '
;

--Create Primary Key Constraints
ALTER TABLE cstl_asm_class ADD CONSTRAINT pk_cstl_asm_class
PRIMARY KEY (cls_id)
USING INDEX tablespace Seat_cstl_idx
;

ALTER TABLE cstl_asm_dollar_leg ADD CONSTRAINT Pk_cstl_dollar_leg
PRIMARY KEY (leg_id)
USING INDEX tablespace Seat_cstl_idx
;

ALTER TABLE cstl_asm_flight ADD CONSTRAINT pk_cstl_asm_flight
PRIMARY KEY (flt_id)
USING INDEX tablespace Seat_cstl_idx
;

ALTER TABLE cstl_asm_leg ADD CONSTRAINT Pk_cstl_asm_leg
PRIMARY KEY (leg_id)
USING INDEX tablespace Seat_cstl_idx
;

ALTER TABLE cstl_asm_seat ADD CONSTRAINT pk_cstl_asm_seat
PRIMARY KEY (seat_id)
USING INDEX tablespace Seat_cstl_idx
;

--Create a normal index
CREATE INDEX index_seat_leg_id on Cstl_asm_seat (leg_id)
Tablespace Seat_cstl_idx
;
CREATE INDEX Index_class_legid on Cstl_asm_class (leg_id)
Tablespace Seat_cstl_idx
;
CREATE INDEX index_flight_number on Cstl_asm_flight (Airline,flight_number)
Tablespace Seat_cstl_idx
;
CREATE INDEX Index_leg_fltid on Cstl_asm_leg (flt_id)
Tablespace Seat_cstl_idx
;
CREATE INDEX index_status_detime on Cstl_asm_dollar_leg (check_status,de_time)
Tablespace Seat_cstl_idx
;
CREATE INDEX share_oc_flt_id on Cstl_asm_share (oc_flt_id)
Tablespace Seat_cstl_idx
;
CREATE INDEX share_mc_flight_number on Cstl_asm_share (Mc_airline, Mc_number, Flight_date)
Tablespace Seat_cstl_idx
;
------------------
--Create a self-increment sequence--
------------------

--Class information table
Create sequence Cstl_asm_class_seq
MinValue 1
MaxValue 9999999999999999
Start with 1
Increment by 1
Cache 3
Cycle

--flight information table, total parent table
Create sequence Cstl_asm_flight_seq
MinValue 1
MaxValue 999999999999
Start with 1
Increment by 1
Cycle

--Air Festival information table
Create sequence Cstl_asm_leg_seq
MinValue 1
MaxValue 99999999999999
Start with 1
Increment by 1
Cycle

--Seating Information table
Create sequence Cstl_asm_seat_seq
MinValue 1
MaxValue 9999999999999999
Start with 1
Increment by 1
Cache 10
Cycle

Oracle-built Table instances