create table state (
  id smallint unsigned not null auto_increment,
  name char(2) not null,
  primary key (id)
);

create table district (
  id int unsigned not null auto_increment,
  name varchar(60) not null,
  address varchar(60),
  city varchar(20),
  zipcode char(5),
  phone char(9),
  stateid smallint unsigned not null references state(id),
  primary key (id)
);

create table school (
  id int unsigned not null auto_increment,
  name varchar(60) not null,
  address varchar(60),
  city varchar(20),
  zipcode char(5),
  phone char(9),
  districtid int unsigned not null references district(id),
  primary key (id)
);


create table instructor (
  id int unsigned not null auto_increment,
  lastname varchar(60) not null,
  firstname varchar(60) not null,
  schoolid int unsigned not null references school(id),
  primary key (id)
);

create table class (
  id int unsigned not null auto_increment,
  name varchar(20),
  grade tinyint unsigned,
  instructorid int unsigned not null references instructor(id),
  primary key (id)
);

create table student (
  id int unsigned not null auto_increment,
  lastname varchar(60) not null,
  firstname varchar(60) not null,
  primary key (id)
);

create table student_enrollment (
  id int unsigned not null auto_increment,
  studentid int unsigned not null references student(id),
  classid int unsigned not null references class(id),
  primary key (id)
);