-- Example commands for (re) creating the STUDENT-COURSE database. -- NOTE: "insert" commands should be modified to create appropriate -- test data. connect to cs348 drop table course create table course ( \ cnum varchar(5) not null, \ cname varchar(40) not null, \ primary key (cnum) ) insert into course values ('CS448', 'Introduction to Databases') drop table professor 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) ) insert into professor values (1, 'Weddell, Grant', 'DC3346', 'Computer Science') insert into professor values (2, 'Ilyas, Ihab', 'DC3348', 'Computer Science') drop table student create table student ( \ snum integer not null, \ sname varchar(20) not null, \ year integer not null, \ primary key (snum) ) insert into student values (1, 'Jones, Fred', 4) drop table class 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) ) insert into class values ('CS448', 'S2006', 1, 1) insert into class values ('CS448', 'S2006', 2, 1) drop table enrollment 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) ) insert into enrollment values (1, 'CS448', 'S2006', 2) drop table mark 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) ) drop table schedule 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 schedule values ('CS448', 'S2006', 1, 'Monday', '09:30', 'MC4063') insert into schedule values ('CS448', 'S2006', 1, 'Wednesday', '09:30', 'MC4063') insert into schedule values ('CS448', 'S2006', 1, 'Friday', '09:30', 'MC4063') insert into schedule values ('CS448', 'S2006', 2, 'Monday', '11:30', 'MC4058') insert into schedule values ('CS448', 'S2006', 2, 'Wednesday', '11:30', 'MC4058') insert into schedule values ('CS448', 'S2006', 2, 'Friday', '11:30', 'MC4058') commit work