create table lessons (
    id number not null,
    name varchar(100),
    time_start varchar(100),
    time_end varchar(200),
    constraint lesson_pk primary key (id)
);
create table days_week (
    id number not null,
    name varchar (50),
    constraint day_pk primary key (id)
);

create table facultys (
    id number not null,
    name varchar(100),
    CONSTRAINT pk_faculty PRIMARY key (id)
);

create table groups (
    id number not null,
    name varchar (50),
    faculty_id number,
    constraint group_pk primary key (id),
    CONSTRAINT fk_faculty
    FOREIGN KEY (faculty_id) REFERENCES facultys(id)
);
create table cind_lessons (
    id number not null,
    name varchar (100),
    constraint cind_pk primary key (id)
);
create table subjects (
    id number not null,
    name varchar (100),
    constraint subject_pk primary key (id)
);
create table corps (
    id  number not null,
    name varchar(100),
    CONSTRAINT pk_corps PRIMARY KEY (id)
);
create table students (
    id number not null,
    name varchar(100),
    group_id numeric,
    CONSTRAINT pk_student PRIMARY KEY (id),
    CONSTRAINT fk_student_group
    FOREIGN KEY (group_id) REFERENCES groups(id)
);
create table audiences (
    id number not null,
    name varchar (100),
    corp_id number not null,
    constraint audience_pk primary key (id),
    CONSTRAINT fk_corps
    FOREIGN KEY (corp_id) REFERENCES corps(id)
);
create table sheldule (
    id number not null,
    day_id number not null,
    lesson_id number not null,
    group_id number not null,
    subject_id number not null,
    cind_lesson_id number not null,
    audience_id number not null,
    day_date date,
    CONSTRAINT sheldule_pk PRIMARY KEY (id),
    CONSTRAINT fk_day_week
        FOREIGN key (day_id)
        references days_week (id),
    CONSTRAINT fk_lesson_id
        FOREIGN key (lesson_id)
        references lessons (id),
    CONSTRAINT fk_group_id
        FOREIGN key (group_id)
        references groups (id),
    CONSTRAINT fk_subject_id
        FOREIGN key (subject_id)
        references subjects (id),
    CONSTRAINT fk_cind_id
        FOREIGN key (cind_lesson_id)
        references cind_lessons (id),
    CONSTRAINT fk_audience_id
        FOREIGN key (audience_id)
        references audiences (id)
);

insert into lessons (id, name, time_start, time_end) values (1, 'Перша пара', '08:30', '09:50');
insert into lessons (id, name, time_start, time_end) values (2, 'Друга пара', '10:05', '11:25');
insert into lessons (id, name, time_start, time_end) values (3, 'Третя пара', '11:40', '13:00');
insert into lessons (id, name, time_start, time_end) values (4, 'Четверта пара', '14:00', '15:20');
insert into lessons (id, name, time_start, time_end) values (5, 'П^ята пара', '15:35', '16:55');
insert into lessons (id, name, time_start, time_end) values (6, 'Шоста пара', '17:10', '18:30');

insert into days_week (id, name) values (1, 'Понеділок');
insert into days_week (id, name) values (2, 'Вівторок');
insert into days_week (id, name) values (3, 'Середа');
insert into days_week (id, name) values (4, 'Четвер');
insert into days_week (id, name) values (5, 'П^ятниця');
insert into days_week (id, name) values (6, 'Субота');
insert into days_week (id, name) values (7, 'Неділля');

insert into facultys (id, name) VALUES(1, 'Факультет Електроніки та Інформаційних технологій');
insert into facultys (id, name) VALUES(2, 'Факультет Технічних систем та Енергоефективних технологій');
insert into facultys (id, name) VALUES(3, 'Факультет Іноземнох фалосовії та Соціальних комунікацій');
insert into facultys (id, name) VALUES(4, 'Навчально-науковий інститут права');
insert into facultys (id, name) VALUES(5, 'Навчально-науковий інститут бізнесу та економіки');
insert into facultys (id, name) VALUES(6, 'Навчально-науковий медичний інститут');

insert into  groups (id, name, faculty_id) values (1, 'ІН-94-1', 1);
insert into groups (id, name, faculty_id) values (2, 'ІН-91', 1);

insert into  cind_lessons (id, name) values (1, 'онлайн');
insert into  cind_lessons (id, name) values (2, 'лабораторна робота');
insert into  cind_lessons (id, name) values (3, 'лекція');
insert into  cind_lessons (id, name) values (4, 'атестація');

insert into subjects (id, name) values (1, 'Бази даних та інформац. сист.');
insert into subjects (id, name) values (2, 'Первинні мер. та мер.оператор');
insert into subjects (id, name) values (3, 'Військова підготовка');
insert into subjects (id, name) values (4, 'Обр. зобр та мультимедія');
insert into subjects (id, name) values (5, 'Серверні технологій та веб-програм.');
insert into subjects (id, name) values (6, 'Клієнтські технології та веб-програм.');
insert into subjects (id, name) values (7, 'Дисц. за вибр. студента');

insert into corps (id, name) values (1, 'Головний');
insert into corps (id, name) values (2, 'Новий');
insert into corps (id, name) values (3, 'Центральний');

insert into audiences (id, name, corp_id) values (1, '210', 1);
insert into audiences (id, name, corp_id) values (2, '310', 2);
insert into audiences (id, name, corp_id) values (3, '410', 3);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(1, 1, 1, 2, 2, 3, 1, to_date('2022-06-06','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(2, 1, 2, 2, 2, 4, 2, to_date('2022-06-06','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(3, 2, 1, 1, 2, 1, 2, to_date('2022-06-07','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(4, 2, 2, 2, 2, 2, 1,to_date('2022-06-07','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(5, 3, 3, 1, 2, 1, 3,to_date('2022-06-08','YYYY-MM-DD'));

insert into sheldule(id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(6, 3, 3, 1, 2, 2, 3,to_date('2022-06-08','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(7, 3, 3, 1, 2, 3, 3,to_date('2022-06-08','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(8, 3, 3, 1, 2, 4, 3,to_date('2022-06-08','YYYY-MM-DD'));

insert into sheldule(id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(9, 4, 2, 2, 2, 2, 4,to_date('2022-06-09','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(10, 4, 3, 2, 2, 3, 5,to_date('2022-06-09','YYYY-MM-DD'));

insert into sheldule(id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(11, 5, 1, 1, 2, 1, 7,to_date('2022-06-10','YYYY-MM-DD'));

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id, day_date)
VALUES(13, 5, 1, 1, 2, 2, 7,to_date('2022-06-10','YYYY-MM-DD'));