02/12/2010
Agenda Pengantar SQL DDL Tipe Data Referential Integrity Constraint
Structured Query Language (SQL)
(Pert. 9) Oleh : Umi Laili Yuhana, S.Kom, M.Sc. Sarwosri, S.Kom, M.T. Dr. Ir. Siti Rochimah BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
1
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
2
SQL • Singkatan dari Structured Query Language, dalam bahasa inggris dibaca SEQUEL • SQL : bahasa query standar yang digunakan untuk mengakses basis data relasional
STRUCTURED QUERY LANGUAGE (SQL) BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
3
Kemampuan SQL
4
Pemakai SQL
• Pendefinisian struktur data (contoh: create table, create view, dll) • Pengubahan data (contoh: update data) • Manipulasi data / memperoleh data • Pengaturan sekuritas
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
• Database Administrator • Programmer • Pengguna
5
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
6
1
02/12/2010
Elemen SQL
Penggunaan SQL
Contoh SQL: SELECT * FROM PEGAWAI WHERE ALAMAT = “JL. Teknik Kimia 25 Surabaya” Perintah SQL yang meminta sesuatu tindakan kepada DBMS; contoh: Alter, commit, create, delete, drop, grant, insert, revoke, rollback, select, update
SQL dipakai oleh DBA untuk menciptakan serta mengendalikan pengaksesan basis data
SQL sebagai bahasa administrasi basis data
Fungsi bawaan: sebuah sub program yang menghasilkan suatu nilai jika dipanggil. Contoh: Min, AVG, dll
SQL sebagai bahasa query interaktif
Pengguna dapat memberikan perintahperintah untuk mengakses basis data sesuai kebutuhan terutama yang diperlukan saat itu
SQL sebagai bahasa pemrograman basis data
Pemrogram dapat menggunakan perintah-perintah SQL dalam aplikasi yang dibuat, guna mengakses basis data
SQL sebagai bahasa klien/server
Klien dapat menjalankan aplikasi yang mengakses Basis data yang ada di server
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
7
Kelompok Pernyataan SQL Data Definition Language (DDL) •
•
Untuk mendefinisikan objek-objek basis data, tabel, atribut, batasanbatasan thd atribut, serta hubungan antar tabel Meliputi : CREATE, ALTER, DROP
Data Control Language (DCL) • •
Untuk mengendalikan pengaksesan data Meliputi : GRANT, REVOKE, LOCK TABLE
Data Manipulation Language (DML) • • •
Untuk memanipulasi data dalam basis data Misal: untuk pengambilan, pemasukan, pengubahan dan penghapusan. Meliputi: SELECT, INSERT, DELETE, UPDATE
Pengendali Transaksi • •
Untuk mengendalikan pengeksekusian transaksi Meliputi : COMMIT, ROLLBACK
Pengendali Programatik • •
Mencakup pernyataan yang berhubungan dengan pemanfaatan SQL dalam bahasa lain BASIS DATA TEKNIKCLOSE INFORMATIKA - ITS Meliputi : DECLARE, FETCH, OPEN, GASAL 2010/2011
9
Nama digunakan sebagai identitas bagi objek-objek pada DBMS. Contoh objek: tabel, kolom, pengguna, dll. Contoh nama tabel: Pegawai
Ekspresi adalah segala sesuatu yang menghasilkan nilai. Ekspresi digunakan untuk menghitung nilai. Contoh: harga_total = harga * jumlah_barang
Setiap data memiliki tipe data. Contoh tipe data: Char, integer, numeric, varchar, money, boolean, blob, serial, date, dll Konstanta menyatakan nilai yang tetap. Contoh: 75, Jl. Teknik Kimia 25 Surabaya, dll BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
8
Tipe Data • Untuk setiap kolom/field yang terdapat pada sebuah tabel, harus ditentukan pula tipe datanya yang menentukan jangkauan nilai yang bisa diisikan • Masing-masing DBMS memiliki jenis dan nama tipe data tersendiri. Bisa jadi ada yang khusus (tidak ada pada yang lain), atau diberi dengan nama lain, atau bahkan tidak memiliki tipe data yang standar BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
10
Tipe Data Beberapa Tipe data yang standar SQL-92 adalah: Binary Binary varying Character() Character varying() Datetime Double precision
DATA DEFINITION LANGUAGE (DDL)
Float Integer Numeric Dsb
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
11
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
12
2
02/12/2010
DDL
Data Definition Language (DDL) berkaitan dengan perintah-perintah untuk pendefinisian obyek-obyek basis data
DDL untuk CREATE Tabel Syntax pembuatan tabel
Jenis Perintah DDL & Contohnya
DDL untuk database
CREATE; untuk membuat obyek baru Contoh: CREATE DATABASE db_akademik
DDL untuk Tabel
ALTER; untuk memodifikasi obyek
[aturann] berisi aturan untuk field/kolom ybs, bersifat opsional. Biasanya berupa: NOT NULL DEFAULT
[aturanTabel] berisi aturan-aturan yang berlaku untuk tabel tersebut. Jika aturan lebih dari satu, maka dibatasi oleh tanda koma. Aturan tabel biasanya: •primary key () •foreign key () references () •[ON DELETE ] [ON UPDATE ]) [AturanTabel] bisa diberi nama. Jika diberi nama, maka syntaxnya sbb: CONSTRAINT
Contoh:
DDL untuk View
ALTER TABLE cust_table ADD (cust_sex char(1) NOT NULL, cust_credit_rating number);
DROP; untuk menghapus obyek
DDL untuk Trigger / stored procedure
Contoh: DROP TABLE
cust_table
DDL untuk Index BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
13
14
DDL Untuk ALTER & DROP Tabel
CREATE TABLE
Modifikasi tabel: 1. Menambahkan kolom baru: ALTER TABLE ADD CONTOH: ALTER TABLE mahasiswa ADD (tgl_lahir date);
Contoh: Pembuatan tabel Mahasiswa dan set nrp menjadi primary key, nilai default pada jeniskel=”L”, nipdosen mereferensi ke tabel dosen field nip
CREATE TABLE Mahasiswa( NRP CHAR(10) not null, NIP CHAR(18) not null, MHSNAMA VARCHAR2(20), MHSALAMAT VARCHAR2(50), MHSJENISKEL CHAR(1) DEFAULT ‘L’, constraint PK_MAHASISWA primary key (NRP), constraint FK_MHS_DSN foreign key (NIP) REFERENCES Dosen(NIP) ); BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
2. Menghapus kolom: ALTER TABLE
DROP TABLE
AS
• Trigger adalah sebuah obyek dalam database yang berupa prosedur yang merespon setiap kali terdapat proses modifikasi pada tabel • Proses modifikasi berupa: Insert, Update dan delete Syntax: CREATE TRIGGER ON TABLE FOR [DELETE] [,] [INSERT] [,] [UPDATE] AS
• Contoh: Membuat View dengan nama MahasiswaPria:
Contoh:
CREATE VIEW MahasiswaPria AS SELECT * FROM Mahasiswa WHERE jeniskel=”L”
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
16
DDL Untuk TRIGGER
• Syntax CREATE VIEW <SQLQuery>
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
15
DDL Untuk View View adalah tabel bayangan Tidak menyimpan data secara fisik. Biasanya berupa hasil query dari tabel-tabel dalam sebuah database
drop
Menghapus tabel, syntax:
17
Membuat trigger dg nama tLogUbahNilai untuk setiap penambahan / update data pada tabel Pesertakul, dilakukan penambahan data pada tabel LogHistoris
CREATE TRIGGER tLogUbahNilai ON TABLE pesertakul FOR UPDATE, INSERT AS INSERT INTO LogHistoris (tanggal, proses) VALUES (getDate(), ‘Terjadi proses perubahan data nilai’) BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
18
3
02/12/2010
Referential Integrity Constraint • Digunakan untuk: menjaga konsistensi barisbaris data antara 2 buah tabel • Berkaitan dengan insert, update dan delete data Child Tabel
REFERENTIAL INTEGRITY CONSTRAINT BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
Tabel mahasiswa berelasi dengan tabel dosen Karenanya, referential integrity constraintnya harus diperhatikan dan dijaga Parent Tabel
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
19
20
Referential Integrity Constraint
Referential Integrity Constraint
Aturan untuk Update berlaku pada proses modifikasi di parent table
Aturan untuk Delete berlaku pada proses modifikasi di parent table
Cascade Pembaruan sebuah baris data diikuti
Cascade Menghapus seluruh baris data pada child
dengan pembaruan baris data pada child table yang terelasikan. Restrict mencegah proses pembaruan data jika terdapat baris data di child table yang terelasikan. Ignore mengabaikan referensi. Boleh memperbarui data pada parent, tapi tidak memperbarui data yang berelasi pada child table.
table yg terelasikan. Restrict mencegah penghapusan jika terdapat baris data yang berelasi pada child table. Ignore mengabaikan referensi. Boleh menghapus data, dan tidak ada efeknya bagi child table.
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
21
Referential Integrity Constraint
Referential Integrity Constraint
Aturan untuk Insert
Contoh
• Restrict Tidak boleh menambah data pada child table, jika nilai yang dimasukkan pada kolom yang berelasi tidak terdapat pada parent tabelnya. • Ignore mengabaikan referensi. Boleh menambah data pada child, walaupun nilai yang dimasukkan pada kolom yang berelasi tidak terdapat pada parent tabel.
CREATE TABLE Mahasiswa (nrp CHAR(10), nama CHAR(20), nip CHAR(10) PRIMARY KEY (nrp), FOREIGN KEY (nip) REFERENCES Dosen ON DELETE CASCADE ON UPDATE CASCADE )
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
23
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
22
24
4
02/12/2010
Latihan Soal
Pembahasan
• Buatlah sebuah tabel barang yang atributnya kode barang, dan nama barang. • Tulislah syntax DDL-nya, lalu dengan syntax alter table, tambahkan primary key pada kode barang. • Dan tambahkan atribut jumlah barang pada tabel barang. • Lalu, buatlah tabel pembelian dengan atribut id pembelian, id barang (merupakan foreign key dari tabel barang), jumlah barang barang transaksi, dan tanggal. BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
25
•
• • •
Membuat tabel barang dengan atribut kode barang dan nama barang : CREATE TABLE BARANG( KODE_BARANG CHAR(8), NAMA_BARANG VARCHAR2(25) ); Menambahkan primary key pada kode barang : ALTER TABLE BARANG ADD CONSTRAINT PK_BARANG PRIMARY KEY (KODE_BARANG) Menambahkan atribut jumlah barang : ALTER TABLE BARANG ADD JUMLAH BARANG (NUMBER) Membuat tabel pembelian : CREATE TABLE PEMBELIAN ( ID_BELI CHAR(8), KODE_BARANG CHAR(8), JUMLAH_TRANSAKSI NUMBER, TANGGAL DATE, CONSTRAINT PK_PEMBELIAN PRIMARY KEY (ID_BELI), CONSTRAINT FK_PEMBELIAN FOREIGN KEY (KODE_BARANG) REFERENCES BARANG(KODE_BARANG) );
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
26
Pustaka • Konsep & Tuntunan Praktis Basis Data, Abdul Kadir, 2001
BASIS DATA TEKNIK INFORMATIKA - ITS GASAL 2010/2011
27
5