I'm sorry if this is a few questions at once, I just figured it made more sense this way since each question carries on from the last. If you'd prefer I upload them individually I can do that too :)

(1)     Create a logical backup of relational tables EMPLOYEE, DRIVER, VEHICLE, SCHEDULE, ROUTE and ROUTE_STATION from the database csit115.

    The logical backup file name is abc001backup.sql.

(2)     Implement the script makes a relational table that contains a general log empty.

(3)     Next, implement the script sets the appropriate values of the variables to save a general log in a relational table and start recording the general log from now.

(4)     Next, download dbupdates.sql from the Sample database section on Moodle if you have not done it. Implement the script makes a database csit115 a current database.

    Then stop recording a report.  Next, executes a script file dbupdates.sql, and then resumes recording a report into a file solution2.rpt.

        Note that a list of SQL statements processed from a script file dbupdates.sql must NOT be included in the report file solution2.rpt.

(5)     Next, implement the script sets the appropriate values of all variables to stop recording a general log from now.

 

(6)     Next, implement one SQL statement finds and lists how many times each one of the relational tables EMPLOYEE, DRIVER, VEHICLE, SCHEDULE, ROUTE and ROUTE_STATION

    in a sample database have been used by the successfully processed SQL statements included in SQL script dbupdates.sql. 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 |

+------------+-------+

| SCHEDULE   |     5 |

| EMPLOYEE   |     3 |

  ...             ...

+------------+-------+

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 dbupdates.sql.

(7)     Next, create a database with the same name as a prefix of your University email account. For example, if your University email account is abc001@uow.edu.au then a name of a database should be abc001.

(8)     Next, implement the script to make a database created in step (6) a current database. It stops recording a report.

    Then it executes the logical backup file generated in step (1) to load the contents of relational tables EMPLOYEE, DRIVER, VEHICLE, SCHEDULE, ROUTE and ROUTE_STATION

    into the database created in step (6). It resumes recording a report into a file solution2.rpt. It is the backup database.

    Note that a list of SQL statements processed from the logical backup file generated in step (1) must NOT be included in the report file solution2.rpt.     

(9)     Use one SELECT statement to list the employee number, first name, last name and employee type for the employees that no longer exist in the database csit115.

    Hint: You can compare the data in the tables between the current database (csit115) and the backup database (prefix of your UOW email account).

(10) Use one SELECT statement to list the schedule number, licence number, registration number, start date and time, return date and time,

    and route number for the schedules that have been modified in the database csit115.

         Note that all the columns except the primary key of the table SCHEDULE may be modified.

The database is uploaded as pictures below and the dbupdate.sql file is the first picture. Thank you for your help!

Transcribed Image Text:

SELECT COUNT(*) FROM EMPLOYEE; INSERT INTO EMPLOYEE VALUES( 41, 'Marc', NULL, 'Smith', '1978-10-12', 23, 'Ane Ave', 'Melbourne', 'Victoria', 3000, 9500.00, 0413456789', 'Casual' ); INSERT INTO DRIVER VALUES( 41, 40015, 'light Rigid', 15 ); SELECT * FROM SCHEDULE WHERE 1num = 20002; INSERT INTO SCHEDULE VALUES (52, 20002, 'SST005', '2021-01-23 06:00:00', '2021-01-29 18:00:00', 17); SELECT * FROM VEHICLE WHERE regnum = 'PKR768"; INSERT INTO ROUTE VALUES( 34, 980.00); INSERT INTO ROUTE_STATION VALUES (34, 1, 'Wollongong'); INSERT INTO ROUTE_STATION VALUES (34, 2, 'Melbourne'); UPDATE SCHEDULE SET regnum = 'PKR008' WHERE snum = 51; UPDATE SCHEDULE SET rnum = 15 WHERE snum = 40; UPDATE SCHEDULE SET start_date_time = '2010-09-17 07:00:00' WHERE snum = 29; DELETE FROM DRIVER WHERE Inum= 20003; DELETE FROM EMPLOYEE WHERE enum=8; SELECT COUNT(*) FROM EMPLOYEE; INSERT INTO EMPLOYEE VALUES( 41, 'Marc', NULL, 'Smith', '1978-10-12', 23, 'Ane Ave', 'Melbourne', 'Victoria', 3000, 9500.00, 0413456789', 'Casual' ); INSERT INTO DRIVER VALUES( 41, 40015, 'light Rigid', 15 ); SELECT * FROM SCHEDULE WHERE 1num = 20002; INSERT INTO SCHEDULE VALUES (52, 20002, 'SST005', '2021-01-23 06:00:00', '2021-01-29 18:00:00', 17); SELECT * FROM VEHICLE WHERE regnum = 'PKR768"; INSERT INTO ROUTE VALUES( 34, 980.00); INSERT INTO ROUTE_STATION VALUES (34, 1, 'Wollongong'); INSERT INTO ROUTE_STATION VALUES (34, 2, 'Melbourne'); UPDATE SCHEDULE SET regnum = 'PKR008' WHERE snum = 51; UPDATE SCHEDULE SET rnum = 15 WHERE snum = 40; UPDATE SCHEDULE SET start_date_time = '2010-09-17 07:00:00' WHERE snum = 29; DELETE FROM DRIVER WHERE Inum= 20003; DELETE FROM EMPLOYEE WHERE enum=8;

Answer rating: 100% (QA)

Answer1 This command takes a logical backup of the relational tables EMPLOYEE DRIVER VEHICLE SCHEDULE ROUTE and ROUTESTATION from the database csit115 mysqldump u root p csit115 EMPLOYEE DRIVER VEHICL... View the full answer