Description
The objective of this assignment is to test your understanding of MongoDB data modeling and query language. The Assignment is worth 5.0% of your final grade. The Assignment is marked out of 100. When doing the assignment you will need to use MongoDB, the cloud database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system.database management system. There is a brief Instruction containing all needed commands given in an Appendix to this Assignment. Read it carefully before starting doing Assignment. In the course of answering assignment questions, you will need to issue command prompt instructions and to use a MongoDB shell of: a single sharded MongoDB installation. For deployment of the installations, a script is provided in the Instruction. The assignment is mainly based on a database named Boat Hire, containing data about marinas, boats, sailors, and boat reservations made by sailors. Sailors may book only those boats in a marina that belong to the marina. Each marina has a unique name. Each boat has a unique number within its marina. Each sailor has a unique sailorId. A boat can have at most one reservation on a given day. A sailor can make at most one reservation on a given day. The database should initially contain at least data given in a sequence of tables on the next pages under the name “Assignment 3 Data”. In the MongoDB environment, the Boat Hire data base can be implemented either as a collection containing embedded documents, or a set of collections containing documents that rely on referencing. There are two parts to this assignment. Both parts are about using insert(), update(), find(), and single purpose aggregations (count() and distinct()) in the mongo shell of a single sharded deployment. If you think there is an advantage of using single purpose aggregations (count() and distinct()) over using the find() method, use single purpose aggregations SWEN 432 2 Assignment 3/17 and justify your decision. You are not expected to use (pipeline) aggregate() methods in this assignment. The documents in the first and second part contain the same data. Only, in the first one, documents are implemented using embedding, while the documents of the second one use referencing. Assignment 3 Data Marina name Location Sea View Petone Port Nicholson Wellington Evans Bay Wellington Boats in Sea View marina name number color driven_by Flying Dutch 313 blue sail Blue Shark 515 yellow motor Killer Whale 111 black row Dolphin 110 blue sail, motor Boats in Port Nicholson marina name number color driven_by Red Cod 616 yellow sail, motor Tarakihi 717 red row, motor Night Breeze 818 black row Mermaid 919 white sail, motor Dolphin 110 white Pretty Lady 515 pink sail Boats in Evans Bay marina Name number color driven_by Sally Ann 313 white motor Charlie 515 blue sail, motor Sailors name sailorId skills address James 707 row, sail, motor, fish Wellington Peter 111 row, sail, motor Upper Hutt Milan 818 row, sail, motor, first aid Wellington Eileen 919 sail, motor, swim Lower Hutt Paul 110 row, swim Upper Hutt Gwendolyn 777 row, sail, motor, dance Masterton SWEN 432 3 Assignment 3/17 Reserves marina boat sailor date Sea View 313 707 2017-03-15 Sea View 313 111 2017-03-16 Sea View 111 111 2017-03-15 Sea View 010 818 2017-03-15 Sea View 515 919 2017-03-15 Port Nicholson 919 818 2017-03-16 Port Nicholson 919 707 2017-03-17 Port Nicholson 515 111 2017-03-17 Port Nicholson 616 707 2017-03-21 Evans Bay 313 818 2017-03-19 Evans Bay 515 818 2017-03-21 Port Nicholson 818 999 2017-03-20 Port Nicholson 818 999 2017-03-21 Port Nicholson 717 919 2017-03-25 Port Nicholson 717 777 2017-03-28 Important 1. In your answers to the assignment questions, show the commands and methods you issued and used and the answers produced by MongoDB on the standard output. 2. Do not delete your MongoDB collections after finishing the assignment. You will need them in Assignment 4. Part I Question 1. Making your reserves Collection [32 marks] To spare you from doing a tedious and repetitive job, Pavle made his reserves MongoDB collection and exported it into the file reserves_17.txt. You will find the file reserves_17.txt on the course Assignments page. Start a single sharded MongoDB deployment and import the file reserves_17.txt. (Import command is given in the Appendix.) Call your collection reserves. This is a must. (Note: The reserves_17.txt may contain some valid documents that are not shown in Assignment 3 Data.) Soon after exporting the file reserves_17.txt, Pavle realized that it contained a number of errors and that it was not complete. In this question, you need to tidy and complete your reserves collection. SWEN 432 4 Assignment 3/17 a) [2 marks] The name of the Port Nicholson marina is misspelled in a number of ways. Use multi option of the db.collection.update() method to bring it in order. b) [4 marks] In the document “_id” : ObjectId(“54f102de0b54b61a031776ed”), the field number is misspelled as numbver. Rename it. c) [4 marks] A document for the row Port Nicholson 717 919 2017-03-25 of the Reserves table is missing. Insert it. d) [5 marks] We need all boats to be in our database, but the boat Dolphin, number 110 from Port Nicholson marina had no reserves yet and its data are missing. Make a document containing Dolphin’s data and store it in your collection. The document should follow the structure of other documents in the reserves collection to the highest possible (and reasonable) extent. e) [5 marks] We need all sailors to be in our database, but the sailor Paul from Upper Hutt did not make any reserves yet and his data are missing. Make a document containing Paul’s data and store it in your collection. The document should follow the structure of other documents in the reserves collection to the highest possible (and reasonable) extent. f) [12 marks] Pavle also realized that he missed to define the following unique constraints: i. A sailor can make at most one reservation on a given day. [4 marks] ii. A boat can have at most one reservation on a given day. [4 marks] iii. Check whether your indexes perform as expected. In your answer, show how did you perform checking, and what messages you received. [4 marks] Question 2. Simple Queries [26 marks] a) [2 mark] Find the number of all documents in your reserves collection. b) [4 marks] Find the number of all documents in your reserves collection containing reserves made in Port Nicholson marina. c) [4 mark] Find unique sailor names. d) [5 marks] Find marina names, boat names, and sailor names having a reservation on “2017-03-16”. e) [5 marks] Find sailors having swim skill. Display just sailor names. SWEN 432 5 Assignment 3/17 f) [6 marks] Find sailors having exactly row, sail, and motor skills (no more and no less, but in an arbitrary order). Display just sailor names. Question 3. Time Table Collection [10 marks] Assume Wellington Tranz Metro has acquired an iPhone application that works as a data recorder for railway vehicles (cars, engines). The application uses mobile data connections to send information to servers in real time. The information includes measurements such as the location and speed of the vehicle. They have selected MongoDB (instead of Cassandra, as in Assignment 1) as the CDBMS to use for this project. They invited you, as a respected database specialist to design an embedded collection of the database. a) [6 marks] Assume your document will contain the following entity types: driver, vehicle, time table, and data point. Design a MongoDB document by representing relationships between entity types by embedding. Make relationships between entity types clearly visible in your design. Use iPhon database sample data of Assignment 1 to populate your document. Show your design in your answer. b) [4 marks] How many instances of each entity type may contain your document maximally (make a best guess if you can’t give an exact number)? Part II Pavle implemented the referencing version of the Boat Hire database as four collections and exported them into files: marina_17.txt, sailor_17.txt, boat_17.txt, res_ref_17.txt. You will find these files at the course Assignments page. Start a single sharded MongoDB deployment and import the files. You may do import into the same database as for reserves collection. Call the new collections: marina, sailor, boat, and res_ref. So far, Pavle did not find any errors in the implementation. Use these collections to answer the following questions. SWEN 432 6 Assignment 3/17 Question 4. Simple Queries [8 marks] a) [4 marks] Find all unique sailors. b) [4 marks] Find sailors having exactly row, sail, and motor skills (no more and no less). Display just sailor names. Question 5. Complex Queries [24 marks] a) [8 marks] Find marina names, boat names, and sailor names having a reservation on “2017-03-16”. b) [16 marks] Find marina names, boat names, and sailor names having a reservation on “2017-03-16” using not more than two commands in mongo shell. Submission Instruction: Submit your answers to assignment questions via the school electronic submission system and hand-in a printed version in the hand-in box on the second floor of the Cotton Building. Please do not submit any .odt, .zip, or similar files. Also, do not submit your files in toll directory trees. All files in the same directory is just fine. Additionally, submit your commands for questions: Q2, Q4, and Q5 as separate .txt files only electronically (Pavle is going to run these commands). SWEN 432 7 Assignment 3/17 Appendix A Short Instruction for Using MongoDB on ECS Workstations 1. MongoDB Scripts Before you try to use MongoDB on our school workstations, you have to type [~] % need mongodb This command will allow all what is needed to deploy MongoDB configurations. You may want to insert need mongodb in your .cshrc file to avoid typing need mongodb whenever you log_on. Our programmer Royce Brown produced the following four scripts for deploying different MongoDB configurations: [~] % single-mongo, [~] % rep-mongo, [~] % sha-mongo, [~] % sharep-mongo. You can run these scripts at the command line prompt of your home directory. Just run them without any parameters to see what each one does. In Assignemnt3_16 you will use single-mongo. In Assignment4_16 you will use single-mongo, sha-mongo, and sharep-mongo. After starting a MongoDB configuration (e.g. single-mongo start), you can: Import a collection from a file into a database by typing: [~] % mongoimport –db –collection –file Connect to a mongo shell by typing: [~] % mongo > 2. Useful mongo shell commands To get help: > help To see existing databases, while being in a mongo(s) shell: > show dbs SWEN 432 8 Assignment 3/17 To use an existing database, or to define a new one: > use To see collections in the current database: > show collections To exit from a mongo(s) shell: CTRL/d Note: The default database is test. If you do not issue a use , all your commands are going to be executed against the test database. Warning: In all deployments the same ports are assigned to servers. After finishing a session you have to stop all servers of your deployment to release ports for other uses. Failing to do so, you will make trouble to other people (potentially including yourself) wanting to use the same workstation. Later, if you want to use the same deployment again, you just do start and your deployment will resume functioning reliably. If you don’t plan to use a deployment again, don’t forget to do cleanall. You are strongly advised to use MongoDB from school lab workstations. The school does not undertake any guarantees for using MongoDB from school servers. You may install and use MongoDB on your laptop, but the school does not undertake any responsibilities for the results you obtain.
