Aljabar Relasional: Logika di Balik Setiap Query SQL

Apa Itu Aljabar Relasional?

Aljabar relasional adalah bahasa query prosedural. Bahasa ini memberi tahu basis data bagaimana mengambil data, bukan sekadar apa yang Anda inginkan.

Itu adalah perbedaan kunci dari SQL, yang bersifat deklaratif. Saat Anda menulis query SQL, Anda mendeskripsikan hasil yang Anda inginkan. Aljabar relasional menjelaskan urutan operasi yang tepat untuk mencapainya.

Bahasa ini beroperasi pada relasi - istilah formal untuk tabel. Anda memberinya satu atau lebih relasi sebagai input, menerapkan sebuah operasi, dan mendapatkan relasi kembali sebagai output.

Bagian terakhir itu adalah sifat tertutup (closure). Tidak peduli operasi mana yang Anda terapkan - memfilter baris, memilih kolom, menggabungkan tabel - hasilnya selalu merupakan relasi lain. Ini penting karena berarti Anda dapat merangkaikan operasi. Output dari satu operasi menjadi input bagi operasi berikutnya, dan Anda dapat menumpuknya sedalam yang diperlukan.

Anda dapat memikirkan aljabar relasional sebagai balok penyusun. Setiap balok mengambil sebuah tabel (atau dua), melakukan sesuatu padanya, dan mengembalikan sebuah tabel. Mesin basis data menggunakan balok-balok ini secara internal untuk memecah dan mengeksekusi query SQL Anda.

Konsep Fundamental dalam Aljabar Relasional

Sebelum Anda dapat bekerja dengan operator aljabar relasional, Anda perlu mengetahui kosakatanya. Kabar baiknya, hanya ada lima istilah yang perlu diingat.

• Relasi hanyalah sebuah tabel. Terdiri dari baris dan kolom, serta merepresentasikan sekumpulan data tentang entitas tertentu - seperti karyawan atau departemen. Dalam aljabar relasional, setiap operasi mengambil satu atau lebih relasi sebagai input dan menghasilkan relasi sebagai output.

• Tuple adalah satu baris di tabel tersebut. Jika tabel Employees Anda memiliki 10 baris, berarti ada 10 tuple. Setiap tuple merepresentasikan satu record - satu karyawan, dalam hal ini.

• Atribut adalah kolom. Tabel Employees mungkin memiliki atribut seperti employee_id, name, dan department_id. Atribut mendefinisikan jenis data apa yang dimiliki setiap tuple.

• Derajat (degree) adalah jumlah atribut dalam suatu relasi. Jika Employees memiliki lima kolom, maka derajatnya adalah 5. Ini penting saat Anda menggabungkan relasi - beberapa operasi memerlukan derajat yang cocok.

• Kardinalitas adalah jumlah tuple dalam suatu relasi. Jika tabel Employees Anda memiliki 200 baris, kardinalitasnya adalah 200. Kardinalitas berubah saat Anda menambah atau menghapus baris; derajat hanya berubah saat Anda menambah atau menghapus kolom.

Berikut pemetaan istilah-istilah ini ke apa yang sudah Anda kenal:

Aljabar relasional versus istilah umum

Pastikan ini benar-benar klop, maka operatornya akan terasa masuk akal.

Operasi Dasar Aljabar Relasional dalam DBMS

Ada lima operator fundamental dalam aljabar relasional. Setiap operasi lain - join, irisan, divisi - dibangun di atas kelima ini.

Agar contoh tetap konsisten, saya akan menggunakan dua tabel sepanjang bagian ini:

Employees

Tabel Employees

Departments

Tabel Departments

Seleksi (σ)

Seleksi memfilter baris berdasarkan suatu kondisi. Ini adalah padanan aljabar relasional dari klausa WHERE.

Notasinya seperti ini:

σ condition (Relation)

Misalnya Anda ingin semua karyawan dengan gaji di atas 60.000:

σ salary > 60000 (Employees)

Hasilnya adalah relasi baru yang hanya berisi baris yang cocok - Alice dan Carol. Struktur tabel tetap sama, jadi Anda hanya menghapus baris yang tidak memenuhi kondisi.

Ini padanan SQL-nya:

SELECT * FROM Employees 
WHERE salary > 60000;

Proyeksi (π)

Proyeksi memilih kolom tertentu dari sebuah relasi dan membuang sisanya. Begitulah cara Anda memangkas tabel hanya ke atribut yang Anda butuhkan.

π attribute1, attribute2 (Relation)

Untuk mendapatkan hanya nama dan gaji semua karyawan:

π name, salary (Employees)

Satu hal yang perlu diperhatikan adalah proyeksi menghapus baris duplikat dari hasil. Jika dua tuple identik setelah Anda membuang kolom tambahan, hanya satu salinan yang masuk ke output.

Ini padanan SQL-nya:

SELECT DISTINCT name, salary 
FROM Employees;

Union (∪)

Union menggabungkan dua relasi menjadi satu, menggabungkan semua tuplenya. Baris duplikat dihapus secara otomatis.

Notasinya:

Relation1 ∪ Relation2

Syaratnya adalah kedua relasi harus union-compatible. Artinya harus memiliki jumlah atribut yang sama, dan atribut yang bersesuaian harus bertipe data yang sama. Anda tidak bisa melakukan union antara tabel karyawan dan tabel departemen karena skemanya tidak cocok.

Misalnya Anda memiliki tabel Contractors dengan struktur yang sama seperti Employees. Union memberi Anda setiap orang di kedua tabel, tanpa duplikasi:

Employees ∪ Contractors

Ini padanan SQL-nya:

SELECT * FROM Employees
UNION
SELECT * FROM Contractors;

Selisih himpunan (−)

Selisih himpunan mengembalikan tuple yang ada di relasi pertama tetapi tidak di relasi kedua. Pikirkan seperti pengurangan:

Relation1 − Relation2

Seperti union, kedua relasi harus union-compatible.

Jika Anda menginginkan semua karyawan yang bukan kontraktor, sintaksnya seperti ini:

Employees − Contractors

Hasilnya hanya berisi baris dari Employees yang tidak memiliki padanan di Contractors.

Padanan SQL:

SELECT * FROM Employees
EXCEPT
SELECT * FROM Contractors;

Produk kartesius (×)

Produk kartesius menggabungkan setiap tuple dari relasi pertama dengan setiap tuple dari relasi kedua. Jika Employees memiliki 4 baris dan Departments memiliki 3 baris, hasilnya memiliki 4 × 3 = 12 baris.

Employees × Departments

Sendirian, hasilnya jarang berguna - Anda mendapatkan setiap kemungkinan kombinasi karyawan dan departemen, termasuk yang tidak saling terkait. Namun produk kartesius adalah dasar dari join. Anda memasangkannya dengan seleksi untuk menyisakan hanya baris yang relasinya masuk akal.

Padanan SQL:

SELECT * FROM Employees, Departments;

Kelima operator ini adalah fondasi. Segala sesuatu lainnya dalam aljabar relasional adalah singkatan dari penggabungan mereka.

Operasi Join dalam Aljabar Relasional

Join adalah operasi yang paling sering digunakan dalam aljabar relasional - dan tidak semisterius kelihatannya.

Setiap join hanyalah produk kartesius diikuti seleksi. Anda menggabungkan semua tuple dari dua relasi, lalu menyaring yang tidak memenuhi kondisi. Join ada sebagai singkatan karena melakukan produk kartesius penuh terlebih dulu itu mahal. Join menerapkan kondisi saat penggabungan, yang mengurangi ukuran hasil perantara.

Natural join (⋈)

Natural join secara otomatis menggabungkan dua relasi pada semua atribut yang namanya sama, dan hanya menyisakan satu salinan kolom bersama tersebut dalam hasil:

Employees ⋈ Departments

Karena kedua tabel berbagi department_id, natural join mencocokkan baris di mana nilainya sama dan menggabungkannya. Alice (department_id = 10) dicocokkan dengan Engineering, Bob dengan Marketing, dan seterusnya.

Tidak ada kondisi yang perlu ditulis - join menentukannya dari skema. Itu praktis, tetapi juga berarti mengganti nama kolom dapat merusak query Anda.

Ini padanan SQL-nya:

SELECT * FROM Employees 
NATURAL JOIN Departments;

Theta join (⋈θ)

Theta join menggabungkan dua relasi berdasarkan kondisi apa pun yang Anda tentukan - kondisinya dapat menggunakan operator perbandingan apa pun: =, ≠, <, >, ≤, ≥.

Employees ⋈ salary > 60000 AND department_id = department_id Departments

Ini memberi Anda kontrol penuh atas kondisi join. Sebagian besar join yang Anda tulis dalam praktik adalah theta join di balik layar.

Ini padanan SQL-nya:

SELECT * FROM Employees, Departments
WHERE Employees.salary > 60000
AND Employees.department_id = Departments.department_id;

Equi join

Equi join adalah theta join di mana kondisinya hanya menggunakan kesetaraan (=). Ini yang paling umum.

Employees ⋈ Employees.department_id = Departments.department_id Departments

Hasilnya mencakup semua kolom dari kedua tabel, yang berarti department_id muncul dua kali. Itulah perbedaan utama dari natural join - equi join tidak menghapus kolom duplikat.

Ini padanan SQL-nya:

SELECT * FROM Employees
INNER JOIN Departments
ON Employees.department_id = Departments.department_id;

Outer join

Join di atas hanya mengembalikan baris yang memiliki padanan di kedua relasi. Outer join mempertahankan baris yang tidak memiliki padanan juga, mengisi NULL untuk nilai yang hilang.

Ada tiga variasi:

• Left outer join (⟕) mempertahankan semua baris dari relasi kiri. Jika sebuah baris di Employees tidak memiliki departemen yang cocok, baris tersebut tetap muncul dalam hasil dengan NULL pada kolom departemen.

• Right outer join (⟖) mempertahankan semua baris dari relasi kanan. Departemen tanpa karyawan tetap muncul, dengan NULL pada kolom karyawan.

• Full outer join (⟗) mempertahankan semua baris dari kedua relasi. Baris yang tidak cocok dari salah satu sisi muncul dengan NULL untuk nilai yang hilang.

Berikut padanan SQL-nya:

-- Left outer join
SELECT * FROM Employees
LEFT JOIN Departments
ON Employees.department_id = Departments.department_id;

-- Right outer join
SELECT * FROM Employees
RIGHT JOIN Departments
ON Employees.department_id = Departments.department_id;

-- Full outer join
SELECT * FROM Employees
FULL OUTER JOIN Departments
ON Employees.department_id = Departments.department_id;

Gunakan outer join saat Anda tidak bisa kehilangan baris yang tidak memiliki padanan - misalnya ketika Anda membutuhkan daftar lengkap karyawan terlepas dari apakah departemennya ada dalam sistem.

Operator Turunan Aljabar Relasional

Operator turunan adalah jalan pintas yang dibangun dari lima operator dasar yang dibahas sebelumnya.

Irisan (∩)

Irisan hanya mengembalikan tuple yang muncul di kedua relasi. Seperti union dan selisih himpunan, kedua relasi harus union-compatible.

Employees ∩ Contractors

Jika seorang karyawan juga merupakan kontraktor, mereka akan muncul dalam hasil. Yang lainnya dibuang.

Di balik layar, irisan hanyalah selisih himpunan yang diterapkan dua kali:

Employees ∩ Contractors = Employees − (Employees − Contractors)

Ini padanan SQL-nya:

SELECT * FROM Employees
INTERSECT
SELECT * FROM Contractors;

Divisi (÷)

Divisi adalah operator yang sering menjebak mahasiswa CS dan junior - namun konsepnya lebih sederhana daripada kelihatannya.

Divisi menjawab pertanyaan: "tuple mana dalam relasi A yang berasosiasi dengan semua tuple dalam relasi B?"

Misalnya Anda memiliki tabel CourseEnrollments yang melacak mahasiswa mana yang terdaftar di mata kuliah mana, dan tabel RequiredCourses yang mencantumkan setiap mata kuliah yang harus diselesaikan mahasiswa. Divisi memberi Anda mahasiswa yang terdaftar di setiap mata kuliah wajib.

CourseEnrollments ÷ RequiredCourses

Inilah sebabnya dianggap rumit: tidak ada padanan SQL langsung. Untuk mereplikasi divisi dalam SQL, Anda memerlukan negasi ganda - "temukan mahasiswa yang tidak memiliki satu pun mata kuliah wajib yang belum diambil":

SELECT DISTINCT student_id FROM CourseEnrollments ce1
WHERE NOT EXISTS (
    SELECT course_id FROM RequiredCourses rc
    WHERE NOT EXISTS (
        SELECT * FROM CourseEnrollments ce2
        WHERE ce2.student_id = ce1.student_id
        AND ce2.course_id = rc.course_id
    )
);

Pola ganda NOT EXISTS itu adalah cara standar untuk mengekspresikan divisi dalam SQL. Jika Anda melihat pertanyaan dalam wawancara yang meminta "temukan semua X yang berelasi dengan setiap Y," divisi adalah operator yang harus Anda gunakan.

Ganti nama (ρ)

Ganti nama memungkinkan Anda memberi relasi atau atributnya nama baru. Ini tidak mengubah data, hanya sebutannya.

ρ NewName (Relation)

Atau untuk mengganti nama relasi dan atributnya sekaligus:

ρ NewName(attr1, attr2, ...) (Relation)

Ganti nama berguna ketika Anda perlu melakukan join pada tabel yang sama dengan dirinya sendiri - tanpa itu, nama atribut akan bertabrakan dan operasinya tidak masuk akal. Ini juga bermanfaat saat merangkaikan operasi dan Anda ingin hasil perantara memiliki nama yang bermakna.

Ini padanan SQL-nya:

SELECT * FROM Employees AS E1;
-- atau mengganti nama kolom:
SELECT employee_id AS id, name AS employee_name FROM Employees;

Ketiga operator ini perlu Anda hafalkan. Irisan dan ganti nama mudah, dan divisi adalah yang layak dilatih sebelum wawancara basis data apa pun.

Aljabar Relasional vs. SQL

SQL dan aljabar relasional bukan hal yang sama - tetapi SQL tidak akan ada tanpanya.

Perbedaan inti terletak pada cara masing-masing memikirkan query. Aljabar relasional bersifat prosedural - Anda menentukan urutan operasi yang tepat untuk mendapatkan hasil. SQL bersifat deklaratif - Anda mendeskripsikan hasil yang diinginkan, dan mesin basis data mencari cara untuk mendapatkannya.

Ambil contoh sederhana. Dalam aljabar relasional, mengambil karyawan Engineering terlihat seperti ini:

π name, salary (σ department_id = 10 (Employees))

Anda memberi tahu basis data persis apa yang harus dilakukan: pertama saring baris, lalu pilih kolom. Urutan operasi eksplisit.

Dalam SQL, Anda menulis:

SELECT name, salary 
FROM Employees 
WHERE department_id = 10;

Anda mendeskripsikan hasil yang Anda inginkan. Mesin basis data menerjemahkannya menjadi urutan operasi - dan operasi internal itu dibangun di atas aljabar relasional.

Itulah hubungan antara keduanya. SQL adalah bahasa yang dihadapi pengguna; aljabar relasional adalah model teoretis di bawahnya. Saat mesin basis data menerima query SQL, mesin akan mem-parsing-nya menjadi representasi internal berdasarkan aljabar relasional, mengoptimalkannya, lalu mengeksekusinya.

SQL juga melampaui apa yang didefinisikan aljabar relasional. Aljabar relasional tidak memiliki konsep:

• Agregasi - SUM(), COUNT(), AVG()

• Pengelompokan - GROUP BY

• Pengurutan - ORDER BY

• Penanganan duplikat - aljabar relasional bekerja dengan himpunan (tanpa duplikat); SQL bekerja dengan multihimpunan (duplikat diizinkan secara default)

Fitur-fitur ini ditambahkan ke SQL karena kebutuhan query di dunia nyata. Namun semuanya bertumpu pada fondasi aljabar relasional.

Intinya, memahami aljabar relasional memberi tahu Anda bagaimana query SQL Anda disusun dan ditransformasikan secara internal. Ini tidak akan mengubah cara Anda menulis SQL sehari-hari, tetapi akan mengubah cara Anda menalar tentang apa yang sebenarnya dilakukan query Anda.

Kesalahan dan Kebingungan yang Umum Terjadi

Sebagian besar kebingungan terkait aljabar relasional bermuara pada beberapa kesalahpahaman yang berulang. Berikut ulasannya.

• Menyamakan SQL dan aljabar relasional. Keduanya bukan hal yang sama. SQL terinspirasi oleh aljabar relasional, tetapi keduanya tidak dapat dipertukarkan. SQL bersifat deklaratif dan mencakup fitur seperti agregasi dan pengurutan yang tidak didefinisikan aljabar relasional. Aljabar relasional adalah model formal; SQL adalah bahasa praktis yang dibangun di atasnya.

• Melupakan persyaratan kompatibilitas skema. Union, irisan, dan selisih himpunan semuanya memerlukan relasi yang union-compatible - jumlah atribut sama, tipe data yang sama dalam urutan yang sama. Mencoba menerapkan operator ini pada tabel dengan skema berbeda tidak akan berhasil. Ini sering membingungkan karena join tidak memiliki persyaratan tersebut.

• Tidak memahami operator divisi. Divisi adalah satu-satunya operator tanpa padanan SQL langsung, itulah sebabnya sering membingungkan. Jika Anda melihat query yang meminta "temukan semua X yang terkait dengan setiap Y," itulah divisi. Terjemahan SQL-nya memerlukan pola ganda NOT EXISTS - tidak intuitif, jadi layak dilatih sebelum ujian atau wawancara.

• Mengira join adalah operator mandiri. Bukan primitif. Setiap join adalah produk kartesius yang dikombinasikan dengan seleksi. Natural join, theta join, equi join - semuanya singkatan dari kombinasi tersebut. Memahami hal ini membuat perilaku join jauh lebih mudah diprediksi saat hasil terlihat tidak sesuai harapan.

Kesimpulan

Aljabar relasional adalah tulang punggung teoretis dari setiap sistem basis data relasional. Setiap query yang Anda tulis dalam SQL diterjemahkan menjadi rangkaian operasi aljabar relasional sebelum mesin basis data menyentuh satu baris pun.

Operator - seleksi, proyeksi, union, selisih himpunan, produk kartesius, dan padanan turunannya - adalah balok penyusun dari penerjemahan tersebut. Setelah Anda memahami cara kerjanya dan bagaimana mereka dirangkaikan, Anda berhenti melihat SQL sebagai kotak hitam dan mulai melihat struktur di baliknya.

Itulah nilai sebenarnya mempelajari aljabar relasional: memahami apa yang sebenarnya dilakukan query Anda, bukan menulisnya dalam notasi formal.