University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
Task 1 – Part A
(1)Connect to MySQL as a user root either through command line interface mysql or graphical user interface MySQL Workbench and create a new database with a name the same as .
(2)While connected as a user root use SQL script A3create.sql to create the relational tables in a database created in the previous step. A script A3create.sql creates the relational tables that can be used to store information about books, authors, customers, purchase, online purchase and walk-in purchase. Execute a script A3load.sql to load data into the relational tables that created by A3create.sql. You can use a script A3drop.sql to drop the relational tables. Do not drop the relational tables now!
No report is expected from the implementation of the steps listed above.
Implement SQL script that performs the following actions as a user root.
(1)Create three users with the following user names: _1
_2 _3
For example, if your UOW email prefix is abc123 then the names of users are abc123_1, abc123_2 and abc123_3. Set all passwords to be the same as the user names. For example, if the username is abc123_1, the password should be set to abc123_1.
(2)Next, the script grants to the user _1 to alter relational tables, to drop relational tables, and to read and write relational tables in a database with the same name as . The privilege must be granted such that the user _1 is able to grant all privileges listed above to the other users.
(3)Next, the script grants to the user _2 to create views, and read data from the relational tables BOOK and AUTHOR in a database with the same name as . The privilege must be granted such that the user _2 is NOT able to grant all privileges listed above to the other users.
(4)Next, the script sets the following values of resource limits to a user _3: total number of queries an account owner can issue per hour must be set to 100, and total number of updates an account owner can issue per hour must be set to 10.
(5)Next, the script expires the password of the account _2
(6)Finally, the script lists the privileges granted to all new users, _1, _2, _3, the values of resource limits set in a step (4) and a status of _2 set in a step (5). To do so your script must access appropriate relational tables in a database mysql. Do not list information NOT related to the actions performed above!
Page 2 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
Deliverables
A)Submit a file with a report from processing of your SQL script. The report MUST have no errors and the report MUST list all SQL statements processed.
B)Please refer to Appendix A, for details on deliverables & file naming conventions for this task.
C)Please refer to Appendix B, for details on submission procedures and when to submit, for this assignment.
D)Please refer to Appendix C, which describes the assessment / grading guidelines for this assignment
Page 3 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
Task 1 – Part B
(1)Connect to MySQL either through command line interface mysql or graphical user interface MySQL Workbench as user csit115 and execute a script file A3drop.sql and immediately after that the scripts A3create.sql and A3load.sql to refresh the contents of a database csit115. Exit command line interface mysql or graphical user interface MySQL Workbench.
(2)Create a logical backup of a relational table BOOK and save it in a file with the same name as .bak.
(3)Connect as a user csit115 to MySQL either through command line interface mysql or graphical user interface MySQL Workbench and execute a script file A3change1b.sql.
(4)Use a text editor and modify a backup file obtained in a step (2) such that a backup of a relational table BOOK can be restored into a relational table with the same name as a _DOC.
(5)Use an updated backup file .bak to load the contents of the backup into a relational table _DOC. DO NOT delete the backup file!
No report is expected from the implementation of the steps listed above.
Implement SQL script that finds the differences between the contents of a relational table BOOK and a relational table with the same name as _DOC.
The script must first list the rows added to the relational table BOOK after the backup file was created, then the rows deleted from a relational tables BOOK after the backup file was created, and finally list the rows changed in relational table BOOK after the backup file was created.
In brief, the script must first list all added rows, then all deleted rows, and finally all changed rows in a relational table BOOK. It is allowed to use more than one SELECT statement to implement this task.
Deliverables
A)Submit a file with a report from processing of your SQL script and the updated backup file in a step (5). The report MUST have no errors and the report MUST list all SQL statements processed.
B)Please refer to Appendix A, for details on deliverables & file naming conventions for this task.
C)Please refer to Appendix B, for details on submission procedures and when to submit, for this assignment.
D)Please refer to Appendix C, which describes the assessment / grading guidelines for this assignment
Page 4 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
Task 2 – Part A
Connect to MySQL either through command line interface mysql or graphical user interface MySQL Workbench as user csit115 and execute a script file A3drop.sql and immediately after that the scripts A3create.sql and A3load.sql to refresh the contents of a database csit115.
No report is expected from the implementation of the steps listed above.
Implement SQL script that performs the following actions.
(1)The script finds all cases that violate in a database csit115 the following consistency
constraint.
“A purchase which is an online purchase should NOT be a walk-in purchase”
The script must list the outcomes of verification of the consistency constraint as a single column table with the following messages as the following rows.
A purchase with the purchase ID of is an online purchase of type and also a walkin purchase of location
Use a function CONCAT to create the messages above. It is NOT allowed to use more than one SELECT statement to implement this task.
Note, that it is NOT your task to eliminate the violations of consistency constraint listed above.
Deliverables
A)Submit a file with a report from processing of your SQL script. The report MUST have no errors and the report MUST list the SQL statement processed.
B)Please refer to Appendix A, for details on deliverables & file naming conventions for this task.
C)Please refer to Appendix B, for details on submission procedures and when to submit, for this assignment.
D)Please refer to Appendix C, which describes the assessment / grading guidelines for this assignment
Page 5 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
Task 2 – Part B
In this task you will implement your own simple method of auditing the database activities.
Connect to MySQL as a user root either through command line interface mysql or graphical user interface MySQL Workbench and execute a script file A3drop.sql and immediately after that execute script A3create.sql and A3load.sql to refresh a database csit115.
No report is expected from the implementation of the steps listed above.
Implement SQL script that performs the following actions.
(1)First, the script sets the appropriate values of the variables that allow create a general log, to save a general log in a relational table, and to start recording a general log from now.
(2)Next, the script makes a relational table that contains a general log empty.
(3)Next, the script executes a script file A3change2b.sql. (Do NOT put results of execution of script A3change2b.sql into a report.)
(4)Next, the script sets the appropriate values of all variables that stop recording a general log from now.
(5)Next, the script lists the DDL statements (CREATE, ALTER, DROP) processed in a period of time when a general log was recorded.
(6)Next, the script lists the DML statements (SELECT, INSERT, DELETE, UPDATE) processed in a period of time when a general log was recorded. Sort the results in a descending order of the total number of times a DML statement has been processed.
Deliverables
A)Submit a file with a report from processing of your SQL script. The report MUST have no errors and the report MUST list all SQL statements processed.
B)Please refer to Appendix A, for details on deliverables & file naming conventions for this task.
C)Please refer to Appendix B, for details on submission procedures and when to submit, for this assignment.
D)Please refer to Appendix C, which describes the assessment / grading guidelines for this assignment
Page 6 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
APPENDIX A
File Naming Conventions
For all files in general, please use following naming format as a PRE-FIX:
<FT/PT>__A3-T1a__.
-<FT/PT> Use “FT” for Full-Time student, “PT” if you are Part-Time student
– refers to your SIM tutorial group (e.g. TutGrp1 / TutGrp2 / etc.)
-A2 if you are submitting assignment 2, A3 if submitting assignment 3, etc.
-T1a if file contains answers for Task 1 part A, T2b if file contains answers for Task 2 part B, etc.
– refers to your UOW assigned student number (e.g. 12345678)
– refers to your UOW registered name (e.g. JohnDoeAnderson)
– refers to the type of file (e.g. .sql, .rpt, .bak)
Deliverables
The deliverables include the following:
a)Four SQL scripts (Task 1 Part A, Task 1 Part B, Task 2 Part A, Task 2 Part B)
Please rename your file according to the required file naming convention! E.g. =>FT_TutGrp2_A3-T1a_1234567_JohnDoeAnderson_Sql.sql
b)Four reports produced using the tee command (Task 1 Part A, Task 1 Part B, Task 2 Part A, Task 2 Part B)
Please rename your file according to the required file naming convention! E.g. =>FT_TutGrp2_A3-T1a_1234567_JohnDoeAnderson_Rpt.rpt
c)A .bakfile for Task 1 Part B
Please rename your file according to the required file naming convention! E.g. =>FT_TutGrp3_A3-T1b_1234567_JohnDoeAnderson_Bak.bak
Page 7 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
d)Compress all your assignment files into a single zip file. Please use the following naming format :
<FT/PT>__A3__.ZIP
Example : FT_TutGrp3_A3_1234567_JohnDoeAnderson.ZIP
-<FT/PT> Use “FT” for Full-Time student, “PT” if you are Part-Time student
– refers to your SIM tutorial group (e.g. TutGrp1 / TutGrp2 / etc.)
-A2 if you are submitting assignment 2, A3 if you are submitting assignment 3, etc.
– refers to your UOW assigned student number (e.g. 12345678)
– refers to your UOW registered name (e.g. JohnDoeAnderson)
e)A Q&A / demo / evaluation will be held during lab session. You must be prepared to present / perform certain tasks / answer questions posed by the tutor, regarding your application of the SQL scripts and the final answer in your submission.
Page 8 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
APPENDIX B
How to Submit – Normal Workflow
A)Please double-check that all your filenames are in the correct naming convention
B)Please submit via Moodle using the following steps:
(i)Login to your Moodle account
(ii)Select the site for this Subject : CSIT115 Data Management and Security
(iii)Scroll down and look for the section on Submissions
(iv)Click at the link “in this place you can submit the outcomes of
Assignment . . .”
(v)Click at a button “Add Submission”
(vi)Use the link “Add . . .”, OR drag your ZIP file into the area “You can drag and drop files here to add them . . .”
(vii)Click at a button “Save changes”
(viii)Click at a button “Submit assignment”
(ix)Click at 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
(x)Click at a button Continue
(xi)… and that’s it!
C)Please take note of the following submission policies
(i)Only one submission of this assignment is allowed and only one submission per student is accepted
(ii)A submission marked by Moodle as “late” is always treated as a late submission no matter how many seconds it is late.
(iii)A submission that contains an incorrect file attached is treated as a correct submission with all consequences coming from the evaluation of the file attached. (i.e. no marks can be given if file does not contain the required content!)
How to Submit – “Emergency” Workflow
In the event of UNFORSEEN SITUATIONS :
(E.g. New student+skip orientation, joining the current quarter as the 1st semester, Student’s moodle account not ready, cannot login to moodle, forget password, eLearning site down on submission day, repeated interrupted internet connection, unable to upload assignment, etc )
Please submit your Assignment using the following steps :
Page 9 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
A)Please double-check that all your filenames are in the correct naming convention
B)Compress all your assignment files into a single zip file. Please use the following naming format :
<FT/PT>__A3__.ZIP
Example :FT_TutGrp3_A3_1234567_JohnDoeAnderson.ZIP
-<FT/PT> Use “FT” for Full-Time student, “PT” if you are Part-Time student
– refers to your SIM tutorial group (e.g. TutGrp1 / TutGrp2 / etc.)
-A2 if you are submitting assignment 2, A3 if submitting assignment 3, etc.
– refers to your UOW assigned student number (e.g. 12345678)
– refers to your UOW registered name (e.g. JohnDoeAnderson)
C)Please email your single zip file to your tutor at :
csit115@yahoo.com forFULL TIME students
kttan007@gmail.com for PART TIME students
In your email subject line, type in the following information :
<FT/PT> and
Example:
To
:
tutor’s email (see above)
Subject
:
FT TutGrp1 A3 1234567 JohnDoeAnderson
Note1 : The timestamp shown on tutor’s email Inbox will be used to determine if the assignment is late or not.
Note2 : After email submission, your mailbox’s SENT FOLDER would have a copy (record) of your sent email, please do not delete that copy !! It could be used to prove your timely submission, in case the Tutor did not receive your email!
Page 10 of 12
University of Wollongong
School of Computing and Information Technology
CSIT115 Data Management and Security
When to Submit
A)After the submission of deliverables, students may be given a chance to explain / demo their understanding of the process, and how they derive their answers. Depending on the time-table, a demo / Q&A / testing for your assignment may be scheduled during the
-3rd – 5th lab session for the semester (i.e. lab 3 – 5), for Full Time (FT) students
-2nd – 4th lab session for the semester (i.e. lab 2 – 4), for Part Time (PT) students
Further instructions will be given by the Tutor during the subsequent respective labs. Please consult your tutor for further details. Some time would be allocated for each student to present / demo / explain his solution during the session. Please pay attention as failure to adhere to instructions may result in deduction of marks.
B)Please refer to the following table which contains general info on the different submission events and deadlines
Assignment Submission Deadline Assignment Demo / Q&A /
(check Moodle for EXACT date-time )
Testing (Tasks), during your
PT FT
respective …
(must be before …) (must be before …)
1 Lab 2 Lab 3 Lab 2(PT), Lab 3(FT)
2 Lab 3 Lab 4 Lab 3(PT), Lab 4(FT)
3 Lab 4 Lab 5 Lab 4(PT), Lab 5(FT)
Note: (PT) = Part Time Students, (FT) = Full Time Students !
C)Non-submission of any of the above mentioned deliverables will result in ZERO marks! Please check with your Tutor personally if you are unsure!
