teknik informatika universitas trunojoyo madura …dikenal sebagai tabel dasar, dan tabel turunan,...

1

TUGAS I

VIEWS

BASIS DATA II

Disusun Oleh :

Kelompok BD2-B5

Bisma alfian imanata 100411100014

Fitria Dwindah mukhtalifah 100411100081

Nur chandra sundara 100411100089

Shohib 100411100101

Dosen Pengampu : Noor Ifada

TEKNIK INFORMATIKA

UNIVERSITAS TRUNOJOYO MADURA

MARET 2012

2

DAFTAR ISI

Cover .............................................................................................................. 1

Daftar Isi ......................................................................................................... 2

26.1 Pendahuluan .............................................................................................. 3

26.2 Membuat Views . ...................................................................................... 3

26.3 Nama Kolom Pada Views . ........................................................................ 7

26.4 Merperbarui Views: WITH CHECK OPTION .......................................... 8

26.5 Pilihan Dari Views .................................................................................... 11

26.6 Menghapus Views .................................................................................... 12

26.7 Views and the Catalog .............................................................................. 13

26.8 Kekurangan pada Perubahan Views .......................................................... 14

26.9 Cara Kerja Statemen View ....................................................................... 16

26.10 Area Applikasi PadaViews ..................................................................... 19

26.11 Jawaban ................................................................................................. 24

26.1 PENDAHULUAN

MySQL mendukung dua jenis tabel: tabel nyata, umumnya

dikenal sebagai tabel dasar, dan tabel turunan, juga disebut views. Tabel

dasar diciptakan dengan CREATE TABLE pernyataan dan adalah satu-

satunya di mana data dapat disimpan. Contohnya adalah PLAYERS dan

TEAMS tabel dari database klub tennis.

Sebuah tabel diturunkan, atau melihat, menyimpan ada baris itu

sendiri. Sebaliknya, ia berfungsi sebagai rumus atau

formula untuk menggabungkan data tertentu dari tabel dasar untuk

membuat"virtual" tabel. Itu kata virtual digunakan karena isi dari pandangan

hanya ada bila digunakan dalam pernyataan. Pada saat itu, MySQL mengambil

rumus yang membentuk view formula, mengeksekusinya, dan menyajikan

pengguna dengan apa yang lihatnyamenjadi tabel nyata.

Bab ini menjelaskan cara view diciptakan dan bagaimana view dapat

digunakan. Beberapa aplikasi yang berguna termasuk menyederhanakan laporan

rutin dan reorganisasi tabel.Dua bagian mencakup pembatasan query dan

memperbarui view.

26.2 MEMBUAT VIEW

View di buat dengan statemen CREATE VIEW.

DEFINISI

<create view statement> ::=

CREATE [ OR REPLACE ] VIEW <view name> [ <column list> ]

AS <table expression>

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Contoh 26.1: Buat tampilan yang memegang nama-nama kota semua dari tabel

PLAYERS dan menampilkan isi virtual pandangan baru.

CREATE VIEW TOWNS AS

SELECT DISTINCT TOWN

FROM PLAYERS

SELECT *

FROM TOWNS

Hasilnya :

4

Contoh 26.2: Buatlah View yang menampilkan nomor pemain dan nomor liga

dari semua pemain yang memiliki sejumlah liga dan menampilkan isi virtual

view ini.

CREATE VIEW CPLAYERS AS

SELECT PLAYERNO, LEAGUENO

FROM PLAYERS

WHERE LEAGUENO IS NOT NULL

SELECT *

FROM CPLAYERS

Hasilnya :

Statemen CREATE VIEW membuat dua tampilan: TOWNS dan CPLAYERS.

Sebuah ekspresi tabel mendefinisikan isi view masing-masing dan membentuk

rumus view. Dua view ini dapat dilihat seperti tabel dasar, dan view

CPLAYERS bahkan diperbarui (lihat Bagian 26.8).

Contoh 26.3: Carilah pemain dan nomor liga untuk pemain yang nomor

kompetisinya antara 6 dan 44.

SELECT *

5

FROM CPLAYERS

WHERE PLAYERNO BETWEEN 6 AND 44

Hasilnya :

Jika kita tidak menggunakan tampilan CPLAYERS untuk pertanyaan

yang sama tetapi diakses tabel PLAYERS langsung, kita akan membutuhkan

statemen SELECT karena lebih kompleks untuk mengambil informasi yang

sama:

SELECT PLAYERNO, LEAGUENO

FROM PLAYERS

WHERE LEAGUENO IS NOT NULL

AND PLAYERNO BETWEEN 6 AND 44

Contoh 26.4: Hapus pemain yang nomor kompetisi liganya adalah 7060.

DELETE

FROM CPLAYERS

WHERE LEAGUENO = '7060'

Bila statemen ini dieksekusi, ia menghapus baris di data tabel

(PLAYERS)

di mana kolom LEAGUENO sama dengan 7060.

Isi view tidak disimpan, tetapi bukan berasal ketika melihat ini

dirujuk. Ini berarti bahwa, menurut definisi, selalu sejalan dengan

isi dari tabel dasar. Setiap update yang dibuat untuk data dalam tabel dasar

segera

terlihat dalam sebuah view. Pengguna tidak perlu khawatir tentang integritas

isi dari view, selama integritas dari tabel dasar tetap terjaga. kita

kembali ke subjek memperbarui view dalam Bagian 26.8.

Tampilan lain dapat ditentukan dalam formula view. Dengan kata lain,

kita mungkin membutuhkan view bersarang.

Contoh 26.5: Buat tampilan yang menampilkan pemain di semua kompetisi

yang nomor pemainnya antara 6 dan 27, dan menampilkan isi virtual view ini

CREATE VIEW SEVERAL AS

6

SELECT *

FROM CPLAYERS

WHERE PLAYERNO BETWEEN 6 AND 27

SELECT *

FROM SEVERAL

Hasilnya :

Dalam kebanyakan kasus, ekspresi tabel mengambil data dari tabel dasar

atau views, namun, ekspresi tabel dapat memberikan hasil tanpa mengakses

tabel (lihat Contoh 7.34). Oleh karena itu, views tidak perlu didefinisikan pada

tabel dasar. Lihat contoh ini:

Contoh 26.6: Buatlah view dimana DIGIT 0 sampai 9 yang muncul, dan

tampilkan hasilnya.

CREATE VIEW DIGITS AS

SELECT 0 DIGIT UNION SELECT 1 UNION

SELECT 2 UNION SELECT 3 UNION



SELECT 8 UNION SELECT 9

SELECT * FROM DIGITS

Hasilnya :

Di balik kata CREATE, kita dapat menentukan OR REPLACE. Jika nama

tampilan sudah ada, rumus view baru menimpa yang lama.

7

26.3 NAMA KOLOM PADA VIEWS

Nama-nama kolom dalam tampilan biasa untuk nama kolom pada klausa

SELECT. Misalnya, dua kolom dalam tampilan SEVERAL disebut

PLAYERNO dan

LEAGUENO. Views mewarisi nama kolom. Anda dapat juga secara eksplisit

mendefinisikan nama kolom views.

Contoh 26.7: Buatlah view yang menampilkan jumlah pemain, nama, inisial,

dan

tanggal kelahiran setiap pemain yang tinggal di Stratford.

CREATE VIEW STRATFORDERS (PLAYERNO, NAME, INIT, BORN)

AS

SELECT PLAYERNO, NAME, INITIALS, BIRTH_DATE

FROM PLAYERS

WHERE TOWN = 'Stratford'

SELECT *

FROM STRATFORDERS

WHERE PLAYERNO > 90

Perhatikan nama kolom yang di hasilkan :

Nama kolom baru yang permanen. Anda tidak lagi dapat merujuk pada

kolom PLAYERNO atau BIRTH_DATE dalam tampilan STRATFORDERS.

MySQL memungkinkan ekspresi dalam klausa SELECT formula views

menjadi fungsi atau perhitungan bukan spesifikasi kolom. Nama kolom adalah

sama dengan ekspresi.

Contoh 26.8: Untuk setiap kota, Buatlah view yang menampilkan nama tempat

dan nomor pemain yang tinggal di kota itu dan kemudian tampilkan isi dari

view.

CREATE VIEW RESIDENTS AS

SELECT TOWN, COUNT(*)

FROM PLAYERS

GROUP BY TOWN

SELECT TOWN, "COUNT(*)"

FROM RESIDENTS

8

Hasilnya :

Penjelasan: Pandangan ini memiliki nama kolom dua: TOWN dan COUNT (*).

Perhatikan bahwa COUNT nama (*) harus diapit oleh tanda kutip ganda.

Latihan 26.1: Buat tampilan yang disebut NUMBERPLS yang berisi semua tim

angka dan jumlah total pemain yang telah bermain untuk tim itu. (Asumsikan

bahwa setidaknya satu pemain telah berkompetisi untuk setiap tim.)

Latihan 26,2: Buat tampilan yang disebut WINNER yang berisi jumlah dan

nama setiap pemain yang, untuk setidaknya satu tim, telah memenangkan satu

pertandingan.

Latihan 26,3: Buat tampilan yang disebut TOTALS yang mencatat jumlah total

hukuman untuk setiap pemain yang telah terjadi setidaknya satu penalti.

26.4 UPDATING VIEWS: WITH CHECK OPTION

Kita telah melihat sejumlah contoh di mana tabel yang mendasarinya

sedang diupdate melalui metode view. Hati-hati dalam menggunakan metode

updating view, karena dapat menghasilkan hasil yang tak terduga. Contoh

berikut menggambarkan situasi ini.

Contoh 26,9: Buat tampilan semua pemain yang lahir lebih awal dari 1960.

CREATE VIEW VETERANS AS

SELECT *

FROM PLAYERS

WHERE BIRTH_DATE < '1960-01-01'

Hasil running :

9

Sekarang kita ingin mengubah tanggal lahir veteran pemain dengan nomor 2 dari

1 September 1948 menjadi 1 September 1970. Querynya seperti dibawah ini :

UPDATE VETERANS

SET BIRTH_DATE = '1970-09-01'

WHERE PLAYERNO = 2

Hasil running :

Proses update sudah benar. Tanggal lahir pemain nomor 2 di table PLAYERS

telah berubah. Namun, akibat dari proses update ini adalah pemain nomor 2

tidak muncul lagi jika kita melihat view menggunakan perintah SELECT. Hal

ini dikarenakan pemain tidak lagi memenuhi kondisi yang ditentukan dalam

rumus view setelah update terjadi. Jika Anda memperluas definisi view

menggunakan WITH CHECK OPTION, MySQL memastikan bahwa efek yang

tidak dikehendaki tidak akan muncul. Definisi view kemudian menjadi:

CREATE VIEW VETERANS AS

SELECT *

FROM PLAYERS

WHERE BIRTH_DATE < '1960-01-01'

WITH CHECK OPTION

Hasil running :

Jika tampilan yang digunakan mencakup klausa with check options, semua

perubahan pada tampilan dengan menggunakan pernyataan UPDATE, INSERT,

dan DELETE diperiksa untuk validitas:

10

Sebuah pernyataan UPDATE adalah benar jika baris yang diupdate masih

termasuk konten (virtual) dari isi tampilan(view).

Sebuah pernyataan INSERT adalah benar jika baris baru yang diupdate masih

termasuk konten (virtual) isi dari tampilan(view).

Sebuah pernyataan DELETE adalah benar jika baris yang dihapus masih

termasuk (virtual) isi dari tampilan(view).

Seperti yang disebutkan sebelumnya, view dapat diulang, atau dengan

kata lain, view bisa ditumpuk di atas view lain. Anda mungkin bertanya-tanya

sejauh mana pengecekan melalui WITH CHECK OPTION dapat dilakukan. Jika

kita melakukan cek dengan WITH CASCADED CHECK OPTION, maka semua

tampilan (view) akan tercek. Ketika WITH LOCAL CHECK

OPTIONdigunakan, LOCAL CHECK OPTION memeriksa kondisi hubungan

dalam tampilan yang sedang diperbarui merupakan satu-satunya kondisi yang

diperiksa. CASCADE merupakan pilihan default.

Example 26.10: Buat tampilan semua pemain yang lahir lebih awal dari tahun

1960 dan tinggal di kota Ingglewood.

CREATE VIEW INGLEWOOD_VETERANS AS

SELECT *

FROM VETERANS

WHERE TOWN = 'Inglewood'

WITH CASCADED CHECK OPTION

Hasil Running :

Tidak muncul, karena dalam View VETERANS tidak ada pemain yang tinggal

di Ingglewood.

Tetapi ketika kita mengubah SELECT *FROM VETERANS menjadi SELECT

* FROM PLAYERS, maka akan muncul tampilan seperti di bawah ini.

Penjelasan: Jika kita menggunakan pernyataan INSERT untuk

menambahkan pemain dalam tampilan ini, ia harus tinggal di Inglewood dan

harus telah lahir lebih awal dari 1 Januari 1960. Ketika kita tidak menggunakan

Cascade, setiap pemain yang kami tambahkan ke table

INGLEWOOD_VETERANS harus tinggal di Inglewood. MySQL tidak lagi

11

melakukan cek itu. with check options hanya dapat digunakan dalam

hubungannya dengan tampilan yang dapat diperbarui sesuai dengan aturan yang

tercantum dalam Bagian 26.8.

26.5 PILIHAN DARI VIEWS

Anda dapat menentukan pilihan khusus seperti hak istimewa dan metode

pengolahan untuk setiap view.ON

<create view statement> ::=

CREATE [ OR REPLACE ]

[ DEFINER = { <user name> | CURRENT_USER } ]

[ SQL SECURITY { DEFINER | INVOKER } ]

[ ALGORITHM = { MERGE | TEMPTABLE | UNDEFINED } ]

VIEW <viewname> [ <column list> ] AS <table expression>


Dengan definer option kita dapat mengindikasikan atau mengetahui creator atau

definer yang membuat tampilan itu. Apabila pilihan itu tidak ditentukan, user

yang menciptakan tampilan itu adalah definer. Kita bisa mengganti ini dengan

membuat tampilan untuk pengguna SQL lain

Example 26.11: Buat tampilan dengan user JACO sebagai definernya.

CREATE DEFINER = 'JACO'@'%' VIEW JACO_VIEW AS

SELECT *

FROM PLAYERS

WHERE PLAYERNO > 100

Menentukan istilah CURRENT_USER sebagai definer, memiliki hasil

yang sama seperti menghilangkan pilihan definer. Seorang user mungkin

memiliki hak untuk tampilan query, tetapi apa yang terjadi jika tampilan table

query untuk user yang sama tidak memiliki hak untuk melakukan SELECT?

SQL SECURITY OPTION yang menentukan hasilnya. Jika SQL SECURITY

OPTION belum ditentukan, aturannya adalah user yang membuat tampilan harus

dapat melakukan SELECT pada table query. Misalnya, jika tampilan V1 pada

table query T1, definer harus bisa melakukan perintah SELECT untuk table T1.

Untuk pengguna lainnya dari V1 tidak perlu melakukan hal yang sama dengan

definer tadi. SQL SECURITY option tidak sama dengan SQL SECURITY

DEFINER. Jika kita spesifikasikan SQL SECURITY INVOKER, tampilan dari

user harus secara eksplisit harus bisa dikenali secara mudah untuk melakukan

akses table. Oleh karena itu, setiap user query V1 harus bisa melakukan perintah

SELECT pada table T1.

12

ALGORITMA option menunjukkan bagaimana view harus diproses

secara internal. Ada dua metode yang bisa dipakai untuk memproses pernyataan

view. Metode pertama disebut MERGE, salah satu statement query yaitu

SELECT, dikombinasikan dengan rumus view. Akibatnya, salah satu dari

pernyataan SELECT yang dikombinasikan, diproses. Dengan metode

TEMPTABLE, perintah SELECT pada view diproses dalam dua tahap. Selama

langkah pertama diproses, hasil sementara dari rumus view ditentukan dan di

simpan dalam table. Pada langkah ke 2, pernyataan SELECT di jalankan sampai

ditemukan hasil sementara. Apabila ALGORITMA option belum ditentukan,

atau jika sudah di atur agar tidak terdefinisi, maka MySQL akan menentukan

metode mana yang akan diterapkan.

Example 26.12: Buat tampilan proses dengan menggunakan metode MERGE

untuk akses setiap user

CREATE SQL SECURITY INVOKER

ALGORITHM = MERGE

VIEW SIMPLE_VIEW AS

SELECT PLAYERNO

FROM PLAYERS

WHERE PLAYERNO > 100

Semua option termasuk creator dan view formula, dapat di ubah dengan

menggunakan pernyataan ALTER USER.

<alter view statement> ::=

ALTER

[ DEFINER = { <user name> | CURRENT_USER } ]

[ SQL SECURITY { DEFINER | INVOKER } ]

[ ALGORITHM = { MERGE | TEMPTABLE | UNDEFINED } ]

VIEW <view name> [ <column list> ] AS <table expression>


26.6 MENGHAPUS VIEWS

DROP VIEW digunakan untuk menghapus tampilan. Dengan

menggunakan statement ini, setiap referensi tampilan yang dihapus, maka

tampilan yang lainnya akan secara otomatis terhapus. Ketika base table di drop,

maka semua tampilan yang sudah ditetapkan maupun yang belum ditetapkan,

juga akan ter drop juga.

13

<drop view statement> ::=

DROP VIEW [ IF EXISTS ] <table specification>

[ , <table specification> ]...

[ RESTRICT | CASCADE ]

Example 26.13: hilangkan tampilan CPLAYERS

Ketika ada spesifikasi tabel yang ditentukan, maka tidak ada pesan error yang

muncul. RESTRICT dan CASCADE pada script di atas tidak memiliki efek

apapun jika tidak ada spesifikasi table yang ditentukan.

26.7 VIEWS DAN CATALOG

Informasi tentang views tercatat di berbagai tabel. Pada tabel VIEWS,

sebuah baris menyimpan masing-masing view. Kolom VIEW_ID merupakan

primary key dari tabel katalog ini. Kolom VIEW_NAME dan CREATOR

merupakan sebuah alternate key.

Nama Kolom Tipe Data Deskripsi

VIEW_CREATOR CHAR Nama dari database

yang termasuk view

VIEW_NAME CHAR Nama dari view

CREATE_TIMESTAMP TIMESTAMP Tanggal saat view

dibuat; tapi, kolom

ini tidak terdapat

pada MYSQL

WITHCHECKPOT CHAR Bernilai YES jika

view didefinisikan

dengan WITH

CHECK,

CASCADED, atau

LOCAL OPTION;

sebaliknya, nilainya

adalah NO

IS_UPDATABLE CHAR Bernilai YES jika

view bisa diperbarui;

sebaliknya, nilainya

adalah NO

14

COMMENT CHAR Komentar

dimasukkan dengan

menggunakan

statement

COMMENT

VIEWFORMULA CHAR Menampilkan

formula (table

lambang)

Kolom-kolom view mewarisi tipe data dari kolom lambang mulai dari kalimat

SELECT sampai view formula.

Contoh 26.14 : Dapatkah sebuah table memanggil STOCK yang telah dibuat di

DATABASE TENNIS, atau apakah namanya sudah ada ?

SELECT TABLE_NAME

FROM TABLES

WHERE TABLE_NAME = 'STOCK'

AND TABLE_CREATOR = 'TENNIS'

UNION

SELECT VIEW_NAME

FROM VIEWS

WHERE VIEW_NAME = 'STOCK'

AND VIEW_CREATOR = 'TENNIS'

Keterangan : Statement SELECT mengecek apakah table atau view telah dibuat

dengan nama STOCK pada DATABASE TENNIS. Jika statement tersebut

memiliki hasil, nama table ini tidak dapat digunakan lagi.

Tabel VIEWS pada INFORMATION_SCHEMA catalog berisi data di views.

26.8 KEKURANGAN PADA PERUBAHAN VIEWS

Statement INSERT, UPDATE, dan DELETE mungkin dapat di eksekusi

pada views. Bagaimanapun, MYSQL memiliki beberapa kekurangan.

Contohnya, baris-baris pada beberapa views tidak boleh di delete atau di update.

Bagian ini menutupi kekurangan yang digunakan untuk merubah views.

Sebuah view dapat dirubah hanya jika satu per satu sarat ada diantara baris-baris

view dan baris-baris pokok table. Tambahan, rumus view akan memenuhi

kondisi-kondisinya. Tujuh kondisi pertama digunakan pada semua statements

update.

15

1. SELECT tidak dapat berisi DISTINCT

2. SELECT tidak dapat berisi kumpulan fungsi

3. FROM tidak dapat berisi lebih dari satu table

4. WHERE tidak dapat menyambungkan subquery

5. Statement SELECT tidak dapat berisi klausa GROUP BY (atau klausa

HAVING)

6. Statement SELECT tidak dapat berisi klausa ORDER BY

7. Statement SELECT tidak dapat menggunakan operator

Pada penambahan, kekurangan selanjutnya ada pada UPDATE

8. Kolom yang sebenarnya tidak dapat di UPDATE

Kolom BEGIN_AGE pada view berikutnya tidak dapat di update

(meskipun PLAYERNO dapat di update) :

CREATE VIEW AGES (PLAYERNO, BEGIN_AGE) AS

SELECT PLAYERNO, JOINED – YEAR(BIRTH_DATE)

FROM PLAYERS

Penambahan, kekurangan selanjutnya ada pada INSERT :

9. SELECT harus ada isinya, dari table yang menentukan FROM, semua

kolom yang nilainya null tidak mengijinkan atau untuk yang nilainya no

default akan ditetapkan.

Ini adalah kenapa INSERT tidak dapat menentang view yang selanjutnya-ini

tidak berisi semua kolom NOT NULL, seperti SEX dan TOWN :

CREATE VIEW PLAYERS_NAMES AS

SELECT PLAYERNO, NAME, INITIALS

FROM PLAYERS

Latihan 26.4 : Bab ini telah banyak menampilkan contoh-contoh view. Untuk

tiap view yang selanjutnya, menandai apakah UPDATE, INSERT, atau

DELETE akan dapat dilakukan oleh :

1. TOWNS

2. CPLAYERS

3. SEVERAL

4. DIGITS

5. STRATFORDERS

6. RESIDENTS

7. VETERANS

8. TOTALS

9. AGES

16

26.9 CARA KERJA STATEMENT VIEW

Tahap berjalannya (lihat Chapter 6, “statement SELECT, Tabel Expresi,

dan subqueries”) tidak dapat di eksekusi satu per satu, seperti yang terjadi pada

table dasar. MYSQL mencapai klausa FROM dan mencoba untuk mengambil

baris dari database; hal ini menjumpai masalah karena view berisi baris yang

tidak tersimpan. MYSQL tahu bahwa ini bekerja pada view. Untuk memproses

step ini, MYSQL dapat memilih diantara dua method dinamakan substitution

dan materialization.

Dengan method pertama, rumus view digabungkan ke statement

SELECT. Method ini dinsmsksn pengganti karena nama view pada statement

SELECT diganti dengan rumus view. Selanjutnya, statement SELECT mencapai

proses. Contoh selanjutnya mengilustrasikan method ini.

Contoh 26.15 : buatlah view dari semua data pemain yang terkena penalty.

Selanjutnya, tampilkan nomor tiap pemain dari COST_RAISERS view yang

telah mendapatkan sedikitnya satu penalty dan tinggal di Stratford.

CREATE VIEW COST_RAISERS AS

SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)

SELECT PLAYERNO

FROM COST_RAISERS


Proses step pertama berisi gabungan dari formula view ke statement SELECT

dan membuat statement selanjutnya:

SELECT PLAYERNO

FROM (SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)) AS VIEWFORMULA


Sekarang statement ini dapat diproses dengan berpindah terus ke step yang tetap.

Akhirnya hasilnya adalah:

PLAYERNO

--------

17

6

Lihat contoh selanjutnya yang menggunakan STRATFORDERS view dari

bagian 26.3.

Contoh 26.16 : hapus semua orang Stratford yang lahir diatas 1965.

DELETE

FROM STRATFORDERS

WHERE BORN > '1965-12-31'

Setelah namanya telah diganti dengan formula view, statement dibaca:

DELETE

FROM PLAYERS

WHERE BIRTH_DATE > '1965-12-31'

AND TOWN = 'Stratford'

Method yang lainnya yang diproses berarti telah berhasil. Pada method ini, table

expresi dari rumus view diproses pertama, yang memberikan sebuah hasil

perantara. Selanjutnya, statement SELECT yang sebenarnya di eksekusi pada

hasil perantara. Jika kita akan memproses Contoh 26.15 yang telah selesai,

statement selanjutnya akan di eksekusi pertama.

SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)

Ini menghasilkan hasil perantara (untuk lebih sederhananya, hanya kolom

PLAYERNO dan TOWN yang telah ditampilkan):

PLAYERNO TOWN

--------------- ---------

6 Stratford

8 Inglewood

27 Eltham

18

44 Inglewood

104 Eltham

MYSQL menyimpan hasil perantara ini di memori internal. Setelah itu,

statement selanjutnya di eksekusi :

SELECT PLAYERNO

FROM <intermediate result>


Kedua method tersebut memiliki kelebihan dan kekurangan. MYSQL sendiri

menentukan method yang mana yang harus digunakan pada setiap situasi;

bagaimanapun, pengguna dapat memilih pemrosesan method dengan

menentukannya pada dafinisi view.

Contoh 26.17: buatlah view dari semua data pemain yang mendapatkan penalty

dan pastikan bahwa MYSQL menggunakan method yang sudah selesai selama

proses.

CREATE VIEW EXPENSIVE_PLAYERS AS

ALORITHM = TEMPTABLE

SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)

Penjelasan : dengan kata TEMPTABLE, kita mengindikasikan bahwa table

sementara harus dibuat dalam proses statement SELECT pada views ini-

penyelesaiannya harus dikerjakan. Jika MERGE dikatakan sebagai algoritma,

pengganti method lah yang digunakan. Dengan UNDEFINED, MYSQL

membuat keputusannya sendiri.

Latihan 26.5 : bagaimana statement berikut muncul setelah rumus view

dimasukkan pada pengganti method?

1. SELECT YEAR(BORN) – 1900 AS DIFFERENCE, COUNT(*)

FROM STRATFORDERS

GROUP BY DIFFERENCE

2. SELECT COST_RAISERS.PLAYERNO

FROM COST_RAISERS, STRATFORDERS

19

WHERE COST_RAISERS.PLAYERNO = STRATFORDERS.PLAYERNO

3. UPDATE STRATFORDERS

SET BORN = '1950-04-04'

WHERE PLAYERNO = 7

26.10 AREA APPLIKASI PADA VIEW

VIEW dapat digunakan dalam berbagai aplikasi. Ada beberapa bagian

dari VIEW ini. Tidak ada signifikansi dengan urutan yang dibahas.

26.10.1 PENYEDERHANAAN DARI PERNYATAAN YANG BIASA

DIGUNAKAN PERNYATAAN YANG SERING DIGUNAKAN ATAU

SECARA STRUKTURAL MIRIP DAPAT DISEDERHANAKAN

MELALUI PENGGUNAAN VIEW.

Contoh 26.18: Lihatlah dari dua pernyataan ini yan dimasukkan.

SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)

AND TOWN = 'Stratford'

dan


FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)

GROUP BY TOWN

Kedua pernyataan diatas berhubungan dengan pemain yang telah dikeluarkan

setidaknya satu

penalti, maka bagian dari pemain dapat didefinisikan oleh view:

CREATE VIEW PPLAYERS AS

SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)

Sekarang dua pernyataan SELECT sebelumnya dapat disederhanakan dengan

menggunakan PPLAYERS view:

20

SELECT *

FROM PPLAYERS


and


FROM PPLAYERS

GROUP BY TOWN

Contoh 26.19: perhatikan tabel PLAYERS sering bergabung dengan tabel

MATCHES.

SELECT ...

FROM PLAYERS, MATCHES

WHERE PPLAYERS.PLAYERNO = MATCHES.PLAYERNO

AND ...

Dalam hal ini, pernyataan SELECT menjadi lebih sederhana jika bergabung dan

didefinisikan sebagai view:

CREATE VIEW PLAY_MAT AS

SELECT ...

FROM PLAYERS, MATCHES

WHERE PLAYERS.PLAYERNO = MATCHES.PLAYERNO

Setelah menggabungkan, sekarang kita dapat mengambil bentuk yang

sederhana:

SELECT ...

FROM PLAY_MAT

WHERE ...

26.10.2 REORGANISASI STRUKTUR TABEL

Tabel dirancang dan digunakan berdasarkankan keadaan tertentu.

Keadaan/kondisi sewaktu-waktu dapat berubah, yang berarti bahwa struktur juga

berubah. Misalnya, kolom baru akan ditambahkan ke sebuah tabel, atau dua

tabel dapat bergabung menjadi tabel tunggal. Dalam beberapa kasus,

reorganisasi struktur tabel membutuhkan alter/ mengubah pernyataan yang

sudah dikembangkan dan operasional. Perubahan tersebut memerlukan banyak

waktu dan biaya. Dengan penanganan yang tepat maka akan meminimalisir

waktu dan biaya yang kita gunakan.

Agar lebih jelas maka perhatikan contoh.

Contoh 26.20: Untuk setiap pemain kompetisi, carilah nama, inisial, dan divisi

di mana ia pernah dimainkan.

21

SELECT DISTINCT NAME, INITIALS, DIVISION

FROM PLAYERS AS P, MATCHES AS M, TEAMS AS T

WHERE P.PLAYERNO = M.PLAYERNO

AND M.TEAMNO = T.TEAMNO

Hasilnya adalah:

NAME INITIALS DIVISION

--------- -------- --------

Parmenter R first

Baker E first

Hope PK first

Everett R first

Collins DD second

Moorman D second

Brown M first

Bailey IP second

Newcastle B first

Newcastle B second

Untuk alasan yang tidak diketahui, tabel TEAMS dan tabel MATCHES perlu

ditata kembali;

kedua tabel tersebut digabungkan untuk membentuk satu tabel, tabel RESULT,

seperti berikut:

Kolom CAPTAIN dalam tabel RESULT sebelumnya adalah kolom

PLAYERNO dari tabel TEAMS. Kolom ini telah diberi nama lain, jika tidak,

maka akan ada dua kolom yang bernama PLAYERNO. Semua pernyataan yang

mengacu pada dua tabel, termasuk pernyataan SELECT sebelumnya, sekarang

22

perlu ditulis ulang. Agar tidak kesulitan dan untuk menulis ulang, solusi yang

lebih baik adalah mendefinisikan dua pandangan yang mewakili tabel TEAMS

dan tabel MATCHES, masing-masing:

CREATE VIEW TEAMS (TEAMNO, PLAYERNO, DIVISION) AS

SELECT DISTINCT TEAMNO, CAPTAIN, DIVISION

FROM RESULT

CREATE VIEW MATCHES AS

SELECT MATCHNO, TEAMNO, PLAYERNO,

WON, LOST

FROM RESULT

Isi yang sebenarnya dari 2 view sama dengan isi 2 table yang asli. Tidak ada

yang perlu ditulis ulang, termasuk kata kunci SELECT.

Kita tidak dapat mengelola setiap reorganisasi tabel dengan view. Karena itu

sangat berpengaruh, misalnya, untuk menyimpan data tentang pemain pria dan

wanita pada tabel yang terpisah. Kedua tabel berisi kolom yang sama sebagai

tabel PLAYERS tetapi menghilangkan

kolom SEX. Hal ini dimungkinkan untuk merekonstruksi tabel PLAYERS asli

dengan melihat menggunakan operator UNION, akan tetapi memasukkan view

tidak diperbolehkan

26.10.3 TAHAP-TAHAP PENGEMBANGAN PERNYATAAN SELECT

kita perlu menjawab pertanyaan berikut: Untuk setiap pemain dari

Stratford yang telah mendapatkan pinalti yang lebih besar daripada pinalti rata-

rata pemain dari tim kedua dan yang bermain minimal satu tim divisi pertama,

carilah nama dan inisial. Kita bisa menulis sebuah pernyataan SELECT yang

besar untuk menjawab ini, tetapi kita juga bisa mengembangkan sebuah query

dengan cara bertahap. Pertama, kita membuat tampilan semua pemain yang telah

mendapatkan setidaknya satu penalti yang lebih besar daripada pinalti rata-rata

untuk pemain dari kedua tim:

CREATE VIEW GREATER AS

SELECT DISTINCT PLAYERNO

FROM PENALTIES

WHERE AMOUNT >

(SELECT AVG(AMOUNT)

FROM PENALTIES

WHERE PLAYERNO IN

(SELECT PLAYERNO

23

FROM MATCHES

WHERE TEAMNO = 2))

Kemudian kita membuat tampilan dari semua pemain yang bertanding untuk tim

dalam divisi pertama:

CREATE VIEW FIRST AS

SELECT DISTINCT PLAYERNO

FROM MATCHES

WHERE TEAMNO IN

(SELECT TEAMNO

FROM TEAMS

WHERE DIVISION = 'first')

Dengan menggunakan dua view/ tampilan, maka kita bisa menjawab pertanyaan

awal:

SELECT NAME, INITIALS

FROM PLAYERS

WHERE TOWN = 'Stratford

AND PLAYERNO IN

(SELECT PLAYERNO

FROM GREATER)

AND PLAYERNO IN

(SELECT PLAYERNO

FROM FIRST)

Kita dapat membagi masalah ke "mini-problem/ masalah-masalah kecil" dan

menjalankannya menggunakan langkah-langkah, membuat satu pernyataan

SELECT panjang.

26.10.4 KENDALA DALAM MENENTUKAN INTEGRITAS

Gunakan klausa WITH CHECK OPTION untuk menerapkan aturan yang

membatasi kemungkinan nilai-nilai yang dapat dimasukkan ke dalam kolom.

Contoh 26.21: Kolom SEX pada tabel PLAYERS berisi nilai 'M' atau 'F' .

Dengan menggunakan klausa WITH CHECK OPTION maka akan otomatis

menjalankan ini. Lihat definisi dibawah:

CREATE VIEW PLAYERSS AS

SELECT *

FROM PLAYERS

WHERE SEX IN ('M', 'F')

24

WITH CHECK OPTION

Kami tidak mengizinkan orang lain mengakses tabel PLAYERS langsung,

sebaliknya, orang lain perlu menggunakan tampilan PLAYERSS. dengan klausa

WITH CHECK OPTION

tes setiap kali menggunakan pernyataan UPDATE dan INSERT untuk

menentukan apakah nilai yang ada di kolom SEX dapat di diizinkan untuk

diakses.

Catatan: Jika cek yang diinginkan dapat didefinisikan dengan batasan integritas

cek, menggunakan aplikasi ini adalah saran yang dianjurkan.

26.10.5 KEAMANAN DATA

Tampilan juga dapat digunakan untuk melindungi bagian tabel. Pada

pembahasan selanjutnya Bab 28, "Pengguna dan Data Keamanan, "mencakup

topik ini secara rinci.

Latihan 26.6: Tentukan apakah reorganisasi dari struktur database berikut yang

mungkin menggunakan view.

1. Kolom NAME ditambahkan ke tabel PENALTIES tetapi juga tetap dalam

tabel PLAYERS.

2. Kolom TOWN akan dihapus dari tabel PLAYERS dan ditempatkan pada

tempat yang sama

dengan kolom PLAYERNO dalam tabel terpisah.

26.11 JAWABAN

26.1 CREATE VIEW NUMBERPLS (TEAMNO, NUMBER) AS

SELECT TEAMNO, COUNT(*)

FROM MATCHES

GROUP BY TEAMNO

26.2 CREATE VIEW WINNERS AS

SELECT PLAYERNO, NAME

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM MATCHES

WHERE WON > LOST)

26.3 CREATE VIEW TOTALS (PLAYERNO, SUM_PENALTIES) AS

SELECT PLAYERNO, SUM(AMOUNT)

25

FROM PENALTIES

GROUP BY PLAYERNO

26.5 1. SELECT YEAR(BORN) – 1900 AS DIFFERENCE, COUNT(*)

FROM (SELECT PLAYERNO, NAME,

NITIALS, BIRTH_DATE AS BORN

FROM PLAYERS

WHERE TOWN = 'Stratford') AS STRATFORDERS

GROUP BY DIFFERENCE

2. SELECT EXPENSIVE.PLAYERNO

FROM (SELECT *

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM PENALTIES)) AS EXPENSIVE,

(SELECT PLAYERNO, NAME,

INITIALS, BIRTH_DATE AS BORN

FROM PLAYERS

WHERE TOWN = 'Stratford') AS

STRATFORDERS

WHERE EXPENSIVE.PLAYERNO =

STRATFORDERS.PLAYERNO

3. UPDATE PLAYERS

SET BIRTH_DATE = '1950-04-04'

WHERE PLAYERNO = 7

26.6 1. ya

2. Ya, tapi melihat dapat di-query hanya, tidak diperbarui, karena

pandangan rumus berisi bergabung.

teknik informatika universitas trunojoyo madura …dikenal sebagai tabel dasar, dan tabel turunan,...

Documents