set search_path='mda104';


create table student (
  uco int primary key,
  first_name varchar(100),
  last_name varchar(100)
);

insert into student values ( 1, 'Petr', 'Novak');
insert into student values ( 2, 'Jan', 'Novak');
insert into student values ( 3, 'Pavel', 'Dobry');
insert into student values ( 4, 'Michal', 'Dlouhy');
insert into student values ( 5, 'Tomas', 'Hrdina');
insert into student values ( 6, 'Antonin', 'Houzek');


create table course (
  code varchar(5) primary key, 
  title varchar(200) not null, 
  credits int, 
  type_of_completion varchar(2) check (type_of_completion IN ('z','zk','k'))
);

insert into course values ( 'MA101', 'Matematika základ', 1, 'z');
insert into course values ( 'MA102', 'Matematika I', 3, 'zk');
insert into course values ( 'PB154', 'DB', 3, 'zk');
insert into course values ( 'PB168', 'Zaklady DB a IS', 4, 'zk');
insert into course values ( 'PB007', 'Software Engineering', 4, 'zk');
insert into course values ( 'PV004', 'UNIX', 2, 'k');
insert into course values ( 'PV009', 'Zaklady programovani', 2, 'z');
insert into course values ( 'PV111', 'NoSQL', NULL, 'k');
insert into course values ( 'VB001', 'Anglictina test', 0, 'z');
insert into course values ( 'VB036', 'Anglictina', 2, 'zk');

create table seminar (
  code varchar(5) references course,
  number int,
  capacity int,  
  description varchar(100),
  primary key (code, number)
);

insert into seminar values ( 'MA102', 1, 25, 'Lichı tıden');
insert into seminar values ( 'MA102', 2, 30, 'Sudı tıden');
insert into seminar values ( 'MA102', 3, 30, 'Lichı tıden');
insert into seminar values ( 'MA102', 4, 35, 'Sudı tıden');
insert into seminar values ( 'MA102', 5, 6, 'Lichı tıden');
insert into seminar values ( 'PB154', 1, 40, 'Lichı tıden');
insert into seminar values ( 'PB154', 2, 40, 'Sudı tıden');
insert into seminar values ( 'PB154', 3, 40, 'Lichı tıden');
insert into seminar values ( 'PV004', 1, 10, 'Windows');
insert into seminar values ( 'PV004', 2, 5, 'Mac');

create table enrollment (
  uco int references student,
  code varchar(5) references course,
  type_of_completion varchar(2) check (type_of_completion IN ('z','zk','k')),
  primary key (uco,code)
);

insert into enrollment values ( 1, 'MA102', 'zk');
insert into enrollment values ( 1, 'PV004', 'k');
insert into enrollment values ( 2, 'PB168', 'zk');
insert into enrollment values ( 3, 'MA102', 'zk');
insert into enrollment values ( 3, 'PB154', 'zk');
insert into enrollment values ( 3, 'VB001', 'z');
insert into enrollment values ( 4, 'PB168', 'z');
insert into enrollment values ( 5, 'MA102', 'zk');
insert into enrollment values ( 5, 'PB168', 'zk');
insert into enrollment values ( 5, 'PV004', 'z');
insert into enrollment values ( 6, 'PB154', NULL);


create table prereq (
  code varchar(5) references course,
  prereq varchar(5) references course,
  primary key (code, prereq)
);

insert into prereq values ('MA102', 'MA101');
insert into prereq values ('PB168', 'PB007');
insert into prereq values ('PB168', 'MA101');
insert into prereq values ('VB036', 'VB001');
insert into prereq values ('PB007', 'MA101');
insert into prereq values ('PB007', 'PV009');
insert into prereq values ('PV111', 'PB154');


create table grading (
  uco int,
  code varchar(5),
  graded_on date,
  grade char(1) check (grade in ('A','B','C','D','E','F','X','-','P','N')),

  primary key (uco,code,graded_on),
  foreign key (uco,code) references enrollment
);

insert into grading values (2, 'PB168', '2023-12-14', 'F');
insert into grading values (2, 'PB168', '2024-01-08', 'C');
insert into grading values (4, 'PB168', '2024-01-08', 'A');
insert into grading values (3, 'VB001', '2023-12-09', 'X');
insert into grading values (5, 'PV004', '2023-12-18', 'A');
insert into grading values (5, 'PB168', '2024-02-01', 'E');
insert into grading values (3, 'MA102', '2023-12-20', 'F');
insert into grading values (3, 'MA102', '2023-01-19', 'F');
insert into grading values (3, 'MA102', '2023-02-14', 'D');