Database

Sebelum adanya basis data, program menyimpan data dalam file datar — biasanya satu file per program. Ini cukup untuk data kecil tetapi tidak berlaku pada skala besar.

Keterbatasan

• redundansi data — data yang sama (alamat pelanggan) disimpan di beberapa file, satu per program, sehingga penyimpanan terbuang sia-sia dan setiap salinan harus diperbarui.
• ketidakkonsistenan data — ketika satu salinan diperbarui dan yang lain tidak, file-file tersebut bertentangan dan tidak ada yang tahu mana yang benar.
• ketergantungan data — setiap program ditulis sesuai dengan tata letak file-nya secara eksak; mengubah panjang bidang atau menambahkan bidang berarti semua program yang membaca file tersebut harus ditulis ulang.
• tidak ada akses bersama — sebuah file terkunci saat digunakan oleh satu program, sehingga pengguna tidak dapat bekerja pada data secara bersamaan.
• integritas yang lemah — tidak ada aturan pusat yang mencegah nilai tidak valid atau tautan ke pelanggan yang tidak ada; keamanan yang lemah — akses bersifat per file, bukan per bidang; dan kueri lintas file memerlukan program baru setiap kali.

A basis data relasional memperbaiki hal ini dengan menyimpan data dalam tabel yang dikelola oleh satu perangkat lunak (DBMS) yang digunakan semua program.

Mengapa basis data relasional lebih baik — jawaban tiga poin. Setiap item data disimpan sekali, dalam satu tabel, dan tabel-tabel dihubungkan melalui kunci, sehingga tidak ada redundansi dan tidak ada ketidakkonsistenan; data bersifat independen dari program, yang meminta DBMS untuk mendapatkan yang mereka butuhkan dan tidak terpengaruh ketika struktur berubah; dan DBMS menerapkan aturan integritas, mengontrol akses per pengguna dan per bidang, memungkinkan banyak pengguna sekaligus, dan menjawab kueri apa pun tanpa perlu menulis program baru.

Contoh soal. Sebuah bengkel menyimpan pelanggannya, perangkat, dan pekerjaan perbaikan menggunakan pendekatan berbasis file, satu file per program. Sebutkan tiga masalah yang ditimbulkannya, dan jelaskan bagaimana basis data relasional akan mengatasinya.

Nama dan nomor telepon pelanggan disimpan di file perbaikan dan faktur (redundansi); ketika pelanggan mengganti nomor, satu file diperbarui dan yang lain tidak (ketidakkonsistenan); dan ketika bengkel ingin membuat laporan baru — perbaikan per teknisi — program baru harus ditulis untuk membaca file (tidak ada kueri ad-hoc). Dalam basis data relasional, pelanggan disimpan sekali dalam tabel CUSTOMER dan dirujuk melalui CustomerID dari tabel REPAIR, sehingga perubahan dilakukan sekali dan terlihat di mana saja; laporan adalah satu kueri SQL.

• tabel (relasi) — grid baris dan kolom; satu tabel per jenis entitas (mis. CUSTOMER).
• rekord (baris, juga disebut tuple) — satu baris; satu实例 dari entitas.
• bidang (kolom, juga disebut atribut) — satu kolom; satu bagian informasi tentang setiap rekord.
• kunci utama (primary key) — bidang (atau bidang-bidang) yang secara unik mengidentifikasi setiap rekord; tidak boleh null atau duplikat.
• kunci asing (foreign key) — bidang yang nilainya cocok dengan kunci utama tabel lain, menghubungkan keduanya.
• kunci komposit — kunci utama yang terdiri dari dua atau lebih bidang bersama-sama.
• kandidat kunci — bidang mana pun yang bisa menjadi kunci utama.
• kunci sekunder (secondary key) — bidang non-kunci utama yang diindeks untuk pencarian cepat.
• indeksasi — membangun indeks pada suatu bidang agar pencarian dan penggabungan berjalan lebih cepat.
• integritas referensial — setiap nilai kunci asing harus cocok dengan kunci utama yang ada (tidak ada rekord yatim).

Sebuah tabel ditulis dalam bentuk singkat dengan kunci utama digarisbawahi dan kunci asing dicatat:

CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate)   -- CustomerID is FK → CUSTOMER

Contoh soal. Jelaskan arti entitas, kunci utama dan integritas referensial dalam basis data relasional, dan lengkapi tabel istilah ↔ deskripsi untuk tuple dan atribut.

Sebuah entitas adalah sesuatu tentang mana data disimpan — seseorang, objek, atau peristiwa — yang menjadi satu tabel. Kunci utama adalah atribut (atau kombinasi atribut) yang secara unik mengidentifikasi setiap rekord dalam tabel. Integritas referensial berarti setiap nilai kunci asing harus cocok dengan nilai kunci utama dalam tabel yang dirujuknya, sehingga rekord tidak dapat merujuk ke rekord yang tidak ada. Tuple adalah satu baris tabel (satu rekord); atribut adalah satu kolom (satu bidang). Hafalkan pasangan: tabel/relasi, rekord/tuple, bidang/atribut.

Sebuah diagram entitas-relasi menunjukkan struktur: setiap entitas adalah persegi panjang, setiap relasi adalah garis, dengan karinalitas ditandai di setiap ujungnya:

• satu-ke-satu (1:1).
• satu-ke-banyak (1:M) — setiap Pelanggan memiliki banyak Pesanan; setiap Pesanan memiliki satu Pelanggan.
• banyak-ke-banyak (M:N) — Siswa mengambil banyak Mata Kuliah, dan Mata Kuliah memiliki banyak Siswa.

Relasi banyak-ke-banyak tidak dapat disimpan secara langsung. Pecah menjadi dua relasi satu-ke-banyak melalui tabel penghubung yang menyimpan dua kunci asing:

ENROLMENT(StudentID, CourseID, EnrolmentDate)

Menggambar diagram E-R untuk serangkaian tabel tertentu. Setiap tabel menjadi sebuah entitas. Relasi terdapat di mana satu tabel memegang kunci asing ke tabel lain; relasi berjalan dari tabel yang memegang kunci asing (ujung banyak) ke tabel yang merupakan primary key-nya (ujung satu). Tabel dengan dua kunci asing dan tanpa identitas lain biasanya adalah tabel penghubung yang memecahkan relasi banyak-ke-banyak. Beri label pada setiap garis dengan jenis relasinya.

Contoh terpecahkan. Sebuah bengkel memiliki tabel CUSTOMER(CustomerID, Name, Phone), DEVICE(DeviceID, CustomerID, Type, Model), TECHNICIAN(TechnicianID, Name) dan REPAIR(RepairID, DeviceID, TechnicianID, RepairDate, Cost). Identifikasi relasi dan jenisnya.

DEVICE memegang CustomerID, jadi CUSTOMER–DEVICE adalah satu-ke-banyak (satu pelanggan, banyak perangkat). REPAIR memegang DeviceID, jadi DEVICE–REPAIR adalah satu-ke-banyak; ia juga memegang TechnicianID, jadi TECHNICIAN–REPAIR adalah satu-ke-banyak. Tidak ada garis langsung CUSTOMER–REPAIR: hubungannya berjalan melalui DEVICE. Tiga garis, tiga kaki burung, semua berada di ujung REPAIR atau DEVICE.

Normalisasi mengorganisir tabel untuk mengurangi redundansi dan inkonsistensi, melewati bentuk normal secara berurutan.

• Bentuk normal pertama (1NF) — setiap bidang memegang satu nilai (atomik), tanpa kelompok berulang, dan memiliki kunci primer.
• Bentuk normal kedua (2NF) — dalam 1NF, dan setiap non-kunci bergantung pada seluruh kunci primer (hanya relevan untuk kunci komposit).
• Bentuk normal ketiga (3NF) — dalam 2NF, dan setiap non-kunci bergantung hanya pada kunci primer, bukan pada non-kunci lain (tidak ada ketergantungan transitif).

Desain 3NF menyimpan setiap fakta sekali, sehingga anomali insert/update/delete hilang. Penggantinya adalah lebih banyak tabel dan lebih banyak join. Targetkanlah 3NF.

Untuk menghasilkan desain 3NF: temukan entitas dan atributnya; pilih kunci primer untuk masing-masing; pecah bidang berulang/non-atomik (1NF); pecah bidang yang bergantung pada sebagian kunci komposit (2NF); pecah bidang yang bergantung transitif pada kunci (3NF); tambahkan kunci asing untuk relasinya.

Contoh terpecahkan. Tabel ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity) memiliki kunci primer komposit (OrderID, ProductID). Normalisasikan tabel ini menjadi 3NF. Tes setiap bidang non-kunci terhadap kunci. Quantity bergantung pada kedua OrderID dan ProductID, yang mana itu benar. Namun CustomerID bergantung pada OrderID saja - hanya sebagian dari kunci komposit. Itu adalah ketergantungan parsial, sehingga tabel tersebut bukan dalam 2NF. Pecahlah menjadi ORDER_LINE(OrderID, ProductID, Quantity) dan ORDER(OrderID, CustomerID, CustomerName). Sekarang tes 3NF: dalam tabel ORDER baru tersebut, CustomerName bergantung pada CustomerID, yang bukan kunci - sebuah ketergantungan transitif. Pecah lagi: ORDER(OrderID, CustomerID) dan CUSTOMER(CustomerID, CustomerName). Sebutkan ketergantungan yang melanggar bentuk normalisasi tersebut (parsial melanggar 2NF, transitif melanggar 3NF); "memiliki data berulang" menggambarkan gejala dan tidak memberikan poin apa pun.

Tiga pertanyaan yang harus diajukan kepada setiap tabel. Apakah setiap sel berisi satu nilai, tanpa kelompok berulang? Jika tidak, maka tidak dalam 1NF. Jika kuncinya komposit, apakah setiap non-kunci bergantung pada seluruh kunci? Jika beberapa bidang bergantung pada sebagian darinya, ada ketergantungan parsial dan tabel tersebut tidak dalam 2NF. Apakah setiap non-kunci bergantung pada kunci semata? Jika suatu bidang bergantung pada non-kunci lain, ada ketergantungan transitif dan tabel tersebut tidak dalam 3NF. Jawaban "jelaskan mengapa tabel tersebut tidak dalam 3NF" harus menyebutkan ketergantungan dan bidang yang terlibat.

Contoh terpecahkan. Sebuah toko penyewaan mobil mencatat setiap sewa sebagai RENTAL(RentalID, RentalDate, CustomerID, CustomerName, CustomerPhone, CarReg, CarModel, DailyRate, Days), di mana satu sewa dapat mencakup beberapa mobil. Jelaskan mengapa tabel tersebut tidak dinormalisasi dan buatkan desain 3NF.

Tidak dalam 1NF: bidang mobil CarReg, CarModel, DailyRate, Days membentuk grup berulang — satu penyewaan memiliki beberapa mobil. Pindahkan ke RENTAL_CAR(RentalID, CarReg, CarModel, DailyRate, Days) dengan kunci komposit (RentalID, CarReg). Tidak dalam 2NF: di RENTAL_CAR, CarModel dan DailyRate bergantung pada CarReg saja — ketergantungan parsial. Pindahkan ke CAR(CarReg, CarModel, DailyRate), meninggalkan RENTAL_CAR(RentalID, CarReg, Days). Tidak dalam 3NF: di RENTAL, CustomerName dan CustomerPhone bergantung pada CustomerID, yaitu bidang non-kunci — ketergantungan transitif. Pindahkan ke CUSTOMER(CustomerID, CustomerName, CustomerPhone), meninggalkan RENTAL(RentalID, RentalDate, CustomerID). Desain 3NF terdiri dari empat tabel — CUSTOMER, RENTAL, RENTAL_CAR, CAR — dengan CustomerID, RentalID dan CarReg sebagai kunci asing; garisbawahi setiap kunci utama.

Sebuah DBMS mengelola database secara terpusat. Fitur yang mengatasi keterbatasan berbasis file:

• ** kamus data **— deskripsi setiap tabel, bidang, tipe, dan kunci; program querying menggunakan ini alih-alih mengkodekan struktur secara statis.
• kontrol redundansi/konsistensi — setiap fakta disimpan hanya sekali.
• kontrol akses konkuren — kunci dan transaksi memungkinkan banyak pengguna bekerja bersamaan.
• cadangan dan pemulihan; keamanan dan izin per-pengguna.
• aturan integritas — kunci, batasan unik dan rentang, ditegakkan secara terpusat.
• transaksi — sekumpulan operasi yang semuanya berhasil atau semua gagal.
• tampilan — tabel virtual yang menampilkan bagian "milik" data kepada setiap pengguna.
• pengelolaan data dan pemodelan data — mengontrol bagaimana data disimpan dan mendefinisikan strukturnya sebagai skema logis (desain logis, independen dari penyimpanan fisik).
• integritas data dan keamanan data — menegakkan kebenaran dan mengontrol akses secara terpusat.
• prosesor query menjalankan query; antarmuka pengembang menyediakan alat dan API untuk membangun aplikasi.

Alat-alatnya meliputi editor kamus data, pembuat query, pembuat formulir, generator laporan, manajemen pengguna, dan editor SQL.

Apa yang disimpan kamus data (soal "sebutkan tiga poin"): nama-nama tabel; nama-nama bidang di setiap tabel; tipe data dan panjang setiap bidang; kunci utama dan kunci asing serta hubungan antar tabel; aturan validasi; indeks; dan siapa yang dapat mengakses setiap tabel. Ini adalah metadata — data tentang data — dan DBMS menggunakannya untuk memeriksa setiap query dan setiap perubahan.

Bagaimana DBMS menjaga keamanan data (soal "jelaskan dua metode"): autentikasi — username dan password, atau biometrik, sebelum akses apa pun; hak akses — setiap pengguna atau grup hanya diizinkan membaca, menulis, atau menghapus tabel atau bidang tertentu, sering kali melalui sebuah tampilan; enkripsi data yang disimpan dan data yang dikirim, sehingga file salinan tidak dapat dibaca; cadangan yang diambil secara berkala, sehingga data dapat dipulihkan setelah kehilangan; dan log transaksi yang mencatat siapa mengubah apa.

Dua perangkat lunak tersebut. Antarmuka pengembang adalah apa yang digunakan programmer untuk membuat database dan aplikasi di atasnya: membuat tabel dan menetapkan kunci serta validasi, menulis query dan SQL, serta merancang formulir dan laporan, tanpa perlu tahu bagaimana data disimpan secara fisik. Prosesor query menerima query (SQL dari program, atau query yang dibuat di antarmuka), memverifikasinya terhadap kamus data, menentukan cara paling efisien untuk menjalankannya, mengambil data, dan mengembalikan hasilnya.

Skema logis. DBMS mempertahankan desain logis (tabel dan bidang apa yang ada serta bagaimana mereka saling berhubungan) terpisah dari penyimpanan fisik (file, indeks, blok disk). Program bekerja dengan skema logis, sehingga penyimpanan fisik dapat diorganisasi ulang tanpa mengubah satu pun program — inilah independensi data yang tidak dimiliki pendekatan berbasis file.

SQL (Structured Query Language) memiliki dua sisi:

• Data Definition Language (DDL) — membuat atau mengubah struktur (tabel, kunci, batasan).
• Data Manipulation Language (DML) — bekerja dengan data (insert, update, delete, query).

Dasar-dasar DDL

CREATE TABLE CUSTOMER (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

Menambahkan kunci asing:

CREATE TABLE ORDER (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

Memodifikasi dan menghapus:

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
DROP TABLE CUSTOMER;

Tipe umum: INTEGER, REAL, VARCHAR(n), CHAR(n) (juga CHARACTER(n)), DATE, TIME, BOOLEAN, DECIMAL(p, s).

Dasar-dasar DML

Query dengan SELECT:

SELECT Name, Phone
FROM CUSTOMER
WHERE City = 'London'
ORDER BY Name ASC;

SELECT mencantumkan bidang, FROM menyebut nama tabel, WHERE menyaring baris, ORDER BY mengurutkan.

Join menggabungkan dua tabel menggunakan hubungan kunci asing:

SELECT C.Name, O.OrderDate
FROM CUSTOMER C INNER JOIN ORDER O
  ON C.CustomerID = O.CustomerID
WHERE O.OrderDate >= '2024-01-01';

Fungsi agregat (COUNT, SUM, AVG, MIN, MAX) sering digunakan bersama GROUP BY:

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDER
GROUP BY CustomerID;

Insert, update, delete:

INSERT INTO CUSTOMER (CustomerID, Name, Phone)
VALUES (101, 'Ada Lovelace', '020-1234-5678');

UPDATE CUSTOMER SET Phone = '020-9999-0000' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

Selalu cantumkan klausa WHERE pada UPDATE dan DELETE, jika tidak perubahan akan mengenai semua baris.

Tips SQL untuk ujian

• gunakan nama tabel dan bidang yang tepat sesuai soal.
• beri tanda kutip pada string dengan tanda petik tunggal ('Smith'); jangan beri tanda kutip pada angka.
• perbandingan: =, <, >, <=, >=, <>.
• LIKE 'A%' cocokkan apa pun yang dimulai dengan A (% = string apa pun, _ = satu karakter); IN (1,2,3); BETWEEN 10 AND 20.
• gabungkan kondisi dengan AND / OR / NOT, dan akhiri setiap pernyataan dengan titik koma.

Pola DDL yang diinginkan ujian. Setiap CREATE TABLE menamai setiap bidang dengan tipenya, menandai kunci utama, dan mendeklarasikan setiap kunci asing beserta tabel yang dirujuknya; kunci komposit dideklarasikan pada baris tersendiri:

CREATE TABLE RENTAL_CAR (
  RentalID INTEGER,
  CarReg VARCHAR(8),
  Days INTEGER,
  PRIMARY KEY (RentalID, CarReg),
  FOREIGN KEY (RentalID) REFERENCES RENTAL(RentalID),
  FOREIGN KEY (CarReg) REFERENCES CAR(CarReg)
);

Contoh terpecahkan. Menggunakan CUSTOMER(CustomerID, Name, Phone) dan DEVICE(DeviceID, CustomerID, Type, Model), tulis skrip SQL untuk: (a) tampilkan nama dan nomor telepon setiap pelanggan yang memiliki perangkat bertipe 'tablet', diurutkan secara alfabetis berdasarkan nama; (b) hitung jumlah perangkat per tipe; (c) catat bahwa pelanggan 17 kini memiliki nomor telepon '0771 234 5678'; (d) tambahkan perangkat baru, ID 305, sebuah 'laptop' model 'X1' milik pelanggan 17.

(a)

SELECT CUSTOMER.Name, CUSTOMER.Phone
FROM CUSTOMER INNER JOIN DEVICE
  ON CUSTOMER.CustomerID = DEVICE.CustomerID
WHERE DEVICE.Type = 'tablet'
ORDER BY CUSTOMER.Name ASC;

(b)

SELECT Type, COUNT(DeviceID) AS NumberOfDevices
FROM DEVICE
GROUP BY Type;

(c) UPDATE CUSTOMER SET Phone = '0771 234 5678' WHERE CustomerID = 17; (d) INSERT INTO DEVICE (DeviceID, CustomerID, Type, Model) VALUES (305, 17, 'laptop', 'X1');

Nilai diberikan per klausa — bidang-bidang, tabel-tabel, kondisi penggabungan, WHERE, ORDER BY — sehingga skrip dengan satu klausa salah tetap mendapatkan nilai untuk sisanya. Tulis Table.Field ketika dua tabel terlibat.

Contoh terpecahkan. Jelaskan apa yang dilakukan skrip ini: SELECT T.Name, SUM(R.Cost) AS Total FROM TECHNICIAN T INNER JOIN REPAIR R ON T.TechnicianID = R.TechnicianID GROUP BY T.Name;

Skrip ini menghasilkan nama setiap teknisi bersama total biaya perbaikan yang telah dilakukan teknisi tersebut, satu baris per teknisi: dua tabel digabungkan berdasarkan TechnicianID, baris-baris dikelompokkan berdasarkan nama, dan biaya dalam setiap kelompok dijumlahkan. Saat ditanyakan apa yang dilakukan skrip, jelaskan hasilnya, bukan sintaksnya.

Soal definisi dinilai berdasarkan frasa tetap. Hafalkan ini persis, dan berikan hanya satu jawaban.

• Definisikan istilah-istilah secara tepat: entitas, atribut, kunci utama, kunci asing, dan jenis hubungan (1:1, 1:banyak, banyak:banyak).
• Berikan alasan pada setiap bentuk normal: 1NF (tidak ada grup berulang), 2NF (tidak ada ketergantungan parsial), 3NF (tidak ada ketergantungan non-kunci) — dan sebutkan bidang-bidang yang terlibat.
• Jelaskan apa yang disediakan oleh DBMS (independensi data, keamanan, integritas, akses konstan, kamus data, antarmuka pengembang, pemroses query).
• Bedakan DDL (mendefinisikan struktur) dari DML (querying dan mengubah data), dan tulis klausa SQL satu per satu: SELECT, FROM, INNER JOIN … ON, WHERE, GROUP BY, ORDER BY.
• Untuk menggambar diagram E-R dari tabel, temukan setiap kunci asing terlebih dahulu: setiap kunci asing adalah satu hubungan satu-ke-banyak, dengan "banyak" berada di tabel yang memilikinya.

Kesalahan umum

• Menggambar hubungan banyak-ke-banyak secara langsung. Harus dipisahkan menjadi dua hubungan satu-ke-banyak melalui tabel penyangga yang memegang kedua kunci asing.
• Menjelaskan "tidak dalam 3NF" dengan "datanya berulang". Sebutkan ketergantungannya (parsial atau transitif) dan bidang-bidang yang terlibat.
• Tanda kutip ganda mengelilingi string dalam SQL, atau tanda kutip mengelilingi angka. String memerlukan 'single quotes'; angka tidak memerlukan apa pun.
• Melewatkan kondisi ON setelah INNER JOIN. Tanpa hal itu, dua tabel tidak terhubung.
• Menempatkan bidang biasa di samping COUNT atau SUM dalam SELECT tanpa GROUP BY.
• UPDATE atau DELETE tanpa WHERE. Ini mengubah atau menghapus setiap baris dalam tabel.