Table Creation for sample_school

/*  The Tables
------------------------------------------------------
    FACULTY(_ID_, LNAME, OFFICE, BLDG, PHONE, SALARY)
    COURSE(_NUMBER_, _DEPT_, LEVEL, MAXSIZE)
    TEACH(_ID_, _NUMBER_, _DEPT_)
    MEMBEROF(_ID_, _DEPT_)
------------------------------------------------------
*/

-- Creates the database
CREATE DATABASE sample_school;

-- Sets the sample school db as the active db
USE sample_school;

-- Creates the table for Faculty Members
CREATE TABLE IF NOT EXISTS Faculty (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Lname VARCHAR(20),
    Office INT,
    Bldg VARCHAR(10),
    Phone VARCHAR(25),
    Salary DECIMAL(8, 2) DEFAULT 76000.00 CHECK (Salary <= 200000)
);

-- Creates the table containing all of the courses
CREATE TABLE IF NOT EXISTS Course (
    Course_Number INT AUTO_INCREMENT NOT NULL,
    Dept CHAR(4) REFERENCES MemberOf,
    Course_Level INT,
    MaxSize INT,

    PRIMARY KEY (Course_Number, Dept)
);

-- Creates the table to connect Members with Departments
CREATE TABLE IF NOT EXISTS MemberOf (
    ID INT REFERENCES Faculty,
    Dept CHAR(4),

    PRIMARY KEY(ID, Dept)
);

-- Creates the teaching table
CREATE TABLE IF NOT EXISTS Teach (
    ID INT REFERENCES Faculty,
    Course_Number INT REFERENCES Course,
    Dept CHAR(4) REFERENCES MemberOf,

    PRIMARY KEY (ID, Course_Number, Dept),
    FOREIGN KEY (Course_Number, Dept) REFERENCES Course(Course_Number, Dept)
);

-- Inserts some default values into the Faculty table
INSERT INTO
    Faculty(Lname, Office, Bldg, Phone, Salary)
VALUES
    ('Cotts', 103, 'DuPont', '555-555-1234', 48000),
    ('Garth', 423, 'DuPont', '555-555-4321', 137500),
    ('Jones', 211, 'Ewing', '555-555-9876', 132546),
    ('Noone', 421, 'Smith', '555-555-6789', 56872),
    ('Smith', 301, 'Ewing', '555-555-3456', 68791),
    ('Smith', 102, 'Smith', '555-555-8765', 87350),
    ('Xyzyx', 099, 'Smith', '555-555-2468', 73426),
    ('Einstein', 001, 'Sharp', '555-555-1111', 175356);

-- Inserts the values into MemberOf table
INSERT INTO
    MemberOf
VALUES
    (2, 'EE'),
    (5, 'MATH'),
    (1, 'EE'),
    (1, 'CIS'),
    (1, 'MATH'),
    (1, 'PHYS'),
    (4, 'CIS'),
    (3, 'MATH'),
    (6, 'CIS'),
    (7, 'CIS'),
    (8, 'PHYS');

-- Inserts the values into the course list
INSERT INTO
    Course
VALUES
    (411, 'CIS', 4, 25),
    (180, 'CIS', 1, 60),
    (220, 'CIS', 2, 40),
    (637, 'CIS', 5, 25),
    (652, 'CIS', 5, 25),
    (221, 'MATH', 1, 60),
    (411, 'MATH', 4, 25),
    (230, 'MATH', 2, 60),
    (413, 'EE', 4, 25),
    (323, 'EE', 3, 25),
    (867, 'EE', 5, 25);

-- Inserts the values for teach
INSERT INTO
    Teach
VALUES
    (2, 323, 'EE'),
    (2, 413, 'EE'),
    (5, 411, 'CIS'),
    (5, 411, 'MATH'),
    (1, 867, 'EE'),
    (1, 652, 'CIS'),
    (4, 180, 'CIS'),
    (3, 230, 'MATH'),
    (6, 220, 'CIS'),
    (6, 637, 'CIS');

-- At the top I display what the relational model for the tables are, I then detail the creation and insertion of data.