Web & Database

'use strict';
/*
User ID: smithp4
Assignment: Lab 06
Date: 2/22/2018
*/
$(document).ready(function() {
    //checking the main id of the section we are about to modify
    $("#jdom a").click(function(evt) {
        //toggling the hide class
        $(this).toggleClass("hide");
        //if the class is not hide, do x
        if ($(this).attr("class") !== "hide") {
            $(this).prev().hide();
            $(this).text("Show more");
        }
        //if the class is hide, do y
        else {
            $(this).prev().show();
            $(this).text("Show less");
        }
        evt.preventDefault();
    }); // end click
    $("#jdom").find("a:first").focus();
}); // end ready

# Pierce Smith
# Assignment 8

# Exercise One

CREATE DATABASE IF NOT EXISTS painters;
USE painters;
# Exercise Two
DROP VIEW IF EXISTS myView;
DROP VIEW IF EXISTS totalPay;

# Exercise Three
DROP TABLE IF EXISTS painters.empjob;
DROP TABLE IF EXISTS painters.employee;
DROP TABLE IF EXISTS painters.job;
DROP TABLE IF EXISTS painters.customer;

# Exercise Four-One

CREATE TABLE customer
(
custid   SMALLINT (4)UNSIGNED NOT NULL PRIMARY KEY,
ctype    ENUM('C','R'),
clname   VARCHAR(35) NOT NULL,
cfname   VARCHAR(15) NOT NULL,
addr     VARCHAR(40) NULL,
city     VARCHAR(20) NULL,
state    CHAR(2) DEFAULT 'SC',
cphone   CHAR(12) NOT NULL UNIQUE
)
ENGINE=INNODB;

# Exercise Four-Two

CREATE TABLE job
(
jobnum   SMALLINT (5)UNSIGNED NOT NULL PRIMARY KEY,
custid   SMALLINT (5)UNSIGNED NOT NULL,
jobdate  DATE NULL,
descr    TEXT NULL,
amobilled DECIMAL(7,2) NULL,
amopaid   DECIMAL(7,2) NULL,
FOREIGN KEY(custid) REFERENCES customer(custid)
)
ENGINE=INNODB;

# Exercise Four-Three

CREATE TABLE employee
(
ssn    CHAR(9) NOT NULL PRIMARY KEY,
elname VARCHAR(35) NOT NULL,
efname VARCHAR(15) NOT NULL,
ephone VARCHAR(12) NULL,
hrrate DECIMAL(5,2) DEFAULT 5.15
)
ENGINE=INNODB;

# Exercise Four-Four
CREATE TABLE empjob

(
ssn       CHAR(9) NOT NULL,
jobnum    SMALLINT(5) UNSIGNED NOT NULL,
hrsperjob DECIMAL(5,2) NULL,
FOREIGN KEY(ssn) REFERENCES employee(ssn),
FOREIGN KEY(jobnum) REFERENCES job(jobnum),
PRIMARY KEY (ssn,jobnum)
)
ENGINE=INNODB;

# Exercise Five
CREATE INDEX full_name
ON customer(cfname, clname);

# Exercise Six

CREATE INDEX job_fk
ON job(custid);

CREATE INDEX empjob_fk
ON empjob(ssn, jobnum);

# Exercise Seven

INSERT INTO customer
(custid,ctype,clname,cfname,addr,city,state,cphone)
VALUES
(0000,'R', 'Smith', 'Pierce', '3 Tetris Ln.', 'Aiken', 'SC', '800 555-5555');

INSERT INTO customer
(custid,ctype,clname,cfname,addr,city,state,cphone)
VALUES
(0001,'C', 'James', 'Greg', '784 Taft Dr', 'Pasco', 'WA', '800 522-7495');

INSERT INTO customer
(custid,ctype,clname,cfname,addr,city,state,cphone)
VALUES
(0002, 'R', 'Judge', 'Taylor', '22 Saint Thomas Ln', 'Portland', 'OR', '800 864-5576');

INSERT INTO job
(jobnum,custid,jobdate,descr,amobilled,amopaid)
VALUES
(11111,0000,'2013-12-24',NULL,4562.14,100.99);

INSERT INTO job
(jobnum,custid,jobdate,descr,amobilled,amopaid)
VALUES
(22222,0002,'2020-07-20','Furniture Re-Painting',1000.00,210.99);

INSERT INTO job
(jobnum,custid,jobdate,descr,amobilled,amopaid)
VALUES
(33333,0001,NULL, NULL, 460.00, 150.99);

INSERT INTO employee
(ssn,elname,efname,ephone,hrrate)
VALUES
('888015600', 'Smith', 'James', '800 244-7426', 15.00);

INSERT INTO employee
(ssn,elname,efname,ephone,hrrate)
VALUES
('123456789', 'Vorhees', 'Jason','800 521-8795',13.13);

INSERT INTO employee
(ssn,elname,efname,ephone,hrrate)
VALUES
('003050009', 'Meyers', 'Michael', '800 884-3265', 10.31);

INSERT INTO empjob
(ssn, jobnum, hrsperjob)
VALUES
('987654321', 11111, 1.00);

INSERT INTO empjob
(ssn, jobnum, hrsperjob)
VALUES
('123456789', 33333, 20.00);

INSERT INTO empjob
(ssn, jobnum, hrsperjob)
VALUES
('658545255', 22222, 7.50);

# Exercise Eight
CREATE VIEW View1 AS
SELECT custid,jobnum,jobdate,ssn
FROM job, employee;

# Exercise Nine
CREATE VIEW View2 AS
SELECT SUM(hrsperjob*hrrate)
FROM empjob, employee;

select *
from painters.view1;

select *
from painters.view2;

# Exercise Ten
UPDATE customer
SET cfname='Jason'
WHERE custid=0002;

UPDATE job
SET descr='Interior Re-Finishing'
WHERE jobnum=11111;

UPDATE employee
SET hrrate=15.50
WHERE ssn='888015600';

UPDATE empjob
SET hrsperjob=100.00
WHERE jobnum=11111;


# Exercise Eleven

DELETE FROM empjob
WHERE ssn= '123456789';

DELETE FROM employee
WHERE ssn= '123456789';

DELETE FROM job
WHERE jobnum= 33333;

DELETE FROM customer
WHERE custid=0001;

# Exercise Twelve

CREATE USER freddykrueger@localhost
IDENTIFIED BY 'freddyiscoming4u';

CREATE USER texarkana@localhost
IDENTIFIED BY 'moonlight';

CREATE USER xboxslayer@localhost
IDENTIFIED BY 'xbox4life';


# Exercise Thirteen

GRANT ALL
ON painters.*
TO freddykrueger@localhost;

GRANT SELECT
ON painters.*
TO xboxslayer@localhost;

GRANT ALL
ON painters.customer
TO texarkana@localhost;

GRANT ALL
ON painters.job
TO xboxslayer@localhost;

GRANT SELECT
ON painters.employee
TO texarkana@localhost;

GRANT SELECT
ON painters.empjob
TO xboxslayer@localhost;

DROP USER xboxslayer@localhost;
DROP USER texarkana@localhost;
DROP USER freddykrueger@localhost;