SQL: Queries, Programming, Triggers - E

Database Management Systems
Bab 5
SQL: Queries,
Constraints & Triggers
(Chap. 5 – Ramakrishnan)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 1/50
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?
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 2/50
Overview




Data Manipulation Language (DML): Subset SQL yang dapat digunakan 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).
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 3/50
Overview (Lanjutan)



Client-Server Execution & Remote Database Access:
Perintah-perintah 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)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 4/50
Reserves (R1)
Contoh Instances

sid bid
22 101
58 103
Contoh-contoh
sebagian instances Sailor (S1)
sid sname
dari relasi Sailors,
Boats, dan Reserves
22 dustin
akan digunakan
31 lubber
dalam contohcontoh spesifikasi
58 rusty
SQL
Sailor(S2)
Boat(B1)
bid bname
color
101 Interlake blue
103 Clipper green
DBMS – Arif Djunaidy – FTIF ITS
sid
28
31
44
58
day
10/10/96
11/12/96
rating
7
8
10
age
45.0
55.5
35.0
sname rating age
yuppy
9
35.0
lubber
8
55.5
guppy
5
35.0
rusty
10
35.0
Bab 5 - 5/50
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!
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 6/50
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.
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 7/50
Contoh Hasil Evaluasi Konseptual
Range variables
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
(sid)
sname rating age
(sid) bid day
22
dustin
7
45.0
22
101 10/10/96
22
dustin
7
45.0
58
103 11/12/96
31
lubber
8
55.5
22
101 10/10/96
31
lubber
8
55.5
58
103 11/12/96
58
rusty
10
35.0
22
101 10/10/96
58
rusty
10
35.0
58
103 11/12/96
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 8/50
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
DBMS – Arif Djunaidy – FTIF ITS
Namun demikian,
merupakan good
style untuk selalu
menggunakan
range variables!
Bab 5 - 9/50
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?

DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 10/50
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 peersamaan
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.
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 11/50
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.
Jika OR diganti dg AND dlm
query pertama, apa yang akan
diperoleh?
Juga tersedia: EXCEPT untuk
menghitung set difference dari
sembarang dua union-compatible
sets of tuples
(Apa yang akan diperoleh jika
UNION diganti dengan
EXCEPT?)
DBMS – Arif Djunaidy – FTIF ITS
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 B.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’
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’
Bab 5 - 12/50
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)
DBMS – Arif Djunaidy – FTIF ITS
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’
Bab 5 - 13/50
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.
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 14/50
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?
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 15/50
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’)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 16/50
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.
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 17/50
Division dalam SQL
Dapatkan sailors yang
telah melakukan reservasi
semua boats

Cara yang lebih sulit, tanpa
menggunakan EXCEPT:
(2) SELECT S.sname
FROM Sailors S
(1)
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 B.bid
FROM Boats B
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 18/50
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)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 19/50
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
DBMS – Arif Djunaidy – FTIF ITS
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
Bab 5 - 20/50
GROUP BY & HAVING


Sejauh ini telah dibahas penggunaan operator aggregasi ke
semua tuples (yang memenuhi kualifikasi) .
Namun, seringkali kita menginginkan utk mengaplikasikannya 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:
DBMS – Arif Djunaidy – FTIF ITS
SELECT MIN (S.age)
FROM Sailors S
WHERE S.rating = i
Bab 5 - 21/50
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.
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 22/50
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 groupinglist.
term (berupa satu atau lebih operator aggregasi)
diaplikasikan pada tuples dari setiap group.
group-qualification kemudian diaplikasikan utk
mengeliminasi beberapa groups. Persamaan dlm groupqualification harus mempunyai single value per group!
Satu jawaban tuple dihasilkan untuk setiap qualifying group
(group yang memenuhi kondisi).
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 23/50
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!)
DBMS – Arif Djunaidy – FTIF ITS
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
Bab 5 - 24/50
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’
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 25/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 26/50
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)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 27/50
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)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 28/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 29/50
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
kolom-kolom 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 !!)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 30/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 31/50
Null Values dan Outer Joins

SQL menyediakan operasi yang disebut outer joins yang merupakan
varian dari operasi join yang bergantung pada null values



Terdapat tiga variasi outer join:




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
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
clause:
sid
bid
SELECT S.Sid, R.bid
FROM
Sailors S NATURAL LEFT OUTER JOIN Reserves R
DBMS – Arif Djunaidy – FTIF ITS
22
31
58
101
null
103
Bab 5 - 32/50
Tidak Membolehkan Null Values

Null values dapat tidak dibolehkan untuk muncul dengan
menspesifikasikan NOT NULL sebagai bagian dari definisi
field-field 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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 33/50
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 .
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 34/50
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 nilainilai dari tipe sumbernya, INTEGER)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 35/50
CREATE TABLE Sailors
General Constraints
( sid INTEGER,
sname CHAR(10),
pada Satu Tabel Tunggal
rating INTEGER,
 Bermanfaat
age REAL,
bilamana
PRIMARY KEY (sid),
melibatkan
CHECK ( rating >= 1
lebih banyak
AND rating <= 10 )
general IC
CREATE TABLE Reserves
 Dapat
( sname CHAR(10),
menggunakan
bid INTEGER,
quries untuk
day DATE,
mengekspresik
PRIMARY KEY (bid,day),
an constraints
CONSTRAINT noInterlakeRes
 Constraints
CHECK (`Interlake’ <>
dapat diberi
( SELECT B.bname
nama.
FROM Boats B
WHERE B.bid=bid)))
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 36/50
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
DBMS – Arif Djunaidy – FTIF ITS
< 100)
Bab 5 - 37/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 38/50
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)
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 39/50
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;
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 40/50
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;
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 41/50
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;
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 42/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 43/50
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)
);
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 44/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 45/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 46/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 47/50
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.
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 48/50
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.

DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 49/50
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
DBMS – Arif Djunaidy – FTIF ITS
Bab 5 - 50/50