CREATE TABLE StudGroupNew (
    name VARCHAR(10) PRIMARY KEY,
    YEAR SMALLINT NOT NULL,
    kafedra VARCHAR(10) NOT NULL
);

CREATE TABLE StudentNew (
    id BIGINT PRIMARY KEY,
    FIO VARCHAR(50) NOT NULL,
    BirthDate DATE NOT NULL,
    Gender CHAR(1) NOT NULL CHECK (Gender = 'M' OR GENDER = 'F') DEFAULT 'F',
    GroupName VARCHAR(10) NOT NULL REFERENCES StudGroupNew(name),
    Salary INT,
    UNIQUE (GroupName, FIO)
);

// -----------------

CREATE TABLE StudGroup (
    name VARCHAR(10) PRIMARY KEY,
    YEAR SMALLINT NOT NULL,
    kafedra VARCHAR(10) NOT NULL
);

CREATE TABLE Student (
    id BIGINT PRIMARY KEY,
    fio VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    gender CHAR(1) NOT NULL CHECK (gender = 'M' OR gender = 'F') DEFAULT 'F',
    group_name VARCHAR(10) NOT NULL REFERENCES StudGroup(name),
    salary INT
);

CREATE TABLE Lecturer (
    id BIGINT PRIMARY KEY,
    fio VARCHAR(50) NOT NULL,
    EXP INT NOT NULL DEFAULT 0,
    kafedra VARCHAR(10) NOT NULL
);

CREATE TABLE Exam (
    id_student BIGINT NOT NULL REFERENCES Student(id),
    subject VARCHAR(15) NOT NULL,
    mark INT,
    exam_date DATE NOT NULL,
    id_lect BIGINT NOT NULL REFERENCES Lecturer(id),
    PRIMARY KEY (id_student, subject)
);

// ------------

CREATE TABLE LecturerNew (
    id BIGINT PRIMARY KEY,
    fio VARCHAR(50) NOT NULL,
    EXP INT CHECK (EXP <= 80),
    kafedra VARCHAR(10) NOT NULL,
    UNIQUE (fio, kafedra)
);

CREATE TABLE ExamNew (
    id_student BIGINT NOT NULL REFERENCES Migunov1.dbo.Student(id),
    subject VARCHAR(50) NOT NULL,
    mark INT CHECK (mark >= 25 AND mark <= 100) DEFAULT 25,
    exam_date DATE DEFAULT GETDATE(),
    id_lecturer BIGINT NOT NULL REFERENCES LecturerNew(id),
    PRIMARY KEY (id_student, subject),
    UNIQUE (id_student, exam_date)
);

CREATE TABLE StudGroupNew (
    name VARCHAR(10) PRIMARY KEY,
    YEAR SMALLINT CHECK (YEAR >= 1 AND YEAR <= 6) DEFAULT 1,
    kafedra VARCHAR(10)
);

//--------------------
DROP TABLE SubjectLect5;
CREATE TABLE SubjectLect5 (
    id_lect BIGINT NOT NULL REFERENCES Migunov1.dbo.Lecturer(id),
    name_subject VARCHAR(15) NOT NULL,
    PRIMARY KEY (id_lect, name_subject)
);

DROP TABLE SubjectLect6;
CREATE TABLE SubjectLect6 (
    id_lect BIGINT NOT NULL,
    name_subject VARCHAR(15) NOT NULL,
    PRIMARY KEY (id_lect, name_subject),
    FOREIGN KEY (id_lect) REFERENCES Migunov1.dbo.Lecturer(id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);

DROP TABLE ExamTest;
CREATE TABLE ExamTest (
    id_student BIGINT NOT NULL,
    subject VARCHAR(10) NOT NULL,
    mark INT,
    exam_date DATE,
    PRIMARY KEY (id_student, subject),
    FOREIGN KEY (id_student) REFERENCES Migunov1.dbo.Student(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

DROP TABLE StudGroupTest;
CREATE TABLE StudGroupTest (
    name VARCHAR(10) PRIMARY KEY,
    YEAR SMALLINT CHECK (YEAR >= 1 AND YEAR <= 6) DEFAULT 1,
    kafedra VARCHAR(10),
    kurator BIGINT,
    FOREIGN KEY (kurator) REFERENCES Migunov1.dbo.Lecturer(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

ALTER TABLE StudGroupTest
ADD starosta BIGINT;
ALTER TABLE StudGroupTest
ADD CONSTRAINT FK_starosta FOREIGN KEY (starosta) REFERENCES Migunov1.dbo.Student ON DELETE SET NULL;

ALTER TABLE StudGroupTest
DROP CONSTRAINT FK_starosta;
ALTER TABLE StudGroupTest
DROP COLUMN starosta;