create table pupils
( sename varchar(15),
  name   varchar(15),
  form   varchar(3),
  no     varchar(9), 
  primary key (no)
);

create table classroom
( building    varchar(15),
  room_number varchar(3),
  capacity    tinyint unsigned,
  primary key (building, room_number)
);

create table department
( dept_name varchar(20), 
  persons tinyint unsigned check (persons > 0),
  primary key (dept_name)
);

create table teachers
( sename  varchar(15),
  name    varchar(15),
  quality varchar(20),
  no      varchar(9),
  primary key (no)
);

create table courses
( title      varchar(15),
  form       char(3),
  no         varchar(9),
  hours      smallint unsigned,
  dept_name  varchar(15),
  primary key (title,form),
  foreign key (no) references teachers(no)
  on delete set null
);

create table marks
( course  varchar(15),
  form    varchar(3),
  sename  varchar(15),
  name    varchar(15),
  day     date,
  mark    tinyint unsigned check (mark < 13),
  type    ENUM ('контрольна','самостійна','усна','домашня'),
  primary key (course,form,sename,name,day,type)
) engine=InnoDB default charset=utf8;

ALTER TABLE pupils     CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE classroom  CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE department CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE teachers   CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE courses    CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE marks      CHARACTER SET utf8 COLLATE utf8_unicode_ci;