drop table departments cascade constraints;
create table departments (
  university varchar2(40),
  dname  varchar2(40),
  primary key (university,dname)
);

drop table programs cascade constraints;
create table programs (
  university varchar2(40),
  dname    varchar2(40),
  program  varchar2(10) check (program in ('MS','PhD')),
  primary key (university,dname,program),
  foreign key (university,dname) references departments
);

drop table applicant cascade constraints;
create table applicant (
  ssn      number(9),
  email    varchar2(40),
  fname    varchar2(20),
  lname    varchar2(20),
  address1 varchar2(40),
  address2 varchar2(40),
  city     varchar2(40),
  state    varchar2(40),
  zip      number(5),
  GREQ    number(3),
  GREV    number(3),
  GREA    number(3,1),
  TOEFL    number(3),
  primary key (email)
);

drop table programAppliedTo cascade constraints;
create table programAppliedTo (
  email        varchar2(40),
  university   varchar2(40),
  dname        varchar2(40),
  program      varchar2(10) check (program in ('MS','PhD')),
  dateOfApp    date,
  termOfAdmission   char(2) check (termOfAdmission in ('FA','SP','SU')),
  yearOfAdmission number(4),
  admissionStatus varchar2(10) check (admissionStatus in ('ACCEPT','REJECT')),
  dataSentToGoLunar char(3) check (dataSentToGoLunar in ('Yes','No')),
  primary key (email,university,dname,program),
  foreign key (university,dname,program) references programs
);
