CREATE TABLE Staff
(
	id NUMBER not null primary key,
	Surname VARCHAR2(50 CHAR),
	Name VARCHAR2(50 CHAR) not null,
	Middle_name VARCHAR2(50 CHAR),
	Birth_date DATE,
	Placement_date DATE not null,
	Race_id NUMBER not null,
	Departament_id NUMBER ,
	Sex CHAR(1 CHAR) not null	
);

CREATE sequence staff_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_staff
BEFORE INSERT OR UPDATE
ON Staff 
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	err_sex	EXCEPTION;
BEGIN
	IF :NEW.Sex <> 'М' THEN
		IF :NEW.Sex <> 'Ж' THEN
			RAISE err_sex;
		END IF;
	END IF;

	

	IF INSERTING THEN
		SELECT staff_id_seq.NEXTVAL INTO :NEW.id FROM DUAL;
	END IF;
EXCEPTION
	WHEN err_sex THEN RAISE_APPLICATION_ERROR(-20999,
		'В указании пола могут участвовать только символы М и Ж');
END autoincrement_tr_staff;
/

CREATE TABLE Scientists
(
	id NUMBER not null primary key,
	Staff_id NUMBER not null,
	Position_id NUMBER not null,
	Degree_id NUMBER not null
);

CREATE sequence scien_id_seq
	start with 1
	increment by 1
	NOCACHE; 

CREATE OR REPLACE TRIGGER autoincrement_tr_scient
BEFORE INSERT 
ON Scientists 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT scien_id_seq.NEXTVAL
	INTO :NEW.id
	FROM DUAL;
END;
/

CREATE TABLE Degrees
(
	id NUMBER not null primary key,
	Degree VARCHAR2(50 CHAR) not null
);

CREATE sequence degree_id_seq
	start with 1
	increment by 1
	NOCACHE; 

CREATE OR REPLACE TRIGGER autoincrement_tr_degree
BEFORE INSERT 
ON Degrees 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT degree_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Positions
(
	id NUMBER not null primary key,
	Position VARCHAR2(50 CHAR) not null
);

CREATE sequence position_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_position
BEFORE INSERT 
ON Positions
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT position_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Races
(
	id NUMBER not null primary key,
	Race VARCHAR2(50 CHAR) not null
);

CREATE sequence race_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_race
BEFORE INSERT 
ON Races 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT race_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Service_Staff
(
	id NUMBER not null primary key,
	Staff_id NUMBER not null,
	Position_id NUMBER not null
);

CREATE sequence ss_id_seq
	start with 1
	increment by 1
	NOCACHE; 

CREATE OR REPLACE TRIGGER autoincrement_tr_ss
BEFORE INSERT 
ON Service_Staff 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT ss_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Hostel_Contracts
(
	id NUMBER not null primary key,
	Staff_id NUMBER not null,
	Room_Number NUMBER not null,
	In_date DATE not null,
	Out_date DATE 
);

CREATE sequence contract_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_contract
BEFORE INSERT 
ON Hostel_Contracts 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT contract_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Dyte_Time
(
	id NUMBER not null primary key,
	SS_id NUMBER not null,
	Departament_id NUMBER not null,
	Dyte_date DATE not null
);

CREATE sequence dyte_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_dyte
BEFORE INSERT 
ON Dyte_Time 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT dyte_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Research
(
	id NUMBER not null primary key,
	Departament_id NUMBER not null,
	Title VARCHAR2(50 CHAR) not null,
	Topic_id NUMBER not null,
	Start_date DATE not null
);

CREATE sequence research_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_research
BEFORE INSERT 
ON Research 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT research_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Research_Topics
(
	id NUMBER not null primary key,
	Topic VARCHAR2(50 CHAR) not null
);

CREATE sequence topic_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_topic
BEFORE INSERT 
ON Research_Topics 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT topic_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Researchers
(
	id NUMBER not null primary key,
	Research_id NUMBER not null,
	Scien_id NUMBER not null,
	Research_role_id NUMBER not null
);
 
CREATE sequence researcher_id_seq
	start with 1
	increment by 1
	NOCACHE;
 
CREATE OR REPLACE TRIGGER autoincrement_tr_researcher
BEFORE INSERT 
ON Researchers 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT researcher_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Research_Roles
(
	id NUMBER not null primary key,
	Research_role VARCHAR2(50 CHAR) not null
);

CREATE sequence research_role_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_research_role
BEFORE INSERT 
ON Research_Roles 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT researcher_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Artifacts_Storage
(
	id NUMBER not null primary key,
	Name VARCHAR2(50 CHAR) not null,
	Entrance_date DATE not null,
	Creation_date DATE,
	Description VARCHAR2(500 CHAR),
	Status_id NUMBER not null
);

CREATE sequence artifact_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_artifacts
BEFORE INSERT 
ON Artifacts_Storage 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT artifact_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Artifact_Status
(
	id NUMBER not null primary key,
	Status VARCHAR2(50 CHAR) not null
);

CREATE sequence art_status_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_art_status
BEFORE INSERT 
ON Artifact_Status 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT art_status_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Artifacts_Delivery_List
(
	id NUMBER not null primary key,
	Artifact_id NUMBER not null,
	Scien_id NUMBER not null,
	Get_date DATE not null,
	Return_date DATE
);

CREATE sequence deliv_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_deliv
BEFORE INSERT 
ON Artifacts_Delivery_List 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT deliv_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Vivarium
(
	id NUMBER not null primary key,
	Name VARCHAR2(50 CHAR) not null,
	Race_id NUMBER not null
);

CREATE sequence creature_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_creature
BEFORE INSERT 
ON Vivarium 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT creature_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE Food
(
	id NUMBER not null primary key,
	Food VARCHAR2(50 CHAR) not null
);

CREATE sequence food_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_food
BEFORE INSERT 
ON Food 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT food_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Ration
(
	id NUMBER not null primary key,
	Food_id NUMBER not null,
	Creature_id NUMBER not null
);

CREATE sequence ration_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_ration
BEFORE INSERT 
ON Ration 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT ration_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Documents_Flow
(
	id NUMBER not null primary key,
	Doctype_id NUMBER not null,
	In_date DATE not null,
	By_pers_id NUMBER not null,
	To_pers_id NUMBER not null
);

create sequence docflow_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_docflow
BEFORE INSERT 
ON Documents_Flow 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT docflow_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Doctypes
(
	id NUMBER not null primary key,
	Doctype VARCHAR2(50 CHAR) not null
);

create sequence doctype_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_doctype
BEFORE INSERT 
ON Doctypes 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT doctype_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Library
(
	id NUMBER not null primary key,
	Title VARCHAR2(50 CHAR) not null,
	Author VARCHAR2(50 CHAR) not null,
	Subject VARCHAR2(50 CHAR) not null,
	Box_number NUMBER not null
);

create sequence book_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_book
BEFORE INSERT 
ON Library 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT book_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/
 
CREATE TABLE Books_Delivery_List
(
	id NUMBER not null primary key,
	ISBN NUMBER not null,
	Staff_id NUMBER not null,
	Get_date DATE not null,
	Return_date DATE 
);

create sequence bdl_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_bdl
BEFORE INSERT 
ON Books_Delivery_List 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT bdl_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/

CREATE TABLE SRI_Departaments
(
	id NUMBER not null primary key,
	Name VARCHAR2(50 CHAR) not null,
	Address VARCHAR2(50 CHAR) not null, 
	Foundation_date DATE not null
);

create sequence SRI_dep_id_seq
	start with 1
	increment by 1
	NOCACHE;

CREATE OR REPLACE TRIGGER autoincrement_tr_sri_dep
BEFORE INSERT 
ON SRI_Departaments 
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
	SELECT SRI_dep_id_seq.NEXTVAL
	INTO :NEW.id 
	FROM DUAL;
END;
/