CS 348 A1 Schema

init.sql

drop table if exists course;
drop table if exists professor;
drop table if exists student;
drop table if exists class;
drop table if exists enorollment;
drop table if exists mark;
drop table if exists schedule;

create table course (
  cnum varchar(5) not null,
  cname varchar(40) not null,
  primary key (cnum));

create table professor (
  pnum integer not null,
  pname varchar(20) not null,
  office varchar(10) not null,
  dept varchar(30) not null,
  primary key (pnum));

create table student (
  snum integer not null,
  sname varchar(20) not null,
  year integer not null,
  primary key (snum));

create table class (
  cnum varchar(5) not null,
  term varchar(5) not null,
  section integer not null,
  pnum integer not null,
  primary key (cnum, term, section),
  foreign key (cnum) references course (cnum),
  foreign key (pnum) references professor (pnum));

create table enrollment (
  snum integer not null,
  cnum varchar(5) not null,
  term varchar(5) not null,
  section integer 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 integer not null,
  cnum varchar(5) not null,
  term varchar(5) not null,
  section integer not null,
  grade integer 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(5) not null,
  term varchar(5) not null,
  section integer not null,
  day varchar(10) not null,
  time varchar(5) not null,
  room varchar(10) not null,
  primary key (cnum, term, section, day, time),
  foreign key (cnum, term, section)
    references class (cnum, term, section));

insert into course values
  ('CS240', 'Data Structures and Data Management'),
  ('CS245', 'Logic and Computation'),
  ('CS343', 'Concurrent and Parallel Programming'),
  ('CS348', 'Introduction to Databases'),
  ('SE380', 'Introduction to Feedback Control'),
  ('SE390', 'Design Project Planning'),
  ('SE464', 'Software Design and Architectures');

insert into professor values
  (1, 'Weddell, Grant', 'DC3346', 'CS'),
  (2, 'Dent, Arthur', 'AB123', 'CO'),
  (3, 'Prefect, Ford', 'CD456', 'CO'),
  (4, 'Baggins, Frodo', 'EF789', 'SE'),
  (5, 'The Grey, Gandalf', 'GH123', 'CS'),
  (6, 'Twist, Oliver', 'JK456', 'SE'),
  (7, 'Dodger, Artful', 'LM789', 'SE');

insert into student values
  (1, 'Jones, Fred', 4),
  (2, 'Ator, Eva Lu', 2),
  (3, 'Reasoner, Louis', 1),
  (4, 'Hacker, Alyssa P.', 1),
  (5, 'Fect, Cy D.', 4),
  (6, 'Tweakit, Lem E.', 3),
  (7, 'Abelson, Harold', 4),
  (8, 'Sussman, Gerald J.', 4);

insert into class values
  ('CS240', 'S2006', 1, 2),
  ('CS240', 'S2006', 2, 2),
  ('CS245', 'W2007', 1, 2),
  ('CS245', 'W2007', 2, 3),
  ('CS343', 'W2007', 1, 4),
  ('CS348', 'S2006', 1, 1),
  ('CS348', 'S2006', 2, 1),
  ('SE380', 'F2017', 1, 5),
  ('SE380', 'F2017', 2, 5),
  ('SE390', 'F2017', 1, 6),
  ('SE390', 'F2017', 2, 7),
  ('SE464', 'F2017', 1, 7),
  ('SE464', 'F2017', 2, 7),
  ('SE464', 'F2017', 3, 7);

insert into enrollment values
  (1, 'CS240', 'S2006', 2),
  (1, 'CS245', 'W2007', 1),
  (1, 'CS343', 'W2007', 1),
  (1, 'CS348', 'S2006', 2),
  (2, 'SE380', 'F2017', 1),
  (2, 'SE390', 'F2017', 2),
  (2, 'SE464', 'F2017', 3),
  (3, 'CS240', 'S2006', 1),
  (3, 'CS348', 'S2006', 1),
  (3, 'SE464', 'F2017', 2),
  (4, 'CS240', 'S2006', 1),
  (5, 'CS245', 'W2007', 2),
  (6, 'CS245', 'W2007', 2),
  (6, 'SE390', 'F2017', 1),
  (6, 'SE464', 'F2017', 1),
  (7, 'CS240', 'S2006', 1),
  (7, 'CS348', 'S2006', 1),
  (7, 'CS343', 'W2007', 1),
  (7, 'SE390', 'F2017', 2),
  (7, 'SE464', 'F2017', 3),
  (8, 'SE464', 'F2017', 2);

insert into mark values
  (1, 'CS240', 'S2006', 2, 100),
  (1, 'CS245', 'W2007', 1, 99),
  (1, 'CS343', 'W2007', 1, 91),
  (1, 'CS348', 'S2006', 2, 93),
  (3, 'CS240', 'S2006', 1, 51),
  (3, 'CS348', 'S2006', 1, 52),
  (4, 'CS240', 'S2006', 1, 75),
  (5, 'CS245', 'W2007', 2, 76),
  (6, 'CS245', 'W2007', 2, 87),
  (7, 'CS240', 'S2006', 1, 89),
  (7, 'CS348', 'S2006', 1, 82),
  (7, 'CS343', 'W2007', 1, 66);

insert into schedule values
  ('CS240', 'S2006', 1, 'Monday', '09:30', 'MC4063'),
  ('CS240', 'S2006', 2, 'Monday', '12:30', 'MC4063'),
  ('CS245', 'W2007', 1, 'Wednesday', '09:30', 'MC2066'),
  ('CS245', 'W2007', 2, 'Friday', '09:30', 'MC2066'),
  ('CS343', 'W2007', 1, 'Tuesday', '14:30', 'OPT347'),
  ('CS348', 'S2006', 1, 'Monday', '11:30', 'MC1006'),
  ('CS348', 'S2006', 2, 'Monday', '16:30', 'MC1006'),
  ('SE380', 'F2017', 1, 'Thursday', '10:00', 'RCH307'),
  ('SE380', 'F2017', 2, 'Friday', '10:00', 'RCH308'),
  ('SE390', 'F2017', 1, 'Monday', '10:30', 'DWE1501'),
  ('SE390', 'F2017', 2, 'Wednesday', '14:30', 'DWE1501'),
  ('SE464', 'F2017', 1, 'Monday', '09:30', 'MC4040'),
  ('SE464', 'F2017', 2, 'Wednesday', '10:30', 'MC4040'),
  ('SE464', 'F2017', 3, 'Friday', '11:30', 'MC4040');

Minor: drop table if exists enorollment; should be drop table if exists enrollment; :)