marks

CREATE TABLE marks.Students (
                Record_Book_Number INT(12) NOT NULL,
                Name CHAR(16) NOT NULL,
                Surname CHAR(16) NOT NULL,
                Patronymic CHAR(16) NOT NULL,
                Birthday DATE NOT NULL,
                Group_number CHAR(10) NOT NULL
);

ALTER TABLE marks.Students ADD CONSTRAINT PRIMARY KEY (Record_Book_Number);

CREATE TABLE marks.Groups (
                Group_number CHAR(10) NOT NULL,
                Group_year_in INT(4) NOT NULL,
                Group_year_out INT(4) NOT NULL,
                Specialty_name CHAR(64) NOT NULL
);

ALTER TABLE marks.Groups ADD CONSTRAINT PRIMARY KEY (Group_number);

CREATE TABLE marks.Specialty (
                Specialty_name CHAR(64) NOT NULL
);

ALTER TABLE marks.Specialty ADD CONSTRAINT PRIMARY KEY (Specialty_name);

CREATE TABLE marks.Disciplines (
                Discipline_name CHAR(64) NOT NULL
);

ALTER TABLE marks.Disciplines ADD CONSTRAINT PRIMARY KEY (Discipline_name);

CREATE TABLE marks.Schedule (
                schedule_id INT(10) NOT NULL,
                course INT(1) NOT NULL,
                term INT(1) NOT NULL,
                Discipline_name CHAR(64) NOT NULL,
                certificate INT(12) NOT NULL,
                exam_type CHAR(25) NOT NULL,
                Group_number CHAR(10) NOT NULL
);

ALTER TABLE marks.Schedule ADD CONSTRAINT PRIMARY KEY (schedule_id);

CREATE TABLE marks.Teachers (
                certificate INT(12) NOT NULL,
                patronymic CHAR(16) NOT NULL,
                name CHAR(16) NOT NULL,
                surname CHAR(16) NOT NULL
);

ALTER TABLE marks.Teachers ADD CONSTRAINT PRIMARY KEY (certificate);

CREATE TABLE marks.Marks (
                mark_id INT(10) NOT NULL,
                Mark_value INT(10) NOT NULL,
                schedule_id INT(10) NOT NULL,
                Record_Book_Number INT(12) NOT NULL
);

ALTER TABLE marks.Marks ADD CONSTRAINT PRIMARY KEY (mark_id);

ALTER TABLE marks.Marks ADD CONSTRAINT Students_Marks_fk
FOREIGN KEY (Record_Book_Number)
REFERENCES marks.Students (Record_Book_Number);

ALTER TABLE marks.Students ADD CONSTRAINT Groups_Students_fk
FOREIGN KEY (Group_number)
REFERENCES marks.Groups (Group_number);

ALTER TABLE marks.Schedule ADD CONSTRAINT Groups_Schedule_fk
FOREIGN KEY (Group_number)
REFERENCES marks.Groups (Group_number);

ALTER TABLE marks.Groups ADD CONSTRAINT Specialty_Groups_fk
FOREIGN KEY (Specialty_name)
REFERENCES marks.Specialty (Specialty_name);

ALTER TABLE marks.Schedule ADD CONSTRAINT Disciplines_Schedule_fk
FOREIGN KEY (Discipline_name)
REFERENCES marks.Disciplines (Discipline_name);

ALTER TABLE marks.Marks ADD CONSTRAINT Schedule_Marks_fk
FOREIGN KEY (schedule_id)
REFERENCES marks.Schedule (schedule_id);

ALTER TABLE marks.Schedule ADD CONSTRAINT Teachers_Schedule_fk
FOREIGN KEY (certificate)
REFERENCES marks.Teachers (certificate);