The following guidelines are expected for all homework submissions:
Follow the instructions [if you haven't already] to install a copy of MongoDB on your local computer, then install the Northwind Sales Database with the instructions also on that page. Perform the following tasks once the database is installed and working correctly.
The assignment is in two parts. Part one is to develop mongo queries for each of the following
exercises. Use the Northwind Sales database. Be sure to include the query you used along
with the results [answers] to the questions. Instructions for installing this database are at
this link. The online version of
the MongoDB manual, including syntax examples, is located at
this location and
this location. The online version of the CSV
files for this exercise are available from Stack Overflow at
this location.
You should download the "master.zip" file from the link on the page, about halfway down. Unzip/untar
them into a new subdirectory in your data/db folder so that mongo can find the files. For example, my
installation on my mac has my CSV directory at /data/db/northwindsample
You will need, at a minimum, the following CSV files to create the database for this exercise:
For the BONUS queries, you will also need:
EXERCISES
=========
Use single queries for these, meaning you may NOT execute one query and then execute
a second, third, fourth, ....., n'th query to determine maximums, minimums or counts.
1. What are the different collections in the Northwind database?
2. How many documents are in the "categories" collection?
3. How many documents are in the "orders" collection?
4. How many orders were handled by the person with EmployeeID number 8?
5. What is the last name of the employee who has the EmployeeID number 1?
6. What are the EmployeeID numbers on orders which have an OrderID less than 10300?
7. What are the Company Names of the suppliers?
8. How many suppliers are there?
9. What is the supplier ID and phone number for the supplier in Boston, Mass.?
Be sure NOT to include the ID of the document...
10. What employee is responsible for the largest number of orders, and for how many
orders is that employee responsible?
11. How many territories have the RegionID value of "2"? What are their territory descriptions?
Be sure NOT to include the ID of the document, and ONLY show the territory descriptions.
12. What is the phone number of the shipper with the company name "United Package"?
Be sure NOT to include the ID of the document in the output, ONLY the phone number.
BONUS:
13. How many documents are in the "order-details" collection? How many in the
"employee-territories" collection?
14. How many orders were shipped to Albuquerque, NM? What are the order numbers?
Be sure NOT to include the ID of the document in the output, ONLY the Order ID numbers.
Follow the instructions [if you haven't already] to install a copy of Neo4J on your local computer, then install the Northwind Sales Database with the instructions also on that page. Perform the following tasks once the database is installed and working correctly.
Develop Neo4J CYPHER queries for each of the following questions. Use the Northwind Sales database. Be sure to include the query you used along with the results [answers] to the questions. Instructions for installing this database are at: this link. The online version of the CSV files for this exercise are available from Stack Overflow at: this link. It's OK that they are the same files for Mongo, and in fact you can use the same CSV files you extracted for that part of this homework. Unzip/untar them into the "import" directory which is under the "neo4jDatabases" directory where you installed the Neo4J application. For example, my installation on my mac in the office has my import directory at:
"/Users/rjohns19/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/...
.../database-a9c63d06-35f6-4a53-92a4-259435da4508/installation-3.4.1/import"
[Note that the "/... .../" is just the line break so I can get this to fit without wrapping]
On Windows, the installation can go into a location of your choosing. For example, on my Windows 10 laptop, I had the database install go to:
"D:\bjohnson\neo4jDatabases\"
...so that the "import" directory path [in full] looks like:
"D:\bjohnson\neo4jDatabases\database-c56f86db-f3fd-478c-8b57-5f82bd491f40\installation-3.4.1\import"
You will need, at a minimum, the following CSV files to create the database for this exercise:
For the BONUS queries, you will also need:
EXERCISES
=========
Use single queries for these, meaning you may NOT execute one query and then execute
a second, third, fourth, ....., n'th query to determine maximums, minimums or counts.
1. What are the different collections in the Northwind database?
2. How many documents are in the "categories" label set?
3. How many documents are in the "orders" label set?
4. How many orders were handled by the person with EmployeeID number 8?
5. What is the last name of the employee who has the EmployeeID number 1?
6. What are the EmployeeID numbers on orders which have an OrderID less than 10300?
7. What are the Company Names of the suppliers?
8. How many suppliers are there?
9. What is the supplier ID and phone number for the supplier in Boston, Mass.?
10. What employee is responsible for the largest number of orders, and for how many
orders is that employee responsible?
11. How many territories have the RegionID value of "2"? What are their territory descriptions?
You can use two queries. Be sure to ONLY show the territory descriptions for that query.
12. What is the phone number of the shipper with the company name "United Package"?
Be sure to ONLY include the phone number in the result.
BONUS:
13. How many relationships are in the "order-details" edges set? How many in the
"employee-territories" edges set?
14. How many orders were shipped to Albuquerque, NM? What are the order numbers?