tutors_script

drop database tutors;
create database tutors;
use tutors;

create table Persons (
  id          int                 not null auto_increment primary key,

  email       varchar(255) unique not null,
  login       varchar(255) unique not null,

  first_name  varchar(255),
  middle_name varchar(255),
  last_name   varchar(255)
);

create table Tutors (
  id        int not null primary key,
  person_id int not null unique, # is it good ??? it is not one to one?

  foreign key (person_id) references Persons (id)
    on delete cascade
);

create table Subjects (
  id   int          not null auto_increment primary key,
  name varchar(255) not null
);

create table TutorSubjects (
  tutor_id   int not null,
  subject_id int not null,

  foreign key (tutor_id) references Tutors (person_id)
    on delete cascade,
  foreign key (subject_id) references Subjects (id),

  primary key (tutor_id, subject_id)
);

create table TutorCertificates (
  tutor_id    int          not null,
  name        varchar(255) not null,
  description varchar(255),

  foreign key (tutor_id) references Tutors (id)
    on delete cascade,
  primary key (tutor_id, name)
);

create table Courses (
  id         int          not null auto_increment primary key,
  name       varchar(255) not null,
  subject_id int,

  foreign key (subject_id) references Subjects (id)
);

create table TutorCourses (
  tutor_id  int not null,
  course_id int not null,

  foreign key (tutor_id) references Tutors (id)
    on DELETE cascade,
  foreign key (course_id) references Courses (id),

  primary key (tutor_id, course_id)
);

create table Students (
  id        int not null auto_increment primary key,
  person_id int not null unique, # is it good ??? it is not one to one?

  foreign key (person_id) references Persons (id)
    on delete cascade
);

create table StudentCourses (
  student_id int not null,
  course_id  int not null,

  foreign key (student_id) references Students (id)
    on delete cascade,
  foreign key (course_id) references Courses (id),

  primary key (student_id, course_id)
);

create table Lesson (
  id         int not null auto_increment primary key,
  tutor_id   int not null,
  student_id int not null,
  course_id  int not null, # how to check validity in TutorCourses??
  time_id    int not null,
  paid_money double,

  foreign key (tutor_id) references Tutors (id),
  foreign key (student_id) references Students (id),
  foreign key (course_id) references Courses (id)
);

create table Time (
  id             int      not null auto_increment primary key,
  start_time     datetime not null,
  length_minutes int      not null,
  lesson_id      int      not null unique,

  foreign key (lesson_id) references Lesson (id)
    on delete cascade
);