Write a Python program to query a MySQL database and print the query results. Details:

Put a comment at the top of the Python file called lab7q1 with your name and student number. (0.5 mark)

Make a connection to the MySQL database with this connection information: (0.5 mark)

 cnx = mysql.connector.connect(user='rlawrenc', password='test', host='cosc304.ok.ubc.ca', database='WorksOn') 

Write and execute a SQL query that returns the department number (dno), department name (dname), project number (pno), project name (pname), sum of hours worked on the project as well as the number of employees working on the project. Only include records where the budget is greater than $140,000. Order by sum of hours worked descending. (3 marks)

Print out your query results. Note that tabs ("\t") are used to line up the data in columns nicely. (1 mark)

To install the MySQL driver using Anaconda, run this command:

conda install -c https://conda.anaconda.org/anaconda mysql-connector-python 

Otherwise, see these download/install instructions.

WorksOn Relational Database Schema

emp (eno CHAR(5), ename VARCHAR(30), bdate DATE, title CHAR(2), salary DECIMAL(9,2), supereno CHAR(5), dno CHAR(5), PRIMARY KEY (eno)) dept (dno CHAR(5), dname VARCHAR(40), mgreno CHAR(5), PRIMARY KEY (dno)) -- mgreno is the employee number of the manager of the department (may join with emp) proj (pno CHAR(5), pname VARCHAR(40), budget DECIMAL(9,2), dno CHAR(5), PRIMARY KEY (pno)) -- dno is the department that has the project (may join with dept) workson (eno CHAR(5), pno CHAR(5), resp VARCHAR(20), hours SMALLINT, PRIMARY KEY (eno,pno)) -- Use eno to join with emp. Use pno to join with proj. 

Sample Output

pno pname dno dname hours numemp P4 Maintenance D2 Consulting 96 2 P3 Budget D3 Accounting 46 2 P1 Instruments D1 Management 36 2 

Question #2 - Analyzing Database Data (5 marks)

Write a Python program to query a MySQL database to extract the age and salary of the employees with salary greater than $20,000 and less than $55,000. Print the age and salaries in descending order by salary then perform a regression to determine if there is a relationship between them. Details:

Put a comment at the top of the Python file called lab7q2 with your name and student number. (0.5 mark)

Make a connection to MySQL database with this connection information: (0.5 mark)

 cnx = mysql.connector.connect(user='rlawrenc', password='test', host='cosc304.ok.ubc.ca', database='WorksOn') 

Write and execute a SQL query that returns the age and salary of employees with salary greater than $20,000 and less than $55,000 ordered by salary descending. (1 mark)

Note: Computing the age requires using the timestampdiff() function of MySQL like this: timestampdiff(YEAR, bdate, CURDATE()). (Click for details)

Create lists of the data so that you can perform linear regression. Convert the salary data using float(). (1 mark)

Perform a linear regression and output the predicted formula, y-values, prediction error, and residual error. (1 mark)

Show the linear regression results in a chart. (1 mark)

Sample Output

age salary 50 50000.00 45 50000.00 44 50000.00 50 40000.00 39 40000.00 42 30000.00 51 30000.00 Formula: 11.2612612613 * x + 40912.1621622 = y Predicted y-values: [ 41475.22522523 41418.91891892 41407.65765766 41475.22522523 41351.35135135 41385.13513514 41486.48648649] Prediction error: [ 8524.77477477 8581.08108108 8592.34234234 -1475.22522523 -1351.35135135 -11385.13513514 -11486.48648649] Residual error: 9855.94438091 

Question #3 - Map-Reduce (5 marks)

Create a Python program that uses Map-Reduce to analyze a data set. You must use map, filter, and reduce functions. Details:

Put a comment at the top of the Python file called lab7q3 with your name and student number. (0.5 mark)

Use a data set that consists of the numbers from 1 to 10. (0.5 mark)

Apply a map function to the data set that will divide the value by 2 if it is even or multiply by 3 if it is odd. Print the result after the map function is applied. (2 marks)

Apply a filter function that will keep the value if it is between 5 and 20 inclusive. Print the result after the filter function is applied. (1 mark)

Apply a reduce function that will add the two values if the first value is greater than the second otherwise it will multiply them. Print the result after reduce function is applied. (1 mark)

Sample Output

Result after map: [3, 1.0, 9, 2.0, 15, 3.0, 21, 4.0, 27, 5.0] Result after filter: [9, 15, 5.0] Result after reduce: 140.0 

50000 45000 40000 . 35000 30000 38 40 42 46 48 50 52 50000 45000 40000 . 35000 30000 38 40 42 46 48 50 52