ALTER USER DEREVO QUOTA UNLIMITED ON USERS;

CREATE TABLE CITIZENSHIP
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "CITIZENSHIP_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "CITIZENSHIP_UK" UNIQUE (NAME)
);

COMMENT ON TABLE CITIZENSHIP IS 'Гражданство';
COMMENT ON COLUMN CITIZENSHIP.ID IS 'Искусственный первичный уникальный идентификатор';
COMMENT ON COLUMN CITIZENSHIP.NAME IS 'Наименование гражданства';

CREATE SEQUENCE CITIZENSHIP_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER CITIZENSHIP_BIR
	BEFORE INSERT ON CITIZENSHIP
	FOR EACH ROW
	BEGIN
		SELECT CITIZENSHIP_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END CITIZENSHIP_BIR;
/

CREATE TABLE HUMANS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "HUMANS_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	SURNAME VARCHAR2(50) NOT NULL,
	SECOND_NAME VARCHAR(50) NOT NULL,
	BIRTH_DATE DATE NOT NULL,
	SEX CHAR(1) NOT NULL CONSTRAINT "Пол модет быть М или Ж"
						CHECK (SEX IN ('М','Ж')),
	CITIZENSHIP_ID NUMBER(6,0) NOT NULL CONSTRAINT "CITIZENSHIP_FK" 
										REFERENCES CITIZENSHIP(ID),
	CONSTRAINT "HUMANS_UK" UNIQUE (SURNAME,NAME,SECOND_NAME,BIRTH_DATE,SEX)
);

COMMENT ON TABLE HUMANS IS 'Список людей';
COMMENT ON COLUMN HUMANS.ID IS 'Уникальный номер человека';
COMMENT ON COLUMN HUMANS.NAME IS 'Имя человека';
COMMENT ON COLUMN HUMANS.SURNAME IS 'Фамилия человека';
COMMENT ON COLUMN HUMANS.SECOND_NAME IS 'Отчество человека';
COMMENT ON COLUMN HUMANS.BIRTH_DATE IS 'Дата рождения человека';
COMMENT ON COLUMN HUMANS.SEX IS 'Пол человека';
COMMENT ON COLUMN HUMANS.CITIZENSHIP_ID IS 'Внешний ключ на таблицу CITIZENSHIP';

CREATE SEQUENCE HUMANS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER HUMANS_BIR
	BEFORE INSERT ON HUMANS
	FOR EACH ROW
	BEGIN
		SELECT HUMANS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.SURNAME := INITCAP(:NEW.SURNAME);
		:NEW.NAME := INITCAP(:NEW.NAME);
		:NEW.SECOND_NAME := INITCAP(:NEW.SECOND_NAME);
		IF :NEW.BIRTH_DATE IS NULL THEN
			:NEW.BIRTH_DATE := TO_DATE('11.11.1111','DD.MM.YYYY');
		END IF;
	END HUMANS_BIR;
/

CREATE TABLE ENTRANTS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "ENTRANTS_PK" PRIMARY KEY,
	HUMAN_ID NUMBER(6,0) NOT NULL CONSTRAINT "ENTRANTS_FK"
									REFERENCES HUMANS(ID),
	ORIGINAL CHAR(1) NOT NULL CONSTRAINT "ORIGINAL IS Y/N"
						CHECK (ORIGINAL IN ('Y','N')),
	MEDAL CHAR(1) NOT NULL CONSTRAINT "MEDAL IS Y/N"
						CHECK (MEDAL IN ('Y','N')),
	ALLOWANCE CHAR(1) NOT NULL CONSTRAINT "ALLOWANCE IS Y/N"
						CHECK (ALLOWANCE IN ('Y','N')),
	ENTRY_YEAR NUMBER(4,0) NOT NULL,
	SCANNED_PROFILE BLOB,
	CONSTRAINT "ENTRANTS_UK" UNIQUE (HUMAN_ID,ENTRY_YEAR)
);

COMMENT ON TABLE ENTRANTS IS 'Список абитуриентов';
COMMENT ON COLUMN ENTRANTS.ID IS 'Уникальный номер абитуриента';
COMMENT ON COLUMN ENTRANTS.HUMAN_ID IS 'Внешний ключ на таблицу HUMANS';
COMMENT ON COLUMN ENTRANTS.ORIGINAL IS 'Наличие оригинала';
COMMENT ON COLUMN ENTRANTS.MEDAL IS 'Наличие медали';
COMMENT ON COLUMN ENTRANTS.ALLOWANCE IS 'Наличие льготы';
COMMENT ON COLUMN ENTRANTS.ENTRY_YEAR IS 'Год подступления';
COMMENT ON COLUMN ENTRANTS.SCANNED_PROFILE IS 'Скан анкеты';

CREATE SEQUENCE ENTRANTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ENTRANTS_BIR
	BEFORE INSERT ON ENTRANTS
	FOR EACH ROW
	BEGIN
		SELECT ENTRANTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END ENTRANTS_BIR;
/

CREATE TABLE BRANCHES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "BRANCHES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "BRANCHES_UK" UNIQUE(NAME)
);

COMMENT ON TABLE BRANCHES IS 'Отделения';
COMMENT ON COLUMN BRANCHES.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN BRANCHES.NAME IS 'Наименование отделения';

CREATE SEQUENCE BRANCHES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER BRANCHES_BIR
	BEFORE INSERT ON BRANCHES
	FOR EACH ROW
	BEGIN
		SELECT BRANCHES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END BRANCHES_BIR;
/

CREATE TABLE SPECIALTIES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "SPECIALTIES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CODE VARCHAR2(10) NOT NULL,
	CONSTRAINT "SPECIALTIES_UK" UNIQUE (CODE)
);

COMMENT ON TABLE SPECIALTIES IS 'Специальности';
COMMENT ON COLUMN SPECIALTIES.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN SPECIALTIES.NAME IS 'Наименование специальности';
COMMENT ON COLUMN SPECIALTIES.CODE IS 'Номер специальности';

CREATE SEQUENCE SPECIALTIES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER SPECIALTIES_BIR
	BEFORE INSERT ON SPECIALTIES
	FOR EACH ROW
	BEGIN
		SELECT SPECIALTIES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := UPPER(:NEW.NAME);
	END SPECIALTIES_BIR;
/

CREATE TABLE PROGRAMS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "PROGRAMS_PK" PRIMARY KEY,
	SPEC_ID NUMBER(3,0) NOT NULL CONSTRAINT "SPECIALTIES_FK"
								REFERENCES SPECIALTIES(ID),
	BRANCH_ID NUMBER(3,0) NOT NULL CONSTRAINT "BRANCHES_FK"
								REFERENCES BRANCHES(ID),
	CONSTRAINT "PROGRAMS_UK" UNIQUE (SPEC_ID,BRANCH_ID)
);

COMMENT ON TABLE PROGRAMS IS 'Учебные программы';
COMMENT ON COLUMN PROGRAMS.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN PROGRAMS.SPEC_ID IS 'Внешний ключ на таблицу SPECIALTIES';
COMMENT ON COLUMN PROGRAMS.BRANCH_ID IS 'Внешний ключ на таблицу BRANCHES';

CREATE SEQUENCE PROGRAMS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER PROGRAMS_BIR
	BEFORE INSERT ON PROGRAMS
	FOR EACH ROW
	BEGIN
		SELECT PROGRAMS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END PROGRAMS_BIR;
/

CREATE TABLE DEPARTMENT_TYPES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "DEPARTMENT_TYPES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "DEPARTMENT_TYPES_UK" UNIQUE(NAME)
);

COMMENT ON TABLE DEPARTMENT_TYPES IS 'Типы отделов';
COMMENT ON COLUMN DEPARTMENT_TYPES.ID IS 'Искусственный первичный уникальный идентификатор';
COMMENT ON COLUMN DEPARTMENT_TYPES.NAME IS 'Наименование отдела';

CREATE SEQUENCE DEPARTMENT_TYPES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER DEPARTMENT_TYPES_BIR
	BEFORE INSERT ON DEPARTMENT_TYPES
	FOR EACH ROW
	BEGIN
		SELECT DEPARTMENT_TYPES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END DEPARTMENT_TYPES_BIR;
/

CREATE TABLE DEPARTMENTS
(
	ID NUMBER (3,0) NOT NULL CONSTRAINT "DEPARTMENTS_PK" PRIMARY KEY,
	NAME VARCHAR2(100) NOT NULL,
	DEPARTMENT_ID NUMBER (3,0) CONSTRAINT "DEPARTMENTS_FK"
								REFERENCES DEPARTMENTS(ID),
	DEPARTMENT_TYPE_ID NUMBER (3,0) NOT NULL CONSTRAINT "DEPARTMENT_TYPES_FK" REFERENCES DEPARTMENT_TYPES(ID),
	CODE NUMBER(3,0) NOT NULL,
	ABBR VARCHAR2(15) NOT NULL,
	CONSTRAINT "DEPARTMENTS_UK" UNIQUE(CODE)
);

COMMENT ON TABLE DEPARTMENTS IS 'Список кафедр/факультетов';
COMMENT ON COLUMN DEPARTMENTS.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN DEPARTMENTS.NAME IS 'Название';
COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_ID IS 'Внеший ключ на таблицу DEPARTMENTS';
COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_TYPE_ID IS 'Внеший ключ на таблицу DEPARTMENT_TYPES';
COMMENT ON COLUMN DEPARTMENTS.CODE IS 'Код отдела';

CREATE SEQUENCE DEPARTMENTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER DEPARTMENTS_BIR
	BEFORE INSERT ON DEPARTMENTS
	FOR EACH ROW
	BEGIN
		SELECT DEPARTMENTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := UPPER(:NEW.NAME);
	END DEPARTMENTS_BIR;
/

CREATE TABLE ACCEPTANCE_PLANS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "ACCEPTANCE_PLANS_PK" PRIMARY KEY,
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "PLANS_PROGRAMS_FK"
									REFERENCES PROGRAMS(ID),
	DEPARTMENT_ID NUMBER(3,0) NOT NULL CONSTRAINT "PLANS_DEPARTMENTS_FK"
									REFERENCES DEPARTMENTS(ID),
	AMOUNT NUMBER(3,0) NOT NULL,
	PLAN_YEAR NUMBER (4,0) NOT NULL,
	CONSTRAINT "ACCEPTANCE_PLANS_UK" UNIQUE(PROGRAM_ID,DEPARTMENT_ID)
);

COMMENT ON TABLE ACCEPTANCE_PLANS IS 'План приема абитуриентов';
COMMENT ON COLUMN ACCEPTANCE_PLANS.PROGRAM_ID IS 'Внеший ключ на таблицу PROGRAMS';
COMMENT ON COLUMN ACCEPTANCE_PLANS.DEPARTMENT_ID IS 'Внеший ключ на таблицу DEPARTMENTS';
COMMENT ON COLUMN ACCEPTANCE_PLANS.AMOUNT IS 'Кол-во людей';
COMMENT ON COLUMN ACCEPTANCE_PLANS.PLAN_YEAR IS 'Год';

CREATE SEQUENCE ACCEPTANCE_PLANS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ACCEPTANCE_PLANS_BIR
	BEFORE INSERT ON ACCEPTANCE_PLANS
	FOR EACH ROW
	BEGIN
		SELECT ACCEPTANCE_PLANS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END ACCEPTANCE_PLANS_BIR;
/


CREATE TABLE DEPARTMENTPROGRAM
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "DEPARTMENTPROGRAM_PK" PRIMARY KEY,
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "DP_PROGRAMS_FK" REFERENCES PROGRAMS(ID),
	DEPARTMENT_ID NUMBER(3,0) NOT NULL CONSTRAINT "DP_DEPARTMENT_FK" REFERENCES DEPARTMENTS(ID),
	CONSTRAINT "DEPARTMENTPROGRAM_UK" UNIQUE(PROGRAM_ID,DEPARTMENT_ID)
);

COMMENT ON TABLE DEPARTMENTPROGRAM IS 'Программы на кафедрах';
COMMENT ON COLUMN DEPARTMENTPROGRAM.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN DEPARTMENTPROGRAM.PROGRAM_ID IS 'Внеший ключ на таблицу PROGRAMS';
COMMENT ON COLUMN DEPARTMENTPROGRAM.DEPARTMENT_ID IS 'Вншний ключ на таблицу DEPARTMENTS';

CREATE SEQUENCE DEPARTMENTPROGRAM_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER DEPARTMENTPROGRAM_BIR
	BEFORE INSERT ON DEPARTMENTPROGRAM
	FOR EACH ROW
	BEGIN
		SELECT DEPARTMENTPROGRAM_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END DEPARTMENTPROGRAM_BIR;
/

CREATE TABLE SUBJECTS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "SUBJECTS_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	CONSTRAINT "SUBJECTS_UK" UNIQUE(NAME)
);

COMMENT ON TABLE SUBJECTS IS 'Предметы';
COMMENT ON COLUMN SUBJECTS.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN SUBJECTS.NAME IS 'Название предмета';

CREATE SEQUENCE SUBJECTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER SUBJECTS_BIR
	BEFORE INSERT ON SUBJECTS
	FOR EACH ROW
	BEGIN
		SELECT SUBJECTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END SUBJECTS_BIR;
/

CREATE TABLE ORDERS_ITEMS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "ORDERS_ITEMS" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	FULL_TEXT CLOB,
	ITEM_NUMBER NUMBER(2,0) NOT NULL,
	CONSTRAINT "ORDERS_ITEMS_UK" UNIQUE(NAME)
);

COMMENT ON TABLE ORDERS_ITEMS IS 'Пункты приказа';
COMMENT ON COLUMN ORDERS_ITEMS.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN ORDERS_ITEMS.NAME IS 'Наименование пункта';
COMMENT ON COLUMN ORDERS_ITEMS.FULL_TEXT IS 'Полное содержание пункта';
COMMENT ON COLUMN ORDERS_ITEMS.ITEM_NUMBER IS 'Номер пункта';

CREATE SEQUENCE ORDERS_ITEMS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ORDERS_ITEMS_BIR
	BEFORE INSERT ON ORDERS_ITEMS
	FOR EACH ROW
	BEGIN
		SELECT ORDERS_ITEMS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.NAME := INITCAP(:NEW.NAME);
	END ORDERS_ITEMS_BIR;
/

CREATE TABLE PRIORITIES
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "PRIORITIES_PK" PRIMARY KEY,
	ENTRANT_ID NUMBER(6,0) NOT NULL CONSTRAINT "PR_ENTRANTS_FK" REFERENCES ENTRANTS(ID),
	PROGRAM_ID NUMBER(6,0) NOT NULL CONSTRAINT "PR_PROGRAMS_FK" REFERENCES DEPARTMENTPROGRAM(ID),
	PRIORITY NUMBER(1,0) NOT NULL CONSTRAINT "Приоритет не больше 3" CHECK(PRIORITY IN (1,2,3)),
	CONSTRAINT "PRIORITIES_UK" UNIQUE(ENTRANT_ID,PRIORITY)
);

COMMENT ON TABLE PRIORITIES IS 'Приоритеты абитуриента';
COMMENT ON COLUMN PRIORITIES.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN PRIORITIES.ENTRANT_ID IS 'Внешний ключ на таблицу ENTRANTS';
COMMENT ON COLUMN PRIORITIES.PROGRAM_ID IS 'Внешний ключ на таблицу PROGRAMS';
COMMENT ON COLUMN PRIORITIES.PRIORITY IS 'Приоритет';

CREATE SEQUENCE PRIORITIES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER PRIORITIES_BIR
	BEFORE INSERT ON PRIORITIES
	FOR EACH ROW
	BEGIN
		SELECT PRIORITIES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END PRIORITIES_BIR;
/

CREATE TABLE EXAMS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "EXAMS_PK" PRIMARY KEY,
	ENTRANT_ID NUMBER (6,0) NOT NULL CONSTRAINT "EXAMS_ENTRANTS_FK" REFERENCES ENTRANTS(ID),
	SUBJECT_ID NUMBER(3,0) NOT NULL CONSTRAINT "EXAMS_SUBJECTS_FK" REFERENCES SUBJECTS(ID),
	MARK NUMBER(3,0) NOT NULL,
	CODE NUMBER(9,0) NOT NULL,
	CONSTRAINT "EXAMS_UK" UNIQUE(CODE)
);

COMMENT ON TABLE EXAMS IS 'Экзамены';
COMMENT ON COLUMN EXAMS.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN EXAMS.ENTRANT_ID IS 'Внешний ключ на таблицу ENTRANTS';
COMMENT ON COLUMN EXAMS.SUBJECT_ID IS 'Внешний ключ на таблицу SUBJECTS';
COMMENT ON COLUMN EXAMS.MARK IS 'Балл';
COMMENT ON COLUMN EXAMS.CODE IS 'Номер сертификата';

CREATE SEQUENCE EXAMS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER EXAMS_BIR
	BEFORE INSERT ON EXAMS
	FOR EACH ROW
	BEGIN
		SELECT EXAMS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END EXAMS_BIR;
/

CREATE TABLE PROGRAMSUBJECTS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "PROGRAMSUBJECTS_PK" PRIMARY KEY,
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "PS_PROGRAMS_FK" REFERENCES PROGRAMS(ID),
	SUBJECT_ID NUMBER(3,0) NOT NULL CONSTRAINT "PS_SUBJECTS_FK" REFERENCES SUBJECTS(ID),
	THRESHOLD_MARK NUMBER(3,0) NOT NULL,
	PS_YEAR NUMBER(4,0) NOT NULL,
	CONSTRAINT "PROGRAMSUBJECTS_UK" UNIQUE(PROGRAM_ID,SUBJECT_ID,PS_YEAR)
);

COMMENT ON TABLE PROGRAMSUBJECTS IS 'Предметы для поступления на программу';
COMMENT ON COLUMN PROGRAMSUBJECTS.ID IS 'Искусственный первичный ключ';
COMMENT ON COLUMN PROGRAMSUBJECTS.PROGRAM_ID IS 'Внешний ключ на таблицу PROGRAMS';
COMMENT ON COLUMN PROGRAMSUBJECTS.SUBJECT_ID IS 'Внеший ключ на таблицу SUBJECTS';
COMMENT ON COLUMN PROGRAMSUBJECTS.THRESHOLD_MARK IS 'Пороговый балл';
COMMENT ON COLUMN PROGRAMSUBJECTS.PS_YEAR IS 'Год';

CREATE SEQUENCE PROGRAMSUBJECTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER PROGRAMSUBJECTS_BIR
	BEFORE INSERT ON PROGRAMSUBJECTS
	FOR EACH ROW
	BEGIN
		SELECT PROGRAMSUBJECTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END PROGRAMSUBJECTS_BIR;
/

CREATE TABLE COMMANDMENTS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "COMMANDMENTS_PK" PRIMARY KEY,
	ENTRANT_ID NUMBER(6,0) NOT NULL CONSTRAINT "COMM_ENTRANTS_FK" REFERENCES ENTRANTS(ID),
	PROGRAM_ID NUMBER(3,0) NOT NULL CONSTRAINT "COMM_PROGRAMS_FK" REFERENCES PROGRAMS(ID),
	ORDER_ID NUMBER(3,0) NOT NULL CONSTRAINT "COMM_ORDERS_ITEMS_FK" REFERENCES ORDERS_ITEMS,
	PRE_COMMANDMENT CHAR(1) NOT NULL CONSTRAINT "PRE_COMMANDMENT IS Y/N"
						CHECK (PRE_COMMANDMENT IN ('Y','N')),
	COMMANDMENT_YEAR NUMBER(4,0) NOT NULL,
	CODE NUMBER(9,0) NOT NULL,
	DEPARTMENT_ID NUMBER(3,0) NOT NULL CONSTRAINT "COMM_DEPARTMENTS_FK" REFERENCES DEPARTMENTS(ID),
	COMMIT_DATE DATE,
	CREATE_DATE DATE,
	CONSTRAINT "COMMANDMENTS_UK" UNIQUE(CODE)
);

COMMENT ON TABLE COMMANDMENTS IS '';
COMMENT ON COLUMN COMMANDMENTS.ID IS '';
COMMENT ON COLUMN COMMANDMENTS.ENTRANT_ID IS '';
COMMENT ON COLUMN COMMANDMENTS.PROGRAM_ID IS '';
COMMENT ON COLUMN COMMANDMENTS.ORDER_ID IS '';
COMMENT ON COLUMN COMMANDMENTS.PRE_COMMANDMENT IS '';
COMMENT ON COLUMN COMMANDMENTS.COMMANDMENT_YEAR IS '';
COMMENT ON COLUMN COMMANDMENTS.DEPARTMENT_ID IS '';
COMMENT ON COLUMN COMMANDMENTS.COMMIT_DATE IS '';
COMMENT ON COLUMN COMMANDMENTS.CREATE_DATE IS '';

CREATE SEQUENCE COMMANDMENTS_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER COMMANDMENTS_BIR
	BEFORE INSERT ON COMMANDMENTS
	FOR EACH ROW
	BEGIN
		SELECT COMMANDMENTS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
		:NEW.CREATE_DATE := SYSDATE;
	END COMMANDMENTS_BIR;
/