CSIT115 Data Management and Security Assignment 2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CSIT115 Data Management and Security
Assignment 2
2022
Scope
This assignment is related to implementation of simple SELECT statements, SELECT statement with GROUP BY and HAVING clauses, SELECT statements that join and antijoin relational tables, nested SELECT statements with set membership operation, and nested SELECT statements with EXISTS/NOT EXISTS clauses.
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 7 May 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 2 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, 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 (2 marks)
Download a file solution1.sql and insert into the file the implementations of the following queries as SELECT statements of SQL.
Your implementation must directly follow a comment with a specification of a subtask.
(1) Find the product number, price, and manufacturer of all products that have brief comments.
(2) Find the product number and keyword of all products that have the keywords Zenith or Fibre.
(3) Find the product number, price and manufacturer of all products that their prices are greater than 15.00 but less than 25.00.
(4) Find the customer number and the created date for all the customers who have placed orders of baskets that were finalised in December 2021.
(5) Find the highest rank provided by the customers for the products.
To create a report from the processing of SELECT statements, 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)
Download a file solution2.sql and insert into the file the implementations of the following queries as SELECT statements of SQL.
Your implementation must directly follow a comment with a specification of a subtask.
The queries listed below must be implemented as SELECT statements with JOIN or LEFT / RIGHT OUTER JOIN operation.
(1) Find the product number, price, and manufacturer of all products that have the
keyword. Do not display repeated results.
Hint: Consider the tables Product and Keyword.
(2) Find the product number, price, and manufacturer of all products that have been
ranked by the customers. Do not display repeated results.
Hint: Consider the tables Product and Cevaluation.
(3) Find the customer number and full name (fname and lname) of all customers that have created baskets of products in January 2022. Do not display repeated results. Hint: Consider the tables Customer and Pbasket.
(4) Find the customer number and full name (fname and lname) of all customers that haven’t created any baskets of products so far.
Hint: Consider the tables Customer and Pbasket. Use LEFT or RIGHT OUTER JOIN.
(5) Find the product number, price, and manufacturer of all products that have been ranked by the customers with the highest rank.
Hint: Consider the tables Product and Cevaluation. Compare the rank value with the highest rank value.
(6) Find the product number, price, manufacturer, and the total number of keywords for all products. Include the products that have no keyword. Sort the data in ascending order of the total number of keywords for the products.
Hint: Consider the tables Product and Keyword. Use LEFT or RIGHT OUTER JOIN.
To create a report from the processing of SELECT statements, 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 solution2.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 a file solution2.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 script solution2.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.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 (3 marks)
Download a file solution3.sql and insert into the file the implementations of the following queries as SELECT statements of SQL.
Your implementation must directly follow a comment with a specification of a subtask.
The queries listed below must be implemented as nested SELECT statements with IN/NOT IN set membership operation.
(1) Find the product number, price and manufacturer that have no keyword. Hint: Consider the tables Product and Keyword.
(2) Find the customer number and full name (fname, lname) for all customers that have created baskets of products.
Hint: Consider the tables Customer and Pbasket.
The queries (3) and (4) listed below must be implemented as nested queries with EXISTS/NOT EXISTS clauses.
(3) Find the product number, price, and manufacturer of all the products that have been ranked.
Hint: Consider the tables Product and Cevaluation.
(4) Find the customer number and full name (fname, lname) of all customers that have not evaluated any product.
Hint: Consider the tables Customer and Cevaluation.
The queries listed below must be implemented with a WITH clause.
(5) Find the product number, price, and manufacturer of all products that have the maximum number of keywords.
Hint: Count the total number of keywords for each product. Find the maximum value. Then compare the number of keywords with the maximum value to find the expected product number. Use the product number found for the product details.
(6) Find the customer number and full name (fname, lname) of the customers that have the biggest product basket.
Note: The biggest product basket is a basket that contains the maximum total number of products.
Hint: Count the total number of products for each basket. Find the maximum value. Then compare the product number of baskets with the maximum value to find the expected basket. Use the basket found before to find the customer.
To create a report from the processing of SELECT statements, 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 solution3.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 a file solution3.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 script solution3.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.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 2
(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