*
* Note: The field naming could be cleaner. For example a field named ’emp_last_name’ is more
* descriptive than ‘l_name’. However, to limit confusion, I used the exact field names
* provided as I walked you through the final design in my videos which support Part 1.
*
*********************************************************/

DROP DATABASE IF EXISTS project_management_center;
CREATE DATABASE project_management_center;

USE project_management_center;

CREATE TABLE role_rate
(
job_code INT PRIMARY KEY,
emp_role VARCHAR(45) NOT NULL,
emp_rate DECIMAL(9,2) NOT NULL
);

CREATE TABLE employee
(
emp_num INT PRIMARY KEY,
f_name VARCHAR(45) NOT NULL,
l_name VARCHAR(45) NOT NULL,
hire_date DATE,
job_code INT NOT NULL,
CONSTRAINT fk_employee_role1
FOREIGN KEY (job_code)
REFERENCES role_rate (job_code)
);

CREATE TABLE project
(
pro_num INT PRIMARY KEY,
pro_name VARCHAR(45) NOT NULL,
emp_num INT NOT NULL,
CONSTRAINT fk_project_emp_lead
FOREIGN KEY (emp_num)
REFERENCES employee (emp_num)
);

CREATE TABLE assignment
(
pro_num INT,
emp_num INT,
hours_bill_to_proj DECIMAL(5,2) NOT NULL,
CONSTRAINT pk_assignment_id PRIMARY KEY (pro_num, emp_num),
CONSTRAINT fk_assignment_project
FOREIGN KEY (pro_num)
REFERENCES project (pro_num),
CONSTRAINT fk_assignment_employee
FOREIGN KEY (emp_num)
REFERENCES employee (emp_num)
);

INSERT INTO role_rate VALUES
(1, ‘CIVIL ENGINEER’, 185.5),
(2, ‘SURVEYOR’, 95),
(3, ‘ENGINEER IN TRAINING’, 65.75),
(4, ‘STRUCTURAL ENGINEER’, 196.75),
(5, ‘SAFETY ENGINEER’, 148.1),
(6, ‘GENERAL SUPPORT’, 25),
(7, ‘ANALYST’, 45.95),
(8, ‘CLERICAL SUPPORT’, 30.5);

INSERT INTO employee VALUES
(103, ‘Devin’, ‘Ryals’, ‘2012-03-14’, 1),
(101, ‘Scott’, ‘Helder’, ‘2012-04-14’, 2),
(105, ‘Asneth’, ‘Famolari’, ‘2012-04-23’, 2),
(106, ‘Jonathan’, ‘Ryker’, ‘2001-01-20’, 3),
(102, ‘Daniel’, ‘Riske’, ‘2002-03-12’, 4),
(114, ‘Melissa’, ‘Johnson’, ‘2005-05-15’, 5),
(118, ‘Jim’, ‘Griffen’, ‘2006-05-05’, 6),
(104, ‘Videll’, ‘Gutierrez’, ‘2007-12-13’, 4),
(112, ‘Tony’, ‘Spinelli’, ‘2009-09-16’, 7),
(113, ‘Terri’, ‘Ward’, ‘2003-03-16’, 5),
(111, ‘Sean’, ‘West’, ‘2012-12-30’, 8),
(107, ‘Steven’, ‘Schaub’, ‘2003-02-28’, 3),
(115, ‘Hamir’, ‘Mehta’, ‘2008-12-17’, 4),
(108, ‘Joseph’, ‘Warren’, ‘2000-03-13’, 4);

INSERT INTO project VALUES
(15, ‘Yosemite’,105),
(18, ‘Grand Canyon’,104),
(22, ‘Everglades’,113),
(25, ‘Grand Teton’,101);

INSERT INTO assignment VALUES
(15, 103, 23.8),
(15, 101, 19.4),
(15, 105, 35.7),
(15, 106, 12.6),
(15, 102, 23.8),
(18, 114, 25.6),
(18, 118, 45.3),
(18, 104, 32.4),
(18, 112, 45),
(22, 105, 65.7),
(22, 104, 48.4),
(22, 113, 23.6),
(22, 111, 22),
(22, 106, 12.8),
(25, 107, 25.6),
(25, 115, 45.8),
(25, 101, 56.3),
(25, 114, 33.1),
(25, 108, 23.6),
(25, 118, 30.5),
(25, 112, 41.4);