Submitted by : Preetha Rajan

Email: praj016@aucklanduni.ac.nz

library(DBI)
## Warning: package 'DBI' was built under R version 3.5.2
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 3.5.2
drv <- dbDriver("MySQL")
conn <- dbConnect(drv, dbname= "athletics", username="root", password="password")
CREATE TABLE Customer(custno VARCHAR(8) NOT NULL,
Custname VARCHAR(100) NOT NULL,
address VARCHAR(100) NOT NULL,
Internal CHAR(1) NOT NULL,
contact VARCHAR(100) NOT NULL,
phone INTEGER NOT NULL,
city VARCHAR(100) NOT NULL,
state CHAR(2) NOT NULL,
zip INTEGER NOT NULL,
CONSTRAINT CustomerTablePKcustno PRIMARY KEY(custno));
INSERT INTO Customer(custno, Custname, address, Internal, contact, phone, city, state, zip)
VALUES('C100', 'Football', 'Box 352200', 'Y', 'Mary Manager',   6857100, 'Boulder', 'CO', 80309);
INSERT INTO Customer(custno, Custname, address, Internal, contact, phone, city, state, zip)
VALUES('C101', 'Men"s Basketball', 'Box 352400', 'Y', 'Sally Supervisor', 5431700, 'Boulder', 'CO', 80309);
INSERT INTO Customer(custno, Custname, address, Internal, contact, phone, city, state, zip)
VALUES('C103', 'Baseball', 'Box 352020', 'Y', 'Bill Baseball', 5431234, 'Boulder', 'CO', 80309);
INSERT INTO Customer(custno, Custname, address, Internal, contact, phone, city, state, zip)
VALUES('C104', 'Women"s Softball', 'Box 351200', 'Y', 'Sue Softball', 5434321, 'Boulder', 'CO', 80309);
INSERT INTO Customer(custno, Custname, address, Internal, contact, phone, city, state, zip)
VALUES('C105', 'High School Football', '123 Any Street', 'N', 'Coach Bob', 4441234, 'Louiville', 'CO', 80027);
SELECT *
FROM Customer;
5 records
custno Custname address Internal contact phone city state zip
C100 Football Box 352200 Y Mary Manager 6857100 Boulder CO 80309
C101 Men“s Basketball Box 352400 Y Sally Supervisor 5431700 Boulder CO 80309
C103 Baseball Box 352020 Y Bill Baseball 5431234 Boulder CO 80309
C104 Women“s Softball Box 351200 Y Sue Softball 5434321 Boulder CO 80309
C105 High School Football 123 Any Street N Coach Bob 4441234 Louiville CO 80027 
CREATE TABLE Facility(facno VARCHAR(8) NOT NULL,
facname VARCHAR(100) NOT NULL,
CONSTRAINT PKFacilityfacno PRIMARY KEY(facno));
INSERT INTO Facility (facno, facname)
VALUES('F100', 'Football Stadium');
INSERT INTO Facility (facno, facname)
VALUES('F101', 'Basketball arena');
INSERT INTO Facility (facno, facname)
VALUES('F102', 'Baseball field');
INSERT INTO Facility (facno, facname)
VALUES('F103', 'Recreation room');
SELECT *
FROM Facility;
4 records
facno facname
F100 Football Stadium
F101 Basketball arena
F102 Baseball field
F103 Recreation room 
CREATE TABLE Location(locno VARCHAR(8) NOT NULL,
locname VARCHAR(100) NOT NULL,
facno VARCHAR(8) NOT NULL,
CONSTRAINT PKLocationlocno PRIMARY KEY(locno),
CONSTRAINT FKFacilityfacno FOREIGN KEY (facno) REFERENCES Facility (facno));
INSERT INTO Location (locno, facno, locname)
VALUES('L100','F100', 'Locker room');
INSERT INTO Location (locno, facno, locname)
VALUES('L101','F100', 'Plaza');
INSERT INTO Location (locno, facno, locname)
VALUES('L102','F100', 'Vehicle gate');
INSERT INTO Location (locno, facno, locname)
VALUES('L103','F101', 'Locker room');
INSERT INTO Location (locno, facno, locname)
VALUES('L104','F100', 'Ticket Booth');
INSERT INTO Location (locno, facno, locname)
VALUES('L105','F101', 'Gate');
INSERT INTO Location (locno, facno, locname)
VALUES('L106','F100', 'Pedestrian gate');
SELECT *
FROM Location;
7 records
locno locname facno
L100 Locker room F100
L101 Plaza F100
L102 Vehicle gate F100
L103 Locker room F101
L104 Ticket Booth F100
L105 Gate F101
L106 Pedestrian gate F100 
CREATE TABLE Eventrequest(eventno VARCHAR(8) NOT NULL,
dateheld DATE NOT NULL,
datereq DATE NOT NULL,
facno VARCHAR(8) NOT NULL,
custno VARCHAR(8) NOT NULL,
Dateauth DATE,
status VARCHAR(8) NOT NULL,
estcost DECIMAL(9,2) NOT NULL,
estaudience INTEGER NOT NULL,
budno CHAR(5),
CONSTRAINT PKEventrequestEventno PRIMARY KEY(eventno),
CONSTRAINT FKEventrequestfacNo FOREIGN KEY(facno) REFERENCES Facility(facno),
CONSTRAINT FKEventrequestcustNo FOREIGN KEY(custno) REFERENCES Customer(custno));
CREATE VIEW EventReq AS
SELECT eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno
FROM Eventrequest
WHERE status IN('Pending', 'Approved', 'Denied') AND estaudience>0
WITH CHECK OPTION;
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E100', str_to_date('25-OCT-2018','%d-%b-%Y'),  str_to_date('06-JUN-2018', '%d-%b-%Y'), 'F100', 'C100', str_to_date('08-JUN-2018', '%d-%b-%Y'), 'Approved', 5000.00, 80000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E101', str_to_date('26-OCT-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F100', 'C100', str_to_date('', '%d-%b-%Y'), 'Pending', 5000.00, 80000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E102', str_to_date('14-SEP-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F100', 'C100', str_to_date('31-JUL-2018', '%d-%b-%Y'), 'Approved', 5000.00, 80000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E103', str_to_date('21-SEP-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F100', 'C100', str_to_date('01-AUG-2018', '%d-%b-%Y'), 'Approved', 5000.00, 80000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E104', str_to_date('03-DEC-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F101', 'C101', str_to_date('31-JUL-2018', '%d-%b-%Y'), 'Approved', 2000.00, 12000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E105', str_to_date('05-DEC-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F101', 'C101', str_to_date('01-AUG-2018', '%d-%b-%Y'), 'Approved', 2000.00, 10000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E106', str_to_date('12-DEC-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F101', 'C101', str_to_date('31-JUL-2018', '%d-%b-%Y'), 'Approved', 2000.00, 10000, 'B1000');
INSERT INTO EventReq(eventno, dateheld, datereq, facno, custno, Dateauth, status, estcost, estaudience, budno)
VALUES('E107', str_to_date('23-NOV-2018', '%d-%b-%Y'), str_to_date('28-JUL-2018', '%d-%b-%Y'), 'F100', 'C105', str_to_date('31-JUL-2018', '%d-%b-%Y'), 'Denied', 10000.00, 5000, '');
SELECT *
FROM EventReq;
8 records
eventno dateheld datereq facno custno Dateauth status estcost estaudience budno
E100 2018-10-25 2018-06-06 F100 C100 2018-06-08 Approved 5000 80000 B1000
E101 2018-10-26 2018-07-28 F100 C100 0000-00-00 Pending 5000 80000 B1000
E102 2018-09-14 2018-07-28 F100 C100 2018-07-31 Approved 5000 80000 B1000
E103 2018-09-21 2018-07-28 F100 C100 2018-08-01 Approved 5000 80000 B1000
E104 2018-12-03 2018-07-28 F101 C101 2018-07-31 Approved 2000 12000 B1000
E105 2018-12-05 2018-07-28 F101 C101 2018-08-01 Approved 2000 10000 B1000
E106 2018-12-12 2018-07-28 F101 C101 2018-07-31 Approved 2000 10000 B1000
E107 2018-11-23 2018-07-28 F100 C105 2018-07-31 Denied 10000 5000 
CREATE TABLE Employee(empno VARCHAR(8) NOT NULL,
empname VARCHAR(100) NOT NULL,
department VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(100) NOT NULL,
CONSTRAINT PKEmployeeempno PRIMARY KEY(empno));
INSERT INTO Employee(empno, empname, department, email, phone)
VALUES('E100', 'Chuck Coordinator', 'Administration', 'chuck@colorado.edu', '3-1111');
INSERT INTO Employee(empno, empname, department, email, phone)
VALUES('E101', 'Mary Manager', 'Football', 'mary@colorado.edu', '5-1111');
INSERT INTO Employee(empno, empname, department, email, phone)
VALUES('E102', 'Sally Supervisor', 'Planning', 'sally@colorado.edu', '3-2222');
INSERT INTO Employee(empno, empname, department, email, phone)
VALUES('E103', 'Alan Administrator', 'Administration', 'alan@colorado.edu', '3-3333');
SELECT *
FROM Employee;
4 records
empno empname department email phone
E100 Chuck Coordinator Administration chuck@colorado.edu 3-1111
E101 Mary Manager Football mary@colorado.edu 5-1111
E102 Sally Supervisor Planning sally@colorado.edu 3-2222
E103 Alan Administrator Administration alan@colorado.edu 3-3333 
CREATE TABLE EventPlan(planno VARCHAR(8) NOT NULL,
Eventno VARCHAR(8) NOT NULL,
workdate DATE NOT NULL,
Notes VARCHAR(100),
activity VARCHAR(100) NOT NULL,
empno VARCHAR(8),
CONSTRAINT PKEventPlanplanno PRIMARY KEY(planno),
CONSTRAINT FKEventPlanempno FOREIGN KEY(empno) REFERENCES Employee(empno),
CONSTRAINT FKEventPlanEventNo FOREIGN KEY(eventno) REFERENCES Eventrequest(eventno));
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P100', 'E100', str_to_date('25-Oct-2018', '%d-%b-%Y'), 'Standard operation', 'Operation', 'E102');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P101', 'E104', str_to_date('03-Dec-2018', '%d-%b-%Y'), 'Watch for gate crashers', 'Operation', 'E100');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P102', 'E105', str_to_date('05-Dec-2018', '%d-%b-%Y'), 'Standard operation', 'Operation', 'E102');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P103', 'E106', str_to_date('12-Dec-2018', '%d-%b-%Y'), 'Watch for seat switching', 'Operation', NULL);
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P104', 'E101', str_to_date('26-Oct-2018', '%d-%b-%Y'),'Standard cleanup', 'Cleanup', 'E101');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P105', 'E100', str_to_date('25-Oct-2018', '%d-%b-%Y'), 'Light cleanup', 'Cleanup', 'E101');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P199', 'E102', str_to_date('10-Dec-2018', '%d-%b-%Y'), 'Standard operation', 'Operation', 'E101');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P299', 'E101', str_to_date('26-Oct-2018', '%d-%b-%Y'),'', 'Operation', 'E101');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P349', 'E106', str_to_date('12-Dec-2018', '%d-%b-%Y'),'', 'Cleanup', 'E101');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P85', 'E100', str_to_date('25-Oct-2018', '%d-%b-%Y'),'Standard operation', 'Setup', 'E102');
INSERT INTO EventPlan(planno, Eventno, workdate, Notes, activity, empno)
VALUES('P95', 'E101', str_to_date('26-Oct-2018', '%d-%b-%Y'),'Extra security', 'Setup', 'E102');
SELECT *
FROM EventPlan;
Displaying records 1 - 10
planno Eventno workdate Notes activity empno
P100 E100 2018-10-25 Standard operation Operation E102
P101 E104 2018-12-03 Watch for gate crashers Operation E100
P102 E105 2018-12-05 Standard operation Operation E102
P103 E106 2018-12-12 Watch for seat switching Operation NA
P104 E101 2018-10-26 Standard cleanup Cleanup E101
P105 E100 2018-10-25 Light cleanup Cleanup E101
P199 E102 2018-12-10 Standard operation Operation E101
P299 E101 2018-10-26 Operation E101
P349 E106 2018-12-12 Cleanup E101
P85 E100 2018-10-25 Standard operation Setup E102 
CREATE TABLE ResourceTbl(resno VARCHAR(8) NOT NULL,
Resname VARCHAR(100) NOT NULL,
rate DECIMAL(5, 2) NOT NULL,
CONSTRAINT PKResourceTblresno PRIMARY KEY(resno));
INSERT INTO ResourceTbl(resno, Resname, rate)
VALUES('R100', 'Attendant', '10.00');
INSERT INTO ResourceTbl(resno, Resname, rate)
VALUES('R101', 'Police', '15.00');
INSERT INTO ResourceTbl(resno, Resname, rate)
VALUES('R102', 'Usher', '10.00');
INSERT INTO ResourceTbl(resno, Resname, rate)
VALUES('R103', 'Nurse', '20.00');
INSERT INTO ResourceTbl(resno, Resname, rate)
VALUES('R104', 'Janitor', '15.00');
INSERT INTO ResourceTbl(resno, Resname, rate)
VALUES('R105', 'food service', '10.00');
SELECT *
FROM ResourceTbl;
6 records
resno Resname rate
R100 Attendant 10
R101 Police 15
R102 Usher 10
R103 Nurse 20
R104 Janitor 15
R105 food service 10 
CREATE TABLE EventPlanLine(planno VARCHAR(8) NOT NULL,
lineno INTEGER NOT NULL,
timestart TIME NOT NULL, 
timeend TIME NOT NULL, 
numberfld INTEGER NOT NULL,
locno VARCHAR(8) NOT NULL,
resno VARCHAR(8) NOT NULL,
CONSTRAINT PKEventPlanLineplannolineno PRIMARY KEY(planno, lineno),
CONSTRAINT FKEventPlanLineplanno FOREIGN KEY(planno) REFERENCES EventPlan(planno),
CONSTRAINT FKEventPlanLinelocno FOREIGN KEY(locno) REFERENCES Location(locno),
CONSTRAINT FKEventPlanLineresno FOREIGN KEY(resno) REFERENCES ResourceTbl(resno));
CREATE VIEW eventplnline AS
SELECT planno, lineno, timestart, timeend, numberfld, locno, resno
FROM EventPlanLine
WHERE timeend>timestart
WITH CHECK OPTION;
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P100', 1, '8:00:00', '17:00:00', 2, 'L100', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P100', 2, '12:00:00', '17:00:00', 2, 'L101', 'R101');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P100', 3, '7:00:00', '16:30:00', 1, 'L102', 'R102');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P100', 4, '18:00:00', '22:00:00', 2, 'L100', 'R102');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P101', 1, '18:00:00', '20:00:00', 2, 'L103', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P101', 2, '18:30:00', '19:00:00', 4, 'L105', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P101', 3, '19:00:00', '20:00:00', 2, 'L103', 'R103');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P102', 1, '18:00:00', '19:00:00', 2, 'L103', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P102', 2, '18:00:00', '21:00:00', 4, 'L105', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P102', 3, '19:00:00','22:00:00', 2, 'L103', 'R103');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P103', 1,'18:00:00','21:00:00', 2, 'L103', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P103', 2,'18:00:00','21:00:00', 4, 'L105', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P103', 3,'19:00:00','22:00:00', 2, 'L103', 'R103');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P104', 1, '18:00:00','22:00:00', 4, 'L101', 'R104');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P104', 2,'18:00:00','22:00:00', 4, 'L100', 'R104');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P105', 1,'18:00:00','22:00:00', 4, 'L101', 'R104');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P105', 2,'18:00:00','22:00:00', 4, 'L100', 'R104');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P199', 1,'8:00:00','12:00:00', 1, 'L100', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P349', 1,'12:00:00','15:30:00', 1, 'L103', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P85', 1,'9:00:00','17:00:00', 5, 'L100', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P85', 2,'8:00:00', '17:00:00', 2, 'L102', 'R101');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P85', 3, '10:00:00', '15:00:00', 3, 'L104', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P95', 1, '8:00:00','17:00:00', 4, 'L100', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P95', 2, '9:00:00','17:00:00', 4, 'L102', 'R101');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P95', 3,'10:00:00','15:00:00', 4, 'L106', 'R100');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P95', 4,'13:00:00','17:00:00', 2, 'L100', 'R103');
INSERT INTO eventplnline(planno, lineno, timestart, timeend, numberfld, locno, resno)
VALUES('P95', 5,'13:00:00','17:00:00', 2, 'L101', 'R104');
SELECT *
FROM EventPlanLine;
Displaying records 1 - 10
planno lineno timestart timeend numberfld locno resno
P100 1 08:00:00 17:00:00 2 L100 R100
P100 2 12:00:00 17:00:00 2 L101 R101
P100 3 07:00:00 16:30:00 1 L102 R102
P100 4 18:00:00 22:00:00 2 L100 R102
P101 1 18:00:00 20:00:00 2 L103 R100
P101 2 18:30:00 19:00:00 4 L105 R100
P101 3 19:00:00 20:00:00 2 L103 R103
P102 1 18:00:00 19:00:00 2 L103 R100
P102 2 18:00:00 21:00:00 4 L105 R100
P102 3 19:00:00 22:00:00 2 L103 R103