CREATE TABLE STUDENT ( Name VARCHAR(30) NOT NULL,
Student_number INTEGER NOT NULL,
Class CHAR NOT NULL,
Major CHAR(4),
PRIMARY KEY (Student_number) );

INSERT INTO STUDENT
  (Name, Student_number, Class, Major)
VALUES
  ("Smith", 17, 1, "CS"),
  ("fsdfs", 48, , "CS"),
  ("Brown", 8, 2, "CS");

CREATE TABLE COURSE ( Course_name VARCHAR(30) NOT NULL,
Course_number CHAR(8) NOT NULL,
Credit_hours INTEGER,
Department CHAR(4),
PRIMARY KEY (Course_number),
UNIQUE (Course_name) );

INSERT INTO COURSE
  (Course_name, Course_number, Credit_hours, Department)
VALUES
  ("Intro to Computer Science", "CS1310", 4, "CS"),
  ("Data Structures", "CS3320", 4, "CS"),
  ("Discrete Mathematics", "MATH2410", 3, "MATH"),
  ("Database", "CS3380", 3, "CS");

CREATE TABLE PREREQUISITE ( Course_number CHAR(8) NOT NULL,
Prerequisite_number CHAR(8) NOT NULL,
PRIMARY KEY (Course_number, Prerequisite_number),
FOREIGN KEY (Course_number) REFERENCES
COURSE (Course_number),
FOREIGN KEY (Prerequisite_number) REFERENCES
COURSE (Course_number) );

INSERT INTO PREREQUISITE
  (Course_number, Prerequisite_number)
VALUES
  ("CS3380", "CS3320"),
  ("CS3380", "MATH2410"),
  ("CS3320", "CS1310");

CREATE TABLE SECTION ( Section_identifier INTEGER NOT NULL,
Course_number CHAR(8) NOT NULL,
Semester VARCHAR(6) NOT NULL,
YEAR CHAR(4) NOT NULL,
Instructor VARCHAR(15),
PRIMARY KEY (Section_identifier),
FOREIGN KEY (Course_number) REFERENCES
COURSE (Course_number) );

INSERT INTO SECTION
  (Section_identifier, Course_number, Semester, YEAR, Instructor)
VALUES
  (85, "MATH2410", "Fall", "07", "King"),
  (92, "CS1310", "Fall", "07", "Anderson"),
  (102, "CS3320", "Spring", "08", "Knuth"),
  (112, "MATH2410", "Fall", "08", "Chang"),
  (119, "CS1310", "Fall", "08", "Anderson"),
  (135, "CS3380", "Fall", "08", "Stone");



CREATE TABLE GRADE_REPORT ( Student_number INTEGER NOT NULL,
Section_identifier INTEGER NOT NULL,
Grade CHAR,
PRIMARY KEY (Student_number, Section_identifier),
FOREIGN KEY (Student_number) REFERENCES
STUDENT (Student_number),
FOREIGN KEY (Section_identifier) REFERENCES
SECTION (Section_identifier) );
;

INSERT INTO GRADE_REPORT
  (Student_number, Section_identifier, Grade)
VALUES
  (17, 112, 'B'),
  (17, 119, 'C'),
  (8, 85, 'A'),
  (8, 92, 'A'),
  (8, 102, 'B'),
  (8, 135, 'A');