DROP DATABASE IF EXISTS student_db;
CREATE DATABASE student_db;
USE student_db;

CREATE TABLE students
(
   studentID SMALLINT NOT NULL,
   first_name VARCHAR(20) NOT NULL,
   last_name VARCHAR(20) NOT NULL,
   other VARCHAR(50) default '' NOT NULL,
   PRIMARY KEY (studentID)
) TYPE=MyISAM;


CREATE TABLE courses
(
   courseID SMALLINT NOT NULL,
   dept_code CHAR(4) NOT NULL,
   dept_number CHAR(4) NOT NULL,
   lang CHAR(1) default 'E' NOT NULL,
   section CHAR(2) default '01' NOT NULL,
   description VARCHAR(50) default '' NOT NULL,
   PRIMARY KEY (courseID)
) TYPE=MyISAM;

CREATE TABLE classes
(
   courseID SMALLINT NOT NULL,
   studentID SMALLINT NOT NULL
) TYPE=MyISAM;

--
-- Create some students
--

INSERT INTO students VALUES (12345, 'Fred', 'Jones', '');
INSERT INTO students VALUES (12346, 'June', 'Jones', '');
INSERT INTO students VALUES (12347, 'Bill', 'Franks', '');
INSERT INTO students VALUES (12348, 'Jane', 'Simpson', '');
INSERT INTO students VALUES (12349, 'Gord', 'Smith', '');
INSERT INTO students VALUES (12350, 'Jim', 'Carter', '');
INSERT INTO students VALUES (12351, 'Bob', 'Marley', '');
INSERT INTO students VALUES (12352, 'Linda', 'Boyer', '');
INSERT INTO students VALUES (12353, 'Carol', 'Jenkins', '');

INSERT INTO students VALUES (22345, 'Ralph', 'Martin', '');
INSERT INTO students VALUES (22346, 'Stephen', 'Jenkins', '');
INSERT INTO students VALUES (22347, 'Lorrie', 'Stevens', '');
INSERT INTO students VALUES (22348, 'Andrew', 'Collins', '');
INSERT INTO students VALUES (22349, 'Barbara', 'Parker', '');
INSERT INTO students VALUES (22350, 'Bill', 'Brady', '');

--
-- Create some courses for them to take
--

INSERT INTO courses VALUES (1, 'MATH', '1036', 'E', '01', 'Calculus I');
INSERT INTO courses VALUES (2, 'MATH', '1037', 'E', '01', 'Calculus II');
INSERT INTO courses VALUES (3, 'COSC', '1046', 'E', '01', 'Computer Science I');
INSERT INTO courses VALUES (4, 'COSC', '1047', 'E', '01', 'Computer Science II');
INSERT INTO courses VALUES (5, 'COSC', '2206', 'E', '01', 'Internet Tools');

--
-- Create some classes of students
--

INSERT INTO classes VALUES (1, 12345);
INSERT INTO classes VALUES (1, 12346);
INSERT INTO classes VALUES (1, 12353);


INSERT INTO classes VALUES (2, 12345);
INSERT INTO classes VALUES (2, 12346);
INSERT INTO classes VALUES (2, 12353);
INSERT INTO classes VALUES (2, 12348);


INSERT INTO classes VALUES (3, 12345);
INSERT INTO classes VALUES (3, 12346);
INSERT INTO classes VALUES (3, 12347);
INSERT INTO classes VALUES (3, 12348);
INSERT INTO classes VALUES (3, 12349);
INSERT INTO classes VALUES (3, 12350);
INSERT INTO classes VALUES (3, 12351);
INSERT INTO classes VALUES (3, 12352);
INSERT INTO classes VALUES (3, 12353);

INSERT INTO classes VALUES (4, 12345);
INSERT INTO classes VALUES (4, 12346);
INSERT INTO classes VALUES (4, 12347);
INSERT INTO classes VALUES (4, 12348);
INSERT INTO classes VALUES (4, 12349);
INSERT INTO classes VALUES (4, 12350);

INSERT INTO classes VALUES (5, 22345);
INSERT INTO classes VALUES (5, 22346);
INSERT INTO classes VALUES (5, 22347);
INSERT INTO classes VALUES (5, 22348);
INSERT INTO classes VALUES (5, 22349);
INSERT INTO classes VALUES (5, 22350);


