CREATE TABLE dept (
    cd VARCHAR(4) NOT NULL,
    name VARCHAR(40) NOT NULL,
    CONSTRAINT dept_pk, 
    PRIMARY KEY (cd)
);
INSERT INTO dept VALUES ( 'CSCI', 'Computer Science' );
INSERT INTO dept VALUES ( 'CPT',  'Computer Technology' );
INSERT INTO dept VALUES ( 'ENGR', 'Freshman Engineering' );
INSERT INTO dept VALUES ( 'GEOL', 'Geology' );
INSERT INTO dept VALUES ( 'PHIL', 'Philosophy' );

CREATE TABLE instr (
    num VARCHAR(5) ,
    dept VARCHAR(4),
    name VARCHAR(40) NOT NULL,
    CONSTRAINT fk_instr_dept FOREIGN KEY ( dept ) REFERENCES dept ( cd ),
    CONSTRAINT instr_pk,
    PRIMARY KEY (num)
);
INSERT INTO instr VALUES ( 'X3801', 'CSCI', 'Bukhres, Omran' );
INSERT INTO instr VALUES ( 'X0329', 'CSCI', 'Fang, Shiaofen' );
INSERT INTO instr VALUES ( 'X0336', 'CSCI', 'Tuceryan, Mihran' );
INSERT INTO instr VALUES ( 'X6722', NULL,   'King, Patrick W' );
INSERT INTO instr VALUES ( 'X1083', 'ENGR', 'Ben-Miled, Zina' );

CREATE TABLE course (
    dept VARCHAR(4),
    num VARCHAR(5),
    name VARCHAR(40) NOT NULL,
    credits INTEGER(2),
    CONSTRAINT course_pk 
    PRIMARY KEY ( dept, num ),
    CONSTRAINT fk_course_dept FOREIGN KEY ( dept ) REFERENCES dept ( cd )
);
INSERT INTO course ( dept, num, credits, name )
 VALUES ( 'CSCI', '541',   3,    'Database Systems' );
INSERT INTO course ( dept, num, credits, name )
 VALUES ( 'CPT',  '541',   4,    'Access Programming' );
INSERT INTO course ( dept, num, credits, name )
 VALUES ( 'CSCI', '701',   NULL, 'PhD Research' );
INSERT INTO course ( dept, num, credits, name )
 VALUES ( 'CSCI', '702',   NULL, 'PhD Thesis' );
INSERT INTO course ( dept, num, credits, name )
 VALUES ( 'GEOL', 'G130',  1,    'Short Courses In Earth Science' );

CREATE TABLE sect (
    dept VARCHAR(4) NOT NULL,
    num VARCHAR(5) NOT NULL,
    sect_num VARCHAR(5),
    name VARCHAR(40) NOT NULL,
    instr VARCHAR(5),
    min_num INTEGER(3),
    max_num INTEGER(3),
    CONSTRAINT sect_pk, 
    PRIMARY KEY ( sect_num ),
    CONSTRAINT fk_sect_course FOREIGN KEY ( dept, num )
        REFERENCES course ( dept, num ),
    CONSTRAINT fk_sect_instr FOREIGN KEY ( instr ) REFERENCES instr ( num )
);
INSERT INTO sect
 VALUES ( 'CSCI', '541', 'B332', 'Database Systems',   'X3801', NULL,  20 );
INSERT INTO sect
 VALUES ( 'CPT',  '541', 'S873', 'Access Programming',    NULL,   30, 150 );
INSERT INTO sect
 VALUES ( 'CSCI', '701', 'B411', 'PhD Research',       'X3801',    1,   1 );
INSERT INTO sect
 VALUES ( 'CSCI', '702', 'B445', 'PhD Thesis',         'X0336',    1,   3 );
INSERT INTO sect
 VALUES ( 'CSCI', '701', 'B412', 'PhD Research',       'X0329',    1,   2 );
INSERT INTO sect
 VALUES ( 'CSCI', '701', 'B413', 'PhD Research',       'X1083',    1,   1 );

CREATE TABLE student (
    sidn INTEGER(9), 
    CONSTRAINT student_pk,
    PRIMARY KEY (sidn),
    major VARCHAR(4),
    name VARCHAR(40),
    CONSTRAINT fk_student_dept FOREIGN KEY ( major ) REFERENCES dept ( cd )
);
INSERT INTO student VALUES ( 111111111, 'PHIL', 'King, Patrick W' );
INSERT INTO student VALUES ( 222222222, 'CSCI', 'Winn, William' );
INSERT INTO student VALUES ( 333333333, 'ENGR', 'Smith, John' );

CREATE TABLE enrolled (
    sidn INTEGER(9),
    sect_num VARCHAR(5),
    credits INTEGER(2) NOT NULL,
    CONSTRAINT enrolled_pk,
    PRIMARY KEY ( sidn, sect_num ),
    CONSTRAINT fk_enrolled_student FOREIGN KEY ( sidn )
        REFERENCES student ( sidn ),
    CONSTRAINT fk_enrolled_sect FOREIGN KEY ( sect_num )
        REFERENCES sect ( sect_num )
);
INSERT INTO enrolled VALUES ( 111111111, 'B411', 2 );
INSERT INTO enrolled VALUES ( 222222222, 'B332', 3 );
INSERT INTO enrolled VALUES ( 111111111, 'B412', 3 );
INSERT INTO enrolled VALUES ( 222222222, 'B412', 4 );
INSERT INTO enrolled VALUES ( 333333333, 'B412', 3 );

CREATE TABLE gp (
    grade VARCHAR(2), 
    CONSTRAINT gp_pk,
    PRIMARY KEY (grade),
    points INTEGER(9)
);
INSERT INTO gp VALUES ( 'A+', 4.0 );
INSERT INTO gp VALUES ( 'A',  4.0 );
INSERT INTO gp VALUES ( 'A-', 3.7 );
INSERT INTO gp VALUES ( 'B+', 3.0 );
INSERT INTO gp VALUES ( 'B',  3.0 );
INSERT INTO gp VALUES ( 'B-', 2.7 );
INSERT INTO gp VALUES ( 'C+', 2.0 );
INSERT INTO gp VALUES ( 'C',  2.0 );
INSERT INTO gp VALUES ( 'C-', 1.7 );
INSERT INTO gp VALUES ( 'D+', 1.0 );
INSERT INTO gp VALUES ( 'D',  1.0 );
INSERT INTO gp VALUES ( 'D-', 0.7 );
INSERT INTO gp VALUES ( 'F',  0.0 );
INSERT INTO gp VALUES ( 'P',  NULL );
INSERT INTO gp VALUES ( 'S',  NULL );
INSERT INTO gp VALUES ( 'I',  NULL );
INSERT INTO gp VALUES ( 'R',  NULL );
INSERT INTO gp VALUES ( 'NC', NULL );
INSERT INTO gp VALUES ( 'NR', NULL );
INSERT INTO gp VALUES ( 'NY', NULL );
INSERT INTO gp VALUES ( 'W',  NULL );
INSERT INTO gp VALUES ( 'WX', NULL );
INSERT INTO gp VALUES ( 'WZ', NULL );

CREATE TABLE trans (
    sidn INTEGER(9),
    dept VARCHAR(4),
    num VARCHAR(5),
    name VARCHAR(40),
    credits INTEGER(2) NOT NULL,
    yr INTEGER(4),
    sem VARCHAR(4),
    grade VARCHAR(2) NOT NULL,
    CONSTRAINT trans_pk
    PRIMARY KEY ( sidn, dept, num, name, yr, sem ),
    CONSTRAINT fk_trans_student FOREIGN KEY ( sidn )
        REFERENCES student ( sidn ),
    CONSTRAINT fk_trans_gp FOREIGN KEY ( grade ) REFERENCES gp ( grade ),
    CONSTRAINT ck_trans_sem CHECK ( sem IN ( 'Fa', 'Sp', 'SuI', 'SuII' ))
);
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'GEOL', 'G130', 1, 2001, 'Fa', 'B-', 'Indiana Geology' );
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'GEOL', 'G130', 1, 2001, 'Fa', 'A', 'Plate Tectonics' );
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'GEOL', 'G130', 1, 2001, 'Fa', 'A-', 'Vulcanism' );
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'CSCI', '502',  3, 1993, 'Fa', 'A',
          'Compiling and Programming Systems' );
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'CSCI', '590',  3, 1994, 'Sp', 'A+', 'Database Systems' );
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'CSCI', '565',  3, 1994, 'Fa', 'B+',
          'Programming Languages' );
INSERT INTO trans ( sidn, dept, num, credits, yr, sem, grade, name )
 VALUES ( 111111111, 'CSCI', '582',  3, 1995, 'Sp', 'B',
          'Automata and Formal Languages' );