Implementasi Sistem Basis Data Relasional pada Aktivitas Pemesanan Tiket Pesawat

Program

Import Library & Menghubungkan Server ke RStudio

Mengimport library dibutuhkan paling awal agar dapat membaca package yang akan digunakan

library(DBI)
## Warning: package 'DBI' was built under R version 4.5.2
library(RSQLite)
## Warning: package 'RSQLite' was built under R version 4.5.2
library(dplyr)
## Warning: package 'dplyr' was built under R version 4.5.1
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.5.2
## 
## Attaching package: 'RMySQL'
## The following object is masked from 'package:RSQLite':
## 
##     isIdCurrent

Menghubungkan ke Server

dbb <- dbConnect(
  RMySQL::MySQL(),
  host = "127.0.0.1",
  user = "root",
  password = "")

Membuat Database

con <- dbConnect(SQLite(), ":memory:")

Membuat Tabel

Membuat tujuh tabel utama dalam sistem pemesanan tiket pesawat. Setiap tabel memiliki primary key dan sebagian mempunyai foreign key.

1. Tabel Maskapai

dbExecute(con, "
  CREATE TABLE maskapai (
    id_maskapai INTEGER PRIMARY KEY,
    kode_maskapai TEXT,
    nama_maskapai TEXT
  );
")
## [1] 0

2. Tabel Bandara

dbExecute(con, "
  CREATE TABLE bandara (
    kode_iata TEXT PRIMARY KEY,
    nama_bandara TEXT,
    kota TEXT
  );
")
## [1] 0

3. Tabel Pesawat

dbExecute(con, "
  CREATE TABLE pesawat (
    id_pesawat INTEGER PRIMARY KEY,
    id_maskapai INTEGER,
    tipe TEXT,
    kapasitas INTEGER,
    FOREIGN KEY (id_maskapai) REFERENCES maskapai(id_maskapai)
  );
")
## [1] 0

4. Tabel Penerbangan

dbExecute(con, "
  CREATE TABLE penerbangan (
    id_flight INTEGER PRIMARY KEY,
    kode_flight TEXT,
    id_maskapai INTEGER,
    id_pesawat INTEGER,
    asal TEXT,
    tujuan TEXT,
    tanggal TEXT,
    waktu_keberangkatan TEXT,
    durasi INTEGER,
    FOREIGN KEY (id_maskapai) REFERENCES maskapai(id_maskapai),
    FOREIGN KEY (id_pesawat) REFERENCES pesawat(id_pesawat),
    FOREIGN KEY (asal) REFERENCES bandara(kode_iata),
    FOREIGN KEY (tujuan) REFERENCES bandara(kode_iata)
  );
")
## [1] 0

5. Tabel Penumpang

dbExecute(con, "
  CREATE TABLE penumpang (
    id_penumpang INTEGER PRIMARY KEY,
    nama TEXT,
    email TEXT,
    telepon TEXT
  );
")
## [1] 0

6. Tabel Booking/Tiket

dbExecute(con, "
  CREATE TABLE booking (
    id_booking INTEGER PRIMARY KEY,
    kode_booking TEXT,
    id_penumpang INTEGER,
    id_flight INTEGER,
    kelas TEXT,
    status TEXT,
    tanggal_pesan TEXT,
    harga REAL,
    FOREIGN KEY (id_penumpang) REFERENCES penumpang(id_penumpang),
    FOREIGN KEY (id_flight) REFERENCES penerbangan(id_flight)
  );
")
## [1] 0

7. Tabel Transaksi

dbExecute(con, "
  CREATE TABLE transaksi (
    id_transaksi INTEGER PRIMARY KEY,
    id_booking INTEGER,
    metode_bayar TEXT,
    jumlah REAL,
    tanggal_bayar TEXT,
    FOREIGN KEY (id_booking) REFERENCES booking(id_booking)
  );
")
## [1] 0

Mengisi Data

Perintah INSERT INTO digunakan untuk mengisi tabel dengan data awal sebagai simulasi.

1. Tabel Maskapai

dbExecute(con, "
  INSERT INTO maskapai VALUES
    (1, 'JT', 'Lion Air'),
    (2, 'QG', 'Citilink'),
    (3, 'GA', 'Garuda Indonesia'),
    (4, 'AK', 'AirAsia');
")
## [1] 4

2. Tabel Bandara

dbExecute(con, "
  INSERT INTO bandara VALUES
    ('KNO', 'Bandara Kualanamu', 'Medan'),
    ('JKT', 'Bandara Soekarno-Hatta', 'Jakarta'),
    ('SUB', 'Bandara Juanda', 'Surabaya'),
    ('DPS', 'Bandara Ngurah Rai', 'Denpasar');
")
## [1] 4

3. Tabel Pesawat

dbExecute(con, "
  INSERT INTO pesawat VALUES
    (1, 1, 'Boeing 737', 180),
    (2, 2, 'ATR 72', 70),
    (3, 3, 'Airbus A320', 160);
")
## [1] 3

4. Tabel Penerbangan

dbExecute(con, "
  INSERT INTO penerbangan VALUES
    (1, 'JT-123', 1, 1, 'JKT', 'DPS', '2025-12-20', '07:00', 120),
    (2, 'QG-456', 2, 2, 'SUB', 'JKT', '2025-12-21', '10:30', 90),
    (3, 'GA-789', 3, 3, 'KNO', 'JKT', '2025-12-22', '14:00', 150);
")
## [1] 3

5. Tabel Penumpang

dbExecute(con, "
  INSERT INTO penumpang VALUES
    (1, 'Andi Wijaya', 'andi@mail.com', '081234567890'),
    (2, 'Siti Rahma', 'siti@mail.com', '081298765432'),
    (3, 'Budi Santoso', 'budi@mail.com', '081355566677');
")
## [1] 3

6. Tabel Booking/Tiket

dbExecute(con, "
  INSERT INTO booking VALUES
    (1, 'BK001', 1, 1, 'Economy', 'Confirmed', '2025-11-01', 1200000),
    (2, 'BK002', 2, 2, 'Economy', 'Confirmed', '2025-11-05', 800000),
    (3, 'BK003', 3, 3, 'Business', 'Cancelled', '2025-11-10', 2500000);
")
## [1] 3

7. Tabel Transaksi

dbExecute(con, "
  INSERT INTO transaksi VALUES
    (1, 1, 'Kartu Kredit', 1200000, '2025-11-01'),
    (2, 2, 'Transfer Bank', 800000, '2025-11-05'),
    (3, 3, 'Kartu Kredit', 2500000, '2025-11-10');
")
## [1] 3

Menampilkan Tabel

dbListTables(con)
## [1] "bandara"     "booking"     "maskapai"    "penerbangan" "penumpang"  
## [6] "pesawat"     "transaksi"
dbGetQuery(con, "SELECT * FROM booking;")
##   id_booking kode_booking id_penumpang id_flight    kelas    status
## 1          1        BK001            1         1  Economy Confirmed
## 2          2        BK002            2         2  Economy Confirmed
## 3          3        BK003            3         3 Business Cancelled
##   tanggal_pesan   harga
## 1    2025-11-01 1200000
## 2    2025-11-05  800000
## 3    2025-11-10 2500000

Menampilkan Data Transaksi Tertentu

dbGetQuery(con, "
  SELECT kode_booking, harga
  FROM booking
  WHERE harga > 1000000
  ORDER BY harga DESC;
")
##   kode_booking   harga
## 1        BK003 2500000
## 2        BK001 1200000

Memperbarui Data (UPDATE)

dbExecute(con, "
  UPDATE booking
  SET status = 'Checked-in'
  WHERE kode_booking = 'BK001';
")
## [1] 1
dbGetQuery(con, "SELECT kode_booking, status FROM booking WHERE kode_booking = 'BK001';")
##   kode_booking     status
## 1        BK001 Checked-in

(DELETE)

dbExecute(con, "
  DELETE FROM booking
  WHERE status = 'Cancelled';
")
## [1] 1

Menampilkan Data Pemesanan Lengkap (JOIN)

query_full <- "
  SELECT b.kode_booking, p.nama AS penumpang, m.nama_maskapai AS maskapai, 
         f.kode_flight, f.tanggal, f.waktu_keberangkatan, b.kelas, b.status, b.harga
  FROM booking b
  JOIN penumpang p ON b.id_penumpang = p.id_penumpang
  JOIN penerbangan f ON b.id_flight = f.id_flight
  JOIN maskapai m ON f.id_maskapai = m.id_maskapai;
"
dbGetQuery(con, query_full)
##   kode_booking   penumpang maskapai kode_flight    tanggal waktu_keberangkatan
## 1        BK001 Andi Wijaya Lion Air      JT-123 2025-12-20               07:00
## 2        BK002  Siti Rahma Citilink      QG-456 2025-12-21               10:30
##     kelas     status   harga
## 1 Economy Checked-in 1200000
## 2 Economy  Confirmed  800000

Menggunakan Fungsi Agregat

dbGetQuery(con, "
  SELECT SUM(t.jumlah) AS total_pendapatan, AVG(b.harga) AS rata_rata_harga
  FROM transaksi t
  JOIN booking b ON t.id_booking = b.id_booking
  WHERE b.status = 'Confirmed';
")
##   total_pendapatan rata_rata_harga
## 1            8e+05           8e+05

Laporan Per Maskapai

dbGetQuery(con, "
  SELECT m.nama_maskapai, SUM(t.jumlah) AS pendapatan_maskapai
  FROM transaksi t
  JOIN booking b ON t.id_booking = b.id_booking
  JOIN penerbangan f ON b.id_flight = f.id_flight
  JOIN maskapai m ON f.id_maskapai = m.id_maskapai
  GROUP BY m.nama_maskapai
  ORDER BY pendapatan_maskapai DESC;
")
##   nama_maskapai pendapatan_maskapai
## 1      Lion Air             1200000
## 2      Citilink              800000