Consider a university database that is used to keep track of departments and professors. The database has two tables only, one for professors called PROFESSOR and a second one for the de- partments and is called DEPARTMENT. The two tables are summarized below. PROFESSOR has the following columns: pno INT NOT NULL pname VARCHAR(20) NOT NULL pjob VARCHAR(25) NOT NULL pmgr INT ALLOWS NULL phiredate DATE NOT NULL psalary INT NOT NULL pbonus INT NULL pdno INT NOT NULL and DEPARTMENT has the following columns: dno INT NOT NULL dname VARCHAR(20) NOT NULL dlocation VARCHAR(20) NOT NULL The primary key of PROFESSOR is pno and that of DEPARTMENT is dno. There are two foreign keys: PROFESSOR.pdno that references DEPARTMENT and PROFESSOR.pmgr that references PROFESSOR. The PROFESSOR.pmgr holds pno of the manager for a professor. Perform each of the following tasks using MySQL. Create an SQL script to keep track of your solutions and copy and paste the SQL statements you executed to perform each of the following tasks in your SQL script. Moreover, copy and paste the output of your SQL statements (if any) in a separate text le. Upload both your SQL script and the results text le on Moodle. (a) Create the above database in MySQL and call it UNIVERSITY. (b) Insert the following tuples in the DEPARTMENT table: (10, 'Computer Science', 'Bliss Hall'); (20, 'Business', 'West Hall'); (30, 'English', 'Fisk Hall'); (40, 'Mathematics', 'Bliss Hall'); (c) Insert the following tuples in the PROFESSOR table: (7839, 'King', 'Dean', NULL, '17 NOV 2003', 6500, 0, 10); (7566, 'Jones', 'Chair', 7839, '2 APR 2003', 3375, 0, 20); (7698, 'Blake', 'Chair', 7839, '1 MAY 2003', 3250, 0, 30); (7782, 'Clark', 'Chair', 7839, '9 JUN 2003', 2850, 0, 10); (7499, 'Allen', 'Full Professor', 7698, '20 FEB 2003', 2000, 500, 30); (7521, 'Ward', 'Full Professor', 7698, '22 FEB 2003', 1650, 800, 30); (7654, 'Martin', 'Full Professor', 7698, '28 SEP 2003', 1650, 1400, 30); (7844, 'Turner', 'Full Professor', 7698, '8 SEP 2003', 1900, 0, 30); (7900, 'James', 'Assistant Professor', 7698, '3 DEC 2003', 1350, 0, 30); (7788, 'Scott', 'Associate Professor', 7566, '27 JUN 2002', 3500, 0, 20); (7902, 'Ford', 'Associate Professor', 7566, '3 DEC 2003', 3500, 0, 20); (7369, 'Smith', 'Assistant Professor', 7902, '17 DEC 2002', 1200, 0, 20); (7876, 'Adams', 'Assistant Professor', 7788, '31 JUL 2002', 1500, 0, 20); (7934, 'Miller', 'Assistant Professor', 7782, '23 JAN 2003', 1700, 0, 10);

Download and install MySQL on your local machine

Create the database described in the attached file and populate it with the data given.

Create a stored procedure to display the location of a given department, the number of professors in that department, and their average salaries.

Create a GUI to retrieve all professors in a given department. The user should provide the department name and you should return the number, name, job, manager's name, hire date, salary and bonus of all the professors that belong to this department.

Update the GUI you create in 4 to display in addition a summary of the department by calling the stored procedure you created in 3.

Deliverables:

Your source code for the above tasks. [40 pts]

An executable where all the above tasks can be tested. [50 pts]

A readme file to help us run your code. [10 pts]