---

CREATE DATABASE RaspisanieDB;
GO

USE RaspisanieDB;
GO

-- =========================
-- СОЗДАНИЕ ТАБЛИЦ
-- =========================

CREATE TABLE Prepodavateli (
    prepodavatel_id INT IDENTITY PRIMARY KEY,
    fio NVARCHAR(100) NOT NULL
);

CREATE TABLE DniNedeli (
    den_id INT IDENTITY PRIMARY KEY,
    nazvanie NVARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Discipliny (
    disciplina_id INT IDENTITY PRIMARY KEY,
    nazvanie NVARCHAR(100) NOT NULL
);

CREATE TABLE TipyZanyatiy (
    tip_id INT IDENTITY PRIMARY KEY,
    nazvanie NVARCHAR(50) NOT NULL
);

CREATE TABLE Gruppy (
    gruppa_id INT IDENTITY PRIMARY KEY,
    nomer NVARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Raspisanie (
    raspisanie_id INT IDENTITY PRIMARY KEY,
    prepodavatel_id INT NOT NULL,
    den_id INT NOT NULL,
    nomer_pary INT NOT NULL CHECK (nomer_pary > 0),
    disciplina_id INT NOT NULL,
    tip_id INT NOT NULL,
    gruppa_id INT NOT NULL,
    auditoriya NVARCHAR(20) NOT NULL,
    vremya TIME NOT NULL,

    FOREIGN KEY (prepodavatel_id) REFERENCES Prepodavateli(prepodavatel_id),
    FOREIGN KEY (den_id) REFERENCES DniNedeli(den_id),
    FOREIGN KEY (disciplina_id) REFERENCES Discipliny(disciplina_id),
    FOREIGN KEY (tip_id) REFERENCES TipyZanyatiy(tip_id),
    FOREIGN KEY (gruppa_id) REFERENCES Gruppy(gruppa_id)
);

-- =========================
-- ЗАПОЛНЕНИЕ СПРАВОЧНИКОВ
-- =========================

INSERT INTO Prepodavateli (fio) VALUES
('Petrov V.I.'), ('Kirov V.A.'), ('Serov A.A.'),
('Ivanov I.I.'), ('Sidorov P.P.'), ('Smirnov A.V.'),
('Kuznetsov D.A.'), ('Popov N.N.'), ('Volkov E.S.'), ('Fedorov M.K.');

INSERT INTO DniNedeli (nazvanie) VALUES
('Ponedelnik'), ('Vtornik'), ('Sreda'), ('Chetverg'), ('Pyatnica');

INSERT INTO Discipliny (nazvanie) VALUES
('Teoriya vychislitelnyh processov'),
('Kompyuternaya grafika'),
('Teoriya informatiki'),
('Programmirovanie na C++'),
('Zashchita informacii'),
('Bazy dannyh'),
('Setevye tehnologii');

INSERT INTO TipyZanyatiy (nazvanie) VALUES
('Lekciya'), ('Laboratornaya rabota'), ('Praktika');

INSERT INTO Gruppy (nomer) VALUES
('4906'), ('4907'), ('4944'), ('4942'), ('4922');

-- =========================
-- ЗАПОЛНЕНИЕ 200 СТРОК
-- =========================

DECLARE @i INT = 1;

WHILE @i <= 200
BEGIN
    INSERT INTO Raspisanie (
        prepodavatel_id,
        den_id,
        nomer_pary,
        disciplina_id,
        tip_id,
        gruppa_id,
        auditoriya,
        vremya
    )
    VALUES (
        (ABS(CHECKSUM(NEWID())) % 10) + 1,
        (ABS(CHECKSUM(NEWID())) % 5) + 1,
        (ABS(CHECKSUM(NEWID())) % 4) + 1,
        (ABS(CHECKSUM(NEWID())) % 7) + 1,
        (ABS(CHECKSUM(NEWID())) % 3) + 1,
        (ABS(CHECKSUM(NEWID())) % 5) + 1,
        CONCAT('Aud-', (ABS(CHECKSUM(NEWID())) % 50) + 100),
        DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 600), '08:00')
    );

    SET @i = @i + 1;
END;

-- =========================
-- ПРОВЕРКА
-- =========================

SELECT * FROM Raspisanie;