create database test
go 
use [ts2]
go



	   --4--


		     create table education

   ( id_education int not null primary key,
     level_of_education varchar (500) not null,
	 qualification_improvement varchar (500) not null,
	  specialization varchar (500) not null,

	  )

	  --3.1--
	   create table Surcharge
   (    id_surcharge int not null primary key ,
        name_surcharge varchar (500) not null,
	      amount_surcharge varchar (500) not null,
	       ) ;

	 --3--
   create table salary (
   id_salary int not null primary key ,
   id_surcharge int not null foreign key references Surcharge (id_surcharge)  ,
   id_position int not null foreign key references Position (id_position), 
    id_employee int not null foreign key references personal_information (id_employee),		
   number_card int not null,
     secondment int not null,
	   vacation int not null,
	     grace int not null,
		   bonus int not null,
		    amendment_to_act varchar (100) not null,
                
     ); 

	 --2.3--

	  create table vacancy
   (   id_vacancy int not null primary key,
        Duties varchar (500) not null,
		 requirements varchar (500) not null,
	     open_position  varchar (500) not null,
	      ) 	

		--2.2.1-- 

		create table work_expirience
		(
		id_work_experience  int  not null primary key,
		 seniority int not null, 
		 general_expirience int not null
		)

 --2.2-- 

 
  create table working_activity
   ( id_work_activity int not null primary key,
     number_decree varchar (500) not null,
	id_work_experience  int  not null foreign key references work_expirience (id_work_experience) ,
	date_of_admission date  not null,
	date_of_dismissal date not null,
    promotion varchar (500) not null,
	 )

--2.1--

	 create table position
   ( id_position int not null primary key,
     id_salary int not null foreign key references salary(id_salary),

    position_name varchar (500) not null,
	 )

--2--
   create table department
  ( id_department INT NOT NULL primary key,
    id_position int not null foreign key references position (id_position) , 
	id_work_activity int not null foreign key references working_activity (id_work_activity) ,
	id_vacancy int  not null foreign key references vacancy(id_vacancy),
    department_name varchar (500) not null,
	 department_phone varchar (500) not null,
	 department_head varchar (500) not null,
	  )

create table employee

(    id_employee INT NOT NULL primary key, 
     id_department INT NOT NULL foreign key references  department (id_department),
     id_salary int not null foreign key references salary (id_salary) ,
     id_education INT NOT NULL foreign key references education (id_education) ,
    

  ) ;

	  --1--
	 
    create table personal_information 

   ( id_employee int not null foreign key references employee (id_employee) ,
     id_presence_of_children int not null Primary key,
     Surname varchar (500) not null,
	 Name varchar (500) not null,
	 Patronymic varchar (100) not null,
	 Age date  not null,
	 Home_address varchar (500) not null,
	 Phone_number int  not null,
	 marital_status varchar (500) not null,
	 passport_data varchar (500) not null,
	 education varchar (500) not null,
	 citizenship varchar (500) not null,
	 gender varchar (500) not null,

    )

  --1.1--
    create table presence_of_children 
   ( id_presence_of_children  int not null foreign key references personal_information (id_presence_of_children), 
    amount_of_adult int not null,
	number_of_minor_child int not null,
	total_quantity int not null,
	)