/* the STUDENT database on E&N p8, used in exercise 4.12 
 */

create table student (
     name             varchar(20)     not null,
     student_number   int             primary key not null,
     class            int,
     major            varchar(10)
);



create table course (
    course_name      varchar(30)      not null,
    course_number    varchar(12)      primary key not null,
    credit_hours     int              not null,
    department       varchar(10)      not null
);


create table section (
    section_identifier  int           primary key not null,
    course_number       varchar(12)   not null,
    semester            varchar(10)   not null,
    year                int,
    instructor          varchar(20)
);

create table grade_report (
    student_number      int           not null,
    section_identifier  int           not null,
    grade               varchar(2),
    primary key (student_number, section_identifier)
);


create table prerequisite (
    course_number       varchar(12),
    prerequisite_number varchar(12)
);


insert into student values ('Smith', 17, 1, 'CS');
insert into student values ('Brown',  8, 2, 'CS');
insert into student values ('Gauss',  9, 4, 'MATH');
insert into student values ('Jones', 15, 4, 'CS');
insert into student values ('Patel', 22, 4, 'CS');

insert into course values
('Intro to Computer Science', 'CS1310', 4, 'CS'),
('Data Structures', 'CS3320', 4, 'CS'),
('Discrete Mathematics', 'MATH2410', 3, 'MATH'),
('Database', 'CS3380', 3, 'CS');

insert into section values
(85,  'MATH2410', 'Fall',   2007, 'King'),
(92,  'CS1310',   'Fall',   2007, 'Anderson'),
(102, 'CS3320',   'Spring', 2008, 'Knuth'),
(112, 'MATH2410', 'Fall',   2008, 'Chang'),
(119, 'CS1310',   'Fall',   2008, 'Anderson'),
(135, 'CS3380',   'Fall',   2008, 'Stone');

insert into grade_report values
(17, 112, 'B'),
(17, 119, 'C'),
( 8,  85, 'A'),
( 8,  92, 'A'),
( 8, 102, 'B'),
( 8, 135, 'A'),
( 9,  85, 'A'),
( 9, 112, 'A'),
(15,  92, 'B+'),
(15, 119, 'B'),
(22, 102, 'A-'),
(22, 119, 'B-'),
(22, 135, 'B');

insert into prerequisite values
('CS3380', 'CS3320'),
('CS3380', 'MATH2410'),
('CS3320', 'CS1310');