-- This script creates the University Database described in Figure 1-1
-- and Exercise 4.21 of the textbook. I made some minor changes to the
-- schema.
--
--              csun@calstatela.edu, 7/23/2009

create table departments (
    id      integer primary key,
    name    varchar(255)
);

insert into departments (id, name) values (10, 'Computer Science');
insert into departments (id, name) values (20, 'Math');
insert into departments (id, name) values (30, 'Drama');

create table faculty (
    id              integer primary key,
    name            varchar(255),
    department_id   integer references departments(id)
);

insert into faculty (id, name, department_id) values (1, 'Turing', 10);
insert into faculty (id, name, department_id) values (2, 'Newton', 20);
insert into faculty (id, name, department_id) values (3, 'Einstein', 20);
insert into faculty (id, name, department_id) values (4, 'Brando', 30);
insert into faculty (id, name, department_id) values (5, 'Joe', 30);
insert into faculty (id, name, department_id) values (6, 'Gray', 10);

create table students (
    id              integer primary key,
    name            varchar(255),
    graduation_date date,
    major_id        integer references departments(id)
);

insert into students (id, name, graduation_date, major_id) values
    (1, 'Joe', null, 10);
insert into students (id, name, graduation_date, major_id) values
    (2, 'Amy', '2009-04-22', 20);
insert into students (id, name, graduation_date, major_id) values
    (3, 'Max', null, 10);
insert into students (id, name, graduation_date, major_id) values
    (4, 'Sue', '2009-01-10', 20);
insert into students (id, name, graduation_date, major_id) values
    (5, 'Bob', '2009-03-05', 30);
insert into students (id, name, graduation_date, major_id) values
    (6, 'Kim', null, 20);
insert into students (id, name, graduation_date, major_id) values
    (7, 'Art', null, 30);
insert into students (id, name, graduation_date, major_id) values
    (8, 'Pat', '2005-07-11', 20);
insert into students (id, name, graduation_date, major_id) values
    (9, 'Lee', null, 10);

create table grades (
    id          integer primary key,
    letter      varchar(255) not null unique,
    value       real
);

insert into grades (id, letter, value) values (1, 'A', 4.0);
insert into grades (id, letter, value) values (2, 'A-', 3.7);
insert into grades (id, letter, value) values (3, 'B+', 3.3);
insert into grades (id, letter, value) values (4, 'B', 3.0);
insert into grades (id, letter, value) values (5, 'B-', 2.7);
insert into grades (id, letter, value) values (6, 'C+', 2.3);
insert into grades (id, letter, value) values (7, 'C', 2.0);
insert into grades (id, letter, value) values (8, 'C-', 1.7);
insert into grades (id, letter, value) values (9, 'D+', 1.3);
insert into grades (id, letter, value) values (10, 'D', 1.0);
insert into grades (id, letter, value) values (11, 'D-', 0.7);
insert into grades (id, letter, value) values (12, 'F', 0.0);
insert into grades (id, letter) values (13, 'CR');
insert into grades (id, letter) values (14, 'NC');

create table courses (
    id              integer primary key,
    title           varchar(255),
    units           integer,
    department_id   integer references departments(id)
);

insert into courses (id, title, units, department_id) values
    (12, 'Databases', 4, 10);
insert into courses (id, title, units, department_id) values
    (22, 'Compilers', 4, 10);
insert into courses (id, title, units, department_id) values
    (32, 'Calculus 1', 4, 20);
insert into courses (id, title, units, department_id) values
    (42, 'Algebra', 4, 20);
insert into courses (id, title, units, department_id) values
    (52, 'Acting', 4, 30);
insert into courses (id, title, units, department_id) values
    (62, 'Elocution', 2, 30);
insert into courses (id, title, units, department_id) values
    (72, 'Calculus 2', 4, 20);
insert into courses (id, title, units, department_id) values
    (82, 'Topology', 4, 20);

create table sections (
    id              integer primary key,
    course_id       integer not null references courses(id),
    instructor_id   integer references faculty(id),
    year            integer
);

insert into sections (id, course_id, instructor_id, year) values
    (12, 12, 6, 2007);
insert into sections (id, course_id, instructor_id, year) values
    (13, 12, 1, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (14, 22, 1, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (23, 12, 6, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (24, 22, 1, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (32, 32, 2, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (33, 32, 2, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (34, 82, 2, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (43, 32, 3, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (51, 62, 4, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (52, 52, 4, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (53, 62, 4, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (54, 52, 4, 2009);

create table enrollment (
    id          integer primary key,
    student_id  integer not null references students(id),
    section_id  integer not null references sections(id),
    grade_id    integer references grades(id)
);

insert into enrollment (id, student_id, section_id, grade_id) values
    (14, 1, 12, 8);
insert into enrollment (id, student_id, section_id, grade_id) values
    (15, 1, 13, 3);
insert into enrollment (id, student_id, section_id, grade_id) values
    (16, 1, 14, 5);
insert into enrollment (id, student_id, section_id, grade_id) values
    (17, 1, 32, 1);
insert into enrollment (id, student_id, section_id, grade_id) values
    (18, 1, 34, 2);
insert into enrollment (id, student_id, section_id, grade_id) values
    (19, 1, 53, 13);
insert into enrollment (id, student_id, section_id, grade_id) values
    (24, 3, 12, 2);
insert into enrollment (id, student_id, section_id, grade_id) values
    (25, 3, 14, 5);
insert into enrollment (id, student_id, section_id, grade_id) values
    (26, 3, 32, 1);
insert into enrollment (id, student_id, section_id, grade_id) values
    (27, 3, 34, 2);
insert into enrollment (id, student_id, section_id, grade_id) values
    (28, 3, 54, 7);
insert into enrollment (id, student_id, section_id, grade_id) values
    (34, 2, 43, 3);
insert into enrollment (id, student_id, section_id, grade_id) values
    (44, 4, 33, 4);
insert into enrollment (id, student_id, section_id, grade_id) values
    (54, 4, 53, 1);
insert into enrollment (id, student_id, section_id, grade_id) values
    (64, 6, 53, 1);