-- order 20010682


CREATE TABLE SIGNALLIGHTSSET (
SIGNALLIGHTSSET_ID NUMBER NOT NULL,
CODE VARCHAR2(6) NOT NULL,
DESCRIPTION VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL);


CREATE SEQUENCE SEQ_SIGNALLIGHTSSET
START WITH 1
INCREMENT BY 1;


CREATE UNIQUE INDEX XPKSIGNALLIGHTSSET ON SIGNALLIGHTSSET
(
SIGNALLIGHTSSET_ID ASC
);


CREATE UNIQUE INDEX XAK1SIGNALLIGHTSSET ON SIGNALLIGHTSSET
(
CODE 
);


ALTER TABLE SIGNALLIGHTSSET
ADD ( CONSTRAINT XPKSIGNALLIGHTSSET 
      PRIMARY KEY (SIGNALLIGHTSSET_ID) ) ;


CREATE TABLE SIGNALLIGHTSSETCOMPONENT (
 SIGNALLIGHTSSETCOMPONENT_ID NUMBER NOT NULL,
 SIGNALLIGHTSSET_ID NUMBER NOT NULL,
 POSITION NUMBER DEFAULT 0 NOT NULL  
      CONSTRAINT POSITION_VALID
      CHECK (POSITION IN (0, 1, 2, 3, 4, 5)) ,
 COMPONENTTYPE NUMBER DEFAULT 1 NOT NULL 
      CONSTRAINT COMPONENTTYPE_VALID
      CHECK (COMPONENTTYPE IN (1, 2)) ,
 DESCRIPTION VARCHAR2(30) NOT NULL,
 EVENT NUMBER DEFAULT 0 NOT NULL 
      CONSTRAINT EVENT_VALID
      CHECK (EVENT IN (0, 1, 2, 3, 4)) ,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL
 );


CREATE SEQUENCE SEQ_SIGNALLIGHTSSETCOMPONENT
START WITH 1
INCREMENT BY 1;


COMMENT ON COLUMN SIGNALLIGHTSSETCOMPONENT.COMPONENTTYPE IS
 ' 1 = LIGHT  2 = BUZZER' ;


COMMENT ON COLUMN SIGNALLIGHTSSETCOMPONENT.EVENT IS
 ' 0 = UNUSED  1 = SCAN ACCEPTED  2 = SCAN REJECTED  3 = FLAG (ALL)  4 = FLAG (SPECIFIC) ' ;


CREATE UNIQUE INDEX XPKSIGNALLIGHTSSETCOMPONENT ON SIGNALLIGHTSSETCOMPONENT
(
       SIGNALLIGHTSSETCOMPONENT_ID           ASC
);


CREATE INDEX XIF1SIGNALLIGHTSSETCOMPONENT ON SIGNALLIGHTSSETCOMPONENT
(
       SIGNALLIGHTSSET_ID               ASC
);


CREATE UNIQUE INDEX XAK1SIGNALLIGHTSSETCOMPONENT ON SIGNALLIGHTSSETCOMPONENT
(
                 SIGNALLIGHTSSET_ID ,
                 POSITION
);


ALTER TABLE SIGNALLIGHTSSETCOMPONENT
       ADD  ( CONSTRAINT XPKSIGNALLIGHTSSETCOMPONENT
              PRIMARY KEY (SIGNALLIGHTSSETCOMPONENT_ID) ) ;


ALTER TABLE SIGNALLIGHTSSETCOMPONENT
       ADD  ( CONSTRAINT SIGNALLIGHSETCOMP_SIGNALLIGH
              FOREIGN KEY (SIGNALLIGHTSSET_ID)
                             REFERENCES SIGNALLIGHTSSET
                             ON DELETE CASCADE ) ;


CREATE TABLE  SIGNALLIGHTSSETCOMPONENTFLAG (
SIGNALLIGHTSSETCOMPFLAG_ID NUMBER NOT NULL,
 SIGNALLIGHTSSETCOMPONENT_ID NUMBER NOT NULL,
 FLAG_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL
 );


CREATE SEQUENCE SEQ_SIGNALLIGHTSSETCOMPFLAG 
START WITH 1
INCREMENT BY 1;


CREATE UNIQUE INDEX XPKSIGNALLIGHTSSETCOMPFLAG ON SIGNALLIGHTSSETCOMPONENTFLAG
(
       SIGNALLIGHTSSETCOMPFLAG_ID           ASC
);


CREATE INDEX XIF1SIGNALLIGHTSSETCOMPFLAG ON SIGNALLIGHTSSETCOMPONENTFLAG
(
       SIGNALLIGHTSSETCOMPONENT_ID               ASC
);


CREATE INDEX XIF2SIGNALLIGHTSSETCOMPFLAG ON SIGNALLIGHTSSETCOMPONENTFLAG
(
       FLAG_ID               ASC
);


CREATE UNIQUE INDEX XAK1SIGNALLIGHTSSETCOMPFLAG ON SIGNALLIGHTSSETCOMPONENTFLAG
(
                 SIGNALLIGHTSSETCOMPONENT_ID ,
                 FLAG_ID
);


ALTER TABLE SIGNALLIGHTSSETCOMPONENTFLAG
       ADD  ( CONSTRAINT XPKSIGNALLIGHTSSETCOMPFLAG
              PRIMARY KEY (SIGNALLIGHTSSETCOMPFLAG_ID) ) ;


ALTER TABLE SIGNALLIGHTSSETCOMPONENTFLAG
       ADD  ( CONSTRAINT SIGNALLIGHTSSETCOMPFLAG_SIGN
              FOREIGN KEY (SIGNALLIGHTSSETCOMPONENT_ID)
                             REFERENCES SIGNALLIGHTSSETCOMPONENT
                             ON DELETE CASCADE ) ;


ALTER TABLE SIGNALLIGHTSSETCOMPONENTFLAG
       ADD  ( CONSTRAINT SIGNALLIGHTSSETCOMPFLAG_FLAG
              FOREIGN KEY (FLAG_ID)
                             REFERENCES FLAG
                             ON DELETE CASCADE ) ;


ALTER TABLE WORKSTATION ADD (SIGNALLIGHTSSET_ID  NUMBER  NULL);


CREATE INDEX XIF5WORKSTATION ON WORKSTATION
(
       SIGNALLIGHTSSET_ID               ASC
);


ALTER TABLE WORKSTATION
       ADD  ( CONSTRAINT WORKSTATION_SIGNALLIGHTSSET_ID
              FOREIGN KEY (SIGNALLIGHTSSET_ID)
                             REFERENCES SIGNALLIGHTSSET ) ;