Database System

Tugas 2

Kontak	: \(\downarrow\)
Email	yosia.yosia@student.matanauniversity.ac.id
Instagram	yyosia
RPubs	https://rpubs.com/yosia/

Difference Between

MariaDB

MariaDB adalah cabang dari MySQL. Dengan kata lain, ini adalah pengganti MySQL drop-in yang disempurnakan.

Pengganti drop-in berarti kita dapat mengganti server MySQL standar dengan versi analog dari server MariaDB dan memanfaatkan sepenuhnya peningkatan di MariaDB tanpa perlu mengubah kode aplikasi kita.

MariaDB memiliki beberapa ekstensi open source tetapi hal itu tidak dimiliki oleh MySql
MariaDB lebih cepat daripada MySql
MariaDB dapat menerima lebih banyak kontribusi komunitas dibandingkan MySQL

RMySQL

RMySQL membutuhkan server untuk dijalankan. MySQL akan memerlukan arsitektur klien dan server untuk berinteraksi melalui jaringan.

MySQL menawarkan kode yang dimilikinya sebagai open source di bawah GPL, dan menyediakan opsi distribusi komersial non-GPL dalam bentuk MySQL Enterprise, sedangkan MariaDB hanya dapat menggunakan GPL

RSQLite

RSQLite adalah database tanpa server dan mandiri. SQLite juga disebut sebagai database tertanam, yang berarti mesin DB berjalan sebagai bagian dari aplikasi.

Beberapa perbedaan yang bisa dilihat dari MariaDB dan MySQL,

1. License

MariaDB : bersifat open source murni, jadi kita bisa menggunakan software tersebut secara gratis dan bisa melakukan modifikasi dan distribusi secara bebas.

RMySQL : Pada mulanya MySQL bersifat open source, namun sejak diakuisisi oleh Oracle software ini tidak lagi full open source. Bisa diartikan dengan Anda dapat menggunakan MySQL secara gratis namun tidak bisa melakukan modifikasi dan pendistribusiannya.

RSQLite : SQLite adalah produk public domain. Artinya tidak punya lisensi

2. Struktur Data

MariaDB : menggunakan sistem regional, belum menggunakan JSON dalam penyimpanannya.

RMySQL : menggunakan sistem regional, sudah menggunakan JSON untuk penyimpanan data

RSQLite : Setiap aplikasi memiliki SQLite database sendiri, database ini dapat diakses oleh setiap class pada aplikasi,tapi tidak bisa diakses oleh aplikasi lain. Database SQLite dibuat pada aplikasi selalu disimpan dalam (/data/nama package/folder database).

3. Tipe Data

MariaDB : menggunakan tipe data LONGTEXT

RMySQL : MySQL yang mendukung tipe data JavaScript Object Notation atau JSON

RSQLite : Numeric, Text, Datetime

4. Keamanan

MariaDB : sudah mendukung data masking, mendukung enkripsi data dengan arti bahwa informasi yang berisi data sensitif akan jauh lebih aman.

RMySQL : otentikasi dengan username, password, dan juga SSH.

RSQLite : SQLite tidak memiliki mekanisme otentikasi bawaan. Dengan begitu, file database SQLite dapat diakses oleh siapa saja.

loading packages :

pacman::p_load(DBI,
               odbc,
               RMariaDB,
               RMySQL,
               RSQLite)

Connecting R to SQl

MariaDB

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

dbListTables (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"

dbExecute(MariaDB, "CREATE DATABASE new_MariaDB")      # membuat database

## [1] 1

dbExecute (MariaDB, "DROP DATABASE new_MariaDB")  # menghapus database

## [1] 0

MySQL

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

dbListTables (MySQL)

##  [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")

## [1] 1

dbExecute (MySQL, "DROP DATABASE new_MySQL")

## [1] 0

RQSLite

RSQLite <- dbConnect(RSQLite::SQLite(), "mydb3.sqlite")
dbListTables (RSQLite)

## character(0)

Import Data

CSV Files

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

XLSX Files

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

Write Dataframe to Database

Kuncinya di sini adalah fungsi dbWriteTable yang memungkinkan kita untuk menulis dataframe R yang langsung ke tabel database. Nama kolom dataframe akan digunakan sebagai database table’s fields. Dalam contoh berikut kita menggunakan RMariaDB.

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)

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 ditulis dengan benar pada database. Untuk menangani masalah ini, ketik kode berikut di concole R :

dbRemoveTable(new_con, "Orders")

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

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

Exercise (lakukan hal yang sama pada datatable Employees)

dbRemoveTable(new_con, "Employees")

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

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

General Query

Select Form

pernyataan SELECT digunakan untuk memilih data dari database

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

datatable(df1)

Where

Klausa WHERE digunakan untuk memfilter record, mengekstrak hanya record yang memenuhi kondisi tertentu.

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

Insert Into

Jika Anda menambahkan nilai untuk semua kolom tabel, Anda tidak perlu menentukan nama kolom dalam query SQL. Namun, pastikan urutan nilai dalam urutan yang sama dengan kolom dalam tabel. Sintaks INSERT INTO adalah sebagai berikut:

dbExecute(new_con,"INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) 
                   VALUES ('Yosia', 'Keren', 'Jl. in aja', 'Melbourne', '0000', 'Australia')")

## [1] 1

Delete From

Pernyataan DELETE digunakan untuk menghapus record yang ada dalam tabel.

dbExecute(new_con, "DELETE FROM Customers
                    WHERE PostalCode='0000'")

## [1] 1

Update

Pernyataan UPDATE digunakan untuk mengubah catatan yang ada dalam tabel.

dbExecute(new_con, "UPDATE Customers
                    SET ContactName='Alfred Schmidt', City='Frankfurt'
                    WHERE CustomerID=1")

## [1] 1

Disconnect Database

Jika Anda sudah selesai dengan proses kueri dan Anda tidak ingin menggunakannya lagi, Anda harus memutuskan koneksi dari database Anda.

dbDisconnect(new_con)