Question

Task 2 (1 mark) Transformation of data stored in the relational tables into data stored in BSON collection.

Answer 1

Mapping Tables, Rows and Columns

Each database in MongoDB consists of collections which are equivalent to an RDBMS database consisting of SQL tables. Each collection stores data in the form of documents which is equivalent to tables storing data in rows. While a row stores data in its set of columns, a document has a JSON-like structure (known as BSON in MongoDB). Lastly, the way we have rows in an SQL row, we have fields in MongoDB. Following is an example of a document (read row) having some fields (read columns) storing user data:

1 2 3 4 5 6 7

{ "_id": ObjectId("5146bb52d8524270060001f3"), "age": 25, "city": "Los Angeles", "email": "[email protected]", "user_name": "Mark Hanks" }

This document is equivalent to a single row in RDBMS. A collection consists of many such documents just as a table consists of many rows. Note that each document in a collection has a unique _id field, which is a 12-byte field that serves as a primary key for the documents. The field is auto generated on creation of the document and is used for uniquely identifying each document.

To understand the mappings better, let us take an example of an SQL table users and its corresponding structure in MongoDB. As shown in Fig 1, each row in the SQL table transforms to a document and each column to a field in MongoDB.

MongoDB uses the find method which is equivalent to the SELECT command in SQL. The following statements simply read all the documents from the posts collection.

1 2 3

SQL: SELECT * FROM `posts` MongoDB: db.posts.find()