cs4430 A5

drop database lab3;
create database lab3;
use lab3;

create table addresses(
  a_id     integer auto_increment primary key,
  street   varchar(20),
  zip      varchar(9),
  city     varchar(40),
  state    char(2)
) ENGINE = InnoDB;



create table instructor(
  fname      varchar(15),
  lname      varchar(15),
  win        varchar(9),
  primary key (win)
) ENGINE = InnoDB;


create table department(
  dno         char(4) primary key, 
  dname       varchar(30) ,
  building    varchar(30),
  room        int,
  ophone      varchar(15),
  college     varchar(30),
  win         varchar(9),
  foreign key (win) references instructor(win)
) ENGINE = InnoDB;


create table student(
  fname      varchar(15),
  lname      varchar(15),
  win        varchar(9),
  caddress   integer,
  cphone     varchar(15), 
  paddress   integer,
  pphone     varchar(15),
  birthdate  date,
  sex        enum('male', 'female', 'NA'),
  level      enum('freshman','sophomore','junior','senior','master','phd'),
  major      char(4),
  dno        char(4),
  primary key (win),
  foreign key (caddress) references addresses(a_id),
  foreign key (paddress) references addresses(a_id),
  foreign key (dno) references department(dno)
) ENGINE = InnoDB;


create table catalog(
  cno           varchar(10),
  cname         varchar(30),
  description   varchar(30),
  chours        varchar(5),
  dno           char(4),
  primary key (dno, cno),
  foreign key (dno) references department(dno)
) ENGINE = InnoDB;

 
create table sections(
  sno         varchar(10),
  win         varchar(9),
  semester    varchar(9),
  year        char(4),
  cno         varchar(10),
  dno         char(4),
  primary key (sno, cno, dno),
  foreign key (win) references instructor(win),
  foreign key (cno,dno) references catalog(cno,dno)
) ENGINE = InnoDB;

ERROR 2013 (HY000): Lost connection to MySQL server during query