DEV Community

Januard Lumbangaol
Januard Lumbangaol

Posted on

Database : DBMS + Postgres — catatan dasar

#sql #beginners #postgres #database

Blog ini berisi mengenai catatan dasar yang saya gunakan untuk memahami konsep dasar database. Semoga blog ini juga dapat berguna bagi teman-teman yang baru ingin terjun ke dunia database.

Sebagai disclaimer catatan ini terbatas pada konsep dasar database, relational database, RDBMS, SQL, dan PostgreSQL.

Sekilas Mengenai Database

Database merupakan kumpulan data yang tersimpan seacara elektronik. Kumpulan data tersebut dapat diakses dan dikelola melalui perangkat lunak yang disebut sebagai Database Management System(DBMS).

Terdapat beberapa jenis database dimana yang membedakannya adalah cara menyimpan, struktur, dan cara bagaimana data tersebut diakases. Namun pada kesempatan kali ini saya fokus untuk mempelajari Relational Databae yang memiliki struktur data tabel dimana data direpresentasikan dengan baris dan kolom. Untuk jenis database dapat dilihat pada blog berikut : Jenis-Jenis Database? Intip Lebih Jauh Macam Jenis Database Yuk!.

Key dalam Database

Key digunakan untuk mengidentifikasi setiap baris data pada tabel dan dapat berfungsi untuk membangun relasi antar table.

  1. Primary Key → kolom yang dijadikan unique identifier untuk setiap data di table, memastikan tidak ada data dengan value key yang sama.

    Pada table blogs diatas yang menjadi primary key adalah blog_id

  2. Foreign Key → kolom yang merujuk pada kolom primary key dari table lain yang digunakan untuk membangun hubungan antar table. Tujuan nya untuk mengambil data dari table lain.

    author_id pada table blogs merupakan foreign key yang merujuk pada user_id yang merupaan primary key dari table users

  3. Composite Key → gabungan 2 atau 3 kolom untuk dijadikan primary key jika 1 kolom tidak cukup untuk dijadikan sebagai primary key.

    Pada tabel diatas customer id + product id berfungsi sebagai unique identifier. SC : https://www.geeksforgeeks.org/dbms/composite-key-in-database/

  4. Super Key → kumpulkan satu kolom atau kombinasi kolom yang dapat digunakan berpotensi menjadi sebagai unique identifiers.

  5. Candidate Key → daftar kolom yang punya potensi untuk dijadikan sebagai primary key.

  6. Alternate Key → key tambahan yang dapat berfungsi sebagai primary key. atau super key yang tidak terpilih sebagai primary key.

Langkah Menentukan Primary Key

  1. Dari table users tentukan super key nya adalah :
    • {user_id}
    • {email}
    • {user_id, username}
    • {user_id, username, email}
  2. Candidate key
    • {user_id}
    • {email}
  3. Primary key → pilih satu yang paling praktis dipakai(stabil, minimal, dan mudah diakses)
    • {user_id}
  4. Alternate Key
    • {email}

Common Data Type

Terdapat banyak data type yang tersedia, namun variasi nya tergantung yang disediakan oleh jenis databasenya. Berikut beberapa data type yang common yang hampir tersedia disemua jenis database.

Angka / Numeric

  1. INT / BIGINT → untuk ID, counter, jumlah, dsb.
    • INT (paling common digunakan, ±2 milyar range)
    • BIGINT (kalau data bisa sangat besar, misalnya log jutaan/milyaran row)
  2. DECIMAL / NUMERIC → untuk angka presisi tetap, terutama keuangan.
  3. FLOAT / DOUBLE → biasanya untuk angka pecahan/perhitungan ilmiah.

Teks / String

  1. VARCHAR(n) → paling sering dipakai untuk teks pendek (nama, email, alamat, dsb.).
  2. TEXT → untuk teks panjang (deskripsi, artikel, isi chat).
  3. CHAR(n) → jarang dipakai, hanya kalau panjang selalu tetap (contoh: kode negara CHAR(2) → “ID”, “US”)

Tanggal & Waktu

  1. DATE → hanya tanggal (YYYY-MM-DD).
  2. TIME → hanya jam (HH:MM:SS).
  3. DATETIME / TIMESTAMP → kombinasi tanggal + waktu (paling sering dipakai untuk created_at, updated_at).

Boolean

  • BOOLEAN / TINYINT(1) / BIT → true/false.

Biner / File

  • BLOB / BYTEA → untuk data biner (gambar, file) ℹ️ Tapi di dunia nyata, jarang simpan file di DB, lebih sering simpan di storage (S3, server) dan DB cuma simpan URL/path.

JSON

  • menyimpan data dalam bentuk JSON yang bisa berupa data kompleks seperti array atau object.

SQL (Structured Query Language)

Merujuk pada SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL adalah bahasa standar yang digunakan dalam DBMS untuk melakukan pengelolaan baik mengambil, menyimpan, dan mengedit data pada database. Hampir semua RDBMS (MySQL, PostgreSQL, Oracle, SQL Server) dapat menggunakan SQL untuk mengelola data namun bisa terjadi pebedaan penulisan sesuai yang disediakan oleh databasenya. Pada catatan ini saya fokus untuk explore untuk database PostgreSQL.

DDL — Data Definition Language

SQL command yang digunakan untuk membuat, mengedit, dan menghapus struktur database seperti table maupun skema. Common command:

  1. CREATE : Create database dan objectnya(table, function, data type, dan lain-lain). ✍🏽: CREATE TABLE table_name (column1 data_type, column2 data_type, …);
  2. DROP : Delete obkect dari database ✍🏽: DROP TABLE table_name;
  3. ALTER : Mengubah struktur dari database baik mengubah table maupun atribut pada table. ✍🏽: ALTER TABLE table_name ADD COLUMN column_name data_type; 
create table blogs (
    blog_id SERIAL primary key,
    title varchar(250) NOT NULL,
    blog text NOT NULL,
    images text,
    author_id integer,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

alter table blogs 
 add blog text NOT NULL,

drop blogs
Enter fullscreen mode Exit fullscreen mode

DQL — Data Query Language

Command yang digunakan untuk mengambil data dimana outputnya dapat dilihat pada atau digunakan pada aplikasi. common command

  1. SELECT : mengambil data dari database
  2. FROM : merujuk table sumber
  3. WHERE : melakukan filter data sebelum data digrup
  4. ORDER BY : melakukan sorting 
select * from blogs 
where blog_id = 2 
order by created_at DESC
Enter fullscreen mode Exit fullscreen mode

DML — Data Manipulation Language

command yang digunakan untuk memanipulasi data pada table database baik menambah, mengedit, maunpun menghapus data.

  1. INSERT : menambahkan data ke table ✍🏽: INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
  2. UPDATE : mengubah data yang sudah ada pada table tanpa menghapusnya. ✍🏽: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  3. DELETE : menghapus data yang ada pada table ✍🏽: DELETE FROM table_name WHERE condition; 
insert into blogs (title, blog, images, author_id) 
  values('Perjalanan', 'Isi blog perjalanan' ...., 'base64:asdaf...', 1) 

update blogs set title = 'Perjalanan Revisi' 
  where blog_id = 1

delete blogs where blog_id = 1
Enter fullscreen mode Exit fullscreen mode

Untuk lebih lengkap nya mengenai SQL dapat mengacu pada blog berikut:
SQL Commands | DDL, DQL, DML, DCL and TCL Commands.

SQL Constraints dan Data Integrity

Untuk memastikan data yang konsisten dan dapat diandalkan tentunya pada pembuatan skema database dan object nya diperlukan aturan dan batasan. SQL constraints merupakan rule dan batasan yang diterapkan pada kolom tabel untuk menjaga integritas data (consisten, valid, and reliable). Beberapa common constraint yang sering digunakan :

  1. NOT NULL : memastikan data pada kolom tersebut tidak boleh ada value null.
  2. UNIQUE : memastikan setiap baris data pada kolom tersebut tidak memiliki value yang sama, namun masih bisa terdapat NULL.
  3. PRIMARY KEY : nilai unique dan tidak bisa NULL yang dijadikan unique identifier untuk setiap baris data.
  4. FOREIGN KEY : kolom yang merujuk pada kolom primary key dari table lain yang digunakan untuk membangun hubungan antar table.
  5. DEFAULT : set value default ke kolom tersebut jika tidak ada set value spesifik ketika insert data. 
---example
create table order (
  order_id SERIAL primary key, --primary key
  status_order varchar(10) NOT NULL, --not null
  total_amount numeric NOT NULL,
)

create table payment_order (
  payment_id SERIAL primary key,
  status_payment varchar(10) DEFAULT 'NEW' ---default constraint
)

alter table payment_order 
 ADD CONSTRAINT fk_order_payment foreign key 
  (order_id) REFERENCES order (order_id); ---foreign key constraint
Enter fullscreen mode Exit fullscreen mode

Database Relation

Relasi database terjadi pada DBMS jika terdapat atribut yang merujuk pada atribut primary key dari table lain.

  1. One to One Relationship Setiap single record dari satu table terhubung ke single record table lain.
  2. One to Many Relationship Setiap single record dari suatu table dapat terhubung ke muliple record dari table lain.
  3. Many to Many Relationship Record yang ada pada table bisa terhubung dengan multiple record dari table lain dan sebaliknya. Contoh Relations

Contoh dengan Query

One to One 

create table order (
  order_id SERIAL primary key,
  status_order varchar(10) NOT NULL,
  total_amount numeric NOT NULL,
)

create table payment_order (
  payment_id SERIAL primary key,
  order_id INT REFERENCES order(order_id)
  status_payment varchar(10)
)
Enter fullscreen mode Exit fullscreen mode

One to Many 

create table user (
  user_id SERIAL primary key,
  email varchar(250) NOT NULL,
  password varchar(250) NOT NULL
)

create table blogs (
  blog_id SERIAL primary key,
  author_id INT REFERENCES user(user_id) NOT NULL,
  title varchar(250) NOT NULL,
  blog text NOT NULL
)
Enter fullscreen mode Exit fullscreen mode

Many to Many 

create table user (
  user_id SERIAL primary key,
  email varchar(250) NOT NULL,
  password varchar(250) NOT NULL
)

create table produk (
  produk_id SERIAL primary key,
  produk_id varchar(250) NOT NULL,
  description text NOT NULL,
  cost numeric NOT NULL
)

--- Di database relasional, relasi many-to-many membutuhkan 
--- tabel ketiga (junction / associative table) agar bisa diimplementasikan.
create table order_produk (
  order_produk_id SERIAL primary key,
  user_id INT REFERENCES user(user_id) NOT NULL,
  produk_id INT REFERENCES produk(produk_id) NOT NULL,
  status varchar(10),
)
Enter fullscreen mode Exit fullscreen mode

SQL Joins

SQL joins adalah command utnuk menggabungkan data dari multiple database yang punya relations.

Left Join

Mengembalikan semua data dari table kiri, beserta baris yang cocok dari table kanan. Jika ada yang tidak cocok dengan tabel kanan maka value nya menjadi NULL.

select b.title, b.blog, u.email
from blog b
left join user u 
on b.author_id = u.user = id
Enter fullscreen mode Exit fullscreen mode 
| title     | blog     | email           | 
| --------- | -------- | ----------------| 
| title 1   | blog.... | email1@mail.com | 
| title 2   | blog.... | email2@mail.com | 
| title 3   | blog.... | NULL            |
Enter fullscreen mode Exit fullscreen mode

Right Join

Mengembalikan semua data dari table kanan, beserta baris yang cocok dari table kiri. Jika ada yang tidak cocok dengan tabel kiri maka value nya menjadi NULL

select b.title, u.email
from blog b
right join user u 
on b.author_id = u.user = id
Enter fullscreen mode Exit fullscreen mode 
| title     | email           | 
| --------- | ----------------| 
| title 1   | email1@mail.com | 
| title 2   | email2@mail.com | 
| NULL      | email4@mail.com |
Enter fullscreen mode Exit fullscreen mode

Inner Join

Select semua baris data dari kedua kolom selama memenuhi kondisi. Memastikan tidak mengembalikan baris data yang tidak punya relasi.

select b.title, u.email
from blog b
inner join user u 
on b.author_id = u.user = id
Enter fullscreen mode Exit fullscreen mode 
| title     | email           | 
| --------- | ----------------| 
| title 1   | email1@mail.com | 
| title 2   | email2@mail.com |
Enter fullscreen mode Exit fullscreen mode

Selain metode join diatas masih terdapat beberapa beberapa metode join lagi yang dapat dilihat pada SQL Joins (Inner, Left, Right and Full Join).

SQL TRANSACTIONS

Transaksi SQL adalah serangkaian satu atau lebih operasi SQL (misalnya, INSERT, UPDATE, DELETE) yang dijalankan sebagai satu unit kerja. Transaksi memastikan semua operasi berhasil atau tidak ada yang diterapkan, sehingga integritas data tetap terjaga. Beberapa command dasar :

  1. BEGIN Command untuk menandai untuk menjalankan serangkaian query.
  2. COMMIT Menyimpan semua perubahan pada database jika seluruh rangkaian query berjaan dengan sukses.
  3. ROLLBACK Membatalkan semua perubahan jika terdapat error pada rangkaian query yang berjalan. Database akan direvert ke kondisi sebelum BEGIN TRANSACTION. 
---Contoh transaction sql untuk checkout produk di e-commerce
---SYNTAX
BEGIN TRANSACTION;

-- Mengurangi jumlah dari tabel produk 
UPDATE product
SET product_count = product_count - 1
WHERE product_id = 1;

-- Menambah data ke table order
INSERT INTO order_product (produk_id, status_payment)
VALUES (1, 'NEW')

-- Commit transaksi jika semua query berjalan dengan sukses
COMMIT;

---Rollback transaksi jika ada query yang eror
ROLLBACK;
Enter fullscreen mode Exit fullscreen mode

Untuk menerapkan ini pada aplikasi misalnya node js umumnya menggunakan konsep try catch.
Untuk lebi lengkap mengenai SQL transaction dapat melihat pada :
SQL TRANSACTIONS.

Top comments (0)