DROP TABLE SEM_PERSON_SEQ ; 

CREATE TABLE SEM_PERSON_SEQ ( 
  SEQUENCE            INT   NOT NULL PRIMARY KEY
) ; 

INSERT INTO SEM_PERSON_SEQ(SEQUENCE) VALUES(0);


DROP TABLE SEM_PERSON ; 

CREATE TABLE SEM_PERSON ( 
  SEM_PERSON_ID             INT  NOT NULL PRIMARY KEY,
  EMAIL                 VARCHAR (100)  NOT NULL,
  WORK_PHONE            VARCHAR (20),
  HOME_PHONE            VARCHAR (20),
  FAX                   VARCHAR (20),
  PASSWORD              VARCHAR (20)  NOT NULL,
  SEM_PERSON_STATUS_ID      INT
) ; 

ALTER TABLE SEM_PERSON ADD  CONSTRAINT U_EMAIL
 UNIQUE (EMAIL) ;


DROP TABLE SEM_PERSON_STATUS_SEQ ;

CREATE TABLE SEM_PERSON_STATUS_SEQ (
  SEQUENCE            INT NOT NULL  PRIMARY KEY
) ;

INSERT INTO SEM_PERSON_STATUS_SEQ(SEQUENCE) VALUES(0);


DROP TABLE SEM_PERSON_STATUS ;

CREATE TABLE SEM_PERSON_STATUS (
  SEM_PERSON_STATUS_ID	INT NOT NULL PRIMARY KEY,
  STATUS            VARCHAR(10),
  STATUS_COMMENT    VARCHAR (255)
);

CREATE INDEX SEM_PERSON_SEM_PERSON_STATUS_FK_IX ON SEM_PERSON
(
       SEM_PERSON_STATUS_ID             ASC
);


ALTER TABLE SEM_PERSON
       ADD  CONSTRAINT SEM_PERSON_SEM_PERSON_STATUS_FK
              FOREIGN KEY (SEM_PERSON_STATUS_ID)
                             REFERENCES SEM_PERSON_STATUS(SEM_PERSON_STATUS_ID)  ;
