drop table catalog cascade constraints;
create table catalog (
  cprefix   char(4) not null,
  cno       number(4) not null,
  ctitle    varchar2(50),
  primary key (cprefix,cno));
insert into catalog values ('CSC',1010,'Computers and Applications');
insert into catalog values ('CSC',2010,'Introduction to Computer Science');
insert into catalog values ('CSC',2310,'Introduction to Programming in Java');
  
drop table gstudents cascade constraints;
create table gstudents (
  sid       number(4) not null,
  fname     varchar2(20),
  lname     varchar2(20) not null,
  primary key (sid));
insert into gstudents values (1111,'John','Davison');
insert into gstudents values (2222,'Jacob','Oram');
insert into gstudents values (3333,'Ashish','Bagai');
insert into gstudents values (4444,'Joe','Harris');

drop table gcourses cascade constraints;
create table gcourses (
  term      char(2) check (term in ('FA','SP','SU')),
  year      number(4),
  crn       number(5) not null,
  cprefix   char(4) not null,
  cno       number(4) not null,
  a         number(2) check(a > 0),
  b         number(2) check(b > 0),
  c         number(2) check(c > 0),
  d         number(2) check(d > 0),
  primary key (term,year,crn),
  foreign key (cprefix,cno) references catalog);
insert into gcourses values 
('FA',2002,10101,'CSC',2010,90,80,70,60);
insert into gcourses values 
('FA',2002,10102,'CSC',2010,90,80,70,60);
insert into gcourses values 
('FA',2002,10103,'CSC',2310,90,80,70,60);

drop table components cascade constraints;
create table components (
  term      char(2) check (term in ('FA','SP','SU')),
  year      number(4),
  crn       number(5) not null,
  compname  varchar2(15) not null,
  maxpoints number(4) check(maxpoints >= 0),
  weight    number(2) check(weight>=0),
  primary key (term,year,crn,compname),
  foreign key (term,year,crn) references gcourses);
insert into components values ('FA',2002,10101,'MidTerm',100,30);
insert into components values ('FA',2002,10101,'Project',100,30);
insert into components values ('FA',2002,10101,'Final',100,40);

drop table genrolls cascade constraints;
create table genrolls (
  sid       varchar2(4) not null,
  term      char(2) check (term in ('FA','SP','SU')),
  year      number(4),
  crn       number(5) not null,
  primary key (sid,term,year,crn),
  foreign key (sid) references gstudents,
  foreign key (term,year,crn) references gcourses);
insert into genrolls values (1111,'FA',2002,10101);
insert into genrolls values (2222,'FA',2002,10101);
insert into genrolls values (3333,'FA',2002,10101);

drop table scores cascade constraints;
create table scores (
  sid       varchar2(4) not null,
  term      char(2) check (term in ('FA','SP','SU')),
  year      number(4),
  crn       number(5) not null,
  compname  varchar2(15) not null,
  points    number(4) check(points >= 0),
  primary key (sid,term,year,crn,compname),
  foreign key (sid,term,year,crn) references genrolls,
  foreign key (term,year,crn,compname) references components);
insert into scores values (1111,'FA',2002,10101,'MidTerm',90);
insert into scores values (2222,'FA',2002,10101,'MidTerm',80);
insert into scores values (3333,'FA',2002,10101,'MidTerm',70);
insert into scores values (1111,'FA',2002,10101,'Project',100);
insert into scores values (2222,'FA',2002,10101,'Project',80);
insert into scores values (3333,'FA',2002,10101,'Project',90);
insert into scores values (1111,'FA',2002,10101,'Final',95);
insert into scores values (2222,'FA',2002,10101,'Final',85);
insert into scores values (3333,'FA',2002,10101,'Final',50);

