Materi Basis Data Lanjut

  • Published on
    06-Jul-2015

  • View
    4.222

  • Download
    14

Embed Size (px)

DESCRIPTION

materi basis data lanjut

Transcript

I. CONSTRAINTTUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: Memahami definisi Constraint Dapat Membuat Constraint Dapat Melakukan pemeliharaan Constraint 1.1 Definisi Constraint Constraint adalah batasan atau aturan yang ada pada table. Constraint mencegah penghapusan data dari suatu table yang mempunyai keterkaitan dengan table yang lain. Misal terdapat keterkaitan antara table department dengan table pegawai. Dimana pada table pegawai menyimpan informasi kolom nomer department yang juga terdapat pada table department. Jika baris dengan nomer department '10' akan dihapus dari table department, sedangkan terdapat data pegawai yang bekerja pada department tersebut, maka dengan adanya constraint antara dua table, penghapusan tersebut tidak bisa dilakukan. Oracle menyediakan beberapa tipe constraint berikut : NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK Sintak penulisan Constraint :CREATE TABLE expr] [schema.]table (column datatype [DEFAULT [column_constraint], ... [table_constraint][,...]);

Contoh penulisan Constraint :CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT CONSTRAINT emp_emp_id_pk PRIMARY KEY

NULL,

1.2 Constraint NOT NULL Suatu kolom yang didefinisikan dengan constraint NOT NULL tidak boleh berisi nilai NULL. Kolom yang befungsi sebagai kunci primer (primary key) otomatis tidak boleh NULL.

Contoh penggunaan Constraint NOT NULL :

1.3 Constraint UNIQUE Constraint UNIQUE mendefinisikan suatu kolom menjadi bersifat unik. Ilustrasi penggunaan Constraint UNIQUE :

2

Perintah pembuatan tabel untuk ilustrasi diatas :

1.4 Constraint PRIMARY KEY Constraint PRIMARY KEY membentuk key yang unik untuk suatu table. Kolom yang didefinisikan sebagai PRIMARY KEY akan mengidentifikasi suatu baris data menjadi unik. Contoh penggunaan Constraint PRIMARY KEY :

3

Ilustrasi penggunaan Constraint PRIMARY KEY :

1.5 Constraint FOREIGN KEY FOREIGN KEY constraint didefinisikan pada suatu kolom yang ada pada suatu tabel, dimana kolom tersebut juga dimiliki oleh table yang lain sebagai suatu PRIMARY KEY. Ilustrasi penggunaan Constraint FOREIGN KEY :

4

Perintah pembuatan tabel untuk ilustrasi diatas :

1.6 Keyword dari Constraint FOREIGN KEY Berikut ini keyword yang ada pada Constraint FOREIGN KEY : FOREIGN KEY anak REFERENCES ON DELETE CASCADE ON DELETE SET NULL 1.7 Constraint CHECK Constraint CHECK digunakan untuk mendefinisikan suatu kondisi yang harus dipenuhi oleh tiap baris data dalam table. Contoh penggunaan : : Mendefinisikan kolom yang ada pada tabel : Mengidentifikasi tabel dan kolom dalam tabel induk : Penghapusan baris dependent dalam tabel anak pada saat sebuah baris pada tabel induk dihapus : Mengkonversi dependent foreign key ke nilai NULL.

1.8 Menambahkan suatu Constraint Untuk menambahkan suatu Constraint, digunakan perintah ALTER TABLE. Cara penulisan : ALTER TABLE table ADD [CONSTRAINT constraint] type (column);

5

1.9 Menambahkan Constraint Misal ditambahkan constraint FOREIGN KEY pada tabel EMPLOYEES yang menunjukkan bahwa data manager harus valid atau berasal dari tabel EMPLOYEES.

1.10 Menghapus Constraint Untuk menghapus suatu Constraint, juga digunakan perintah ALTER TABLE. Cara penulisan : ALTER TABLE table DROP [CONSTRAINT constraint] type (column); Untuk menghapus constraint manager dari tabel EMPLOYEES :

Untuk menghapus constraint PRIMARY KEY pada tabel departments dan menghapus asosiasi constraint FOREIGN KEY pada kolom EMPLOYEES.DEPARTMENT_ID

1.11 Men-Disable Constraint Untuk menonaktifkan constraint digunakan klausa DISABLE. Pilihan CASCADE digunakan untuk men-disable dependent integrity constraint.

1.12 Meng-Enable Constraint Untuk mengaktifkan kembali constraint, digunakan klausa ENABLE

6

7

1.13 Cascading Constraint Klausa CASCADE CONSTRAINTS digunakan dengan klausa DROP COLUMN. Klausa CASCADE CONSTRAINTS menghapus semua referential integrity constraints yang merefer ke primary dan unique key yang didefinisikan pada kolom yang dihapus.

1.14 Menampilkan Constraint Tabel USER_CONSTRAINT menyimpan informasi tentang constraint. Kolom tipe constraint (constraint_type) dapat berisi : C untuk constraint CHECK, R untuk referential integrity atau FOREIGN KEY, P untuk PRIMARY KEY, U untuk constraint UNIQUE.

1.15 Menampilkan Kolom yang Berasosiasi dengan suatu Constraint Berikut ini untuk menampilkan Kolom yang Berasosiasi dengan suatu nama suatu Constraint yang ada pada view USER_CONS_COLUMNS

8

1.16 Latihan 1. Tambahkan table-level PRIMARY KEY Constraint pada tabel EMP pada kolom ID. Constraint diberi nama : my_emp_id_pk 2. Buat PRIMARY KEY Constraint pada tabel DEPT dengan menggunakan kolom ID. Constraint diberi nama : my_dpet_id_pk 3. Tambahkan kolom DEPT_ID ke dalam tabel EMP. Tambahkan foreign key references pada tabel EMP yang memastikan bahwa data pegawai dimasukkan berdasarkan nomer department yang ada pada tabel DEPT. 4. Tampilkan constraint-constraint yang telah ditambahkan dengan melihat pada view USER_CONSTRAINTS. 5. Tampilkan nama object dan tipenya dari data dictionary USER_OBJECTS untuk tabel EMP dan DEPT. 6. Modifikasi tabel EMP. Tambahkan kolom COMMISSION dengan tipe data NUMBER(2). Tambahkan Constraint untuk memastikan bahwa kolom ini harus diisi dengan nilai > 0.

9

II. VIEWTUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: Memahami definisi View Dapat membuat View Dapat Memanggil data melalui View Merubah definisi View Insert, Update, dan Delete data melalui View Menghapus (drop) view

2.1 Definisi View View adalah salah satu object database, yang secara logika merepresentasikan sub himpunan dari data yang berasal dari satu atau lebih table. Kegunaan dari view adalah : Membatasi akses database Membuat query kompleks secara mudah Mengijinkan independensi data Untuk menampilkan view (pandangan) data yang berbeda dari data yang sama. Ada 2 (dua) tipe view, yaitu Simple View dan Complex View. Berikut ini perbandingan antara Simple View dan Complex View :

2.2 Pembuatan View View dapat dibuat dengan perintah CREATE VIEW. Subquery dapat dicantumkan dalam CREATE VIEW, tapi subquery yang digunakan tidak boleh berisi klausa ORDER BY. Sintak penulisan VIEW :

10

2.3 Memanggil Data dari View Buat view EMPVU80 yang department 80. berisi detail pegawai yang bekerja di

Untuk menampilkan struktur dari view digunakan perintah DESCRIBE :

Perintah pembuatan View dapat dengan menggunakan kolom alias :

Untuk memanggil data dari view, digunakan perintah yang sama seperti memanggil data dari table.

11

2.4 Memodifikasi data View Untuk memodifikasi View digunakan klausa CREATE OR REPLACE VIEW.

2.5 Pembuatan Complex View Berikut ini akan dicontohkan pembuatan Complex View yang berisi fungsi group untuk menampilkan nilai yang berasal dari dua table.

2.6 Aturan untuk membentuk Operasi DML pada View Berikut ini aturan untuk membentuk operasi DML pada View : Operasi DML dapat dibentuk pada Simple View Baris data pada View tidak dapat dihapus, jika berisi : o Fungsi Group o Klausa GROUP BY o Keyword DISTINCT Data pada View tidak bisa dimodifikasi jika berisi : o 3 Kondisi yang sudah disebutkan diatas o Kolom yang didefinisikan oleh suatu ekspresi o Kolom ROWNUM Pada View tidak bisa ditambahkan data, jika : o View berisi 5 kondisi yang sudah disebutkan diatas o Terdapat kolom NOT NULL pada base table (table asal darimana view dibuat) yang tidak dipilih oleh View. 2.7 Menggunakan Klausa WITH CHECK OPTION Jika klausa WITH CHECK OPTION digunakan, maka tidak diperbolehkan terjadi perubahan data pada kolom yang punya relasi ke table yang lain. Misal pada view EMPVU20 kolom deptno punya relasi ke kolom deptno pada table department, maka perubahan data yang dilakukan pada kolom ini tidak diperbolehkan. 12

2.8 Mengabaikan Operasi DML Klausa READ ONLY digunakan jika kita ingin mengabaikan atau tidak mengijinkan semua operasi DML yang dilakukan pada data.

2.9 Menghapus View View dapat dihapus dengan menggunakan nama_view; Sintak penulisan untuk menghapus view : perintah DROP VIEW

Contoh penghapusan View :

2.10 Inline View Inline view adalah subquery dengan nama alias yang digunakan dalam SQL Statement yang digunakan untuk membuat view. Sebuah inline view bukan merupakan object dari suatu schema. 2.11 Top-N Analysis Top N-queries adalah query untuk mendapatkan n buah nilai terbesar atau terkecil dari suatu kolom Semisal : produk apa saja yang penjualannya 10 terbesar ?

13

2.12 Membentuk Top-N Analysis Sintak penulisan Top-N Analysis :

2.13 Contoh Top-N Analysis Untuk menampilkan nama dan salary dari pegawai yang memiliki penghasilan tiga besar ?

14

2.14 Latihan 1. Buat view EMP_VU yang berisi nomer pegawai, nama pegawai, nomer department yang berasal dari table pegawai. Ubah judul kolom nama pegawai menjadi PEGAWAI. 2. Tampilkan view EMP_VU

3. Tampilkan nama view dan teks-nya dari data dictionary USER_VIEWS

4. Buat view dengan nama DEPT20 yang berisi nomer, nama dan gaji dari pegawai yang bekerja di department 20. Beri judul kolom EMPLOYEE_ID, EMPLOYEE, dan DEPARTMENT_ID. Jangan perbolehkan pegawai untuk mendaftar kembali (atau mengisi datanya lagi) ke department yang lain melalui view. 5. Tampilkan struktur dari view DEPT20.

6. Buat view SALARY_VU yang berisi nama pegawai, nama department, gaji dan grade dari gaji untuk semua pegawai. Beri judul PEGAWAI, DEPARTMENT, GAJI, JOB_TITLE. Tampilkan data pada SALARY_VU.

15

III. PL/SQL ITUJUAN BELAJAR: Setelah mempelajari materi dalam bab ini, mahasiswa diharapkan mampu: Mengenal blok PL/SQL beserta bagianny