Email         : brigita.melantika@student.matanauniversity.ac.id
RPubs       : https://rpubs.com/brigitatiaraem/
Jurusan : Statistika
Address : ARA Center, Matana University Tower
   Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.

1 PERBEDAAN MARIADB, RMYSQL, and RSQLITE

1.1 MARIADB

MariaDb merupakan bentuk dari pengembangan MySQL dari sebuah implementasi sistem manajemen basis data rasional yang dapat digunakan secara gratis / open source dan berlisensi General Public Licence sehingga para penggunanya juga dapat mengetahui perkembangan pada setiap versinya (community-driven project). Pada struktur data, MariaDB ini menggunakan sistem regional namun belum menggunakan JSON untuk menyimpan data melainkan menggunakan fitu lainnya.Pada tipe data, MariaDB mendukung tipe data LONGTEXT dan MariaDB menggunakan data masking untuk keamanannya / enkripsi data sehingga informasi yang bersifat data sensitif akan lebih aman. MariaDb juga menggunakan shell SQL dalam command line client yang mana ini hanya mendukung bahasa pemrograman PJP saja. MariaDB menggunakan software untuk memanipulasi data pada database / storage engine seperti ColumnStore, Aria, MyRocks, dan Cassandra.

1.2 RMYSQL

RMySQL ini memiliki sifat multi user, multi platform, dan open source sehingga dapat digunakan secara gratis namun dalam penggunaan DBSM MySQL memerlukan biaya. RMySQL ini dengan mudah diintegrasikan ke bahasa pemrograman karena syntax yang sederhana. RMySQL ini memiliki keamanan seperti secure connections, authentication services, Privilege Level Akses User, Encryption password. RMySQL memiliki hardware requirements yang tidak terlalu tinggi dengan menggunakan intel core dan ram yang kecil RMySQL dapat berjalan lancar dan baik.bersifat Multi User dan Multi Platform

1.3 RSQLITE

SQLite memiliki sifat yang open-source dimana tersedia di domain publik dan mendukung tipe data seperti Blob, Integer, Null, Text, Real. Pada sistem penyimpanan, RSQLite ini dapat menyimpan informasi dalam satu file dan informasinya dapat disalin dengan mudah dan bersifat portabel. RSQLite ini tidak bersifat multi user karena tidak memiliki fungsionalitas user management. RSQLite dikhususkan untuk database yang memiliki ukuran lebih kecil sehingga tidak cocok untuk database yang besar dan keamanan pada RSQLite ini tidak ada sehingga dapat dengna mudah diakses oleh siapapun. RSQLite ini menggunakan sintaks SQL standar dengan perubahan kecil.

2 PENGENALAN DASAR

Database adalah kumpulan data yang terstruktur dan memiliki terminologi sedikit berbeda saat menggunakan sistem manajemen database dibandingkan dengan sistem data di R.

field: variabel atau kuantitas.
record: koleksi lapangan.
table: kumpulan dari record dengan semua field yang sama.
database: kumpulan dari tabel.

Pada terminologi R, field itu sama artinya dengan coloumn, record sama dengan artinya row, table sama artinya dengan data frame, table schema sama artinya dengan type of coloum, dan frame database sama artinya dengan kumpulan data/ colelection of data.

SQL memungkinkan untuk berinteraksi langsung dengan database dan melakukan tugas-tugas seperti menarik data dan membuat pembaruan. R memiliki dua packages yang tersedia untuk memudahkan menjalankan query SQL: DBI, RmariaDB, RMyQL, dan RSQLite.

3 MENYIAPKAN

#install.packages("DBI")
#install.packages("odbc")
#install.packages("RMariaDB")
#install.packages("RMySQL")
#install.packages("RSQLite")

#install.packages("pacman")
pacman::p_load(DBI,
               odbc,
               RMariaDB,
               RMySQL,
               RSQLite
               )

4 MENGKONEKSIKAN R KE SQL

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

dbListTables(MariaDB)                             # table list on your database

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

#dbExecute(MariaDB,"CREATE DATABASE new_mariadb")  # Create a new Database
#dbExecute(MariaDB,"DROP DATABASE new_mariadb")    # Drop a Database

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

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

#dbExecute(MariaDB,"CREATE DATABASE new_MariaDB")
#dbExecute(MariaDB,"DROP DATABASE new_MariaDB")

RSQLite <- dbConnect(RSQLite::SQLite(), "mydb3.sqlite")
dbListTables(RSQLite)                             # table list on your database

## character(0)

5 IMPORT DATA

Tahapan import data tidak diperlukan jika data (tabel) yang dibutuhkan sudah terdaftar di database. Namun jika belum terdaftar, maka diperlukan pengimportan kumpulan data sesuai dengan file yang tersedia (di sini asaya menggunakan file csv dan xlsx).

5.1 FILE CSV

print(getwd())

## [1] "D:/brigita tiara KULIAH/kuliah sem4/basis data"

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

5.2 FILE XLSX

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

6 MENULIS DATA FRAME KE DATABASE

Pada tahapan ini menggunakna dbWriteTable yang memungkinkan untuk menulis bingkai data R langsung ke tabel database yang mana nama pada kolom bingkai data ini digunakan untuk bidang tabel database. Dalam contoh berikut akan mengkoneksikan ke RMariaDB.

new_con<-dbConnect(MariaDB(),
                    user='root',
                    password='',
                    dbname='mysql',
                    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)

Adapun hal penting yang harus diperhatikan dalam menyimpan data tabel yaitu penyesuaian struktur data dan perubahan tipe data (khususnya Date dan Time).

kasus ini, kami memiliki masalah dengan tabel data Employees dan Orders. Pada tabel ini tidak ada tanggal yang ditulis dengan benar dalam database. Berikut solusinya.

dbRemoveTable(new_con,"Orders")

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

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

dbRemoveTable(new_con,"Employees")

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

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

7 GENERAL QUERY

7.1 SELECT FROM

pernyataan SELECT digunakan untuk memilih data dari database.

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

df01<-dbGetQuery(new_con, 'SELECT City, Address, Country
                FROM Customers')
datatable(df01)

7.2 WHERE

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

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

7.3 INSERT INTO

Jika menambahkan VALUES untuk semua kolom tabel maka tidak perlu menentukan nama kolom dalam query SQL. Namun, pastikan urutan values dalam urutan yang sama dengan column dalam tabel. Masukkan ke dalam sintaks sebagai berikut:

dbExecute(new_con,"INSERT INTO Customers(CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES('Brigita','Tiara','Jl. Sugiyopranata','Semarang','50614','Indonesia')")

## [1] 1

7.4 DELETE FROM

Perintah DELETE FROM ini untuk hapus catatan yang keluar dalam table.

dbExecute(new_con,"DELETE FROM customers
          WHERE CustomerName='Brigita'")

## [1] 1

7.5 UPDATE

Perintah UPDATE merupakan perintah yang digunakan untuk mengubah record yang ada di table.

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

## [1] 1

7.6 DISCONNECT DATABASE

jika sudah selesai dengan query dan tidak ingin menggunakannya lagi maka ada perintah untuk memutuskan koneksi dari database sebagai berikut.

dbDisconnect(new_con)

DATABASE SYSTEMS

WEEK 3

Brigita Tiara Elgityana Melantika (20204920001)

February 10, 2022