Description
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.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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}.
- 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”}.
- 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
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.
- Find all parts that belongs to a given brand.
- Find all parts that has a retail price greater than a given value.
- Find the names of all suppliers.
- Find the brands and types of all parts.
- 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.
