Database System

Tugas 2

Kontak	: \(\downarrow\)
Email	clara.evania@student.matanauniversity.ac.id
Instagram	https://www.instagram.com/claraevania/
RPubs	https://rpubs.com/claradellaevania/

What is the difference between MariaDB, RMySQL, and RSQLite

MariaDB merupakan database relasional open source yang dibangun oleh pengembang asli MySQL. MariaDB merupakan suatu aplikasi yang dikembangkan oleh sistem basis data dalam tujuannya mempertahankan dan menjaga kompatibilitas tinggi.

RMySQL merupakan sebuah database management system dimana dalam penggunaannya menggunakan perintah SQL dalam mengelola database pada website yang terkoneksi dalam R studio.Menyematkan mesin database SQLite di R, menyediakan antarmuka yang sesuai dengan DBI.

RSQLite merupakan perangkat lunak yang menyediakan sistem manajemen basis data relasional (RDBMS) dimana databse yang berbasis file dan terdiri dari satu file pada disk, yang membuatnya menjadi software yang portabel dan andal, dimana terkoneksi dalam R studio.

Perbedaan MariaDB, RMySQL, dan RSQLite :

Perbedaan Arsitektur

MariaDB : pengembangannya sendiri yang berbasis komunitas, namun dalam versi enterprise didukung oleh MariaDB Coorporation AB, sedangkan untuk versi komunitas, didukung oleh MariaDB Foundation

MySQL : merupakan Proyek opensource yang dimiliki oleh Oracle pada tahun 2010 setelah berasil mengakuisi Sun Microsystem

SQLite : merupakan database tanpa server dan mandiri yang tersedia pada domain publik dimana SQlite disebut sebagai databse tertanam yang berarti Database berjalan sebagai bagian dari suatu aplikasi.
Tipe Data

MariaDB : Dalam Pengembangannya, Maria DB belum mendukung tipedata JSON, namun dapat menggunakan tipe data LONGTEXT dikarenakan JSON dianggap memiliki kontradiksi dengan standar penggunaaan SQL.

MySQL : Mendukung Tipe data JSON (JavaScript Objec Notation) yang banyak digunakan dalam pertukaran data pada web dan cukup ringan. MySQL mendukung tipe data Tinyint, Smallint, Mediumint, Int, Bigint, Double, Float, Real, Decimal, Double precision, Numeric, Timestamp, Date, Datetime, Char, Varchar, Year, Tinytext, Tinyblob, Blob, Text, MediumBlob, MediumText, Enum, Set, Longblob, Longtext.

SQLite :mendukung tipe data ini Blob, Integer, Null, Text, Real
Lisensi

MariaDB : MariaDB Server berlisensikan GPLv2, dimana dapat mendapatkan full-featured aplikasi seperti Threadpool.

MySQL : Mmemiliki dua versi lisensi, yang satu berlisensi GPLv2 untuk versi komunitas, dan yang satu Enterprise. Dimana contohnya fitur Threadpool dalam SQL diharuskan membeli lisensi versi Enterprise padahal fitur ini dapat mempengaruhi performa database secara signifikan.

SQLite : SQLite merupakan produk public domain. Artinya tidak punya lisensi.

Connecting the database to R.

Getting Started

install.packages(c("DBI",
                   "odbc",
                   "RMariaDB",
                   "RMySQL",
                   "RSQLite")
                 )

Lalu memuat semua paket menggunakan pacman

# install.packages("pacman")
pacman::p_load(DBI,                  # membantu menghubungkan R ke DBMS
               odbc,                 # Menghubungkan ke Database yang Kompatibel dengan ODBC
               RMariaDB,             # Antarmuka Basis Data dan Driver 'MariaDB'
               RMySQL,               # Antarmuka Basis Data dan Driver 'RMySQL' 
               RSQLite               # Antarmuka Basis Data dan Driver 'RSQLite' 
          )

Connecting R to SQL

Terdapat banyak cara dalam menghubungkan database pada r, 3 cara yang paling umum adalah :

MariaDB <- dbConnect(MariaDB(), 
                  user='root',
                  password='', 
                  dbname='mysql', 
                  host='localhost')

MariaDB

## <MariaDBConnection>
##   Host:    localhost
##   Server:  
##   Client:

dbListTables(MariaDB)                             # Data Table yang ada pada MariaDB

##  [1] "columns_priv"              "column_stats"             
##  [3] "db"                        "event"                    
##  [5] "func"                      "general_log"              
##  [7] "global_priv"               "gtid_slave_pos"           
##  [9] "help_category"             "help_keyword"             
## [11] "help_relation"             "help_topic"               
## [13] "index_stats"               "innodb_index_stats"       
## [15] "innodb_table_stats"        "plugin"                   
## [17] "proc"                      "procs_priv"               
## [19] "proxies_priv"              "roles_mapping"            
## [21] "servers"                   "slow_log"                 
## [23] "tables_priv"               "table_stats"              
## [25] "time_zone"                 "time_zone_leap_second"    
## [27] "time_zone_name"            "time_zone_transition"     
## [29] "time_zone_transition_type" "transaction_registry"     
## [31] "user"

Membuat Database Baru

dbExecute(MariaDB,"CREATE DATABASE new_MariaDB")

## [1] 1

New Database

Menghapus Database

dbExecute(MariaDB,"DROP DATABASE new_MariaDB")

## [1] 0

Drop Database

MySQL <- dbConnect(MySQL(), 
                  user='root',
                  password='', 
                  dbname='mysql', 
                  host='localhost')
dbListTables(MySQL)                               # Data table yang ada pada database tersebut

##  [1] "column_stats"              "columns_priv"             
##  [3] "db"                        "event"                    
##  [5] "func"                      "general_log"              
##  [7] "global_priv"               "gtid_slave_pos"           
##  [9] "help_category"             "help_keyword"             
## [11] "help_relation"             "help_topic"               
## [13] "index_stats"               "innodb_index_stats"       
## [15] "innodb_table_stats"        "plugin"                   
## [17] "proc"                      "procs_priv"               
## [19] "proxies_priv"              "roles_mapping"            
## [21] "servers"                   "slow_log"                 
## [23] "table_stats"               "tables_priv"              
## [25] "time_zone"                 "time_zone_leap_second"    
## [27] "time_zone_name"            "time_zone_transition"     
## [29] "time_zone_transition_type" "transaction_registry"     
## [31] "user"

dbExecute(MySQL,"CREATE DATABASE new_MySQL")      # Membuat database baru

## [1] 1

New Database new_MySQL

dbExecute(MySQL,"DROP DATABASE new_MySQL")        # Menghapus Database new_MySQL

## [1] 0

Drop Database new_MySQL

RSQLite <- dbConnect(RSQLite::SQLite(),"mydb.sqlite")
dbListTables(RSQLite)                             # Data table yang ada pada database tersebut

## character(0)

Masuk ke dalam Folder

Import Data

Jika Data Table yang dibutuhkan sudah terdaftar pada database, maka diperlukan mengimpor kumpulan data yang sesuai dengan file yang tersedia dengan file CSV dan XLSX.

CSV Files

Customers   <-read.csv("data/Customers.csv")
Categories  <-read.csv("data/Categories.csv")  
Employees   <-read.csv("data/Employees.csv")  
OrderDetails<-read.csv("data/OrderDetails.csv")  
Orders      <-read.csv("data/Orders.csv")  
Products    <-read.csv("data/Products.csv")  
Shippers    <-read.csv("data/Shippers.csv")
Suppliers   <-read.csv("data/Suppliers.csv")

Employees

XLSX Files

library("readxl")                                  
Customers   <-read_excel("data/RawDatabase.xlsx",sheet=1)      
Categories  <-read_excel("data/RawDatabase.xlsx",sheet=2) 
Employees   <-read_excel("data/RawDatabase.xlsx",sheet=3)
OrderDetails<-read_excel("data/RawDatabase.xlsx",sheet=4) 
Orders      <-read_excel("data/RawDatabase.xlsx",sheet=5)
Products    <-read_excel("data/RawDatabase.xlsx",sheet=6) 
Shippers    <-read_excel("data/RawDatabase.xlsx",sheet=7)  
Suppliers   <-read_excel("data/RawDatabase.xlsx",sheet=8)

Orders

Write Dataframe to Database

Menggunakan fungsi dbWriteTable dimana memungkinkan dalam menulis bingkai data pada R langsung ke tabel database. Nama kolom data digunakan sebagai bidang tabel database. Dengan menggunakan koneksi RMariaDB, kita dapat menerapkan driver yang diinginkan.

dbExecute(MariaDB,"CREATE DATABASE new_mariadb")

## [1] 1

new_con <- dbConnect(MariaDB(), 
                  user='root',
                  password='', 
                  dbname='new_mariadb', 
                  host='localhost')

dbWriteTable(new_con, "Customers", Customers, append=T) 
dbWriteTable(new_con, "Categories", Categories, append=T) 
dbWriteTable(new_con, "Employees", Employees, append=T) 
dbWriteTable(new_con, "OrderDetails", OrderDetails, append=T) 
dbWriteTable(new_con, "Orders", Orders, append=T) 
dbWriteTable(new_con, "Products", Products, append=T) 
dbWriteTable(new_con, "Shippers", Shippers, append=T) 
dbWriteTable(new_con, "Suppliers", Suppliers, append=T)

Semua Datatable masuk ke dalam database mariadb

Dalam menyimpan data tabel, terdapat beberapa hal yang harus diperhatikan yaitu :

Penyesuaian Struktur Data
Perubahan Tipe Data Khususnya, Tanggal dan Waktu.

Dalam mempertimbangkan Data Employees dan Orders, ditemukan tidak terdapat penulisan tanggal yang benar pada database. Untuk menangani masalah tersebut, code yang tepat adalah :

dbRemoveTable(new_con, "Orders")

DataTable Orders sudah tidak ada didalam database

dbListTables(new_con)

## [1] "categories"   "customers"    "employees"    "orderdetails" "products"    
## [6] "shippers"     "suppliers"

Orders["OrderDate"] <-as.Date(Orders$OrderDate, format = "%Y-%m-%d") 
Orders

dbWriteTable(new_con, "Orders", Orders, append=T)
dbListTables(new_con)

## [1] "categories"   "customers"    "employees"    "orderdetails" "orders"      
## [6] "products"     "shippers"     "suppliers"

Melakukan Hal yang sama pada data employees

dbRemoveTable(new_con, "Employees")

DataTable employees sudah tidak ada didalam database

dbListTables(new_con)

## [1] "categories"   "customers"    "orderdetails" "orders"       "products"    
## [6] "shippers"     "suppliers"

Employees["BirthDate"] <-as.Date(Employees$BirthDate, format = "%d%Y-%m-%d") 
Employees

dbWriteTable(new_con,"Employees", Employees, append=T)
dbListTables(new_con)

## [1] "categories"   "customers"    "employees"    "orderdetails" "orders"      
## [6] "products"     "shippers"     "suppliers"

General Query

Select From

Digunakan untuk menyeleksi data dari database

library(DT)
df1<-dbGetQuery(new_con,'SELECT City, Address,Country
                         FROM Customers')
datatable(df1)

dbWriteTable(new_con, "df1", df1, append=T)
dbListTables(new_con)

## [1] "categories"   "customers"    "df1"          "employees"    "orderdetails"
## [6] "orders"       "products"     "shippers"     "suppliers"

Dataframe1 sudah disimpan kedalam database

Where

Digunakan dalam memfilter record, mengekstrak hanya record yang memenuhi kondisi tertentu

df2<-dbGetQuery(new_con,"SELECT* 
                         FROM Customers 
                         WHERE Country='Canada'")
datatable(df2)

dbWriteTable(new_con, "df2", df2, append=T)
dbListTables(new_con)

##  [1] "categories"   "customers"    "df1"          "df2"          "employees"   
##  [6] "orderdetails" "orders"       "products"     "shippers"     "suppliers"

Dataframe2 sudah disimpan kedalam database

Insert Into

Digunakan jika ingin menambahakn nilai pada semua kolom tabel, tidak perlu menentukan nama kolom dalam query SQL, namun hanya memastikan urutan nilai dalam urutan yang sama dengan kolom yang ada pada tabel

dbExecute(new_con,"INSERT INTO Customers(CustomerName,ContactName,Address,City,PostalCode, Country)
VALUES('Clara','Della','Jalan Bunga 24B','Jakarta','11650','Indonesia')")

## [1] 1

Customers Name Clara

Delete From

Digunakan untuk menghapus catatan yang ada pada tabel.

dbExecute(new_con,"DELETE FROM Customers
                   WHERE CustomerName ='Clara' ")

## [1] 1

Menghapus Customers Name Clara

Update

Digunakan untuk mengubah catatan yang ada pada tabel

dbExecute(new_con,"UPDATE Customers
                   SET ContactName = 'Alfreds Mario', City= 'Hamburg'
                   WHERE CustomerID = 1")

## [1] 1

Merubah contactname dan city

Disconnect Database

Jika sudah selesai dalam proses Query dan tidak ingin menggunakannya lagi, dapat memutuskan koneksi dari database tersebut.

dbDisconnect(new_con)