An objective of this task is to implement your own simple method of auditing the database activities. It is strongly recommended to connect to MySQL either through command line interface mysql or graphical user interface MySQL Workbench and process a script file dbdrop.sql and immediately after that the scripts dbcreate.sql and dbload.sql to refresh a sample database csit115. Download a file solution3.sql and insert into the file the implementations of the following actions. You must connect as a user root with a password csit115. Your implementation must directly follow a comment with a specification of a subtask. (1) First, the script makes a relational table that contains a general log empty.
An objective of this task is to implement your own simple method of auditing the
It is strongly recommended to connect to MySQL either through command line interface mysql or graphical user interface MySQL Workbench and process a script file dbdrop.sql and immediately after that the scripts dbcreate.sql and dbload.sql to refresh a sample database csit115.
Download a file solution3.sql and insert into the file the implementations of the following actions.
You must connect as a user root with a password csit115.
Your implementation must directly follow a comment with a specification of a subtask.
(1) First, the script makes a relational table that contains a general log empty.
(2) Next, the script sets the appropriate values of the variables to save a general log in a relational table and to start recording a general log from now.
(3) Next, the script makes a database csit115 a default database, it stops recording a report, it executes a script file updates.sql, and it resumes recording a report into a file solution3.rpt.
A listing of SQL statements processed from a script file updates.sql must
NOT be included in the report file solution3.rpt.
(4) Next, the script sets the appropriate values of all variables to stop recording a general log from now.
(5) Finally, the script finds and lists how many times each one of the relational tables included in a sample database have been used by the successfully processed SQL statements included in SQL script updates.sql. You have to consider the relational tables with the following names LPOSITION, DEPARTMENT, EMPLOYEE, SUBJECT, RUNNINGSUBJECT, and TEACHES. No other relational tables need to be considered. The script must list the names of relational tables together with the total number of times each table has been used. Please, find a fragment of a sample output listed below.
+------------+-------+
| TABLE_NAME | TOTAL |
+------------+-------+
| DEPARTMENT | 8 |
| SUBJECT | 6 |
... ...
+------------+-------+
6 rows in set (0.01 sec)
To simplify this task, assume that a relational table is used no more than one time in a SQL statement.
The results must be listed in the descending order of the total number of times each one of the relational tables has been used by the successfully processed SQL statements included in a script updates.sql.
When ready connect as root user, process a script file solution3.sql, and save a report from processing in a file solution3.rpt.
Step by step
Solved in 4 steps
