create table Groups
(
    id             int     not null unique,
    name           char(5) not null,
    admission_year int     not null,
    specialty      char(8) not null,
    primary key (id)
);
create table Persons
(
    id         int          not null unique,
    first_name varchar(30)  not null,
    last_name  varchar(30)  null,
    patronymic varchar(30)  null,
    mail       varchar(320) null,
    primary key (id)
);
create table Students
(
    grants    int not null,
    group_id  int not null,
    person_id int not null unique,
    primary key (person_id),
    foreign key (person_id) references Persons (id),
    foreign key (group_id) references Groups (id)
);
create table Subjects
(
    id          int         not null unique,
    name        varchar(30) not null,
    is_required boolean     not null,
    primary key (id)
);
create table Professors
(
    degree    varchar(30) not null,
    position  varchar(30) not null,
    salary    int         not null,
    person_id int         not null unique,
    primary key (person_id),
    foreign key (person_id) references Persons (id)
);
create table Studying
(
    year       int not null,
    semester   int not null,
    subject_id int not null,
    student_id int not null,
    primary key (subject_id, student_id),
    unique (subject_id, student_id),
    foreign key (subject_id) references Subjects (id),
    foreign key (student_id) references Students (person_id)
);
create table Grades
(
    value        char not null,
    subject_id   int  not null,
    student_id   int  not null,
    professor_id int  not null,
    primary key (subject_id, student_id),
    foreign key (subject_id) references Subjects (id),
    foreign key (student_id) references Students (person_id),
    foreign key (professor_id) references Professors (person_id),
    unique (subject_id, student_id, professor_id)
);
create table Teaching
(
    year         int not null,
    semester     int not null,
    subject_id   int not null,
    professor_id int not null,
    primary key (subject_id, professor_id),
    unique (subject_id, professor_id),
    foreign key (subject_id) references Subjects (id),
    foreign key (professor_id) references Professors (person_id)
);