drop table lstudents cascade constraints;
create table lstudents (
  sid      number(9) primary key,
  email    varchar2(40),
  password number(5),
  fname    varchar2(20),
  lname    varchar2(20),
  address1 varchar2(40),
  address2 varchar2(40),
  city     varchar2(40),
  state    varchar2(40),
  zip      number(5),
  sType    varchar2(5) check (sType in ('MS','PhD','UGRAD')),
  majorDept    char(4) check (majorDept in ('CSC','MATH','POLS','HIST')),
  gradAssistant char(1) check (gradAssistant in ('Y','N')),
);
--
drop table lcourses cascade constraints;
create table lcourses (
  cprefix  char(4),
  cno      number(4),
  ctitle   varchar2(50),
  chours   number(2),
  primary key (cprefix,cno)
);
insert into lcourses values ('CSC',1010,'Computers and Applications',3);
insert into lcourses values ('CSC',2010,'Introduction to Computer Science',3);
insert into lcourses values ('CSC',2310,'Introduction to Programming in Java',3);
insert into lcourses values ('CSC',2311,'Introduction to Programming in C++',3);
insert into lcourses values ('CSC',3410,'Data Structures',3);
insert into lcourses values ('CSC',3210,'Computer Organization',3);
insert into lcourses values ('CSC',3320,'System Level Programming in Unix and C',3);
insert into lcourses values ('MATH',2211,'Calculus I',5);
insert into lcourses values ('MATH',2212,'Calculus II',5);
insert into lcourses values ('MATH',2420,'Discrete Mathematics',3);
insert into lcourses values ('CSC',6220,'Networks',4);
insert into lcourses values ('CSC',8220,'Advanced Networks',4);
insert into lcourses values ('CSC',6710,'Database',4);
insert into lcourses values ('CSC',8710,'Advanced Database',4);
insert into lcourses values ('CSC',6820,'Graphics',4);
insert into lcourses values ('CSC',8820,'Advanced Graphics',4);
insert into lcourses values ('POLS',1200,'Intro Political Sci',3);
--
drop table sections cascade constraints;
create table sections (
  term        char(2) check (term in ('FA','SP','SU')),
  year        number(4),
  crn         number(5),
  cprefix     char(4),
  cno         number(4),
  section     number(2),
  days        char(6),
  startTime   char(5), -- example 08.15, 13.30 etc.
  endTime     char(5),
  room        varchar2(10),
  cap         number(3),
  instructor  varchar2(30),
  auth        char(1) check (auth in ('Y','N')),
  primary key (term,year,crn),
  foreign key (cprefix,cno) references lcourses
);
--
insert into sections values 
('SU',2005,10101,'CSC',1010,1,'MWF','09.00','09.50','105G',35,'Bhola','N');
insert into sections values 
('SU',2005,10701,'POLS',1200,1,'TR','09.00','09.50','205Sp',25,'Jones','N');
--
insert into sections values 
('FA',2005,10101,'CSC',2010,1,'MWF','09.00','09.50','105G',35,'Bhola','N');
insert into sections values 
('FA',2005,10102,'CSC',2010,2,'MWF','10.00','10.50','105CS',40,'Henry','N');
insert into sections values 
('FA',2005,10103,'CSC',2310,1,'MWF','12.00','12.50','106G',30,'Henry','N');
insert into sections values 
('FA',2005,10104,'CSC',2311,1,'MWF','15.00','15.50','205G',35,'Liu','N');
insert into sections values 
('FA',2005,10201,'CSC',6220,1,'TR','19.00','20.40','405G',25,'Hundewale','N');
insert into sections values 
('FA',2005,10202,'CSC',6710,1,'TR','16.00','17.15','115CS',25,'Madiraju','N');
insert into sections values 
('FA',2005,10203,'CSC',8820,1,'MWF','09.00','09.50','605G',25,'Owen','N');
insert into sections values 
('FA',2005,10301,'MATH',2211,1,'TR','11.00','12.50','305G',35,'Li','N');
insert into sections values 
('FA',2005,10302,'MATH',2211,2,'MWF','09.00','10.50','106GB',35,'Davis','N');
--
drop table lenrolls cascade constraints;
create table lenrolls (
  sid    number(9),
  term   char(2) check (term in ('FA','SP','SU')),
  year   number(4),
  crn    number(5),
  grade  char(2) check (grade in ('A','B','C','D','F','I','IP','S','U')),
  primary key (sid,term,year,crn),
  foreign key (sid) references lstudents,
  foreign key (term,year,crn) references sections
);
--
