Microsoft Word – 412H-mongo-cw.docx
412H Document Database Coursework
Task Description
In this course work you will write several MongoDB queries for an example dataset. You can test your queries on
a test server available at 146.169.46.53 with a database called ocean (user name h412 and password h412).
There are multiple ways to test the queries. You can install MongoDB local and use the mongo console, you can
use it programmatically with whatever language you prefer and, finally, you can also use a dedicated client
application (Robomongo – https://robomongo.org/ – is recommended and available for most platforms).
An SQL/Postgres database with the same data but shredded to fit a relational database is loaded on the same
server. The database name is ocean and user name is h412 and the password is 412h.
You should turn in your solution electronically (text document, PDF or similar) via CATe.
Data Description
The dataset used is based on real oceanography data from NOAA with two basic collections, stations and
oceandata. The former contains all station information and the latter all measurements from stations. Stations
can be from different vendors and the structure of the documents can consequently differ. See the examples
below for the two document types.
Figure 1 Example oceandata document, i.e., one measurement.
jesse
高亮
jesse
高亮
jesse
高亮
jesse
高亮
jesse
高亮
jesse
高亮
jesse
高亮
Figure 2 Example station document.
Figure 3 Relational schema
Queries
Write MongoDB queries for the questions below. Also write queries 1, 5, 6, 8, 10, 12, 13 in SQL given the
relational schema defined before.
1) Find all stations with the name “Kawaihae”.
2) Find all stations in the great state of Hawaii.
3) Find all stations established before 1960.
4) Find all stations that measure air temperature and water temperature.
5) Find all stations that do not measure the air temperature.
6) Find all stations not making any measurements.
7) Find all measurement of the air temperature reported not using a double as data type.
8) Find all stations and list only their name and state. Sort by name ascending.
9) Find all measurements with an air temperature between 60 and 70 and an air pressure bigger than
1000.
10) Count the stations by state.
11) Get average values of measurements grouped by year and by product, where the product name is
“water_temperature”.
12) Find the nearest station to the point -117.1572600, 32.7153300.
13) Calculate the average water temperature of the five nearest stations to point -117.1572600,
32.7153300.
14) Find all measurements of stations in the great state of Hawaii.
jesse
高亮
jesse
高亮