\c graddb

drop table student cascade;
create table student (
  sid      serial primary key not null check (sid > 999 and sid < 10000),
  email    varchar(40),
  password varchar(20),
  fname    varchar(20),
  lname    varchar(20),
  address1 varchar(40),
  address2 varchar(40),
  city     varchar(40),
  state    varchar(40),
  zip      int,
  sType    varchar(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'))
);
ALTER sequence student_sid_seq RESTART WITH 1000;
--
drop table course cascade;
create table course (
  cprefix  char(4),
  cno      int,
  ctitle   varchar(50),
  chours   int,
  primary key (cprefix,cno)
);
insert into course values ('CSC',1010,'Computers and Applications',3);
insert into course values ('CSC',2010,'Introduction to Computer Science',3);
insert into course values ('CSC',2310,'Introduction to Programming in Java',3);
insert into course values ('CSC',2311,'Introduction to Programming in C++',3);
insert into course values ('CSC',3410,'Data Structures',3);
insert into course values ('CSC',3210,'Computer Organization',3);
insert into course values ('CSC',3320,'System Level Programming in Unix and C',3);
insert into course values ('MATH',2211,'Calculus I',5);
insert into course values ('MATH',2212,'Calculus II',5);
insert into course values ('MATH',2420,'Discrete Mathematics',3);
insert into course values ('CSC',6220,'Networks',4);
insert into course values ('CSC',8220,'Advanced Networks',4);
insert into course values ('CSC',6710,'Database',4);
insert into course values ('CSC',8710,'Advanced Database',4);
insert into course values ('CSC',6820,'Graphics',4);
insert into course values ('CSC',8820,'Advanced Graphics',4);
insert into course values ('POLS',1200,'Intro Political Sci',3);
--
drop table section cascade;
create table section (
  term        char(2) check (term in ('FA','SP','SU')),
  year        int,
  crn         int,
  cprefix     char(4),
  cno         int,
  section     int,
  days        char(6),
  startTime   char(5), -- example 08.15, 13.30 etc.
  endTime     char(5),
  room        varchar(10),
  cap         int,
  instructor  varchar(30),
  auth        char(1) check (auth in ('Y','N')),
  primary key (term,year,crn),
  foreign key (cprefix,cno) references course
);
--
insert into section values 
('SU',2005,10101,'CSC',1010,1,'MWF','09.00','09.50','105G',35,'Bhola','N');
insert into section values 
('SU',2005,10701,'POLS',1200,1,'TR','09.00','09.50','205Sp',25,'Jones','N');
--
insert into section values 
('FA',2005,10101,'CSC',2010,1,'MWF','09.00','09.50','105G',35,'Bhola','N');
insert into section values 
('FA',2005,10102,'CSC',2010,2,'MWF','10.00','10.50','105CS',40,'Henry','N');
insert into section values 
('FA',2005,10103,'CSC',2310,1,'MWF','12.00','12.50','106G',30,'Henry','N');
insert into section values 
('FA',2005,10104,'CSC',2311,1,'MWF','15.00','15.50','205G',35,'Liu','N');
insert into section values 
('FA',2005,10201,'CSC',6220,1,'TR','19.00','20.40','405G',25,'Hundewale','N');
insert into section values 
('FA',2005,10202,'CSC',6710,1,'TR','16.00','17.15','115CS',25,'Madiraju','N');
insert into section values 
('FA',2005,10203,'CSC',8820,1,'MWF','09.00','09.50','605G',25,'Owen','N');
insert into section values 
('FA',2005,10301,'MATH',2211,1,'TR','11.00','12.50','305G',35,'Li','N');
insert into section values 
('FA',2005,10302,'MATH',2211,2,'MWF','09.00','10.50','106GB',35,'Davis','N');
--
drop table enroll cascade;
create table enroll (
  sid    int,
  term   char(2) check (term in ('FA','SP','SU')),
  year   int,
  crn    int,
  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 student,
  foreign key (term,year,crn) references section
);
--