[SOLVED] database SQL Microsoft Word 412H-mongo-cw.docx

Microsoft Word 412H-mongo-cw.docx

412HDocumentDatabaseCoursework
TaskDescription
InthiscourseworkyouwillwriteseveralMongoDBqueriesforanexampledataset.Youcantestyourquerieson
a test server available at 146.169.46.53with a database called ocean (user name h412 and password h412).
Therearemultiplewaystotestthequeries.YoucaninstallMongoDBlocalandusethemongoconsole,youcan
use it programmatically with whatever language you prefer and, finally, you can also use a dedicated client
application(Robomongohttps://robomongo.org/isrecommendedandavailableformostplatforms).
AnSQL/Postgresdatabasewiththesamedatabutshreddedto fitarelationaldatabase is loadedonthesame
server.Thedatabasenameisoceanandusernameish412andthepasswordis412h.
Youshouldturninyoursolutionelectronically(textdocument,PDForsimilar)viaCATe.

DataDescription
ThedatasetusedisbasedonrealoceanographydatafromNOAAwithtwobasiccollections,stationsand
oceandata.Theformercontainsallstationinformationandthelatterallmeasurementsfromstations.Stations
canbefromdifferentvendorsandthestructureofthedocumentscanconsequentlydiffer.Seetheexamples
belowforthetwodocumenttypes.

Figure1Exampleoceandatadocument,i.e.,onemeasurement.

jesse

jesse

jesse

jesse

jesse

jesse

jesse

Figure2Examplestationdocument.

Figure3Relationalschema

Queries
WriteMongoDBqueriesforthequestionsbelow.Alsowritequeries1,5,6,8,10,12,13inSQLgiventhe
relationalschemadefinedbefore.

1) FindallstationswiththenameKawaihae.
2) FindallstationsinthegreatstateofHawaii.
3) Findallstationsestablishedbefore1960.
4) Findallstationsthatmeasureairtemperatureandwatertemperature.

5) Findallstationsthatdonotmeasuretheairtemperature.
6) Findallstationsnotmakinganymeasurements.
7) Findallmeasurementoftheairtemperaturereportednotusingadoubleasdatatype.
8) Findallstationsandlistonlytheirnameandstate.Sortbynameascending.
9) Findallmeasurementswithanairtemperaturebetween60and70andanairpressurebiggerthan

1000.
10) Countthestationsbystate.
11) Getaveragevaluesofmeasurementsgroupedbyyearandbyproduct,wheretheproductnameis

water_temperature.
12) Findtheneareststationtothepoint-117.1572600,32.7153300.
13) Calculatetheaveragewatertemperatureofthefiveneareststationstopoint-117.1572600,

32.7153300.
14) FindallmeasurementsofstationsinthegreatstateofHawaii.

jesse

jesse