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

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

create table courses(crscode char(10), 
	title char(30),
      	primary key (crscode));

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

create table transcripts(studid integer, 
	crscode char(10), 
	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', 'Intro to CS');
insert into courses values('CS482', 'DB 1');
insert into courses values('CS582', 'DB 2');
insert into courses values('CS272', 'Data Structure');
insert into courses values('CS510', 'Automata');
insert into courses values('CS570', 'Algorithm - The beloved course');
insert into courses values('CS370', 'Compiler');

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', 0);


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