Rem     
Rem Create the four table 
Rem        transcripts
Rem        schedule    (like teaching in the book)
Rem        courses 
Rem        students
Rem 
Rem 

drop table transcripts;
drop table schedule;
drop table courses;
drop table students;

create table students(id integer, 
        name char(30), 
	address varchar(100), 
	status char(10), 
	primary key (id));

create table courses(crscode char(6), 
        depid char(4),
	crsname char(20),
        descr char(100),
      	primary key (crscode), -- primary key 
	unique (depid, crsname) -- candidate key 
        );

create table schedule(crscode char(6),
	semester char(12),
	maxenroll integer,
	primary key (crscode, semester), 
 	foreign key (crscode) references courses(crscode));	

create table transcripts(studid integer, 
	crscode char(6), 
	semester char(12), 
	grade char(1), 
	primary key (studid, crscode, semester), 
	foreign key (studid) references students(id),
 	foreign key (crscode) references courses(crscode));

insert into students values(111111111, 'John Montana', 'ABC 2', 'Freshman');
insert into students values(222222222, 'Deborah Fame', 'Street 2', 'Senior');
insert into students values(333333333, 'Mark Fliz', 'NBC 9', 'Junior');
insert into students values(444444444, 'Star Gate', 'Fox 14', 'Sophomore');
insert into students values(555555555, 'Humberto Li', 'KRWG 22', 'Senior');
insert into students values(666666666, 'Antonio Jump', 'Alicia', 'Senior');

insert into courses values('CS110', 'CS', 'Intro to CS', 'This is the first course CS students take');
insert into courses values('CS482', 'CS', 'DB 1', 'Introduction to Database');
insert into courses values('CS502', 'CS', 'DB 1', 'Introduction to Database for Grads');
insert into courses values('CS582', 'CS', 'DB 2', '');
insert into courses values('CS272', 'CS', 'Data Structure', 'Basic data structures');
insert into courses values('CS510', 'CS', 'Automata', 'Learn and understand why we study CS');
insert into courses values('CS570', 'CS', 'Algorithm', 'The beloved course of all graduate students');
insert into courses values('CS370', 'CS', 'Compiler', 'Important stuff');
insert into courses values('EE110', 'EE', 'Introduction to EE', '');

insert into schedule values('CS482', 'Fall2006', 30);
insert into schedule values('CS502', 'Fall2006', 30);
insert into schedule values('CS110', 'Fall2005', 40);
insert into schedule values('CS110', 'Fall2004', 40);
insert into schedule values('CS110', 'Spring2006', 40);
insert into schedule values('CS110', 'Spring2005', 40);
insert into schedule values('CS482', 'Fall2005', 30);
insert into schedule values('CS482', 'Fall2004', 30);
insert into schedule values('CS272', 'Fall2005', 30);
insert into schedule values('CS272', 'Fall2002', 30);
insert into schedule values('CS272', 'Fall2003', 30);
insert into schedule values('CS272', 'Fall2004', 30);
insert into schedule values('CS370', 'Spring2006', 40);
insert into schedule values('CS370', 'Spring2005', 40);
insert into schedule values('CS370', 'Spring2004', 10);
insert into schedule values('CS370', 'Fall2003', 20);


insert into transcripts values (111111111, 'CS110', 'Fall2005', 'B');
insert into transcripts values (222222222, 'CS482', 'Fall2004', 'A');
insert into transcripts values (222222222, 'CS272', 'Fall2002', 'B');
insert into transcripts values (222222222, 'CS370', 'Fall2003', 'B');

Rem update schedule set maxenroll = 0 where crscode = 'CS370 ' and semester = 'Fall2003';

Rem insert into transcripts values (555555555, 'CS370', 'Fall2003','I');
Rem insert into transcripts values (555555555, 'C482', 'Fall2006','I');
