create table student (
     snum int not null,
     sname varchar(20),
     year int,
     primary key (snum)
);
create table professor (
     pnum int not null,
     pname varchar(20),
     office varchar(6),
     dept varchar(20),
     primary key (pnum)
);
create table course (
     cnum varchar(10) not null,
     cname varchar(50),
     primary key (cnum)
);
create table class (
     cnum varchar(10) not null,
     term char(3) not null,
     section int not null,
     pnum int,
     primary key (cnum,term,section),
     foreign key (cnum) references course(cnum),
     foreign key (pnum) references professor(pnum)
);
create table enrollment (
     snum int not null,
     cnum varchar(10) not null,
     term char(3) not null,
     section int not null,
     primary key (snum,cnum,term,section),
     foreign key (snum) references student(snum),
     foreign key (cnum,term,section) references class(cnum,term,section)
);
create table mark (
     snum int not null,
     cnum varchar(10) not null,
     term char(3) not null,
     section int not null,
     grade int not null,
     primary key (snum,cnum,term,section),
     foreign key (snum,cnum,term,section) references enrollment(snum,cnum,term,section)
);
create table schedule (
     cnum varchar(10) not null,
     term char(3) not null,
     section int not null,
     day varchar(10) not null,
     time varchar(7) not null,
     room varchar(6),
     primary key (cnum,term,section,day,time),
     foreign key (cnum,term,section) references class (cnum,term,section)
);
insert into course values
   ('AMATH331','Applied Real Analysis'),
   ('AMATH447','Introduction to Symbolic Computation'),
   ('CO342','Introduction to Graph Theory'),
   ('CO350','Linear Optimization'),
   ('CS135','Designing Functional Programs'),
   ('CS136','Elementary Algorithm Design and Data Abstraction'),
   ('CS240','Data Structures and Data Management'),
   ('CS241','Foundations of Sequential Programs'),
   ('CS245','Logic and Computation'),
   ('CS246','Software Abstraction and Specification'),
   ('CS251','Computer Organization and Design'),
   ('CS341','Algorithms'),
   ('CS350','Operating Systems'),
   ('CS462','Formal Languages and Parsing'),
   ('CS466','Algorithm Design and Analysis'),
   ('CS487','Introduction to Symbolic Computation'),
   ('MATH135','Algebra for Honours Mathematics'),
   ('MATH136','Linear Algebra 1 for Honours Mathematics'),
   ('MATH137','Calculus 1 for Honours Mathematics'),
   ('MATH138','Calculus 2 for Honours Mathematics'),
   ('MATH237','Calculus 3 for Honours Mathematics'),
   ('MATH239','Introduction to Combinatorics'),
   ('PMATH340','Elementary Number Theory'),
   ('STAT230','Probability'),
   ('STAT231','Statistics');
insert into professor values
   (19,'Brown','DC2410','CS'),
   (5,'Wilson','DC2320','CS'),
   (8,'Williams','MC6295','STAT'),
   (6,'Smith','DC3850','CS'),
   (10,'Jones','MC5048','CO'),
   (9,'Clark','DC3109','CS'),
   (13,'Campbell','MC5127','PMATH'),
   (12,'Lee','DC2662','CS'),
   (3,'Li','DC2849','CS'),
   (16,'Roy','DC2734','CS'),
   (18,'Tremblay','DC2770','CS'),
   (14,'Chan','MC5181','CO'),
   (15,'Lam','DC2782','CS'),
   (2,'Taylor','MC5318','PMATH'),
   (4,'Gagnon','DC3581','CS'),
   (11,'White','MC6605','STAT'),
   (7,'Anderson','MC6193','CO'),
   (1,'Martin','DC3732','CS'),
   (17,'Johnson','MC6522','STAT');
insert into student values
   (4462,'Robinson',1),
   (4510,'Hill',3),
   (4612,'Clark',2),
   (4526,'Green',3),
   (4364,'King',3),
   (4321,'Roberts',4),
   (4680,'Adams',3),
   (4457,'Green',3),
   (4635,'Campbell',3),
   (4536,'Wright',3),
   (4707,'Li',3),
   (4573,'Liu',4),
   (4674,'Chen',3),
   (4691,'Gonzalez',2),
   (4670,'Gonzalez',1),
   (4560,'Lee',2),
   (4623,'Pirez',2),
   (4534,'Walker',1),
   (4663,'Clark',1),
   (4734,'Lee',1),
   (4345,'Sanchez',3),
   (4694,'Scott',3),
   (4416,'Young',2),
   (4730,'Jiang',3),
   (4596,'Harris',1),
   (4719,'Wang',3),
   (4328,'Allen',2),
   (4503,'Mitchell',3),
   (4391,'Carter',4),
   (4429,'Li',3),
   (4549,'Nelson',3),
   (4455,'Scott',3),
   (4417,'Hall',2);
insert into class values
   ('CO342','W05',3,7),
   ('CO350','F06',1,7),
   ('CS246','W07',1,6),
   ('CS462','S08',1,12),
   ('CS466','S08',2,6),
   ('CS466','S08',1,5),
   ('CS246','F07',1,6),
   ('CO342','W05',1,14),
   ('CO342','W05',2,14),
   ('CS246','W07',2,16),
   ('PMATH340','W06',1,13);
insert into enrollment values
   (4560,'CO342','W05',1),
   (4734,'CS246','F07',1),
   (4462,'CS246','F07',1),
   (4462,'CO342','W05',2),
   (4328,'CO342','W05',2),
   (4536,'PMATH340','W06',1),
   (4321,'CS246','W07',1),
   (4612,'CS246','W07',2),
   (4321,'CO342','W05',2),
   (4674,'CO350','F06',1),
   (4328,'CS246','F07',1),
   (4694,'CO342','W05',2),
   (4391,'CS246','W07',2),
   (4455,'CO342','W05',2),
   (4635,'CS246','W07',1),
   (4364,'CO342','W05',1),
   (4503,'CO342','W05',3),
   (4674,'CO342','W05',1),
   (4364,'CO350','F06',1),
   (4534,'CS246','W07',2),
   (4734,'PMATH340','W06',1),
   (4457,'CS246','F07',1),
   (4429,'CS246','F07',1),
   (4734,'CS246','W07',1),
   (4457,'CO342','W05',1),
   (4364,'CS246','W07',1),
   (4623,'CS246','W07',2),
   (4536,'CO342','W05',3),
   (4663,'CO342','W05',3),
   (4635,'CO342','W05',3),
   (4328,'CS246','W07',2),
   (4680,'CO342','W05',3),
   (4455,'PMATH340','W06',1);
insert into schedule values
   ('CS466','S08',2,'Wednesday','9:30am','MC4884'),
   ('CS246','W07',2,'Tuesday','10:00am','MC4655'),
   ('CS246','W07',1,'Monday','2:30pm','MC4550'),
   ('CS246','F07',1,'Monday','11:30am','MC4183'),
   ('CS246','W07',2,'Thursday','10:00am','MC4763'),
   ('CS466','S08',1,'Friday','8:30am',NULL),
   ('CO342','W05',3,'Monday','7:00pm','MC4278'),
   ('CS462','S08',1,'Monday','11:30am','MC4264'),
   ('CS466','S08',1,'Wednesday','8:30am','MC4264'),
   ('CS246','F07',1,'Friday','11:30am','MC4183'),
   ('CO350','F06',1,'Tuesday','4:00pm','MC4264'),
   ('CS466','S08',2,'Monday','9:30am','MC4884'),
   ('CS246','W07',1,'Wednesday','2:30pm','MC4550'),
   ('CS466','S08',1,'Monday','8:30am','MC4264'),
   ('CO342','W05',2,'Thursday','10:00am','MC4267'),
   ('CO342','W05',2,'Tuesday','10:00am','MC4267'),
   ('CS246','F07',1,'Wednesday','11:30am','MC4183'),
   ('CO342','W05',1,'Thursday','7:00pm','MC4183'),
   ('PMATH340','W06',1,'Tuesday','1:00pm','MC4837'),
   ('PMATH340','W06',1,'Thursday','1:00pm','MC4837'),
   ('CS466','S08',2,'Friday','9:30am','MC4884'),
   ('CS462','S08',1,'Wednesday','11:30am','MC4264');
insert into mark values
   (4364,'CS246','W07',1,82),
   (4534,'CS246','W07',2,68),
   (4321,'CS246','W07',1,71),
   (4663,'CO342','W05',3,69),
   (4734,'PMATH340','W06',1,98),
   (4734,'CS246','W07',1,38),
   (4503,'CO342','W05',3,57),
   (4321,'CO342','W05',2,79),
   (4364,'CO350','F06',1, 95),
   (4635,'CS246','W07',1,92),
   (4674,'CO350','F06',1,88),
   (4455,'CO342','W05',2,57),
   (4635,'CO342','W05',3,70),
   (4328,'CS246','W07',2,41),
   (4674,'CO342','W05',1,92),
   (4612,'CS246','W07',2,94),
   (4457,'CO342','W05',1,84),
   (4391,'CS246','W07',2,53),
   (4536,'CO342','W05',3,77),
   (4680,'CO342','W05',3,52),
   (4455,'PMATH340','W06',1,61),
   (4694,'CO342','W05',2,91),
   (4462,'CO342','W05',2,59),
   (4536,'PMATH340','W06',1,72),
   (4560,'CO342','W05',1,73),
   (4328,'CO342','W05',2,45),
   (4364,'CO342','W05',1,90),
   (4623,'CS246','W07',2,61);