Lab 1 1. Using MySQL Workbench: · Create a new schema for this lab and import Lab07DB.sql. 2. You will execute some commands to get information out of the database. Each bullet point represents an...

Please read the files I uploaded carefully. The requirement ones are written there.



Lab 1 1. Using MySQL Workbench: · Create a new schema for this lab and import Lab07DB.sql. 2. You will execute some commands to get information out of the database. Each bullet point represents an individual task. Index i. Write a query to display everything on the student table for those with a student program of ‘Computer Science’. Execute the statement as: EXPLAIN SELECT… Take a screen image of the result of the EXPLAIN ii. Create an index on the studentprogram column of the student table, give the index a name of IN_STUDENT. Paste this SQL in a Word document for later submission to Brightspace, spcify this requirement number before the SQL (ii). iii. Repeat step 2i and take a screen image of the result of the EXPLAIN. You’ll notice on the first EXPLAIN output the rows value is 40. This means the DBMS had to do a full scan of the entire table, from top to bottom to search for all occurrences of the Computer Science program. Now notice the second EXPLAIN output, the rows value is now 9. This means the DBMS used the index to retreive the data (9 rows in the index). You’ll also notice the key value reads the name of the index, IN_STUDENT. EXPLAIN is documented here: (https://dev.mysql.com/doc/refman/8.0/en/using-explain.html). View iv. Create a view called ‘computer_science_students’ for the SELECT created in step 2i (do not include the EXPLAIN clause). Paste this SQL in the same Word document mentioned above, specify this requirement number before the SQL (iv). Trigger v. Create a ‘professor_audit_trail’ table. Use the code below: use ; create table professor_audit_trail ( id int unsigned primary key auto_increment not null, ProfessorName VARCHAR(255) not null, ProfessorProgram VARCHAR(255) not null ); vi. Create a trigger on professor table which executes whenever a row in professor table is deleted. The deleted professor name and professor program should be stored in the newly created ‘professor_audit_trail’ table. Note, the ID column in the audit table is NOT the ProfessorId column from the professor table. Paste the CREATE TRIGGER SQL in the same Word document mentioned above, specify this requirement number before the SQL (vi). vii. Delete the record in the Professor table where the ProfessorId equals 10. viii. Display the contents of the professor_audit_trail table. Take a screen image of the SQL and output, paste this image in the same Word document mentioned above, specify this requirement number before the image (viii). Drop ix. Create the necessary DDL commands to drop the index, view and trigger. Paste these three SQL statements in the same Word document mentioned above, specify this requirement number before the SQL (ix). Lab 1 Requirements: Submit the Word document containing the requirements for review (ii, iv, vi, viii, ix). Make sure the requirement number is displayed ahead of the requirement. If the file does not include the requirement number before the deliverable, the submission will not be graded. Lab 2 1-Using MySQL Workbench: · Create a new schema for this lab and import Lab07DB.sql. 2-Reverse-engineer the database to produce the ER diagram for this database. Rely on the ERD to perform tasks 3a to 3c. 3-You will write and execute SQL commands to get information from the database. a) Create a stored procedure called MentoredStudents which will display student first and last names. Only students who are being mentored are to be selected. Sort the output by student last name, then first name. Paste this SQL in a Word document for later submission to Brightspace; specify this requirement number before the SQL (3a) b) Create a stored procedure called MentoringCount which will display the professor name and the number of students he/she is mentoring. Sort the output by the professor name. Paste the SQL in above noted Word document; specify this requirement number before the SQL (3b). c) Create a stored procedure called NonMentoringProfessors which will display professor name of those who are not mentoring (meaning, there is no record for them in the student_professor table). Use a subquery to determine the output. Sort the output by the professor name. Paste the SQL in above noted Word document; specify this requirement number before the SQL (3c). d) Execute the MentoredStudents stored procedure and capture a screen image of the entire desktop. Image should show the number of records being returned by the stored procedure from the MySQL Workbench Output window. Paste the screen image in above noted Word document; specify this requirement number before the SQL (3d). e) Execute the MentoringCount stored procedure and capture a screen image of the entire desktop. Image should show the number of records being returned by the stored procedure from the MySQL Workbench Output window. Paste the screen image in above noted Word document; specify this requirement number before the SQL (3e). f) Execute the NonMentoringProfessors stored procedure and capture a screen image of the entire desktop. Image should show the number of records being returned by the stored procedure from the MySQL Workbench Output window. Paste the screen image in above noted Word document; specify this requirement number before the SQL (3f). 4-While referring to this video: https://www.youtube.com/watch?v=DaAbmHJUmKM a) Backup one of the database you have created during this course. Note, when you get to the Object Selection tab (as shown in video), make sure to select only the database you want to backup. b) Execute this SQL, make sure to change databasename on third line to the database you are backing up SELECT table_schema AS Database_Name, MIN(create_time) AS Creation_Time FROM information_schema.tables WHERE table_schema = 'databasename'; Capture a screen image of the output of this query and paste the image in above noted Word document; specify this requiremednt number before the SQL (4b). c) Restore the database using the backup file you just created d) Execute this SQL, make sure to change databasename on third line to the database you are backing up SELECT table_schema AS Database_Name, MIN(create_time) AS Creation_Time FROM information_schema.tables WHERE table_schema = 'databasename'; Capture a screen image of the output of this query and paste the image in above noted Word document; specify this requirement number before the SQL (4d). Lab 2 Requirements: Submit the Word document containing the requirements for review (3a, 3b, 3c, 3d, 3e, 3f, 4b, 4d) . Make sure the requirement number is displayed ahead of the requirement. If the file does not include the requirement numbers before the deliverables, the submission will not be graded.