---

CREATE TABLE Employee2 (
    id_employee INT PRIMARY KEY IDENTITY(1,1),
    full_name NVARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    address NVARCHAR(150),
    phone NVARCHAR(20),
    passport_data NVARCHAR(50),
    email NVARCHAR(100)
);
GO

CREATE TABLE Employer2 (
    id_employer INT PRIMARY KEY IDENTITY(1,1),
    company_name NVARCHAR(150) NOT NULL,
    inn NVARCHAR(20) NOT NULL,
    address NVARCHAR(150),
    phone NVARCHAR(20)
);
GO

CREATE TABLE HR2 (
    id_hr INT PRIMARY KEY IDENTITY(1,1),
    full_name NVARCHAR(100) NOT NULL,
    position NVARCHAR(100),
    phone NVARCHAR(20),
    email NVARCHAR(100),
    id_employer INT NOT NULL,
    FOREIGN KEY (id_employer) REFERENCES Employer2(id_employer)
);
GO

CREATE TABLE Employment_record2 (
    id_record_book INT PRIMARY KEY IDENTITY(1,1),
    record_number NVARCHAR(20) NOT NULL,
    issue_date DATE NOT NULL,
    closing_date DATE NULL,
    status NVARCHAR(30),
    format NVARCHAR(20)
);
GO

CREATE TABLE Employee_record_book2 (
    id_link INT PRIMARY KEY IDENTITY(1,1),
    id_employee INT NOT NULL,
    id_record_book INT NOT NULL,
    FOREIGN KEY (id_employee) REFERENCES Employee2(id_employee),
    FOREIGN KEY (id_record_book) REFERENCES Employment_record2(id_record_book)
);
GO

CREATE TABLE EmploymentBookRecords2 (
    id_entry INT PRIMARY KEY IDENTITY(1,1),
    entry_date DATE NOT NULL,
    entry_type NVARCHAR(50),
    position NVARCHAR(100),
    department NVARCHAR(100),
    document_number NVARCHAR(30),
    id_record_book INT NOT NULL,
    id_employer INT NOT NULL,
    FOREIGN KEY (id_record_book) REFERENCES Employment_record2(id_record_book),
    FOREIGN KEY (id_employer) REFERENCES Employer2(id_employer)
);
GO