---

CREATE DATABASE TrudovayaKnizhkaBD;
GO
USE TrudovayaKnizhkaBD;
GO

CREATE TABLE Sotrudnik (
    id_sotrudnika INT PRIMARY KEY IDENTITY(1,1),
    FIO NVARCHAR(100) NOT NULL,
    data_rozhdeniya DATE NOT NULL,
    adres NVARCHAR(150),
    telefon NVARCHAR(20),
    pasportnye_dannye NVARCHAR(50),
    email NVARCHAR(100)
);
GO

CREATE TABLE Rabotodatel (
    id_rabotodatelya INT PRIMARY KEY IDENTITY(1,1),
    nazvanie NVARCHAR(150) NOT NULL,
    inn NVARCHAR(20) NOT NULL,
    adres NVARCHAR(150),
    telefon NVARCHAR(20)
);
GO

CREATE TABLE Kadrovik (
    id_kadrovika INT PRIMARY KEY IDENTITY(1,1),
    FIO NVARCHAR(100) NOT NULL,
    dolzhnost NVARCHAR(100),
    telefon NVARCHAR(20),
    email NVARCHAR(100),
    id_rabotodatelya INT NOT NULL,
    FOREIGN KEY (id_rabotodatelya) REFERENCES Rabotodatel(id_rabotodatelya)
);
GO

CREATE TABLE Trudovaya_knizhka (
    id_trudovoy_knizhki INT PRIMARY KEY IDENTITY(1,1),
    nomer_knizhki NVARCHAR(20) NOT NULL,
    data_oformleniya DATE NOT NULL,
    data_zakrytiya DATE NULL,
    status NVARCHAR(30),
    format NVARCHAR(20)
);
GO

CREATE TABLE Sotrudnik_trudovaya_knizhka (
    id_sotrudnik_trudovaya_knizhka INT PRIMARY KEY IDENTITY(1,1),
    id_sotrudnika INT NOT NULL,
    id_trudovoy_knizhki INT NOT NULL,
    FOREIGN KEY (id_sotrudnika) REFERENCES Sotrudnik(id_sotrudnika),
    FOREIGN KEY (id_trudovoy_knizhki) REFERENCES Trudovaya_knizhka(id_trudovoy_knizhki)
);
GO

CREATE TABLE Zapis_trudovoy_deyatelnosti (
    id_zapisi INT PRIMARY KEY IDENTITY(1,1),
    data_zapisi DATE NOT NULL,
    tip_zapisi NVARCHAR(50),
    dolzhnost NVARCHAR(100),
    podrazdelenie NVARCHAR(100),
    nomer_dokumenta NVARCHAR(30),
    id_trudovoy_knizhki INT NOT NULL,
    id_rabotodatelya INT NOT NULL,
    FOREIGN KEY (id_trudovoy_knizhki) REFERENCES Trudovaya_knizhka(id_trudovoy_knizhki),
    FOREIGN KEY (id_rabotodatelya) REFERENCES Rabotodatel(id_rabotodatelya)
);
GO