CSIT115 Data Management and Security Assignment 3
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CSIT115 Data Management and Security
Assignment 3
2022
Scope
This assignment is related to verification of a complex consistency constraint, implementation of a simple auditing system, and database backup and recovery techniques.
Please read very carefully information listed below.
This assignment contributes to 8% of the total evaluation in a subject CSIT115.
The outcomes of the assignment work are due by Saturday 4 June 2022, 7.00 pm (sharp).
A submission procedure is explained at the end of specification.
This assignment consists of 3 tasks and specification of each task starts from a new page.
It is recommended to solve the problems before attending the laboratory classes in order to efficiently use supervised laboratory time.
A submission marked by Moodle as Late is treated as a late submission no matter how many seconds it is late.
A policy regarding late submissions is included in the subject outline.
A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The compressed files will not be evaluated.
All files left on Moodle in a state "Draft(not submitted)" will not be evaluated.
An implementation that does not compile due to one or more syntactical errors scores no marks and implementation that has the processing errors scores no marks.
It is expected that all tasks included within Assignment 3 will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result in a FAIL grade being recorded for the assessment task.
Prologue
Connect to Moodle and download the files dbcreate.sql, dbdrop.sql, dbload.sql, dbcount.sql, 2022Dump.sql, dbupdates.sql and dbschema.bmp from Sample database on Moodle.
SQL script dbcreate.sql can be used to create the relational tables of a sample database. SQL script dbdrop.sql can be used to drop the tables of a sample database. SQL script dbload.sql can be used to load data into a sample database. SQL script dbcount.sql can be used to display the total number of rows in each table included in a sample database. Finally, a file dbschema.bmp contains a conceptual schema of a sample database.
Connect to MySQL database server either through command line interface mysql or graphical user interface MySQL Workbench.
When connected, select a database csit115 with a command use csit115.
To create the relational tables of a sample database, process SQL script
dbcreate.sql.
To load data into the relational tables created in the previous step process SQL script dbload.sql.
To list the names of relational tables created, use a command show tables.
To list a structure of a relational table <table-name> use a command describe <table-name>.
To list the total number of rows in each relational table process a script dbcount.sql.
Use an image viewer to open a file dbschema.bmp with a conceptual schema of the sample database.
No report is expected from the implementation of the actions listed above.
Tasks
Task 1 (3 marks)
An objective of this task is to implement SQL script that verifies the following logical consistency constraint imposed on the contents of a sample database.
"Each product should have one or several keywords. "
Download a file solution1.sql and insert into the file the implementations of the following actions.
Your implementation must directly follow a comment with a specification of a subtask.
(1) First, write a SQL statement to create a single column relational table MESSAGE to store variable size strings no longer than 500 characters.
(0.5 marks)
(2) Next, write a SQL statement to insert into the relational table MESSAGE information about the contents of a sample database that verifies the consistency constraint for each product.
"Each product should have one or several keywords. "
The script must insert outcomes of the verifications of the consistency constraint as single column values with the following messages as the rows in the table.
A product <insert pNumber here> with a price in <insert price here> and manufactured by <insert manufacturer here> has no keyword.
For example, if the product with product number 6 has no keyword, the verification of the consistency constraint must insert the following message into the table MESSAGE.
A product 6 with a price in 36.85 and manufactured by ABC Pty Ltd has no keyword.
Use the function CONCAT to create single column messages like the one listed above. Please consider applying space in some strings to give spaces between words in a displaced sentence.
(2.0 marks)
(3) Next, lists the contents of the table MESSAGE.
(0.3 marks)
(4) Finally, drop the table MESSAGE.
(0.2 marks)
To implement and to test SQL script file solution1.sql you can either use graphical user interface MySQL Workbench or command line interface mysql.
It is recommended to drop the table MESSAGE if you need to retest the solutions.
To create a report from processing of a script file solution1.sql, open a Terminal window, change the directory to the working directory, and start the command line interface mysql in the following way:
mysql -u csit115 -p -v -c
Next, process SQL script solution1.sql and save a report in a file solution1.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solution1.sql.
We have already practiced saving a report from the processing of SQL script in Laboratories 1, 3 and 4. You can also find more information about creating reports from the processing of SQL scripts in Cookbook, Recipe 3.1 How to use “mysql? Command based interface to MySQL database server? Step 4 How to save the results of SQL processing in a file?”
A report that contains no listing of processed SQL statements scores no marks, and report that contains errors of any kind also scores no marks!
Deliverables
A file solution1.rpt with a report from the processing of SQL script solution1.sql. The report must be created with the command line interface mysql, the report MUST NOT include any errors, and the report must list all SQL statements processed and all comments included in the original (downloaded) version of solution1.sql. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks.
Task 2 (3 marks)
An objective if this task is to use a backup file and recovery feature of a database system to find historical information from a sample database. On 1 January 2022 a database administrator created a backup of all relational tables in a file 2022Dump.sql.
Your implementation must directly follow a comment with a specification of a subtask.
You must connect as a user root with a password csit115.
Download a file solution2_1-2.sql and insert into the file the implementations of the following actions.
(1) Create a database with the same name as a prefix of your University email account. For example, if your University email account is [email protected] then a name of a database should be abc001.
(0.5 marks)
(2) Use a backup file 2022Dump.sql to load the contents of relational tables before 1 January 2022 into the database created in step (1).
A listing of the processing results from a script file 2022Dump.sql must NOT be included in the report file solution2.rpt.
(0.5 marks)
Download a file solution2_3-5.sql and insert into the file the implementations of the following actions.
(3) Use a SELECT statement to list all purchase records including the customer number and payment card in the relational table Pbasket that opened after 31 December
2021. The query must compare csit115 database and the backup.
(0.5 marks)
(4) Use a SELECT statement to list all purchase records including the product number in the relational table PP that opened after 31 December 2021. The query must compare csit115 database and the backup.
(0.5 marks)
(5) Use a SELECT statement with WITH clause to list all information with full join in relational tables Pbasket and PP that closed after 31 December 2021. The query must compare csit115 database and the backup.
(1.0 marks)
To implement and to test SQL script files solution2_1-2.sql and solution2_3-5.sql you can either use graphical user interface MySQL Workbench or command line interface mysql.
It is recommended to drop the database created in step (1) if you need to retest the solutions.
When ready connect as root user, process script files solution2_1-2.sql and
solution2_3-5.sql, and save a report from processing in a file solution2.rpt.
To create a report from the processing of files solution2_1-2.sql and
solution2_3-5.sql, open a Terminal window, change the directory to the working directory, and start the command line interface mysql in the following way:
mysql -u root -p -v -c
Next, process SQL scripts solution2_1-2.sql and solution2_3-5.sql and save a report in a file solution2.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of files solution2_1- 2.sql and solution2_3-5.sql.
We have already practiced saving a report from the processing of SQL script in Laboratories 1, 3 and 4. You can also find more information about creating reports from the processing of SQL scripts in Cookbook, Recipe 3.1 How to use “mysql? Command based interface to MySQL database server? Step 4 How to save the results of SQL processing in a file?”
A report that contains no listing of processed SQL statements scores no marks, and report that contains errors of any kind also scores no marks!
Deliverables
A file solution2.rpt with a report from the processing of SQL scripts solution2_1-2.sql and solution2_3-5.sql. The report must be created with the command line interface mysql, the report MUST NOT include any errors, and the report must list all SQL statements processed and all comments included in the original (downloaded) version of solution2_1-2.sql and solution2_3-5.sql. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks.
Task 3 (2 marks)
An objective of this task is to implement your own simple method of auditing the database activities.
Your implementation must directly follow a comment with a specification of a subtask.
You must connect as a user root with a password csit115.
Download a file solution3_1-3.sql and insert into the file the implementations of the following actions.
(1) First, the script makes a relational table that contains a general log empty.
(0.3 marks)
(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.
(0.5 marks)
(3) Next, the script makes a database csit115 a default database, it stops recording a report, it executes a script file dbupdates.sql, and it resumes recording a report into a file solution3.rpt.
A listing of the processing results from a script file dbupdates.sql must NOT be included in the report file solution3.rpt.
(0.5 marks)
Download a file solution3_4-5.sql and insert into the file the implementations of the following actions.
(4) Next, the script sets the appropriate values of all variables to stop recording a general log from now.
(0.2 marks)
(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 dbupdates.sql. You only need to consider the relational tables with the following names Pbasket and PP. 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 |
+------------+-------+
2 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 ascending order of the relational tables’ names after
the successfully processed SQL statements included in a script dbupdates.sql. (1.5 marks)
It is strongly recommended to refresh the database if you need to retest the solutions. You can process a script dbdrop.sql to drop all relational tables, then re-create the original database with the processing of a script dbcreate.sql and dbload.sql. In such a way, your script always operates on the original structures and data of the sample database csit115.
When ready connect as the root user, process script files solution3_1-3.sql, solution3_4-5.sql and save a report from processing in a file solution3.rpt.
To create a report from processing of files solution3_1-3.sql, solution3_4- 5.sql, open a Terminal window, change the directory to the working directory, and start the command line interface mysql in the following way:
mysql -u root -p -v -c
Next, process SQL scripts solution3_1-3.sql, solution3_4-5.sql and save a report in a file solution3.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of files solution3_1-3.sql, solution3_4-5.sql.
We have already practiced saving a report from the processing of SQL script in Laboratories 1, 3 and 4. You can also find more information about creating reports from the processing of SQL scripts in Cookbook, Recipe 3.1 How to use “mysql? Command based interface to MySQL database server? Step 4 How to save the results of SQL processing in a file?”
A report that contains no listing of processed SQL statements scores no marks, and report that contains errors of any kind also scores no marks!
Deliverables
A file solution3.rpt with a report from processing of SQL scripts solution3_1- 3.sql, solution3_4-5.sql. The report must be created with the command line interface mysql, the report MUST NOT include any errors, and the report must list all SQL statements processed and all comments included in the original (downloaded) version of solution3_1-3.sql, solution3_4-5.sql. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks.
Submission
Note, that you have only one submission. So, make it absolutely sure that you submit the correct files with the correct contents and correct types. No other submission is possible!
Submit the files solution1.rpt, solution2.rpt, and solution3.rpt through Moodle in the following way:
(1) Access Moodle at http://moodle.uowplatform.edu.au/
(2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page
(3) When logged select a site CSIT115 (S122) Data Management and Security
(4) Scroll down to a section Submissions
(5) Click on a link In this place you can submit the outcomes of Assignment 3
(6) Click on a button Add Submission
(7) Move a file solution1.rpt into an area You can drag and drop files here to add them. You can also use a link Add…
(8) Repeat step (7) for the files solution2.rpt and solution3.rpt.
(9) Click on the checkbox with a text attached: By checking this box, I confirm that this submission is my own work, … in order to confirm the authorship of your submission
(10) Click on a button Save changes
End of specification
2022-06-14