1 Modul 1 : Perancangan Basis Data - Sistem Basis Data - DBMS - Model Data - Normalisasi - Metodologi Perancangan Basis Data - ERD - Kardinalitas Rela...
Sistem Basis Data DBMS Model Data Normalisasi Metodologi Perancangan Basis Data ERD Kardinalitas Relasi
Modul 2 : DDL, DML, Operator n Function Modul 3 : Union Join Modul 4: Views Modul 5: Store Procedure n Trigger Modul 6: PHP MySQL
MODUL 2 DDL, DML, OPERATOR DAN FUNCTION
1. Materi 1.1 Data Definition Language (DDL) Dalam DDL berisi perintah yang digunakan untuk membuat atau menghapus struktur basisdata secara keseluruhan. Perintah ini meliputi: -
ALTER TABLE Sintaks ALTER TABLE tbl_name alter_specification [, alter_specification];
Untuk mengubah struktur dalam tabel, terdapat tiga macam spesifikasi perubahan (alter specification) yang dapat dilakukan, yaitu: a. Menambah (Add): Sintaks SQL untuk menambah kolom: ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ];
Atau : ADD [COLUMN] (col_name column_definition,...);
b. Mengubah (Change atau Modify): Sintaks SQL untuk mengubah kolom: CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]; MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];
c. Menghapus (Drop): Sintaks SQL untuk menghapus kolom: DROP [COLUMN] col_name; DROP PRIMARY KEY; DROP FOREIGN KEY fk_symbol ;
Contoh kasus: Tambahkan kolom KERJA pada tabel PEKERJA dengan tipe karakter, panjang karakter = 12. Perintah: ALTER TABLE PEKERJA ADD KERJA CHAR(12);
DROP DATABASE Sintaks: DROP DATABASE nama_database;
Contoh: Menghapus basisdata PERUSAHAAN DROP DATABASE PERUSAHAAN;
DROP TABLE Sintaks: DROP TABLE nama_table;
Contoh: Menghapus tabel KERJA DROP TABLE KERJA;
1.2 Data Manipulation Language (DML) DML berisi perintah SQL yang digunakan untuk memanipulasi data dan pengambilan data pada suatu basisdata. Manipulasi data dapat berupa: -
Penyisipan/penambahan data baru ke suatu basisdata (INSERT)
-
Pemanggilan/menampilkan data yang ada dalam basisdata (SELECT)
-
Pengubahan data di suatu basis data (UPDATE)
-
Penghapusan data dari suatu basisdata (DELETE)
Pada level fisik, kita harus mendefinisikan algoritma yang memungkinkan pengaksesan yang efisien terhadap data. Pada level yang lebih tinggi, yang dipentingkan bukan hanya efisiensi akses, tetapi juga efisiensi interaksi manusia (pemakai) dengan sistem (kemudahan permintaan akses)
PERINTAH INSERT Sintaks perintah INSERT langsung: INSERT INTO
[] VALUES (<expression> [{,<expression>}. . . ]);
Sintaks perintah INSERT dengan menggunakan SELECT : INSERT INTO
[]
<select clause> [ <where clause> ] [ ] [ ];
Contoh: Masukkan data pada tabel PEKERJA dengan isian NIP: 198007082005042002, Nama: Sugeng Adiguna, Tanggal lahir: 1980-07-08, Alamat: Telang Raya Bangkalan, Sex: L. INSERT INTO PEKERJA (NIP,NAMA,TGL_LHR, ALAMAT, SEX) VALUES („198007082005042002‟,‟SUGENG ADIGUNA‟,19800708,‟Telang Raya Bangkalan‟,‟L‟); Atau INSERT INTO PEKERJA VALUES („198007082005042002‟,‟SUGENG ADIGUNA‟,19800708,‟L‟,‟Telang Raya Bangkalan‟);
PERINTAH SELECT SELECT satu tabel SELECT field_name FROM table_name WHERE [kondisi];
SELECT lebih dari satu tabel SELECT table_name1.field_name,table_name2.field_name FROM table_name1, table_name2 WHERE [kondisi];
Contoh: SELECT FROM WHERE
NAMA,ALAMAT PEKERJA NIP=„198007082005042002‟;
PERINTAH UPDATE Sintaks:
UPDATE
SET [ WHERE ];
Contoh: Rubah data Sugeng Adiguna untuk field alamat menjadi Telang Raya No.2 Bangkalan UPDATE SET WHERE
PEKERJA ALAMAT = „Telang Raya No.2 Bangkalan‟ NAMA=‟SUGENG ADIGUNA‟
PERINTAH DELETE Sintaks: DELETE FROM
[ WHERE ]
Contoh: Hapus data Sugeng Adiguna pada tabel PEKERJA DELETE FROM PEKERJA WHERE NAMA=‟SUGENG ADIGUNA‟;
1.3 Operator dan Function Function yang dimaksud adalah function yang telah disediakan oleh server basisdata atau dapat juga disebut sebagai Build-in-function, meski sebenarnya function dapat dibuat sendiri oleh pemrogram.
Operator IS [NOT] NULL Operator IS NULL digunakan untuk memilih baris dalam kolom tertentu yang tidak memiliki nilai atau NULL. Sintaks: <predicate with null> ::= <scalar expression> IS [ NOT ] NULL Contoh:
SELECT NAMA FROM PEKERJA WHERE ALAMAT IS NOT NULL;
Operator BETWEEN Operator BETWEEN digunakan untuk menentukan range nilai suatu ekspresi dalam perintah SQL. Sintaks: <predicate with between> ::= <scalar expression> [ NOT ] BETWEEN <scalar expression> AND <scalar expression> Contoh:
Cari nama dan tanggal lahir pekerja antara tahun 1980 sampai 1983 SELECT FROM WHERE
NAMA,TGL_LHR PEKERJA YEAR(TGL_LHR) BETWEEN 1980 AND 1983;
Operator LIKE Operator LIKE digunakan untuk memilih nilai alfanumerik yang memiliki pola tertentu. Sintaks perintah SQL untuk operator LIKE adalah: <predicate with like> ::= <scalar expression> [ NOT ] LIKE [ ESCAPE ] ::= <scalar alphanumeric expression>
Contoh: Cari nama pekerja dari tabel PEKERJA yang huruf kedua terakhirnya adalah „a‟ tanpa melihat panjang charakter dalam namanya. SELECT FROM WHERE
NAMA PEKERJA NAMA LIKE „%a_‟;
Operator IN Operator ini digunakan ketika data disajikan bentuk list/daftar yang dicek kondisinya. Operator IN dapat dibandingkan dengan operator OR. Sintaks:
<predicate with in> ::= <scalar expression> [ NOT ] IN <scalar expression list> | [ NOT ] IN ::= ( <scalar expression list> [ , <scalar expression list> ]... ) <scalar expression list> ::= ( <scalar expression> [ , <scalar expression> ]... )
Contoh: Tampilkan nama dan kota dari pekerja pada tabel PEKERJA yang tinggal di kota Bangkalan, Malang, Surabaya dan Pasuruan Perintah SQL dengan menggunakan operator OR: SELECT FROM WHERE
NAMA, KOTA PEKERJA KOTA = „Bangkalan‟ OR KOTA = „Malang‟ OR KOTA = „Surabaya‟ OR KOTA = „Pasuruan‟;
Perintah SQL dengan menggunakan operator IN: SELECT NAMA, KOTA FROM PEKERJA WHERE KOTA IN („Bangkalan‟, „Malang‟, „Surabaya‟, „Pasuruan‟);
2. Soal Pre Test 1. Apa yang dimaksud dengan DDL, DML dan DCL? 2. Sebutkan masing-masing 2 perintah yang termasuk DDL, DML dan DCL, serta beri contoh sederhana penulisannya!
3. Praktikum Untuk latihan modul 2, silahkan melanjutkan soal praktikum modul 1 untuk diimplementasikan pada DBMS MySQL :
1. Generate ERD yang sudah dibuat sebelumnya (Basisdata Kantin Bersama dan Basisdata Akademik ke DBMS My SQL melalui Software perancangan ERD yang telah digunakan sebelumnya.
2. Dengan semakin berkembangnya kantin bersama, bagi pelanggan yang sering melakukan transaksi dalam kantin tersebut, maka ujicoba untuk membuat tabel baru pada basisdata Kantin Bersama dengan nama: a. Tabel „FAKULTAS‟ dengan atribut: KODE_FAKULTAS smallint, dan NAMA_FAK char(15) b. Tabel „JURUSAN‟ dengan atribut KODE_JUR smallint, KODE_FAK smallint, NAMA_JUR char(20) c. Tabel „PELANGGAN‟, dengan atribut: NO_ANGGOTA char(10), KODE_JUR smalint, NAMA char(20), ALAMAT char(20), AKTIF boolean null. 3. Tambahkan data baru untuk ketiga tabel tersebut dengan perintah SQL 4. Buat Tabel baru dengan nama TIDAK_AKTIF yang atributnya adalah NO_ANGGOTA dan NAMA yang berasal dari Tabel PELANGGAN tetapi statusnya TIDAK AKTIF 5. Tampilkan data dari tabel PELANGGAN untuk atribut NO_ANGGOTA, NAMA, dan data dari Tabel Jurusan untuk NAMA_JUR
4. Pertanyaan dan Tugas 1. Cari Fungsi dan contoh sederhana untuk: o Operator EXISTS o Operator ALL dan ANY o Fungsi DISTINCT o Fungsi COUNT o Fungsi MAX, MIN o Fungsi SUM o Fungsi AVG 2. Generate ERD yang sudah dibuat sesuai studi kasus yang diperoleh pada Modul 1 3. Analisa ulang untuk tabelnya, tambahkan tabel baru dan atribut pada tabel tersebut dengan menggunakan perintah SQL 4. Isi data pada Basisdata tersebut dengan 10 isian pada masing-masing tabelnya dengan menggunakan perintah SQL (INSERT INTO dan INSERT INTO-SELECT) 5. Tampilkan beberapa atribut yang meliputi perintah: -
SELECT satu tabel
-
SELECT dua atau lebih tabel
-
SELECT dengan menggunakan 1 operator dan 1 Function yang telah disebutkan diatas.
MODUL 3 UNION DAN JOIN
1. Materi 1.1 Operator UNION Operator UNION digunakan untuk menggabungkan /mengkombinasikan hasil yang dikeluarkan oleh beberapa input tabel. Jika dua buah ekspresi tabel digabungkan dengan operator UNION, maka hasil akhirnya adalah setiap baris yang merupakan hasil dari salah satu ekspresi tabel atau keduanya. Biasanya, permasalahan yang diselesaikan dengan menggunakan operator UNION juga dapat diselesaikan dengan operator OR. Ada kalanya suatu permasalahan yang rumit jika diselesaikan dengan operator UNION menjadi lebih ringkas jika diselesaikan dengan operator OR, dan demikian juga sebaliknya.
Sintaks: SELECT FROM WHERE UNION SELECT FROM WHERE
nama_kolom nama_tabel ekspresi kondisi nama_kolom nama_tabel ekspresi kondisi;
Contoh: Dalam table pekerja, tampilkan nama dan alamat pekerja yang bertempat tinggal di Malang dan Surabaya. Dan urutksn berdasarkan huruf pertama nama pekerja.
SELECT FROM WHERE UNION SELECT FROM WHERE ORDER BY
NAMA, ALAMAT PEKERJA KOTA = „malang‟ NAMA, ALAMAT PEKERJA KOTA = „Surabaya‟ NAMA;
Analisa Hasil, jika table Pekerja terisi nilai berikut: NIP
Hasilnya adalah gabungan dari hasil SELECT pada blok pertama dan SELECT pada blok kedua NAMA Aris Zulkarnaen Bunda Satifa Mahfud Damnik
ALAMAT Ketintang raya 78a Gang Kelinci 41 Mulyosari IX/9
operator UNION akan menghilangkan duplikasi baris yang memiliki nilai yang sama dalam hasil akhir perintah SQL. Cara kerja operator UNION untuk menghapus duplikasi baris sama halnya dengan cara kerja operator DISTINCT.
Operator UNION ALL Pada
Operator
DISTINC.
Jadi
UNION
ALL,
ketika
hasil
cara
kerjanya
perintah
SQL
tidak dengan
mengadopsi JOIN
ALL
operator terdapat
beberapa baris yang memiliki nilai sama, maka baris tersebut tidak dihapuskan dan tetap ada. Sintaks: SELECT nama_kolom FROM nama_tabel WHERE ekspresi kondisi UNION ALL SELECT nama_kolom FROM nama_tabel WHERE ekspresi kondisi;
1.2 Operator JOIN Operator Join digunakan untuk menghubungkan dua tabel atau lebih pada kolom yang bersesuaian. Klausa JOIN ada beberapa macam, diantaranya: CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
Sintaks perintah JOIN: Sintaks perintah SQL untuk operator JOIN adalah: ::= FROM
[ <join condition> ] <join condition> ::= ON | USING <join type> ::= [ INNER ] JOIN | LEFT [ OUTER ] JOIN | RIGHT [ OUTER ] JOIN | NATURAL [ LEFT | RIGHT ] [ OUTER ] JOIN | CROSS JOIN ::= ( [ , ]... );
CROSS JOIN Jika ada dua tabel: tabel satu dan tabel dua, dianalisa dengan CROSS JOIN, maka Perintah SQL dengan operator CROSS JOIN, akan mengembalikan hasil dari setiap row yang ada pada tabel satu dan dikombinasi dengan nilai tabel yang ada pada tabel dua. HAsilnya seperti model Cartesian product. Semisal tabel satu memiliki sepuluh row, tabel dua memiliki sembilan row. Maka Hasil perintah SQL dengan CROSS join akan dihasilkan 10x9 row. Penggunaan CROSS JOIN perlu kehati-hatian, karena dapat saja memunculkan data-data yang tidak relevan.
INNER JOIN Di dalam INNER JOIN, hasil akhir hanya akan menampilkan data yang ada di kedua tabel. Pada perintah tersebut akan dilakukan proses pencocokan atau penyamaan data berdasarkan kolom yang didefinisikan dengan menggunakan ON.
Contoh: Jika dalam basisdata „PERUSAHAAN‟ memiliki table „PEKERJA‟(berisi biodata pekerja), dan table „KEGIATAN‟(berisi data pekerja yang melakukan kegiatan/menghadiri acara tertentu) dengan atribut: NIP, Tgl, Kegiatan, Jenis kegiatan. Dengan isi dari Tabel KEGIATAN: NIP 198704052006091000 197607092010011000
TGL 15-12-12 04-08-12
KEGIATAN PELATIHAN TOEFL SEMINAR KEAMANAN KERJA
JENIS KEGIATAN TRAINING SEMINAR
Maka tampilkan atribut nip, nama dan jenis kegiatan yang pernah diikuti oleh pekerja. SELECT FROM
A.NIP, A.NAMA, B.JENIS KEGIATAN PEKERJA A INNER JOIN KEGIATAN B ON (A.NIP = B.NIP);
OUTER JOIN Pada Outer Join, data pada tabel satu tetap diikutkan walaupun tidak memiliki hubungan apapun pada tabel dua. Macam OUTER JOIN adalah:
LEFT JOIN Perintah dengan operator ini akan menghasilkan data pada tabel kiri (tabel satu) walaupun tidak memiliki relasi pada tabel kanan (tabel dua). Jika data yang terdapat di sebelah kiri tidak terdapat pada tabel sebelah kanan, maka SQL Engine akan tetap mengeluarkan nilai NULL. Contoh:
SELECT FROM
A.NIP, A.NAMA, B.JENIS KEGIATAN PEKERJA A LEFT JOIN KEGIATAN B ON (A.NIP = B.NIP);
Aris Zulkarnaen Mahfud Damnik Bunda Satifa Maria wati
JENIS KEGIATAN TRAINING NULL SEMINAR NULL
RIGHT JOIN Perintah dengan operator ini akan menghasilkan data pada tabel kanan (tabel dua) walaupun tidak memiliki relasi pada tabel kiri (tabel satu). Contoh: SELECT FROM
A.NIP, A.NAMA, B.JENIS KEGIATAN PEKERJA A RIGHT JOIN KEGIATAN B ON (A.NIP = B.NIP);
2. Soal Pre-test 1. Apa kegunaan dari operator UNION? 2. Apa beda INNER JOIN, LEFT JOIN dan RIGHT JOIN? 3. Beri contoh sederhana untuk menampilkan data atribut NAMA dosen, NAMA mhs dari tabel DOSEN dengan atribut: NIP, NAMA dosen, ALAMAT dosen dan tabel MAHASISWA WALI dengan atribut NIP, NRP, Nama mhs, menggunakan perintah INNER JOIN
3. Praktikum 1. Gunakan operator UNION dan UNION ALL untuk Basisdata Kantin Bersama (pada modul 1), yang menggabungkan lebih dari satu tabel. Buatlah 3 model penggunaan perintah tersebut beserta tampilkan hasilnya.
2. Gunakan operator INNER JOIN untuk menggabungkan dua tabel dengan ekspresi kondisi tertentu, dan bandingkan hasilnya ketika kita mengubah perintah INNER JOIN menjadi LEFT JOIN dan RIGHT JOIN.
4. Pertanyaan dan Tugas Analisa Basisdata yang telah dibuat sebelumnya dengan menggunakan operator;
UNION,
INNER JOIN
LEFT JOIN
RIGT JOIN
Tuliskan perintah SQL dan tampilkan isi dari masing-masing tabel dan hasil dari perintah diatas.
MODUL 4 VIEWS
1. Materi View merupakan 'derived tables' sehingga ia harus didefinisikan dalam perintah SQL pada tabel basis atau view yang lain. Tabel basis yang dimaksud di sini adalah tabel yang dimiliki oleh suatu basisdata.
Membuat View Sintaks: CREATE VIEW view_name (column_name) AS [SELECT BLOCK]
Contoh: Buatlah View untuk membuat daftar seluruh pekerja yang ada di tabel PEKERJA dengan nama view „LISTPEKERJA‟ Perintah SQL 1: CREATE SELECT FROM
VIEW LISTPEKERJA (NIP,NAMA,KOTA,SEX) AS NIP, NAMA PEKERJA;
Perintah SQL 2: CREATE SELECT FROM
VIEW LISTPEKERJA AS NIP, NAMA PEKERJA;
Jika selanjutnya ingin melihat isi view LISTPEKERJA, maka perintah SQL yang diperlukan: SELECT FROM
* LISTPEKERJA;
Informasi: -
Isi View bisa berupa gabungan beberapa tabel
-
didalam sintaks view bisa dimasukkan clausa yang sering digunakan dalam, seperti: -
IS [NOT] NULL
-
JOIN
-
DISTINC
-
BETWEEN
-
Operator AND, OR
-
MIN, MX, AVG dan lain-lain
Perintah INSERT, UPDATE, atau DELETE dapat dilakukan terhadap data yang ada di dalam tabel basis melalui view tabel basis. Ketika perintah tersebut diberikan kepada view, maka isian data yang ada di tabel basis juga ikut berubah sesuai perintah yang diberikan.
Contoh: Hapuslah data Pegawai yang ada dalam tabel pekerja yang NIP nya 198704052006091000 DELETE FROM LISTPEKERJA WHERE NIP = 198704052006091000 ;
Ketika sintak tersebut dijalankan maka data yang ada di dalam view LISTPEKERJA dan yang ada di tabel pekerja dengan NIP= 198704052006091000 akan dihapus. Selain tabel basis yang menjadi referensi, view lainpun dapat digunakan sebagai referensi ketika kita membuat view yang baru Contoh: Buatlah view untuk membuat daftar seluruh pekerja yang bertempat tinggal di MALANG CREATE VIEW MLG AS SELECT NIP, NAMA, KOTA FROM LISTPEKERJA WHERE KOTA = ‘MALANG’ ;
Menghapus VIEW View yang ada dalam basisdata dapat dihapus dengan menggunakan sintaks SQL berikut: DROP VIEW view_name;
Contoh : Hapuslah view LISTPEKERJA
Perintah SQL: DROP VIEW LISTPEKERJA;
2. Soal Pre Test 1. Apa yang dimaksud dengan VIEW 2. Sebutkan apa saja kegunaan atau manfaat dari adanya VIEW
3. Praktikum 1. Dari Basis Data Kantin Bersama/Basis Data Akademik yang telah dibuat, buatlah view yang referensinya: -
Satu tabel basis (Nama View: ONE)
-
Satu view sebelumnya yang telah dibuat (Nama view: ONE2)
-
Dua tabel basis dengan INNER JOIN (Nama view: TWO)
-
Tiga tabel basis dengan Join implisit (Nama view: THREE)
2. Lakukan perubahan data terhadap satu data di view ONE 3. Cek kembali data yang diubah di nomor 2 pada tabel basis untuk view ONE
4. Pertanyaan dan Tugas Analisa Basisdata yang telah dibuat pada Tugas Modul 1 dengan menggunakan VIEW yang referensinya: -
Tabel basis View yang telah dibuat sebelumnya
Cek hasilnya, dan buat laporan mengenai: -
Isi data dari tiap-tiap tabel dalam basis data Kasus yang akan dibuat Sintaks SQL yang dibuat Hasil perintah SQL yang telah dijalankan
5. Daftar Pustaka Noor Ifada, Bahan Ajar Basisdata2, Teknik Informatika, Universitas Trunojoyo Madura, 2012.
van der Lans, R. “Introduction to SQL, Mastering Relational Database Language”, (2nd Edition), Addison-Wesley, 2007. Hinz, S., DuBois, P., Stephens, J., Olson, P., and Russell, J.. “MySQL 5.0 Reference Manual”, URL: http://downloads.mysql.com/docs/refman-5.0-en.a4.pdf
MODUL 5 STORED PROCEDURE DAN TRIGGER
1. Teori Stored procedure dan trigger sama-sama merupakan perintah yang dibuat dalam bentuk deklaratif dan prosedural. Perbedaannya adalah Stored procedure diaktifkan sebagai suatu perintah oleh editor SQL, program, atau oleh Stored Procedure atau Trigger lain. Sedangkan Trigger diaktifkan hanya oleh Sistem Manajemen Basisdata dalam suatu kondisi tertentu (ketika pernyataan INSERT, UPDATE, DELETE dilaksanakan) [1].
