Tugas 2

Sistem Basis Data

Email             : albert.prayogo99@gmail.com
RPubs            : https://rpubs.com/albert23899
Jurusan          : Statistika
Address         : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.

---

1 Perbedaan Maria DB, RmySQL, RSQLite

2 Memulai Pembuatan Basis Data

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

2.1 Connecting R to SQL

#MariaDB<-dbConnect(MariaDB(),
                   #user='root',
                   #password='',
                   #dbname='mysql',
                   #host='localhost')
# dbListTables(MariaDB) # List Tabel on Your Database
# dbExecute(MariaDB,"CREATE DATABASE new_MariaDB") # Membuat Database
# dbExecute(MariaDB,"DROP DATABASE new_MariaDB") # Drop Database

#library(RMySQL)
#library(DBI)
#MySQL<-dbConnect(MySQL(),
                 #user='root',
                 #password='',
                 #dbname='mysql',
                # host='localhost')
#dbListTables(MySQL)
#dbExecute(MySQL,"CREATE DATABASE new_MySQL") # Membuat Database
#dbExecute(MySQL, "DROP DATABASE new_mySQL") # Drop Database

2.2 Mengimpor Data CSV

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

2.3 Mengimpor Data XLSX

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)

2.4 Menulis Data Drame menjadi Database

library(DBI)
library(odbc)
library(RMariaDB)
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)

2.5 Remove Table

dbRemoveTable(new_con, "Orders")

2.6 Mengubah Tipe Data di Database

Tipe data orders date adalah tanggal

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

3 Operasi Umum Basis Data

3.1 Menyeleksi Kota , Alamat dan Negara dari Customers

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

3.2 Menyeleksi Customers yang Berasal Dari Jerman

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

3.3 Menambahkan data baru pada Database Customers

dbExecute(new_con,"INSERT INTO Customers(CustomerName,ContactName,Address,City,PostalCode,Country) VALUES('Bakti','Siregar','Jl.Bahagia Selalu','Tangerang','081369','Indonesia')")

## [1] 1

3.4 Menghapus Data dari Customers

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

## [1] 1

3.5 Mengubah isi Basis Data

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

## [1] 1

Disconnect Database

dbDisconnect(new_con)