CSI235-Lab 6 BSON Documents and Query Processing in MongoDB Database System Solved

This laboratory includes two tasks related to the design and implementation of BSON documents and query processing in MongoDB database system.

 

Task 1  

Logical design and implementation of BSON documents 

 

Consider the following conceptual schema of a sample database that contains information about the transportation companies that own the trucks and employ the drivers. 

 

  

 

Transform a conceptual schema given above into a logical schema of BSON document. To draw a logical schema of BSON document you can use a graphical notation presented in the lecture slides 22 BSON DESIGN. 

 

An important objective of the design is to maximize the size and complexity of hierarchical structures in a database and in the same moment to eliminate any redundancies from a database.

 

Next, use a diagram of a logical schema created in the previous step to implement JSON schema, that can be used to validate the documents, that contain information about transportation companies, drivers, and trucks.

 

Next, create a MongoDB script solution1.js that performs the following actions.

 

(1)     First, the script creates a collection task1 using a method db.createCollection() with the JSON schema implemented in the previous step as a validator (see a presentation 21 Validation with JSON Schema).

 

(2)     Next, the script inserts into a collection task1 the documents that contains information about one transportation company that owns two trucks and employs two employees. The documents must validate well against JSON schema used as a validator for a collection task1. The documents must contain meaningful data and the types of values associated with the keys must be consistent with the meanings of the keys. For example, a value associated with a key "date of birth" must be of type date.

 

(3)     Next, the script inserts into a collection task1 the documents that contains information about one transportation company that owns two trucks and employs two employees. One of the documents must fail validation against JSON schema used as a validator for a collection task1. The documents must contain meaningful data and the types of values associated with the keys must be consistent with the meanings of the keys. For example, a value associated with a key "date of birth" must be of type date.

 

(4)     Finally, the script prints the explanations on why one of the documents failed the validation. A simple way to print the explanation is to use print("text').

 

To process a script solution1.js and to create a report solution1.lst from processing of a script, perform the following steps.

 

(1)     Use gedit editor to open a file solution1.js with the implementations of the actions listed above.

 

(2)     Select the entire contents of gedit window and Copy it into a buffer.  

 

(3)     Open a new Terminal window and start mongo client in the following way.

 

mongo –port 4000 

 

(4)     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.

 

(5)     Select the entire contents of the Terminal window and Copy&Paste it into a file solution1.lst. Save a file solution1.lst.

 

Deliverables 

A file solution1.lst with a report from processing of MongoDB script solution1.js that creates a collection task 1 with JSON validator and inserts the documents into the collection. Do not forget about the explanations why one of the documents failed the validation.

 

Please remember that:

- a report without listings of the processed methods scores no marks, - a report that contains any kind of processing errors scores no marks.

                                                                                                                                                              

Task 2 

Implementation of simple queries in MongoDB 

 

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.  

 

Use the methods find() and pretty() to implement the following queries.

 

(1)     Display in a pretty format information about the total number of customers who submitted empty orders, i.e. orders with no lines.

 

(2)     Display in a pretty format information about the available quantities of parts (availqty), that have retail price greater than 908. List only information about the available quantities, and retail prices.

 

(3)     Display in a pretty format information about the customers from the nations of JORDAN or MALAWI. Do not list information about the submitted orders.

 

(4)     Display in a pretty format information about the customers whose account balance (acctbal) is less then 122 and about the parts that have size less than 3. In a relation to customers, list only information about the customer keys and account balances. In a relation to parts, list only information about part key and part size.

 

(5)     Display in a pretty format information about the part keys and the supplier keys of all suppliers who supplied at least one part that has a retail price equal to 909 and a size equal to 12.

 

Implement the queries in a query language of MongoDB, i.e. use the methods find() and pretty() to implement the queries. Write your solutions into a file solution2.js into the empty slots following a specification of each query. Do not remove the specifications of the queries and semicolons following the specifications !

 

When ready create a report from processing of the queries in the following way.  

 

Use gedit editor to open a file solution2.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 solution2.lst. Save a file solution2.lst.