drop table cstudents cascade constraints;
create table cstudents (
  sid   number(9) primary key,
  fname varchar2(20),
  lname varchar2(20),
  email varchar2(40),
  degree varchar2(10) check constraint (degree in ('MS','PhD'))
);

drop table assistantships cascade constraints
create table assistantships (
  sid    number(9),
  term   char(2) check (term in ('FA','SP','SU')),
  year   number(4),
  amount number(5),
  primary key (sid,term,year),
  foreign key (sid) references cstudents
);

drop table ccourses cascade constraints;
create table ccourses (
  cprefix   char(4) default 'CSc',
  cno       number(4),
  ctitle    varchar2(40),
  hours     number(2),
  primary key (cprefix,cno)
);

drop table enrolls cascade constraints;
create table enrolls (
  sid      number(9),
  term     char(2) check (term in ('FA','SP','SU')),
  year     number(4),
  cprefix  char(4),
  cno      number(4),
  grade    char(2) check (grade in ('A','B','C','D','F','I','IP','S','U')),
  primary key (sid,term,year,cprefix,cno),
  foreign key (sid) references cstudents,
  foreign key (cprefix,cno) references ccourses,
);

