/*

Создание тестовой базы данных Kafedra



BEGIN;
DROP TABLE lectures;
DROP TABLE students;
DROP TABLE semestrs;
DROP TABLE courses;
DROP TABLE balls;
COMMIT;
*/

BEGIN;

/* Лекторы  */

CREATE TABLE lectures (
    id_lect  INTEGER PRIMARY KEY,
    surname  VARCHAR(50) NOT NULL,
    name     VARCHAR(20) NOT NULL,
    patronim VARCHAR(50) NOT NULL,
    phone    VARCHAR(20) NOT NULL,
    mail     VARCHAR(20) NOT NULL,
    position VARCHAR(50) NOT NULL
);

COMMENT ON TABLE lectures IS 'Преподаватели кафедры';
COMMENT ON COLUMN lectures.id_lect IS 'Идентификатор преподавателя';
COMMENT ON COLUMN lectures.surname  IS 'Фамилия';
COMMENT ON COLUMN lectures.name  IS 'Имя';
COMMENT ON COLUMN lectures.patronim  IS 'Отчество';
COMMENT ON COLUMN lectures.phone  IS 'Телефон';
COMMENT ON COLUMN lectures.mail  IS 'Адрес электронной почты';
COMMENT ON COLUMN lectures.position  IS 'Должность';

INSERT INTO lectures VALUES (1,'Тлисова','Светлана','Магомедовна','8909495','Tlisova@mail.ru','Зав. кафедры ПОВТ');
INSERT INTO lectures VALUES (2,'Тлисов','Азамат','Борисович','8909495','Tlisov@mail.ru','Доцент кафедры ПОВТ');
INSERT INTO lectures VALUES (3,'Сидоров','Сидор','Сидорович','8909495','Сидоров@mail.ru','Препод. кафедры ПОВТ');

/*
/* Студенты */

CREATE TABLE students (
    id_student INTEGER PRIMARY KEY,
    surname    VARCHAR(50) NOT NULL,
    name       VARCHAR(20) NOT NULL,
    patronim   VARCHAR(50) NOT NULL,
    phone      VARCHAR(20) NOT NULL,
    mail       VARCHAR(20) NOT NULL,
    id_kaf     VARCHAR(6)  NOT NULL,
    groups      INTEGER
);

COMMENT ON TABLE students  IS 'Студенты кафедры';
COMMENT ON TABLE students.id_student IS 'Номер зачетки';
COMMENT ON TABLE students.surname  IS 'Фамилия';
COMMENT ON TABLE students.name  IS 'Имя';
COMMENT ON TABLE students.patronim  IS 'Отчество';
COMMENT ON TABLE students.phone  IS 'Телефон';
COMMENT ON TABLE students.mail  IS 'Адрес электронной почты';
COMMENT ON TABLE students.id_kaf IS 'Кафедра';
COMMENT ON TABLE students.groups IS 'Группа';

INSERT INTO students VALUES (1,"Иванов","Иван","Иванович","2345-567","Ivanov@mail.ru","ПО",2041);
INSERT INTO students VALUES (2,"Сидорова","Татьяна","Ивановна","2345-567","Sid@mail.ru","ПО",2041);
INSERT INTO students VALUES (1,"Джексон","Майкл","Иванович","2345-567","Jackson@mail.ru","ПО",2041);

/* Курсы */

CREATE TABLE courses (
    id_courses INTEGER PRIMARY KEY,
    course     VARCHAR (200) NOT NULL,
    lect       SERIAL REFERENCES lectures (id_lect)
		      ON UPDATE CASCADE
);
COMMENT ON TABLE courses IS 'Названия курсов';
COMMENT ON TABLE courses.id_courses IS 'Идентификатор курса';
COMMENT ON TABLE courses.courses IS 'Название курса';

INSERT INTO courses VALUES (1,"Высшая математика",1);
INSERT INTO courses VALUES (2,"Базы данных",1);
INSERT INTO courses VALUES (3,"Программирование",2);
INSERT INTO courses VALUES (4,"Английский язык",3);


/* Предметы */

CREATE TABLE semestrs (
    nsubsem    INTEGER PRIMARY KEY,
    nsemestr   INTEGER NOT NULL,
    id_subject INTEGER REFERENCES courses(id_courses) ON UPDATE CASCADE,
    id_lect    INTEGER REFERENCES lectures(id_lect) ON UPDATE CASCADE,
    zachet     INTEGER,
    ekzamen    INTEGER,
    kursovaja  INTEGER
);

COMMENT ON TABLE semestrs IS 'Курсы по семестрам';
COMMENT ON TABLE semestrs.nsubsem_subject IS 'Номер курса в семестре';
COMMENT ON TABLE semestrs.nsemestr IS 'Номер семестра';

INSERT INTO semestrs VALUES (1,1,1,1,1,1,0);
INSERT INTO semestrs VALUES (2,1,2,1,1,1,0);
INSERT INTO semestrs VALUES (3,1,3,2,1,1,0);
INSERT INTO semestrs VALUES (4,1,4,3,1,1,0);
INSERT INTO semestrs VALUES (5,2,1,1,1,1,0);
INSERT INTO semestrs VALUES (6,2,2,1,1,1,0);
INSERT INTO semestrs VALUES (7,2,3,2,1,1,0);
INSERT INTO semestrs VALUES (8,2,4,3,1,1,0);

CREATE TABLE zachetka (
    nzach INTEGER REFERENCES students(id_student),
    nsubsem INTEGER REFERENCES semestrs(nsunsem),
    zachet INTEGER,
    ekzamen INTEGER,
    kursovaja INTEGER
);

INSERT INTO zachetka VALUES (1,1,1,4,NULL);
INSERT INTO zachetka VALUES (1,2,1,NULL,NULL);
INSERT INTO zachetka VALUES (1,3,1,NULL,NULL);
INSERT INTO zachetka VALUES (2,4,1,NULL,NULL);
INSERT INTO zachetka VALUES (2,1,1,3,NULL);
INSERT INTO zachetka VALUES (2,2,1,NULL,NULL);
INSERT INTO zachetka VALUES (2,3,1,NULL,NULL);
INSERT INTO zachetka VALUES (2,4,1,NULL,NULL);
INSERT INTO zachetka VALUES (3,1,1,4,NULL);
INSERT INTO zachetka VALUES (3,2,1,NULL,NULL);
INSERT INTO zachetka VALUES (3,3,1,NULL,NULL);
INSERT INTO zachetka VALUES (3,4,1,NULL,NULL);
*/

COMMIT;