SQL - E-learning UPN JATIM

SQL: Queries,
Constraints & Triggers
(Chap. 5 – Ramakrishnan)
Pokok Bahasan
 Apa yang termasuk dalam bahasa SQL? Apa SQL/1999?
 Bagaimana queries disajikan dalam SQL? Bagaimana makna suatu query
dinyatakan dalam standar SQL?
 Bagaimana SQL menjadikan aljabar dan kalkulus relasional sebagai
dasar dan memperluas keduanya?
 Apa yang dimaksud dengan “grouping”? Dan bagaimana ia dapat
digunakan dalam operasi-operasi aggregasi?
 Apa yang dimaksud dengan “nested queries”?
 Apa yang dimaksud dengan null values ?
 Bagaimana queries dapat digunakan utk menuliskan integrity constraints
yang komplek?
 Apa yang dimaksud triggers, dan mengapa triggers menjadi berguna?
Dan bagaimana kaitan triggers dan integrity constarints?
Overview
 Data Manipulation Language (DML): Subset SQL yang dapat diguna-kan
utk menspesifikasikan queries dan utk menyisipkan (insert), menghapus
(delete), dan memodifikasi (modify) baris-baris tabel
 Data Definition Language (DDL): Subset SQL utk mendukung pembuatan,
penghapusan, dan modifikasi tables & views.
 Integrity Constraints (ICs) dapat didefinisikan pada tables pada saat
suatu tabel dibuat atau nanti sesudahnya
 Triggers & Advanced ICs: Standar SQL/99 memberikan dukungan untuk
triggers, yang merupakan tindakan-tindakan otomatis yang dijalankan oleh
DBMS bilamana perubahan-perubahan pada database memenuhi kondisi
yang dispesifikasikan dalam trigger
 Embedded & Dynamic SQL: Fitur-fitur embedded SQL memungkinkan SQL
utk dipanggil dari suatu bahasa induk seperti C atau Java. Sedang fitur-fitur
dynamic SQL memungkinkan sebuah query utk disusun (dijalankan) pada
saat “run-time” (Ramakrishnan, Chap 6).
Overview (Lanjutan)
 Client-Server Execution & Remote Database Access: Perintahperintah SQL untuk ini dapat digunakan mengendalikan
bagaimana suatu program aplikasi client dapat dihubungkan ke
sebuah SQLdatabase server, atau mengakses data dari sebuah
database melalui jaringan (Ramakrishnan, Chap 7)
 Transaction Management: Berbagai perintah SQL
memungkinkan seorang pengguna utk secara eksplisit
mengendalikan aspek bgm sebuah transaksi harus dijalankan
(Ramakrishnan, Chap 21)
 Security: SQL menyediakan mekanisme utk mengendalikan
akses pengguna ke obyek database seperti tables & views
(Ramakrishnan, Chap 21)
Contoh Instances
 Contoh-contoh sebagian instances dari relasi Sailors, Boats,
dan Reserves akan digunakan dalam contoh-contoh spesifikasi
SQL
Sailor (S1)
Reserves (R1)
sid
sname
rating
age
sid
bid
day
22
101
10/10/96
58
103
11/12/96
bname
Interlake
Clipper
dustin
lubber
rusty
7
8
10
45.0
55.5
35.0
Sailor(S2)
Boat(B1)
bid
101
103
22
31
58
color
blue
green
sid
28
31
44
58
sname
yuppy
lubber
guppy
rusty
rating
9
8
5
10
age
35.0
55.5
35.0
35.0
Basic SQL Query
SELECT [DISTINCT] target-list
FROM
relation-list
WHERE qualification
 relation-list Satu list nama-nama relasi (mungkin diikuti dengan
sebuah range-variable setelah setiap nama relasi).
 target-list Satu list attribut dari relasi dlm relation-list
 qualification Perbandingan (Attr op const, or Attr1 op Attr2,
dimana op adalah salah satu dari (, , , , , ) yang
dikombinasikan menggunakan konjungsi logika AND, OR, atau
NOT.
 DISTINCT merupakan sebuah keyword optimal yang
mengindikasikan bahwa jawaban query tidak boleh berisi
duplikasi baris. Default-nya adalah duplikasi TIDAK dieliminasi!
Strategi Evaluasi Konseptual
 Semantik dari sebuah query SQL didefinisikan berdasarkan strategi
evaluasi konseptual berikut:

Hitung hasil “cross-product” dari relation-list.

Buang tuples hasil jika tuples tsb tidak memenuhi
qualifications.

Hapus attributes yang tidak ada dalam target-list.

Jika digunakan DISTINCT, lakukan eliminasi baris-baris yang
duplikasi.
 Strategi di atas mungkin merupakan cara yang paling tidak efisien
utk menghitung sebuah query! Untuk ini, sebuah “optimizer” akan
mencari strategi yang lebih efisien utk menghitung jawaban yang
sama.
Contoh Hasil Evaluasi Konseptual
Range variables
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
(sid)
22
22
31
31
58
58
sname
dustin
dustin
lubber
lubber
rusty
rusty
rating
7
7
8
8
10
10
age
45.0
45.0
55.5
55.5
35.0
35.0
(sid)
22
58
22
58
22
58
bid
101
103
101
103
101
103
day
10/10/96
11/12/96
10/10/96
11/12/96
10/10/96
11/12/96
Catatan utk “Range Variables”
 Sebenarnya hanya diperlukan jika ralasi yang sama muncul
dua kali dalam FROM clause. Untuk ini query sebelumnya
dapat juga ditulis menjadi:
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND bid=103
ATAU
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid
AND bid=103
Namun demikian,
merupakan good
style untuk selalu
menggunakan
range variables!
Dapatkan sailors yang telah melakukan
reservasi boat
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid=R.sid
 Apakah penambahan opsi DISTINCT pada query di atas akan
memberikan perbedaan hasil query?
 Apa efek yang akan terjadi jika S.sid diganti dengan S.sname
dalam SELECT clause?
Juga, Apakah penambahan opsi DISTINCT pada query ini akan
memberikan perbedaan hasil query?
Persamaan & Strings
SELECT S.age, age1=S.age-5, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE ‘B_%B’
 Contoh di atas mengilustrasikan penggunan persamaan matematik
dan penyamaan pola string:
Dapatkan triples (terdiri dari ages of sailors dan dua fields yang
didefinsisikan oleh persamaan) untuk sailors yang namanya
diawali dan diakhiri dengan huruf B dan paling sedikit terdiri dari
3 karakter
 AS dan = merupakan dua cara utk menamakan fields dalam hasil
query.
 LIKE digunakan utk “string matching”. ‘_’ menyatakan sembarang
satu karakter, dan ‘%’ menyatakan sembarang string karakter
dengan panjang 0 atau lebih.
UNION, INTERSECT & EXCEPT
Dapatkan sid dari sailors yang telah melakukan
reservasi sebuah red boat atau sebuah green boat
 UNION: dpt digunakan utk
menghitung union dari sembarang
dua union-compatible sets of tuples
yang merupakan hasil dari dua SQL
queries.
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ OR
 Jika OR diganti dg AND dlm query
pertama, apa yang akan diperoleh?
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
UNION
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’
 Juga tersedia: EXCEPT untuk
menghitung set difference dari
sembarang dua union-compatible
sets of tuples
(Apa yang akan diperoleh jika
UNION diganti dengan EXCEPT?)
B.color=‘green’)
Dapatkan sid dari sailors yang telah melakukan reservasi
sebuah red boat dan sebuah green boat
 INTERSECT: dpt digunakan
utk menghitung interseksi
dari sembarang dua unioncompatible sets of tuples.
 Dimasukkan dlm standar
SQL/92, tetapi beberapa
sistem tidak mendukungnya.
 Bedakan secara jelas query
UNION & INTERSECT !
Seberapa jauh kedua berbeda
? (tanpa secara eksplisit
menggunakan keyword
UNION & INTERSECT)
SELECT S.sid
FROM Sailors S, Boats B1, Reserves R1,
Boats B2, Reserves R2
WHERE S.sid=R1.sid AND R1.bid=B1.bid
AND S.sid=R2.sid AND R2.bid=B2.bid
AND (B1.color=‘red’ AND B2.color=‘green’)
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’
Nested Queries
Dapatkan names dari sailors yang telah
melakukan reservasi boat bernomor 103
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)
 Merupakan fitur SQL yang sangat berguna: sebuah WHERE clause dpt
berisikan sebuah SQL query!
 Dalam conth di atas, untuk mendapatkan sailors yang tidak melakukan reservasi
boat bernomor 103  gunakan NOT IN.
 Utk memahami semantik dari nested queries, pikirkan evaluasi sebuah nested
loops : Untuk setiap tuple dari outer query, cek kualifikasinya dengan cara
membandingkannya dengan hasil perhitungan subquery.
Nested Queries dengan
Korelasi
Dapatkan names dari sailors yang telah
melakukan reservasi boat bernomor103
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid)
 EXISTS adalah bentuk operator perbandingan yang lain (seperti halnya IN)
yang bernilai TRUE jika subquery yang digunakan sbg parameternya tidak
menghasilkan set kosong.
 Jika keyword UNIQUE digunakan pada subquery, dan * diganti dengan R.bid
pada spesifikasi SQL di atas, maka query akan menjadi:
dapatkan sailors yang melakukan reservasi paling banyak satu boat bernomor
103. (UNIQUE memeriksa duplikasi tuples; * menyatakan semua attributes)
 Untuk query di atas, secara umum subquery harus dihitung ulang untuk setiap
Sailors tuple, mengapa?
Lebih Jauh mengenai
Set-Comparison Operators
 Sebelumnya telah dibahas penggunaan IN, EXISTS dan
UNIQUE. Juga dpt digunakan NOT IN, NOT EXISTS dan
NOT UNIQUE.
 Selain itu, juag tersedia: op ANY, op ALL, dimana op salah
satu dari , , , , , . Contoh: dapatkan sailors yang
mempunyai rating lebih besar dari rating sailor bernama
Horatio
SELECT *
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname=‘Horatio’)
Menuliskan kembali INTERSECT
Queries dg Menggunakan operator IN
Dapatkan sid dari sailors yang telah melakukan
reservasi baik red boat maupun green boat
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
AND S.sid IN (SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid=R2.sid AND R2.bid=B2.bid
AND B2.color=‘green’)
 Untuk mendapatkan names (bukan sid) dari Sailors yang telah
melakukan reservasi baik red boat maupun green boats, hanya ganti
S.sid dengan S.sname dalam SELECT clause.
 Demikian pula, EXCEPT queries dapat ditulis kembali dengan
menggunakan NOT IN.
Division dalam SQL
Dapatkan sailors yang
telah melakukan reservasi
semua boats
(1)
 Cara yang lebih sulit, tanpa
menggunakan EXCEPT:
(2) SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B)
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid=S.sid))
WHERE NOT EXISTS (SELECT R.bid
FROM Reserves R
Dapatkan sailor S sedemikian rupa
WHERE R.bid=B.bid
sehingga TIDAK terdapat boat yang
AND R.sid=S.sid))
TIDAK direservasi oleh S
Operator
Aggregasi
 Perluasan yang signifikan dari
aljabar relasional
COUNT (*)
COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)
single column
SELECT COUNT (*)
FROM Sailors S
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10
SELECT COUNT (DISTINCT S.rating)
FROM Sailors S
WHERE S.sname=‘Bob’
SELECT AVG (DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10
SELECT S.sname
FROM Sailors S
WHERE S.rating= (SELECT MAX(S2.rating)
FROM Sailors S2)
Dapatkan name dan age dari sailor(s) yang paling tua
 Query pertama ilegal!
(Mengapa?, alasan lihat
penggunaan keyword
GROUP BY)
 Query ketiga ekivalen
dengan query kedua yang
diperbolehkan dalam
SQL/92, tetapi tidak lagi
banyak didukung dalam
beberapa sistem yang ada
pada saat ini
SELECT S.sname, MAX (S.age)
FROM Sailors S
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age =
(SELECT MAX (S2.age)
FROM Sailors S2)
SELECT S.sname, S.age
FROM Sailors S
WHERE (SELECT MAX (S2.age)
FROM Sailors S2) = S.age
GROUP BY & HAVING
 Sejauh ini telah dibahas penggunaan operator aggregasi ke semua
tuples (yang memenuhi kualifikasi) .
Namun, seringkali kita menginginkan utk mengaplikasi-kannya
pada setiap beberapa kelompok (groups) dari tuples.
 Sebagai contoh, perhatikan query:
Dapatkan age dari sailor yang termuda untuk setiap level dari
rating yang ada
 Secara umum, kita tidak tahu berapa jumlah level dari rating yang ada
dalam database, demikian pula berapa nilai-nilai rating dari semua level
yang ada dalam database !
 Jika, misalnya, kita tahu bahwa nilai-nilai rating berada dalam range 1 s.d.
10, maka kita dapat menuliskan 10 queries seperti berikut !
For i = 1, 2, ... , 10:
SELECT MIN (S.age)
FROM Sailors S
WHERE S.rating = i
Queries yang Melibatkan
GROUP BY & HAVING
SELECT
FROM
WHERE
GROUP BY
[HAVING
[DISTINCT] target-list
relation-list
qualification
grouping-list
group-qualification]
 target-list terdiri dari: (a) attribute names, dan (b) “terms” berisikan
operasi-operasi aggregasi (misal, MIN (S.age)).

attribute name dituliskan dlm target-list harus juga menjadi subset
dari grouping-list.

Secara intuitif, setiap tuple jawaban berkorespondensi dengan sebuah group,
dan attribute tersebut harus memiliki sebuah nilai tunggal untuk setiap group.

Sebuah group adalah satu set tuples yang mempunyai nilai yang sama untuk
semua attributes yang ada dalam grouping-list.
Evaluasi Konseptual dari Queries yang
Melibatkan GROUP BY & HAVING
 Cross-product dari relation-list dihitung, tuples yang tidak
memenuhi qualification dibuang, fields “yang tidak diperlukan”
dihapus, dan tuples yang tersisa dipartisi menjadi sejumlah group
berdasarkan nilai attribut yang ada dalam grouping-list.
 term (berupa satu atau lebih operator aggregasi) diaplikasikan pada
tuples dari setiap group.
 group-qualification kemudian diaplikasikan utk mengeliminasi
beberapa groups. Persamaan dlm group-qualification harus
mempunyai single value per group!
 Satu jawaban tuple dihasilkan untuk setiap qualifying group (group
yang memenuhi kondisi).
Dapatkan age dari sailor termuda dengan usia
 18 untuk setiap level rating dan minimal
harus terdiri dari 2 sailors untuk setiap levelnya
SELECT S.rating, MIN (S.age)
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT (*) > 1
 Hanya S.rating dan S.age yang
disebutkan dalam SELECT, GROUP
BY atau HAVING clauses;
attributes lainnya menjadi tidak
diperlukan.
 Kolom ke-2 dari hasil query tidak
mempunyai nama. (Gunakan AS
utk menamakannya!)
sid
22
31
71
64
29
58
rating
1
7
7
8
10
sname rating age
dustin
7
45.0
lubber
8
55.5
zorba
10
16.0
horatio
7
35.0
brutus
1
33.0
rusty
10
35.0
age
33.0
45.0
35.0
55.5
35.0
Relasi Jawaban
rating
7
35.0
Untuk setiap red boat, dapatkan jumlah
reservasi untuk boat tersebut
SELECT B.bid, COUNT (*) AS scount
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
GROUP BY B.bid
 Grouping terhadap join tiga relasi.
 Apa yang akan diperoleh jika B.color=‘red’ dihapus dari
WHERE clause dan menambahkan HAVING clause dengan
kondisi tersebut (B.color=‘red’ )?
SELECT B.bid, COUNT (*) AS scount
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
GROUP BY B.bid
HAVING B.color=‘red’
Dapatkan age dari sailor termuda dengan
usia  18 untuk setiap rating yang minimal
mempunyai 2 sailors (dari sembarang usia)
SELECT S.rating, MIN (S.age)
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating=S2.rating)
 Menunjukkan bahwa HAVING clause juga dapat berisikan
sebuah subquery.
 Apa yang terjadi jika HAVING clause deganti dengan:

HAVING COUNT(*) > 1
Dapatkan ratings yang mempunyai rata-rata age
paling rendah dibandingkan semua ratings yang ada
 Solusi yang SALAH: operasi-operasi aggregasi tidak boleh digunakan
dalam nested query!
SELECT S.rating
FROM Sailors S
WHERE S.age = (SELECT MIN (AVG (S2.age))
FROM Sailors S2
GROUP BY S2.rating)

Solusi yang benar:
SELECT Temp.rating, Temp.avgage
FROM (SELECT S.rating, AVG (S.age) AS avgage
FROM Sailors S
GROUP BY S.rating) AS Temp
WHERE Temp.avgage = (SELECT MIN (Temp.avgage)
FROM Temp)
Null Values
 Nilai-nilai fields dalam sebuah tuple kadang-kadang tidak
diketahui (unknown) (Misal, sebuah nilai rating tidak diberikan)
atau tidak dapat digunakan (inapplicable) (Misal, tidak mempunyai
nama isteri/suami).

SQL menyediakan nilai spesial null utk keadaan-keadaan tersebut.
 Adanya nilai null dapat menimbulkan beberapa komplikasi.
Sebagai contoh:

Operators spesial diperlukan utk melakukan pengecekan apakah nilai null
atau not null.

Diperlukan 3-valued logic (true, false dan unknown).

Bagaimana dengan penggunaan penghubung AND, OR dan NOT?

Makna dari bentukan-bentukan query harus didefinisikan dengan cermat.
(misal, WHERE clause akan mengeliminasi baris-baris yang tidak dievaluasi
dengan nilai true)
Null Values dan Operator Perbandingan serta
Konjungsi AND, OR dan NOT
 Perbandingan dua nilai null menggunakan operator perbandingan , , , , , 
selalu menghasilkan nilai unknown
 SQL menyediakan sebuah operator perbandingan khusus IS NULL atau IS NOT
NULL untuk melakukan pengujian apakah nilai dari suatu kolom adalah NULL
atau NOT NULL
 NOT unknown dievaluasi unknown
 OR dari dua buah argumen akan dievaluasi:
 true jika salah satu argumen bernilai true
 false jika kedua argumen bernilai false
 unknown jika satu argumen dievaluasi false dan argumen lainnya dievaluasi
unkwon
 AND dari dua buah argumen akan dievaluasi:
 false jika salah satu argumen bernilai false
 true jika kedua argumen bernilai true
 unknown jika satu argumen dievaluasi true dan argumen lainnya dievaluasi
unknown
Dampak Null Values pada Bentukan SQL
 Untuk kualifikasi dalam WHERE clause, keberadaan null values
mengakibatkan sembarang baris yang menghasilkan evaluasi false atau
unknown harus dieliminasi

Eliminasi baris-baris yang menghasilkan evaluasi unknown, walaupun tidak kentara
tetapi dapat mempunyai dampak yang signifikan pada SQL querires, terutama nested
queries yang melibatkan EXISTS atau UNIQUE
 Persoalan lain dalam kaitan dengan keberadaan null values adalah
definisi kapan dua baris dalam sebuah nilai relasi dianggap duplikasi

Definisi SQL menyatakan bhw dua baris dinyatakan duplikasi bilamana nilai kolomkolom yang bersesuaian adalah sama atau berisi nilai null

Dalam kenyataan, perbandingan dua buah nilai null menggunakan operator “=“ akan
menghasilkan nilai unknown; dimana untuk kontek duplikasi, perbandingan ini
secara implisit akan diperlakukan sebagai nilai true (yang merupakan anomali !!)
Dampak Null Values
pada Konsepsi SQL (Lanjutan)
 Operasi-operasi aritmatika +, -, *, dan /, semuanya menghasilkan
nilai null jika salah satu argumennya bernilai null
 Null values dapat menimbulkan kelakukan yang tidak diharapkan
untuk operasi-operasi aggregasi

COUNT (*) memperlakukan null values seperti halnya nilai-nilai lainnya
(ikut diperlhitungkan)

Operasi-operasi aggregasi lainnya (COUNT, SUM, AVG, MIN, MAX, dan
variasi penggunaan DISTINCT) mengabaikan null values
Null Values dan Outer Joins
 SQL menyediakan operasi yang disebut outer joins yang merupakan varian dari
operasi join yang bergantung pada null values

Dalam operasi [inner] join, tuples yang tidak memenuhi kondisi join tidak muncul dalam hasil

Dalam outer join, baris-baris yang tidak memenuhi kondisi join muncul dalam hasil dengan
kolom-kolom yang tidak memenuhi kondisi diberikan null values
 Terdapat tiga variasi outer join:

Left outer join, baris-baris dari relasi pertama yang tidak mempunyai matching dengan baris
dalam relasi kedua muncul dalam hasil

Right outer join, baris-baris dari relasi kedua yang tidak mempunyai matching dengan baris
dalam relasi pertama muncul dalam hasil

Full outer join, baris-baris yang tidak mempunyai matching dalam relasi kedua muncul dalam
hasil
 Dalam SQL, jenis join yang diinginkan dispesifikasikan dalam FROM
sid clause:
bid
SELECT S.Sid, R.bid
FROM
Sailors S NATURAL LEFT OUTER JOIN Reserves R
22
31
58
101
null
103
Tidak Membolehkan Null Values
 Null values dapat tidak dibolehkan untuk muncul dengan
menspesifikasikan NOT NULL sebagai bagian dari definisi fieldfield yang diinginkan. Contoh:
sname CHAR(20)NOT NULL
 Selain itu, fields dalam primary key tidak diperbolehkan bernilai
null

Untuk ini terdapat constraint implisit NOT NULL untuk setiap field yang
dinyatakan dalam PRIMARY KEY constraint
Pembahasan null values dalam bab ini jauh dari
lengkap. Bagi yang ingin mengetahuinya secara
lebih rinci dapat mengacu ke beberapa buku yang
secara khusus membahas tentang SQL
Integrity Constraints (Review)
 Sebuah IC menjelaskasn kondisi yang harus dipenuhi oleh setiap
legal instance dari suatu relasi.

Inserts/deletes/updates yang melanggar IC tidak diperbolehkan.

Dapat digunakan utk menjamin semantik dari aplikasi (misal, sid adalah
sebuah key), atau mencegah terjadinya inkonsistensi (misal, sname hrs
berupa string, age harus < 200)
 Jenis IC: Domain constraints, primary key constraints, foreign key
constraints, general constraints.

Domain constraints: Nilai-nilai field harus dari tipe data yang benar. Harus
selalu dipaksakan .
Domain Constraints & Distinct Types
 Sebuah domain baru dapat didefinisikan menggunakan statement
CREATE DOMAIN dan CHECK constranits
CREATE DOMAIN ratingval INTEGER DEAFULT 1
CHECK (VALUE >= 1 AND VALUE <= 10)
 Begitu sebuah domain telah didefinisikan, nama domain dpt digunakan
dalam deklarasi sebuah skema
 Sebuah distinct type dapat didefiniskan dalam SQL/99:
CREATE TYPE ratingtype is INTEGER

Nilai-nilai dengan tipe ratingtype dapat dibandingkan satu dengan lainnya

Namun, nilai-nilai dengan tipe ratingtype akan diperlakukan berbeda dengan
nilai tipe-tipe lainnya yang sejenis (termasuk nilai-nilai dari tipe sumbernya,
INTEGER)
CREATE TABLE Sailors
General Constraints
( sid INTEGER,
sname CHAR(10),
pada Satu Tabel Tunggal
rating INTEGER,
 Bermanfaat
age REAL,
bilamana
PRIMARY KEY (sid),
melibatkan lebih
CHECK ( rating >= 1
banyak general
AND rating <= 10 )
IC
CREATE TABLE Reserves
 Dapat
( sname CHAR(10),
menggunakan
bid INTEGER,
quries untuk
day DATE,
mengekspresika
PRIMARY KEY (bid,day),
n constraints
CONSTRAINT noInterlakeRes
 Constraints
CHECK (`Interlake’ <>
dapat diberi
( SELECT B.bname
nama.
FROM Boats B
WHERE B.bid=bid)))
Constraints utk Multiple Relations
Nampak aneh
dan salah.
Berkaitan dg
Sailors, tapi
melibatkan Boats!
 Jika Sailors
kosong, jumlah
Boats tuples
dapat berupa
sesuatu dan > 100
!
CREATE TABLE Sailors
( sid INTEGER,
Constraint:
sname CHAR(10),
Jumlah boats
rating INTEGER,
plus jumlah
age REAL,
Sailors hrs < 100
PRIMARY KEY (sid),
CHECK
((SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B))
< 100 )
adalah solusi
yang benar;
tidak
diasosiasikan
dengan sutau
tabel tertentu
CREATE ASSERTION smallClub
CHECK(
((SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B))


ASSERTION
< 100)
Triggers
 Trigger: procedure yang dipanggil secara otomatis oleh DBMS
sebagai respon terhadap perubahan-perubahan dalam database,
khusunya yang dispesifikasikan oleh DBA
 Deskripsi trigger terdiri dari 3 bagian:

Event: Perubahan pada database yang mengaktifkan trigger

Condition: Query atau pengujian yang dijalankan bilamana trigger
diaktifkan

Action: Prosedur yang dijalankan bilamana trigger diaktifkan dan
kondisinya bernilai true
 Database yang memiliki satu set triggers disebut active database
Triggers dalam SQL/99
 Basic events yang dapat dispesifikasikan untuk mentrigger sejumlah aturan
(rules) adalah perintah-perintah SQL UPDATE standar: INSERT, DELETE,
dan UPDATE.
 Untuk kasus UPDATE, attributes yang akan di-update dapat dinyatakan secara
eksplisit
 Menyediakan row-level trigger dan statement-level trigger.

Menggunakan clause FOR EACH ROW utk row-level, dan cluase FOR EACH
STATEMENT utk statement-level

Dapat menggunakan tuple/table variable name untuk mengacu ke tuples yang lama
(old) dan baru (new)

Dalam row-level trigger, clause REFERENCING digunakan untuk mengacu ke
sebuah baris baru (yang baru disisipkan atau baru di-update) yang dinyatakan sbg
new row variable name, sedang old row variable name digunakan utk mengacu ke
sebuah baris lama (baris yang telah dihapus atau tuple lama sebelum dilakukan
update)

Dalam statement-level trigger, clause REFERENCING digunakan untuk mengacu
ke tabel dari semua tuples baru (yang baru disisipkan atau baru di-update) sbg new
table variable name, sedang old table variable name digunakan utk mengacu ke
tabel dari semua tuples lama (tuples yang telah dihapus atau tuples lama sebelum
dilakukan update)
Row-Level Trigger: Contoh-1
 Tables:
 Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
 Department (Dname, DNO, Total_Sal, Manager_SSN)
CREATE TRIGGER TotalSal_1
AFTER INSERT ON Employee
REFERENCING OLD ROW AS O, NEW ROW AS N
FOR EACH ROW
WHEN (N.DNO IS NOT NULL)
UPDATE Department
SET Total_Sal = Total_Sal + N.Salary
WHERE DNO = N.DNO;
Row-Level Trigger: Contoh-2
 Tables:
 Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
 Department (Dname, DNO, Total_Sal, Manager_SSN)
CREATE TRIGGER TotalSal_2
AFTER UPDATE OF DNO ON Employee
REFERENCING OLD ROW AS O, NEW ROW AS N
FOR EACH ROW
BEGIN
UPDATE Department
SET Total_sal = Total_Sal + N.Salary
WHERE DNO = N.DNO;
UPDATE Department
SET Total_sal = Total_Sal - O.Salary
WHERE DNO = O.DNO
END;
Row-Level Trigger: Contoh-3
 Tables:
 Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
 Department (Dname, DNO, Total_Sal, Manager_SSN)
CREATE TRIGGER TotalSal_3
AFTER DELETE ON Employee
REFERENCING OLD ROW AS O
FOR EACH ROW
WHEN (O.DNO IS NOT NULL)
UPDATE Department
SET Total_sal = Total_Sal - O.Salary
WHERE DNO = O.DNO
END;
Row-Level Trigger: Contoh-4
Tables:
 Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
 Department (Dname, DNO, Total_Sal, Manager_SSN)
CREATE TRIGGER Inform_Supervisor_1
BEFORE INSERT OR UPDATE OF Salary, Supervisor_SSN ON EMPLOYEE
REFERENCING OLD ROW AS O, NEW ROW AS N
FOR EACH ROW
WHEN
(N.Salary > ( SELECT Salary FROM Employee
WHERE SSN = N.Supervisor_SSN
)
)
inform_supervisor (N.Supervisor_SNN, N.SSN);
//Diasumsikan “inform_supervisor” sebagai sebuah external procedure untuk
memberikan notifikasi kepada supervisor
Statement-Level Trigger: Contoh-1
 Tables:
 Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
 Department (Dname, DNO, Total_Sal, Manager_SSN)
CREATE TRIGGER TotalSal_4
AFTER UPDATE OF Salary ON EMPLOYEE
REFERENCING OLD TABLE AS O, NEW TABLE AS N
FOR EACH STATEMENT
WHEN
EXISTS ( SELECT * FROM N WHERE N.DNO IS NOT NULL ) OR
EXISTS ( SELECT * FROM O WHERE O.DNO IS NOT NULL )
UPDATE Department AS D
SET D.Total_Sal =D.Total_Sal
+ ( SELECT SUM (N.Salary) FROM N WHERE D.DNO = N.DNO )
- ( SELECT SUM (O.Salary) FROM O WHERE D.DNO = O.DNO )
WHERE DNO IN ( (SELECT DNO FROM N)
UNION
(SELECT DNO FROM O)
);
Statement-Level Trigger: Contoh-2
 Table:
 Sailors (sid, name, age, rating)
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON Sailors
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE N.age <= 18
Statement-Level Trigger: Contoh-3
 Table:
 Students (sid, name, age, gpa)
CREATE TRIGGER setCount
AFTER INSERT ON Students
REFERENCING NEW TABLE AS InsertedTuples
FOR EACH STATEMENT
INSERT
INTO StatisticsTable(ModifiedTable,
ModificationType, Count)
SELECT ‘Students’, ‘Insert’, COUNT *
FROM InsertedTuples I
WHERE I.age < 18
Active Databases: Mengapa Triggers
Dpt Menjadi Sulit Dipahami?
 Dlm sebuah active database system, pada saat DBMS akan
mengeksekusi suatu statement yang memodifikasi database, DBMS
akan memeriksa apakah beberapa trigger diaktifkan oleh statement tsb.
 Jika ya, maka DBMS memproses trigger dengan cara mengevaluasi bagian
kondisinya, dan (jika evalusai kondisi bernilai true) maka DBM akan
mengeksekusi bagian action dari trigger
 Jika sebuah statement mengaktifkan lebih dari satu trigger, secara
tipikal DBMS akan memproses semuanya dengan urutan sembarang.
 Sebuah trigger dpt mengaktifkan trigger lainnya, yang pada gilirannya trigger
yang diaktifkan dpt mengaktifkan trigger yang sama yang disebut “recursive
triggers”.
 Aktivasi triggers yang membentuk “rantai” dapat menimbulkan kesulitan untuk
memahami efek yang dapat ditimbulkannya
Active Databases:
Constraints v.s. Triggers
 Penggunaan triggers yang umum adalah untuk memelihara
konsistensi database.
 Untuk ini kita harus selalu mempertimbangkan bahwa penggunaan sebuah
IC (misal, FK constraint) dapat memenuhi tujuan yang sama
 Makna dari sebuah constraint tidak didefinisikan secara
operasional (tidak seperti pengaruh sebuah trigger)
 Sifat ini menjadikan constraint lebih mudah untuk dipahami, dan memberi
peluang bagi DBMS untuk mengoptimasi eksekusinya
 Constraint juga mencegah terjadinya inkonsistensi data oleh suatu
statement. Beda dengan trigger yang diaktivasi oleh suatu statement yang
spesifik (INSERT, DELETE, atau UPDATE). Hal ini menjadikan
constraint lebih mudah dipahami
 Sebaliknya, triggers memungkinkan kita untuk memelihara
database integrity dalam cara yang lebih fleksibel.
Rangkuman
 SQL menjadi faktor yang penting pada saat awal penerimaan model
relasional, karena lebih natural dibandingkan bahasa-bahasa
database sebelumnya yang bersifat prosedural (procedural query
languages), seperti aljabar relasional.
 SQL lengkap secara rasional; bahkan dalam kenyataannya memiliki
daya ekspresi yang signifikan dibandingkan aljabar relasional
 Bahkan, queries yang dapat dinyatakan dalam aljabar relasional
seringkali dpt dinyatakan dengan lebih natural dalam SQL.
Rangkuman (Lanjutan)
 Terdapat banyak alternatif utk menulsikan sebuah query.
Optimizer harus mencari rencana evaluasi yang paling efisien

Dalam praktek, pengguna perlu memikirkan bagaimana queries
dioptimalkan dan dievaluasi untuk memberikan hasil terbaik
 Nilai NULL untuk nilai-nilai field yang tidak diketahui
(unknown) dapat menimbulkan banyak komplikasi
 SQL memungkinkan untuk menspesifikasikan berbagai jenis
integrity constraints
 Triggers memberikan respon otomatis terhadap terjadinya
perubahan-perubahan dalam database