CSCI235 -Ass3 -Solved

Prologue
Install VirtualBox on your system, if it is not installed yet. If you do not remember how you did it in CSIT115 then it is explained in  

 

https://documents.uow.edu.au/~jrg/115/cookbook/e1-1frame.html 

 

how to do it.  

 

Download from Moodle ova image of a virtual machine with Ubuntu and MongoDB. The image is available in a section OTHER RESOURCES. You should get a file:

 

Ubuntu18.04-64bits-MongoDB-4.2.2-08-JAN-2020.ova 

 

Start VirtualBox and import ova image of a virtual machine with Ubuntu and MongoDB. You should get a new virtual machine Ubuntu18.04-64bits-MongoDB-4.2.208-JAN-2020.

 

Start a virtual machine Ubuntu18.04-64bits-MongoDB-4.2.2-08-JAN-2020.

 

A password to login as CSCI235 user is:

 

csci235 

 

When logged in, start Terminal program (3rd icon from bottom in a column of icons on the left-hand size of a screen).

 

To start MongoDB server, process the following command in Terminal window.

 

mongod --dbpath DATA --port 4000 

 

When MongoDB server is ready then among many, many, … the other messages you should get a message:

 

… waiting for connection on port 4000 

 

Minimize Terminal window. Do not close the window, from now, it is used as a console window by MongoDB server.

 

Open another Terminal window and to start MongDB command line interface, process the following command.

 

mongo –port 4000 

 

For a good start, process a command help.

 

Download to your virtual machine the files: bsontpchr.bmp, customer.zip, part.zip, and supplier.zip from a section SAMPLE DATABASES on Moodle.

 

Unzip the files: customer.zip, part.zip, and supplier.zip.

 

You should get the files: customer.js, part.js, and supplier.js.

 

To create a collection tpchr and to load the documents into the collection, process the scripts customer.js, part.js, and supplier.js. at > prompt of mongo client in the following way.

 

load("customer.js"); load("part.js"); load("supplier.js"); 

 

A logical schema of a collection tpchr is available in a file bsontpchr.bmp. It is strongly recommended to make yourself familiar with a logical schema of a sample database.

 

Next, you can use the methods  

 

db.orders.find().count() and   db.orders.find().pretty()  

 

to count the total number of the documents in a collection tpchr and to list all documents in a pretty format.

                                                                                                                                                 

Task 1 (7.5 marks) Data manipulations
 

Download and unzip a file solution1.zip. You should get a file solution1.js. The file contains the specifications of the following 5 data manipulation operations on a collection tpchr.  

 

(1)     Remove information about an address from a description of a customer who submitted an order with an order key 1031. Next list a complete description of a customer who submitted an order with an order key 1031.

 

(2)     Rename a key "shipped" to "shipped by" in the parts that have a retail price less than 902. Next, display all information about parts that have a retail price less than 902.

 

(3)     Increase an account balance of all suppliers from Japan by 50% of the present value. Next, display a supplier name, nation and balance of all suppliers from Japan in a pretty format.

 

(4)     Append to all parts that belong to a brand Brand#54 the following information about a new shipment:

"partsupp_id" : "5_1", 

"availqty" : 100, 

"supplycost" : 17, "ref supplier" : "1".  

Next list all information about all parts that belong to a brand Brand#54 in a pretty format.

 

(5)     Remove information about an order that has a key 1031 submitted by a customer whose key is 8. Next, list all information about all orders submitted by a customer whose key is 8.

 

Implement the data manipulations listed above in a data manipulation language of MongoDB. Write your solutions into the empty slots following a specification of each data manipulation in a file solution1.js. Do not remove the specifications of the data manipulations and semicolons following the specifications.

 

Implementation of each data manipulation is worth 1.5 mark. 

 

When ready create a report from processing of the data manipulations in the following way.  

 

Use gedit editor to open a file solution1.js with the specifications and implementations of the data manipulations.  

 

Select the entire contents of the file and Copy it into a buffer.  

 

Open a new Terminal window and start mongo client in the following way.

 

mongo –port 4000 

 

Paste the contents of the buffer copied earlier from gedit window in front of > prompt of mongo client. You may have to press Enter key to process the last data manipulation in a case when it is not followed by a newline control character.

 

Select the entire contents of the Terminal window and Copy&Paste it into a file solution1.lst. Save a file solution1.lst.

 


 

And again, please remember that:

-       a report without the specifications of the data manipulations and listings of the processed data manipulations scores no marks,

-       a report that contains any kind of processing errors scores no marks.

                                                                                                                                                             

        


Task 2 (7.5 marks) Query processing and data transformation with aggregation framework
 

Drop a collection tpchr in the following way.

 

db.tpchr.drop(); 

 

To re-create a collection tpchr and to load the documents into the collection, process the scripts customer.js, part.js, and supplier.js. at > prompt of mongo client in the following way.

 

load("customer.js"); load("part.js"); load("supplier.js"); 

 

Download and unzip a file solution2.zip. You should get a file solution2.js. The file contains the comments with the specifications of the following 5 queries and data transformations.  

 

(1)     Save information about a customer key, name, and nation of all customers from SUDAN or ROMANIA or CANADA into a collection SUROCAN. Display in a pretty format without document identifiers the contents of a collection SUROCAN.

 

(2)     Save all information about the supply costs (supplycost)of a part with a name floral moccasin royal powder burnished into a collection supplycosts that consists of the documents like {"supply cost": avalue-of-supply-cost}. Display in a pretty format without document identifiers all documents in a collection supplycosts.

 

(3)     Find the total number of part shipments of the parts of type LARGE BURNISHED STEEL or SMALL BURNISHED STEEL. Display a result in a format     {"total number  of shipments":integer-value}.

 

(4)     Find the total number of shipments per each part. List the results in a format  

   {"total number of shipments":integer-value,     "part key":integer-value"}.

 

(5)     Find 5 largest extended prices (extended price) from all orders. List the results in a format  

{"customer key": integer-value, 

 "order key":integer-value, 

 "line number":integer-value,  "price":floating-point-value}}.

 

Use the methods aggregate() and pretty() to implement all the queries and data transformations and to display the results. Note, that you may need two or more statements to implement a single task.

 

Implementation of each query/data transformation is worth 1.5 mark.

 

When ready create MongoDB script file solution2.js with the implementations of your queries and create a report from processing of the data manipulations in the following way.  

 

Use gedit editor to open a file solution2.js with the specifications and implementations of the data manipulations.  

 

Select the entire contents of the file and Copy it into a buffer.  

 

Open a new Terminal window and start mongo client in the following way.

 

mongo –port 4000 

 

Paste the contents of the buffer copied earlier from gedit window in front of > prompt of mongo client. You may have to press Enter key to process the last data manipulation in a case when it is not followed by a newline control character.

 

Select the entire contents of the Terminal window and Copy&Paste it into a file solution2.lst. Save a file solution2.lst.

 



Please remember that:

-       a report without the specifications of the queries and data manipulations and listings of the processed queries and data manipulations scores no marks,

-       a report that contains any kind of processing errors scores no marks.

                                                                                                                                                          Task 3 (5 marks) 
Implementation of indexing 

 

Download and unzip a file solution2.zip. You should get a file solution2.js.  

 

Consider the documents included in a collection tpchr and the queries consistent with the following query templates.

 

(1)     Find all parts that belongs to a given brand.

 

(2)     Find all parts that has a retail price greater than a given value.

 

(3)     Find the names of all suppliers.

 

(4)     Find the brands and types of all parts.

 

(5)     Find the names of customers who submitted at least one order.

 

Repeat the implementations of the following four steps for each one of the query patterns listed above.

 

Step 1 Create an index that speeds up processing of a query consistent with a pattern.

 

Step 2 Apply a method getIndexes() to list all existing indexes, for example db.collection.getIndexes().

 

Step 3 Apply a method explain() to verify whether the system plans to use the indexes created for processing of a query consistent with a pattern, for example

db.tpchr.find({"CUSTOMER.nation":"KENYA"}).explain(); 

The constants used in a query are up to you.

 

Step 4 Drop an index created in Step 1 with a method dropIndex(), e.g. db.collection.dropIndex("index_name").  

You can find a name given to an index by the system from the results of the Step 2.

 

Write your solutions into a file solution3.js in the empty slots following a specification of each problem. Do not remove the comments with the specifications of queries and semicolons following the comments !

 

Implementation of each index, displaying query processing plans and dropping an index is worth 1 mark.

 

When ready create a report from processing of the queries in the following way. Use gedit editor to open a file solution3.js with the specifications of the queries and implementations of the queries.

 

Select the entire contents of the file and Copy it into a buffer.

Open a new Terminal window and start mongo client in the following way.

 

mongo –port 4000 

 

Paste the contents of the buffer copied earlier from gedit window in front of > prompt of mongo client. You may have to press Enter key to process the last query in a case when it is not followed by a newline control character.

 

Select the entire contents of the Terminal window and Copy&Paste it into a file solution3.lst. Save a file solution3.lst. Examine the contents of a file solution3.lst for possible errors.