1 Connecting MySQL

Supaya lebih mudah menghubungkan ke MySQL, kita dapat menggunakan RMySQL package. Untuk menghubungkan ke MySQL database caranya hanya menginstall package dan memuat library.

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

## package 'odbc' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
##    C:\Users\user\AppData\Local\Temp\RtmpaeQdjG\downloaded_packages

MySQL <- dbConnect(MySQL(), 
                  user='root',
                  password='', 
                  dbname='week4', 
                  host='localhost')
dbListTables(MySQL)                  # a list of the tables in our connection

Catatan : Buka XAMPP, klik start pada Aphace dan MySQL. Lalu, pastikan sudah memiliki akses admin sebelum membuat database apapun.

1.1 Create DB

Jika ingin membuat database baru, maka pernyataan CREATE DATABASE seperti di bawah ini:

dbExecute(MySQL, "CREATE DATABASE factory_db")

Hasilnya 1, artinya database sudah berhasil dibuat.

1.2 Drop DB

Jika ingin menghapus database, kita menggunakan DROP DATABASE seperti di bawah ini :

dbExecute(MySQL, "DROP DATABASE factory_db")

Hasilnya 0, artinya sudah berhasil menghapus database.

2 Create Table

Setelah memiliki database, kita bisa ke tahap berikutnya yaitu membuat tabel seperti di bawah ini :

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

dbExecute(factory_db, "CREATE TABLE Persons(PersonID int,
          LastName varchar(255),
          FirstName varchar(255),
          Address varchar(255),
          City varchar(255))")

2.1 Insert Value

Jika ingin menambahkan nilai untuk semua kolom tabel, tidak perlu menentukan nama kolom dari SQL query. Pastikan urutan nilainya sama dengan urutan kolom pada tabel. Kita gunakan INSERT INTO syntax seperti di bawah ini :

dbExecute(factory_db,"INSERT INTO Persons(PersonID,LastName,FirstName, Address, City) VALUES(1,'Ramadhani','Sausan','Jl.Buntu','Jakarta')")

2.2 Truncate Table

TRUNCATE TABLE berguna untuk menghapus data dalam tabel tetapi bukan tabelnya sendiri.

dbExecute(factory_db, "TRUNCATE TABLE Persons")

2.3 Drop Table

DROP TABLE digunakan untuk menghapus tabel yang ada dalam database.

dbExecute(factory_db, "DROP TABLE Persons")

2.4 Write Table

Kita menggunakan dbWriteTable function untuk membuat R data frame langsung ke tabel database. Nama kolom data frame digunakan sebagai kolom pada tabel database.

Orders <-read.csv("Orders.csv")
dbWriteTable(factory_db, "Orders", Orders, append=T)

3 Alter Table

ALTER TABLE digunakan untuk menambahkan, menghapus, atau modifikasi kolom pada tabel. Selain itu juga digunakan untuk menambah dan menghapus berbagai masalah pada tabel.

3.1 Add Column

Kita gunakan syntax di bawah ini untuk menambahkan kolom tabel.

dbExecute(factory_db, "ALTER TABLE Orders ADD Email varchar(255)")

3.2 Drop Column

Kita bisa gunakan syntax di bawah untuk menghapus kolom tabel.

dbSendQuery(factory_db, "ALTER TABLE Orders DROP COLUMN Email")

3.3 Modify Column

dbSendQuery(factory_db, "ALTER TABLE Orders MODIFY COLUMN OrderDate date")

4 Constraints

SQL Contraints digunakan untuk menentukan aturan data pada tabel. Contraints digunakan untuk membatasi jenis data yang bisa masuk dalam tabel. Hal ini guna memastikan keakuratan dan keandalan data dalam tabel. Jika ada pelanggaran antara batasan dan tindakan data maka tindakan tersebut dibatalkan.

Contraints bisa berupa tingkat kolom atau tingkat tabel. Contraints tingkat kolom berlaku untuk kolom, dan contraints tingkat tabel berlaku untuk seluruh tabel. Berikut ini contraints yang biasanya digunakan dalam SQL :

NOL NULL : untuk memastikan kolom tidak boleh memiliki nilai
UNIQUE : untuk memastikan semua nilai di kolom berbeda
PRIMARY KEY : kombinasi antara NOT NULL dan UNIQUE. Secara unik mengidentifikasi baris dalam tabel
FOREIGN KEY : Secara unik mengidentifikasi baris atau record di tabel lain.
CHECK : untuk memastikan semua nilai dalam kolom memenuhi kondisi tertentu
DEFAULT : menetapkan nilai default untuk kolom saat tidak ada yang ditentukan
INDEX : untuk membuat dan mengambil data dari database dengan sangat cepat

4.1 Not Null

SQL memastikan kolom “ID”, “LastName”, dan “FirstName” tidak akan menerima nilai NULL saat tabel Persons_NotNull dibuat :

dbSendQuery(factory_db, "CREATE TABLE Person_NotNull (
            ID int NOT NULL,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255) NOT NULL,
            Age int)")

4.2 Unique

SQL membuat UNIQUE contraint pada kolom “ID” saat tabel “Persons” dibuat:

dbSendQuery(factory_db, "CREATE TABLE Persons_Unique (ID int NOT NULL UNIQUE,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255) NOT NULL,
            Age int)")

Untuk membuat UNIQUE contraint pada kolom “ID” saat tabel sudah dibuat, gunakan SQL berikut ini:

dbSendQuery(factory_db,"ALTER TABLE Persons_Unique
            ADD UNIQUE (ID)")

Untuk mendefinisikan UNIQUE contraint pada beberapa kolom, gunakan SQL berikut ini:

dbSendQuery(factory_db,"ALTER TABLE Persons_Unique
            ADD CONSTRAINT UNIQUE (ID,LastName)")

Untuk menghapus UNIQUE contraint, gunakan SQL berikut ini:

dbSendQuery(factory_db,"ALTER TABLE Persons_Unique
            DROP INDEX ID")

4.3 Primary Key

PRIMARY KEY contraint secara unik mengidentifikasi setiap record dalam tabel. Primary keys harus berisi nilai unik, dan tidak boleh bernilai null. Sebuah tabel hanya bisa memiliki satu primary key; dan dalam tabel, primary key bisa terdiri dari satu atau beberapa kolom.

dbSendQuery(factory_db,"CREATE TABLE Persons_PK (ID int NOT NULL PRIMARY KEY,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Age int)")

Untuk mengizinkan penamaan primary key contraint, dan untuk mendefinisikan primary key contraint pada beberapa kolom, gunakan SQL seperti berikut ini :

dbSendQuery(factory_db,"CREATE TABLE Persons_PK (ID int NOT NULL,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Age int,
            CONSTRAINT Persons_PK PRIMARY KEY (ID,LastName))")

Untuk membuat primary key contraint pada kolom “ID” saat tabel sudah dibuat, gunakan SQL berikut ini :

dbSendQuery(factory_db,"ALTER TABLE Persons_PK
            ADD PRIMARY KEY (ID)")

4.4 Foreign Key

FOREIGN KEY adalah kunci yang digunakan untuk menghubungkan dua tabel bersama-sama. FOREIGN KEY merupakan bidang (atau kumpulan bidang) dalam satu tabel yang mengacu pada PRIMARY KEY di tabel lain. Tabel yang berisi foreign key disebut sebagai tabel anak, dan tabel yang berisi candidate key disebut tabel referensi atau tabek induk.

Perhatikan kedua tabel di bawah ini:

Two Tables

Perhatikan bahwa kolom PersonID pada tabel Orders menunjuk ke kolom PersonID dalam tabel Persons

Kolom “PersonID” pada tabel Persons adalah PRIMARY KEY di tabel Persons.
Kolom “PersonID” pada tabel Orders adalah FOREIGN KEY di tabel Orders.
FOREIGN KEY constraint digunakan untuk mencegah tindakan yang akan merusak link antar tabel.
FOREIGN KEY contraint juga mencegah data yang tidak valid dimasukkan ke dalam kolom foreign key, karena itu harus menjadi salah satu nilai yang terkandung dalam tabel yang ditunjuknya.

Untuk mengizinkan penamaan FOREIGN KEY constraint, dan untuk mendefinisikan FOREIGN KEY constraint pada beberapa kolom, gunakan SQL syntax di bawah ini:

dbSendQuery(factory_db, "CREATE TABLE Orders (OrderID int NOT NULL,
                      OrderNumber int NOT NULL,
                      PersonID int,
                      CONSTRAINT FOREIGN KEY (PersonID))")

Untuk mengizinkan penamaan FOREIGN KEY constraint, dan untuk mendefinisikan FOREIGN KEY constraint pada beberapa kolom, gunakan SQL syntax di bawah ini:

dbSendQuery(factory_db,
"CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    PRIMARY KEY (PersonID), REFERENCES Persons_pk (PersonID))")

Untuk mengizinkan penamaan FOREIGN KEY constraint, dan untuk mendefinisikan FOREIGN KEY constraint pada beberapa kolom, gunakan SQL syntax di bawah ini:

dbSendQuery(factory_db,
            "ALTER TABLE Orders
            ADD CONSTRAINT FK_PersonOrder
            FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)")

4.5 Check

Constraint CHECK digunakan untuk membatasi rentang nilai yang dapat ditempatkan dalam kolom. Jika ingin menentukan CHECK constraint pada satu kolom, itu hanya memungkinkan nilai tertentu untuk kolom ini. Jika ingin mendefinisikan CHECK constraint pada tabel, dapat membatasi nilai di kolom tertentu berdasarkan nilai di kolom lain di baris tersebut. SQL membuat CHECK constraint pada kolom “Age” saat tabel “Persons” dibuat. CHECK constraint memastikan bahwa usia seseorang harus 18 atau lebih tua:

dbSendQuery(factory_db,
            "CREATE TABLE Persons (ID int NOT NULL,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Age int,
            City varchar(255),
            CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes'))")

Untuk mengizinkan penamaan CHECK constraint, dan untuk mendefinisikan CHECK constraint pada beberapa kolom, gunakan SQL syntax di bawah ini:

dbSendQuery(factory_db, "ALTER TABLE Persons
            ADD CHECK (Age>=18)")

Untuk membuat CHECK constraint pada kolom “Age” saat tabel sudah dibuat, gunakan SQL berikut ini:

dbSendQuery(factory_db, "ALTER TABLE Persons
            ADD CONSTRAINT CHK_PersonAge
            CHECK (Age>=18 AND City='Sandnes')")

4.6 Default

DEFAULT constraint digunakan untuk memberikan nilai default untuk kolom. Nilai default akan ditambahkan ke semua records baru jika tidak ada nilai lain yang ditentukan. SQL berikut menetapkan nilai DEFAULT untuk kolom City saat tabel Persons digunakan:

dbSendQuery(factory_db,
"CREATE TABLE Persons_default (ID int NOT NULL,
                              LastName varchar(255) NOT NULL,
                              FirstName varchar(255),
                              Age int,
                              City varchar(255) DEFAULT 'Sandnes')")

Untuk membuat DEFAULT constraint pada kolom city saat tabel sudah dibuat, gunakan SQL berikut ini:

dbSendQuery(factory_db,"ALTER TABLE Persons
                        ALTER City SET DEFAULT 'Sandnes'")

4.7 Index

CREATE INDEX digunakan untuk membuat indeks dalam tabel. Indeks digunakan untuk mengambil data dari database lebih cepat daripada sebaliknya. Pengguna tidak dapat melihat indeks, mereka hanya digunakan untuk mempercepat searches/queries. Membuat index pada tabel. Nilai duplikat diperbolehkan:

dbSendQuery(factory_db, "CREATE INDEX idx_pname
                        ON Persons (LastName, FirstName)")

Catatan: Memperbarui tabel dengan index membutuhkan waktu lebih lama daripada memperbarui tabel tanpa (karena index juga perlu diperbarui). Jadi, hanya membuat index pada kolom yang akan sering dicari.

4.8 Auto Increment

Auto Increment memungkinkan nomor unik dihasilkan secara otomatis saat catatan baru dimasukkan ke dalam tabel. Seringkali ini adalah primary key field yang ingin kita buat secara otomatis setiap kali record baru dimasukkan. SQL mendefinisikan kolom “Personid” menjadi auto increment primary key field di tabel “Persons”:

dbSendQuery(factory_db,
"CREATE TABLE Persons_ai (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid))")

Database System

Fundamental Operations to SQL

Sausan Ramadhani

September 22, 2022

1 Connecting MySQL

1.1 Create DB

1.2 Drop DB

2 Create Table

2.1 Insert Value

2.2 Truncate Table

2.3 Drop Table

2.4 Write Table

3 Alter Table

3.1 Add Column

3.2 Drop Column

3.3 Modify Column

4 Constraints

4.1 Not Null

4.2 Unique

4.3 Primary Key

4.4 Foreign Key

4.5 Check

4.6 Default

4.7 Index

4.8 Auto Increment