Week13 Assignemnt-NoSQL Migration

For this assignment,take information from a relational database and migrate it to a NoSQL database.

library(RMySQL)

## Loading required package: DBI

library(sqldf)

## Loading required package: gsubfn

## Loading required package: proto

## Loading required package: RSQLite

## 
## Attaching package: 'RSQLite'

## The following object is masked from 'package:RMySQL':
## 
##     isIdCurrent

## sqldf will default to using MySQL

library(mongolite)

## Warning: package 'mongolite' was built under R version 3.5.3

Connect to local MySQL Database

mydb<-dbConnect(MySQL(), user='wei', password='0000', dbname='movie_survey', host='localhost')

write sql to join three tables

sr<- dbGetQuery(mydb, "
                select s.survey_id, r.firstname, r.lastname, r.sex,                                   m.moviesname, m.type, s.score,s.date
                from survey s
                     join movies m on s.movie_id = m.movies_id
                     join respondents r on s.respondent_id = r.respondents_id")
head(sr)

##   survey_id firstname lastname sex       moviesname      type score
## 1         1     jacky   jordon   m    What Men Want    comedy     5
## 2         2     jacky   jordon   m     Cold Pursuit    action     3
## 3         3     jacky   jordon   m The Lego Moive 2 animation     4
## 4         4     jacky   jordon   m   The Final Wish    horror     4
## 5         5     jacky   jordon   m            Glass     drama     5
## 6         6       tom    peter   m The Lego Moive 2 animation     5
##         date
## 1 2019-02-09
## 2 2019-02-09
## 3 2019-02-09
## 4 2019-02-09
## 5 2019-02-09
## 6 2019-02-08

Disconnect from local mysql database

dbDisconnect(mydb)

## [1] TRUE

migrate database into local Mongodb database

mangodb <- mongo(collection = "airports", db = "mgdb")

Advantage of relational database

It works with well-structured data. We can easily pull data from database like MySQL writing SQL.

Advantage of NoSQL database

A NoSQL database is good for storing and processing large amounts of unstructured data.

Disadvantages of relational databases

1.It requires well developed architecture before loading data into database, it could be huge challenge when we deal with a large and complicate dataset. 2.It requires someone who has great expertise to design the database to fulfill the 3 normalization forms. It is time consuming for optimizing the database which is very complicated.

Disadvantages of NoSQL databases

It lacks of standardization, it may needs a standard query language like SQL.