14_Relational_Data

Pendahuluan

Database Management System (DBMS): kumpulan data yang saling berelasi beserta sekumpulan program untuk mengaksesnya.
Database: kumpulan data yang saling berelasi, berisi informasi relevan untuk suatu enterprise.
Relational database: terdiri dari kumpulan tabel (relasi), masing-masing diberi nama unik.

Kenapa Memakai Relational Database?

Relational database tetap menjadi pilihan utama untuk aplikasi pemrosesan data komersial. Kekuatan utamanya:

Data model sederhana, mudah dipahami dan dipakai.
Punya fondasi matematis yang solid dan dipahami baik (teori relasi).
Teknik implementasi sudah dikenal luas, efisien, dan dipakai secara umum.
Ada standar baik untuk query language (SQL) maupun antarmuka lewat bahasa pemrograman.
Prosedur desain basis data yang lugas.
Untuk proyek dengan data yang predictable (dari sisi struktur, ukuran, frekuensi akses), relational database masih pilihan terbaik — bahkan menurut MongoDB sendiri.

Model Data Relasional

Data direpresentasikan dalam bentuk tabel (relasi). Tiap tabel dalam basis data diberi nama unik.
Tiap tabel punya beberapa kolom (atribut), masing-masing kolom punya nama unik.
Tiap baris (tuple) tabel merepresentasikan satu unit informasi.
Urutan tuple dalam relasi tidak relevan — relasi bersifat unordered (berbeda dari daftar/array biasa).

Relation Schema dan Instance

A1, A2, …, An adalah atribut. R = (A1, A2, …, An) disebut relation schema. Contoh: instructor = (ID, name, dept_name, salary).
Relation instance r yang didefinisikan atas schema R dinotasikan r(R). Nilai-nilai terkini suatu relasi ditampilkan sebagai tabel.
Elemen t dari relasi r disebut tuple, direpresentasikan sebagai satu baris tabel.

Atribut

Domain: himpunan nilai yang diizinkan untuk tiap atribut.
Nilai atribut (normalnya) harus atomic — tidak dapat dibagi lagi.
Nilai spesial null adalah anggota setiap domain, menandakan nilai “tidak diketahui”. Nilai null menimbulkan komplikasi dalam definisi banyak operasi.

Key

Misalkan K ⊆ R:

K adalah superkey R jika nilai-nilai K cukup untuk mengidentifikasi satu tuple unik pada setiap kemungkinan relasi r(R). Contoh: {ID} dan {ID, name} adalah superkey instructor.
Superkey K adalah candidate key jika K minimal (tidak ada subset propernya yang juga superkey). Contoh: {ID} adalah candidate key instructor.
Salah satu candidate key dipilih menjadi primary key. Candidate key yang tidak dipilih disebut alternate key.
Foreign key constraint: nilai pada satu relasi harus muncul di relasi lain. Ada relasi referencing (yang mereferensikan) dan referenced (yang direferensikan). Contoh: dept_name pada instructor adalah foreign key dari instructor yang mereferensikan department, dinotasikan FK: instructor(dept_name) → department(dept_name).

Schema Diagram

Skema basis data, beserta constraint primary key dan foreign key, dapat digambarkan lewat schema diagram: tiap relasi digambar sebagai kotak berisi daftar atribut (primary key digarisbawahi), dan panah menunjukkan arah foreign key ke relasi yang direferensikan.

Reduksi ER Model ke Relational Model

Entity set dan relationship set dapat diekspresikan secara seragam sebagai relation schema yang merepresentasikan isi basis data:

Basis data yang sesuai dengan suatu ER diagram dapat direpresentasikan lewat sekumpulan skema.
Untuk tiap entity set dan relationship set ada satu skema unik, diberi nama sesuai nama entity set/relationship set terkait.
Tiap skema punya sejumlah kolom (umumnya berkorespondensi dengan atribut), yang masing-masing punya nama unik.

Merepresentasikan Entity Set

Strong entity set direduksi menjadi skema dengan atribut yang sama persis. Contoh: course = (course_id, title, credits).
Weak entity set menjadi tabel yang mencakup kolom untuk primary key dari identifying strong entity set-nya. Contoh: section = (course_id, sec_id, sem, year) dengan FK: section(course_id) → course(course_id).

Atribut Kompleks pada Entity Set

Composite attribute diratakan (flattened) dengan membuat atribut terpisah untuk tiap komponen. Contoh: name (dengan komponen first_name, last_name) direduksi menjadi dua atribut first_name dan last_name (atau name_first_name/name_last_name bila ada potensi ambiguitas).
Multivalued attribute M pada entity E direpresentasikan sebagai skema terpisah EM. Contoh: atribut phone_number direpresentasikan sebagai skema instructor_phone = (ID, phone_number) dengan FK: instructor_phone(ID) → instructor(ID).
Derived attribute tidak direpresentasikan secara eksplisit pada model relasional — pada data model lain bisa direpresentasikan sebagai stored procedure, function, atau method.

Merepresentasikan Relationship Set

Many-to-many: direpresentasikan sebagai skema dengan atribut berupa primary key kedua entity set yang berpartisipasi, plus atribut deskriptif milik relationship set itu sendiri. Contoh: advisor = (s_id, i_id, date) dengan FK ke instructor(ID) dan student(ID).
Many-to-one / one-to-many: direpresentasikan dengan menambahkan atribut ekstra pada sisi “many” berisi primary key sisi “one” — terutama jika ada total participation pada sisi many. Contoh: student = (ID, name, tot_cred, dept_name, i_id) — atribut dept_name (FK ke department) dan i_id (FK ke instructor, dari relationship advisor) ditambahkan langsung ke tabel student, tanpa perlu tabel relationship terpisah.
One-to-one: sisi mana pun bisa dipilih menjadi sisi “many” (tempat menambahkan foreign key). Pilih entity set dengan total participation (jika ada) sebagai sisi “many”, karena partial participation berpotensi menghasilkan nilai null.

Merepresentasikan Specialization

Buat skema untuk entity higher-level.
Buat skema untuk tiap entity set lower-level, sertakan primary key entity higher-level plus atribut lokalnya masing-masing. Contoh: Person = (ssn, address, type), Employee = (ssn, salary) dengan FK: Employee(ssn) → Person(ssn), Customer = (ssn, credit-rating) dengan FK: Customer(ssn) → Person(ssn).

Merepresentasikan Aggregation

Untuk merepresentasikan aggregation, buat skema yang berisi: primary key dari relationship yang diagregasi, primary key entity set yang terkait, dan atribut deskriptif. Contoh: proj_guide = (i_id, p_id, s_id), lalu eval_for = (i_id, p_id, s_id, e_id) dengan FK: eval_for(i_id, p_id, s_id) → proj_guide(i_id, p_id, s_id) dan FK: eval_for(e_id) → evaluation(e_id).

SQL

SQL (Structured Query Language) awalnya adalah bahasa Sequel yang dikembangkan IBM sebagai bagian dari proyek System R di IBM San Jose Research Laboratory, kemudian diganti nama menjadi SQL. Standar ANSI/ISO SQL berkembang melalui beberapa versi: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003. Sistem komersial umumnya mendukung mayoritas fitur SQL-92 plus berbagai fitur dari standar yang lebih baru serta fitur proprietary masing-masing vendor.

Bagian-bagian SQL:

DML (Data Manipulation Language): menyediakan kemampuan query informasi dari basis data serta menyisipkan (insert), menghapus (delete), dan mengubah (update) tuple.
DDL (Data Definition Language): menyediakan spesifikasi informasi tentang relasi, termasuk integrity constraint dan definisi view.
Bagian lain (tidak dibahas mendalam di sini): transaction control, embedded/dynamic SQL, dan authorization.

Kategori bahasa query:

Imperative: user menginstruksikan sistem melakukan urutan operasi spesifik untuk menghasilkan hasil yang diinginkan.
Functional: komputasi diekspresikan sebagai evaluasi fungsi yang beroperasi pada data atau hasil fungsi lain (contoh: relational algebra).
Declarative: user mendeskripsikan informasi yang diinginkan tanpa memberi urutan langkah spesifik (contoh: tuple relational calculus, domain relational calculus).

SQL memuat elemen dari ketiga pendekatan tersebut sekaligus.

DDL (Data Definition Language)

DDL SQL memungkinkan spesifikasi informasi tentang relasi, termasuk: skema tiap relasi, tipe nilai tiap atribut, integrity constraint, set index yang dijaga untuk tiap relasi, informasi keamanan/otorisasi, dan struktur penyimpanan fisik tiap relasi di disk.

Tipe domain bawaan SQL (di antaranya):

Tipe	Deskripsi
`char(n)`	String karakter fixed-length, panjang n ditentukan user
`varchar(n)`	String karakter variable-length, panjang maksimum n
`int`	Integer (subset bilangan bulat, bergantung mesin)
`smallint`	Integer kecil
`numeric(p,d)`	Fixed-point number, presisi p digit, d digit di belakang desimal (mis. `numeric(3,1)` bisa menyimpan 44.5 tapi tidak 444.5 atau 0.32)
`real`, `double precision`	Floating-point dan double-precision floating-point
`float(n)`	Floating-point dengan presisi minimal n digit
`date`	Tanggal (tahun 4 digit, bulan, hari)
`time`	Waktu (jam, menit, detik)
`interval`	Rentang waktu

Create table:

create table r (
  A1 D1,
  A2 D2,
  ...,
  An Dn,
  <integrity-constraint1>,
  ...,
  <integrity-constraint>
);
 
create table instructor (
  ID       char(5),
  name     varchar(20),
  dept_name varchar(20),
  salary   numeric(8,2),
  primary key (ID)
);

Integrity constraint yang bisa dideklarasikan:

primary key
not null — menandai atribut wajib diisi.
unique (A1, A2, …, Am) — menyatakan atribut-atribut tersebut membentuk candidate key (alternate key), namun tetap diperbolehkan bernilai null (berbeda dari primary key).
check (P) — predikat P yang wajib dipenuhi tiap tuple.
foreign key (<referencing_columns>) references <referred_table> — bisa juga menyertakan (<referred_pk_columns>) secara eksplisit.

Contoh lengkap:

create table section (
  course_id     varchar(8),
  sec_id        varchar(8),
  semester      varchar(6),
  year          numeric(4,0),
  building      varchar(15) not null,
  room_number   varchar(7)  not null,
  time_slot_id  varchar(4)  not null,
  primary key (course_id, sec_id, semester, year),
  check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
  foreign key (course_id) references course,
  foreign key (building, room_number) references room (building, room_number)
);

Catatan: jika tidak dideklarasikan, secara default seluruh atribut boleh null, kecuali yang dideklarasikan sebagai primary key.

Mengubah struktur tabel:

drop table r — menghapus relasi.
alter table r add A D — menambahkan atribut A bertipe D.
alter table r drop A — menghapus atribut A (dropping atribut tidak didukung oleh banyak DBMS).

SQL — Data Manipulation Language

DML memungkinkan pengguna mengakses/memanipulasi data sesuai data model: retrieval (query), insertion, deletion, dan modification.

Struktur Dasar Query

select A1, A2, ..., An
from   r1, r2, ..., rm
where  P

select: mendaftar atribut yang diinginkan dalam hasil query.
from: mendaftar relasi yang terlibat dalam query.
where: menentukan kondisi yang harus dipenuhi hasil.
Hasil query SQL selalu berupa sebuah relasi.

Contoh:

select name from instructor;
select name from instructor where dept_name = 'Comp. Sci.';
select '437';   -- atribut boleh berupa literal tanpa klausa from

Klausa select:

select distinct — menghapus duplikat; select all — menyatakan duplikat tidak dihapus (default).
select * — menandakan “semua atribut”.
Klausa select boleh berisi ekspresi aritmetika (+ - * /) atas konstanta atau atribut, dan atribut bisa diberi nama ulang memakai as (mis. salary/12 as monthly_salary).

Klausa from: hasilnya adalah cartesian product antar relasi yang terlibat — biasanya baru berguna jika dikombinasikan dengan kondisi pada where, atau memakai operasi join. Relasi dan atribut bisa diganti nama memakai as (old-name as new-name; kata kunci as opsional).

Joined relations: operasi join mengambil dua relasi dan menghasilkan relasi baru berisi tuple yang cocok dengan suatu kondisi.

Join type	Join condition
inner join	natural
full outer join	`on <predicates>`
left outer join	`using (A1, A2, ..., An)`
right outer join

natural join: mencocokkan otomatis atribut bernama sama di kedua relasi.
Outer join menyertakan tuple yang tidak punya pasangan cocok, dengan nilai null mengisi kolom dari sisi yang tidak cocok — left outer join mempertahankan semua tuple sisi kiri, right outer join mempertahankan semua tuple sisi kanan, full outer join mempertahankan semua tuple dari kedua sisi.

Klausa where: mendukung operator logika and, or, not; operator perbandingan <, <=, >, >=, =, <>; operator pattern-matching like (dengan wildcard % dan _); operator between untuk rentang nilai numerik/tanggal; serta tuple comparison, mis. where (instructor.ID, dept_name) = (teaches.ID, 'Biology').

Null Values

null menandakan nilai tidak diketahui atau tidak ada.
Hasil ekspresi aritmetika yang melibatkan null adalah null (mis. 5 + null → null).
Predikat is null / is not null dipakai untuk mengecek nilai null.
SQL memperlakukan hasil perbandingan yang melibatkan null sebagai unknown (mis. 5 < null, null <> null, null = null semuanya unknown).

Ordering, Set Operation, Aggregate Function

order by mengurutkan hasil; boleh asc (default) atau desc per atribut, dan boleh multi-atribut.
Set operation: union, intersect, except — otomatis menghapus duplikat; tambahkan all (union all, dst.) untuk mempertahankan duplikat.
Aggregate function: avg, min, max, sum, count — beroperasi pada multiset nilai suatu kolom, mengembalikan satu nilai.
group by: mengelompokkan tuple menjadi set berdasarkan atribut/kombinasi atribut sebelum agregasi diterapkan per grup.
having: menyatakan kondisi yang berlaku pada grup, bukan tuple individual — predikat having diterapkan setelah grup terbentuk, sedangkan predikat where diterapkan sebelum grup terbentuk.

Nested Subquery

SQL mendukung subquery — ekspresi select-from-where yang disarangkan dalam query lain:

Pada klausa from: ri bisa digantikan subquery apapun yang valid.
Pada klausa where: P bisa digantikan ekspresi B <operation> (subquery).
Pada klausa select: Ai bisa digantikan subquery yang menghasilkan satu nilai (scalar subquery — runtime error bila subquery mengembalikan lebih dari satu tuple hasil).

Set membership: in menguji keanggotaan suatu himpunan, not in menguji ketidakanggotaan.

Test for empty relation: exists r ⇔ r ≠ ∅; not exists r ⇔ r = ∅. Variabel pada query luar yang dipakai kembali di query dalam disebut correlation name, dan subquery yang memakainya disebut correlated subquery.

With clause: mendefinisikan relasi sementara yang hanya berlaku untuk query tempat klausa with tersebut muncul — berguna memecah query kompleks menjadi bagian yang lebih mudah dibaca.

Subquery pada Insert, Update, Delete

-- delete dengan subquery pada where
delete from instructor
where dept_name in (select dept_name from department where building = 'Watson');
 
-- update dengan subquery
update instructor
set salary = salary * 1.05
where salary < (select avg(salary) from instructor);

Insert, Update, Delete Statement

insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
insert into course (course_id, title, dept_name, credits)
  values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
 
update instructor set salary = salary * 1.05;
update instructor set salary = salary * 1.05 where salary < 70000;
 
delete from instructor;
delete from instructor where dept_name = 'Finance';

insert juga bisa mengambil hasil sebuah select (bukan values literal) untuk menyisipkan banyak baris sekaligus dari hasil query.

Views

Tidak semua user perlu melihat seluruh model logis (seluruh relasi aktual) basis data. View menyediakan mekanisme menyembunyikan data tertentu dari user tertentu:

create view v as < query expression >

Setelah didefinisikan, nama view bisa dipakai untuk merujuk relasi virtual yang dihasilkan view tersebut. Contoh: create view faculty as select ID, name, dept_name from instructor; — menyembunyikan kolom salary dari pengguna view faculty.

Materialized view: beberapa DBMS memungkinkan relasi hasil view disimpan secara fisik (materialized) saat pertama didefinisikan. Jika relasi yang mendasarinya berubah, hasil materialized view menjadi usang dan perlu di-refresh: refresh materialized view v.

Sumber

Silberschatz, Korth, Sudarshan: Database System Concepts, 7th ed., Chapter 2 (Introduction to Relational Model), Chapter 3 (Introduction to SQL), Chapter 4 (Intermediate SQL), Chapter 6.7 (Reducing ER Diagrams to Relational Schema).

Flashcard

flashcards Apa perbedaan superkey dan candidate key? :: Superkey adalah himpunan atribut yang cukup mengidentifikasi tuple secara unik; candidate key adalah superkey yang minimal (tidak ada subset propernya yang juga superkey). Apa itu foreign key constraint, dan istilah apa untuk relasi yang mereferensikan vs direferensikan? :: Constraint bahwa nilai suatu atribut di satu relasi (referencing relation) harus muncul di relasi lain (referenced relation) — menjaga integritas referensial. Bagaimana strong entity set dan weak entity set direduksi menjadi skema relasional? :: Strong entity set → skema dengan atribut yang sama persis. Weak entity set → skema yang menyertakan kolom primary key dari identifying strong entity set-nya sebagai foreign key. Bagaimana relationship many-to-many direpresentasikan pada model relasional, dan bagaimana bedanya dengan one-to-many? :: Many-to-many: skema tersendiri berisi primary key kedua entity yang berpartisipasi plus atribut deskriptifnya. One-to-many: cukup tambahkan foreign key (primary key sisi “one”) sebagai atribut ekstra pada tabel sisi “many”, tanpa tabel relationship terpisah. Pada relationship one-to-one, sisi mana yang sebaiknya dipilih sebagai “sisi many” (tempat foreign key ditaruh)? :: Sisi yang punya total participation, karena partial participation berisiko menghasilkan banyak nilai null pada foreign key. Apa perbedaan constraint unique dan primary key pada SQL DDL? :: unique menyatakan candidate key/alternate key yang tetap boleh bernilai null; primary key adalah key utama yang tidak boleh null dan hanya ada satu per tabel. Apa perbedaan predikat pada klausa where dan having? :: where diterapkan pada tuple individual sebelum grup dibentuk (group by); having diterapkan pada grup setelah grup terbentuk, biasanya menyaring hasil agregat. Apa perbedaan left outer join dan right outer join? :: Left outer join mempertahankan semua tuple dari relasi kiri meski tidak ada pasangan cocok di kanan (kolom kanan diisi null); right outer join sebaliknya mempertahankan semua tuple relasi kanan. Apa fungsi exists dan not exists pada SQL, dan apa itu correlated subquery? :: exists r bernilai true jika r tidak kosong (r ≠ ∅), not exists r bernilai true jika r kosong. Correlated subquery adalah subquery yang memakai variabel (correlation name) dari query luar di dalam kondisinya. Apa perbedaan view biasa dan materialized view? :: View biasa adalah relasi virtual yang dihitung ulang setiap kali diakses (tidak disimpan fisik); materialized view disimpan secara fisik saat dibuat dan perlu di-refresh manual (refresh materialized view) agar tetap sinkron dengan perubahan data sumber.

Catatan Kuliah

Penjelajah

14_Relational_Data_Model