/* the UNIVERSITY database on E&N p8, used in exercise 4.12 
 *
 * This version has complete foreign-key constraints
 * However, NO CONSTRAINTS ARE "CIRCULAR", so this should be ok
 */

-- The STUDENT entity, with key student_number
create table student (
     name             varchar(20)     not null,
     student_number   int             primary key not null,
     class            int,
     major            varchar(10)
);


-- The COURSE entity, with key course_number
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
);

-- The SECTION entity, with key section_identifier
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),
    constraint FK_section_course_course_number
        foreign key (course_number) references course(course_number)
);

create table grade_report (
    student_number      int           not null,
    section_identifier  int           not null,
    grade               varchar(2),
    primary key (student_number, section_identifier),
    constraint FK_grade_report_student_student_number
        foreign key (student_number) references student(student_number),
    constraint FK_grade_report_section_section_identifier
        foreign key (section_identifier) references section(section_identifier)
);


create table prerequisite (
    course_number       varchar(12),
    prerequisite_number varchar(12),
    constraint FK_prerequisite_course_course_number_1
         foreign key (course_number) references course(course_number),
    constraint FK_prerequisite_course_course_number_2
        foreign key (prerequisite_number) references course(course_number)
);


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