(Assignment)

For this assignment, you should take information from a relational database and migrate it to a NoSQL database of your own choosing.

For the relational database, you might use the flights database, the tb database, the “data skills” database your team created for Project 3, or another database of your own choosing or creation.

For the NoSQL database, you may use MongoDB (which we introduced in week 7), Neo4j, or another NoSQL database of your choosing.

Your migration process needs to be reproducible. R code is encouraged, but not required. You should also briefly describe the advantages and disadvantages of storing the data in a relational database vs. your NoSQL database.

Relational Database VS NoSQL Database

Relational

This type of database is Versatile and Popular which makes it a safe choice for the most common DB needs.

Because this database is so widely used, it is easy to find support via vendors and independent consultations

These databases have fixed or static predefined schema, which may be restrictive - all of your data must follow the same structure.

The static predefined schema may also be an advantage - it works great with applications that require multi-row transactions such as an accounting systems

NoSQL Database

For support of NoSQL database you may have to rely on community support and there are few outside experts available for deploying a large scale deployment.

A NoSQL database has dynamic schema for unstructured data. Data is stored in many ways which means it can be document-oriented, column-oriented, graph-based or organized as a KeyValue store.

NoSQL Databases scale horizontally; adding more servers to the database so it becomes much larger and more powerful than an SQL Databases can scale.

I used packages RMySQL and mongolite. Below are the steps i followed in order to migrate data from my relational database (which is a local MySQL server) to a local MongoDB instance.

STEP 1 - connect to my local SQL database

con <- dbConnect(RMySQL::MySQL(),
                 dbname='assign2', 
                 host='localhost',
                 user = 'testuser',
                 password = "password")

STEP 2 - list the existing tables in my relational database

dbListTables(con)

STEP 3 - load the tables into R dataframes

rKids <- dbReadTable(con, "Kids")
rMovies <- dbReadTable(con, "Movies")
rRatings <- dbReadTable(con, "Ratings")

STEP 4 - Connecting to MongoDB

mongoDB_Movies = mongo(collection = "Movies", db = "Movies")
mongoDB_Kids = mongo(collection = "Kids", db = "Kids")
mongoDB_Ratings = mongo(collection = "Ratings", db = "Ratings")

STEP 5 - Load the data into the MongoDB

mongoDB_Kids$insert(Kids)

mongoDB_Ratings$insert(Ratings)

mongoDB_Movies$insert(Movies)