CREATE TABLE Type_instrumenta
(
    Kod_type_instrumenta INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Nazvanie_type_instrumenta VARCHAR(20) NOT NULL DEFAULT 'akzii',
    UNIQUE (Nazvanie_type_instrumenta),
    CHECK (Nazvanie_type_instrumenta IN ('akzii', 'futures'))
);
 
CREATE TABLE Instruments
(
    Kod_instrumenta INT NOT NULL PRIMARY KEY IDENTITY(2,3),
    Nazvanie_instrumenta VARCHAR(20) NOT NULL,
    Kod_Type_instrumenta INT NOT NULL DEFAULT 'akzii',
    CHECK (Nazvanie_instrumenta LIKE CASE WHEN Nazvanie_type_instrumenta = 'futures' THEN '[a-zA-Zа-яА-Я][a-zA-Zа-яА-Я]-[0-9].' + RIGHT(str(YEAR(getdate())), 2) ELSE '%' END),
    FOREIGN KEY (Kod_type_instrumenta) REFERENCES Type_instrumenta(Kod_type_instrumenta) ON DELETE cascade
);
 
CREATE TABLE Schet
(
    Nomer_scheta INT NOT NULL PRIMARY KEY IDENTITY(10000000,1) CHECK (Nomer_scheta <= 99999999),
    familia VARCHAR(20) NOT NULL,
    imya VARCHAR(20) NOT NULL,
    otchestvo VARCHAR(20) NOT NULL
);
 
CREATE TABLE Sdelki
(
    Kod_sdelki INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    nomer_scheta INT NOT NULL,
    FOREIGN KEY (nomer_scheta) REFERENCES Schet(nomer_scheta) ON DELETE cascade,
    datavremya_otkrytiya datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    datavremya_zakrytiya datetime NULL,
    kolichestvo REAL CHECK (kolichestvo>0),
    Kod_instrumenta INT NOT NULL,
    FOREIGN KEY (Kod_instrumenta) REFERENCES Instruments(Kod_instrumenta) ON DELETE cascade,
    napravlenie_sdelki VARCHAR(10) NOT NULL DEFAULT 'long' 
    CHECK (napravlenie_sdelki IN ('long', 'short')),
    CHECK (datavremya_zakrytiya > datavremya_otkrytiya)
);