SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
	FROM USER_OBJECTS
	WHERE OBJECT_TYPE = 'TRIGGER';

CREATE TABLE HUMANS
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "HUMANS_PK" PRIMARY KEY,
	SURNAME VARCHAR2(50) NOT NULL,
	NAME VARCHAR2(50) NOT NULL,
	SECOND_NAME VARCHAR2(50) NOT NULL,
	BIRTH_DATE DATE NOT NULL,
	SEX CHAR(1) NOT NULL CONSTRAINT "Пол может быть М или Ж" 
						CHECK (SEX IN('М','Ж')),
	BIRTH_PLACE VARCHAR2(100),
	ADDRESS VARCHAR2(100),
	PHONE_NUMBER NUMBER(11),
	CONSTRAINT "HUMANS_UK" UNIQUE (SURNAME,NAME,SECOND_NAME,BIRTH_DATE,SEX)
);

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 DEPARTMENTS
(
	ID NUMBER (6,0) NOT NULL CONSTRAINT "DEPARTMENTS_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	ABBREVIATION VARCHAR2(10) NOT NULL,
	DEPARTMENT_NUMBER NUMBER(6,0) NOT NULL,
	FUTURE VARCHAR2(200),
	CONSTRAINT "DEPARTMENTS_UK" UNIQUE (NAME,DEPARTMENT_NUMBER)
);

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 := INITCAP(:NEW.NAME);
	END DEPARTMENTS_BIR;
/

CREATE TABLE WORK_MODES
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "WORK_MODES_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	ABBREVIATION VARCHAR2(10) NOT NULL,
	CONSTRAINT "WORK_MODES_UK" UNIQUE (NAME)
);

CREATE SEQUENCE WORK_MODES_SEQ INCREMENT BY 1 START WITH 1;

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

CREATE TABLE POSTS
(
	ID NUMBER(3,0) NOT NULL CONSTRAINT "POSTS_PK" PRIMARY KEY,
	NAME VARCHAR2(50) NOT NULL,
	ABBREVIATION VARCHAR2(10) NOT NULL,
	CONSTRAINT "POSTS_UK" UNIQUE (NAME)
);

CREATE SEQUENCE POSTS_SEQ INCREMENT BY 1 START WITH 1;

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

CREATE TABLE JOURNAL
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "JOURNAL_PK" PRIMARY KEY,
	HUMAN_ID NUMBER(6,0) NOT NULL CONSTRAINT "HUMANS_FK"
								REFERENCES HUMANS(ID),
	DEPARTMENT_ID NUMBER(6,0) NOT NULL CONSTRAINT "DEPARTMENTS_FK"
								REFERENCES DEPARTMENTS(ID),
	DAY_1 CHAR(1),
	DAY_2 CHAR(1), 
	DAY_3 CHAR(1),
	DAY_4 CHAR(1),
	DAY_5 CHAR(1),
	DAY_6 CHAR(1),
	DAY_7 CHAR(1),
	DAY_8 CHAR(1),
	DAY_9 CHAR(1),
	DAY_10 CHAR(1),
	DAY_11 CHAR(1),
	DAY_12 CHAR(1),
	DAY_13 CHAR(1),
	DAY_14 CHAR(1),
	DAY_15 CHAR(1),
	DAY_16 CHAR(1),
	DAY_17 CHAR(1),
	DAY_18 CHAR(1),
	DAY_19 CHAR(1),
	DAY_20 CHAR(1),
	DAY_21 CHAR(1),
	DAY_22 CHAR(1),
	DAY_23 CHAR(1),
	DAY_24 CHAR(1),
	DAY_25 CHAR(1),
	DAY_26 CHAR(1),
	DAY_27 CHAR(1),
	DAY_28 CHAR(1),
	DAY_29 CHAR(1),
	DAY_30 CHAR(1),
	CONSTRAINT "JOURNAL_UK" UNIQUE (HUMAN_ID,DEPARTMENT_ID)
);

CREATE SEQUENCE "JOURNAL_SEQ" INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER JOURNAL_BIR
	BEFORE INSERT ON JOURNAL
	FOR EACH ROW
	BEGIN
		SELECT JOURNAL_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END JOURNAL_BIR;
/

CREATE TABLE WAGES
(
	ID NUMBER(6,0) NOT NULL CONSTRAINT "WAGES_PK" PRIMARY KEY,
	POST_ID NUMBER(3,0) NOT NULL CONSTRAINT "POSTS_FK"
									REFERENCES POSTS(ID),
	WAGE NUMBER(6,0) NOT NULL,
	CONSTRAINT "WAGES_UK" UNIQUE (POST_ID)
);

CREATE SEQUENCE WAGES_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER WAGES_BIR
	BEFORE INSERT ON WAGES
	FOR EACH ROW
	BEGIN
		SELECT WAGES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
	END WAGES_BIR;
/