DDL (Data Definition Language)
• Create Tables
• Create Indexes
• Altering Tables
• Dropping Tables/Indexes
DDL (Data Definition Language)
• Create Tables statement
CREATE TABLE table (An Dn, An Dn, .., An Dn)
Create Tables Structure
• Membuat tabel siswa
CREATE TABLE siswa
( NISN char(6), nama_siswa char(30),
tgl_lahir date(), tempat_lahir char (30),
KLS char(2), alamat char(30),
nama_ortu_wali char(30))
DDL
• Create Tables statement
CREATE TABLE tabel (A1 D1, A2 D2, .., An Dn)
Keterangan :
tabel = nama tabel yang akan dibuat
A1..An = atribut atau variabel field
D1, Dn= tipe data untuk A1..An
Create Tables Statement
• Membuat tabel siswa
CREATE TABLE siswa
( NISN char(6), nama_siswa char(30),
tgl_lahir date(), tempat_lahir char(30),
KLS char(2), alamat char(30),
nama_ortu_wali char(30))
Create Index
Index
Index adalah struktur data eksternal yang digunakan untuk mengurutkan atau mengatur pointer data dalam sebuah table
Peringatan :
Jika kita menggunakan beberapa index pada suatu tabel akan menunjukkan penurunan performa dikarenakan extra overhead dalam pemeliharaan indexnya.
Selian itu :
Banyak menggunakan index dapat menyebabkan masalah penguncian record, bila digunakan dalam peralatan multiuser.
Dengan demikian :
Gunakanlah index dalam konteks yang benar, sebuah index dapat memperbaiki performa lebih tinggi sebuah aplikasi.
Create Index Statement
Gunakan pernyataan CREATE INDEX untuk membuat index.
Structure pembuatan index adalah :
CREATE INDEX nama_index
ON nama_tabel (nama_field)
Contoh membuat index pada tabel customers dalam database invoicing sebagai berikut :
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID)
Pengurutan menggunakan INDEX secara baku data diurutkan dari kecil ke besar. Jika Anda ingin mengurutkan data dari nilai terbesar ke nilai terkecil. Strukturnya adalah :
CREATE INDEX nama_index
ON nama_tabel (nama_field DESC)
Contoh membuat index pada tabel customers dalam database invoicing sebagai berikut :
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID DESC)
CREATE INDEX idxCustomerID
ON tblCustomers
(CustomerID) WITH PRIMARY
CREATE INDEX idxCustomerName
ON tblCustomers
([Last Name], [First Name]) WITH PRIMARY
CREATE UNIQUE INDEX nama_index
ON nama_tabel (nama_field)
DROP Index Statement
DROP INDEX nama_index
ON nama_tabel
Contoh :
DROP INDEX idxCustomerPhone
ON tblCustomers
DROP Table Statement
DROP TABLE nama_tabel
Contoh :
DROP TABLE tblCustomers
Altering Tables Statement
• Altering Tables Structure
ALTER TABLE tabel
ADD | MODIFY (An dn, An dn, .., An dn);
ALTER TABLE tblCustomers
ADD COLUMN Address TEXT(30)
Mengganti ukuran field :
ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40)
• Altering Tables & DROP
Menghapus Field :
ALTER TABLE tblCustomers
DROP COLUMN Address
Structure Query Language
[SQL]
[SQL]
DML ( Data Manipulation Language)
• • Insertion : Menyisipkan data record ke dalam suatu table
• • Updating : Memperbaiki data record dalam suatu table
• • Deletion : Menghapus data record pada suatu tabel
• Selection : Menampilkan data record dari suatu tabel
Query Insertion Sructure
INSERT INTO tabel (A1, A2, .., An )
VALUES (C1, C2, …,Cn-1, Cn)
CONTOH :
INSERT INTO siswa ( NISN, nm_siswa, nilai )
VALUES ( 123456, ‘Fadhel Muhammad’, 89 );
SELECT A1, A2, ………,An-1, An
FROM T1, T2, ………,Tn-1, Tn
WHERE Criteria
GROUP BY A1, A2, ………,An-1, An
HAVING Criteria_Agregate_function
ORDER BY Criteria_A
Query Update Sructure
UPDATE tabel
SET assignments
WHERE Criteria
CONTOH :
UPDATE siswa
SET nilai = 89
WHERE NISN =‘123456’;
Query Deletion Sructure
DELETE FROM Tabel
WHERE Criteria
CONTOH :
DELETE FROM siswa
WHERE NISN = ‘123456’;
CDL (Control Definition Language)
• GRANT
Memberikan otoritas (hak akses) manipulasi data pada suatu tabel (database) kepada user
• REVOKE
Mencabut otoritas (hak akses) manipulasi data pada suatu tabel (database) dari user
• GRANT statement structure
GRANT <otoritas> ON <nm_tabel> TO <user_name>
Grant Type :
insert , select,
update , delete , all
• GRANT for insert
GRANT insert ON <nm_tabel> TO <user_name>
Contoh :
GRANT insert ON siswa TO fadhel
GRANT insert ON siswa TO agung, fadhel, septi
GRANT insert ON siswa TO all
• GRANT for update
GRANT update ON <nm_tabel> TO <user_name>
Contoh :
GRANT update ON siswa TO fadhel
GRANT update (NIM, nm_kul) ON siswa TO agung, septi
• GRANT for select
GRANT select ON <nm_tabel> TO <user_name>
Contoh :
GRANT select ON siswa TO fadhel
GRANT insert, select ON siswa TO septi
GRANT all ON siswa TO ani
• GRANT for all and public
GRANT all ON <nm_tabel> TO <user_name>
GRANT <otoritas> ON <nm_tabel> TO public
Contoh
GRANT all ON siswa TO ani
GRANT select ON siswa TO public
• REVOKE structure
REVOKE <otoritas> ON <nm_tabel> FROM <user_name>
Revoke Type :
insert , select,
update , delete , all
• REVOKE for insert
REVOKE insert ON <nm_tabel> FROM <user_name>
• REVOKE for select
REVOKE select ON <nm_database> FROM <user_name>
Advanced Query
• Complex Integrity Constraints
- Constraints over single table
- Domain constraints
• ICs over several tables
• IF conditional into query
• Aggregate function
Integrity Constraint
Menjaga integritas atau satu kesatuan data dalam suatu database, gunakan kunci utama (Primary key) dan kunci tamu (Foreign key).
– Primary key :
Adalah atribut kunci yang dapat menunjukkan identitas informasi dari atribut yang bersangkutan.
– Foreign key :
Adalah atribut kunci milik relasi utama yang disisipkan pada relasi transaksi untuk menunjukkan relationship antara relasi transaksi dengan relasi utama.
Integrity Constraint
Data Integrity
Mendefinisikan himpunan aturan integritas data adalah penting, pendefinisian ini dilakaukan oleh database administrator atau application developer. Sebagai contoh data integrity, dengan pertimbangan tables employees and departments dengan business rules untuk informasi pada setiap table sebgai ilustrasi sbb :
Types of Data Integrity
Bagian ini menggambarkan atauran yang dapaat diterapkan pada kolom tabel ayang menekankan perbedaan tipe data pada integritas data.
• Null Rule
Aturan null adalah definisi aturan pada single column yang membolehkan atau tidak membolehkan inserts atau updates untuk pengisian rows kosong (the absence of a value) pada kolom ini.
• Unique Column Values
Aturan nilai unique didefinisan pada sebuah column (or set of columns) yang membolehkan insert or update hanya pada row jika itu berisi sebuah nilai unique dalam sebuah kolomcolumn (or set of columns).
• Primary Key Values
Aturan nilai primary key didefinisikan pada sebuah key (a column or set of columns) tertentu bahwasetiap each row dalam table dapat mengidentifikasi keunikan dengan nilai kunci tersebut
• Referential Integrity Rules
Aturan referential integrity adalah definsi aturan pada sebuah kunci key (a column or set of columns) dalam sebuah table yang menjamin bahwa data dalam kunci cocok dengan nilai dalam sebuah relasi table (the referenced value).
Integrity Constraint
– Primary key
ALTER TABLE tblCustomers
ALTER COLUMN CustomerID INTEGER CONSTRAINT PK tblCustomers PRIMARY KEY
ALTER TABLE tblCustomers
ALTER COLUMN CustomerID INTEGER PRIMARY KEY
– Primary key
ALTER TABLE tblCustomers
ADD CONSTRAINT CustomerNames
UNIQUE ([Last Name], [First Name])
ALTER TABLE tblInvoices
ADD CONSTRAINT CheckAmount
CHECK (Amount > 0)
– Foreign key
ALTER TABLE tblShipping
ADD CONSTRAINT FK tblShipping
FOREIGN KEY (CustomerID)
REFERENCES tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE
Query Optimation
• What Is Optimization?
• Optimization adalah pemilihan proses ekseskusi SQl statement yang efficient. Langkah ini sangat penting di dalam processing untuk data manipulation language (DML) statement:
SELECT,
INSERT,
UPDATE,
or DELETE.
• Terdapat beberapa cara untuk mengakses SQL statement yang sering ada, contoh, dengan variasi tahapan pengaksesan data dalam tabel atau indexes. Pada procedure Oracle menggunakan eksekusi sebuah statement sangat berpengaruh pada kesepatanm eksekusi statement.
Bagain ini pada Oracle disebut perhitungan optimizer cara yang sangat efficient untuik sebuah SQL statement. Beberapa faktor evaluasi optimizer dapat dipilih dari beberapa access paths alternatif . Hal ini dapat menggunakan cost-based or rule-based approach
• Rule based optimization adalah sebuah method lama bahwa optimizer dapat menggambarkanhasil bagaimana query and dieksekusi.
• The Cost based optimizer lebih optimizer "intelligent" yang dapat mencari dan menentukan bagaimana QUERY anda dieksekusi.
• Rule based optimization
| Rank | Access Path |
| 1 | Single row by rowid |
| 2 | Single row by cluster join |
| 3 | Single row by hash cluster key with unique or primary key |
| 4 | Single row by unique or primary key |
| 5 | Cluster join |
| 6 | Hash cluster key |
| 7 | Indexed cluster key |
| 8 | Composite key |
| | |
| 9 | Single-column indexes |
| | |
| | |
| 10 | Bounded range search on indexed columns |
| 12 | Unbounded range search on indexed columns |
| 13 | Sort-merge join |
| 14 | MAX or MIN of indexed column |
| 15 | ORDER BY on indexed columns |
| 16 | Full table scan |
| | |
| | |
| | |
• Rule based optimization : Single row by rowid
This Access path ini hanya hanya tersedia jika statement's WHERE clause dipilih identitas baris atau dengan menambahkan dukungan tulisan SQL CURRENT OF CURSOR oleh Oracle Precompilers. Menjalankan statement, Oracle accesses tabel perbaris.
Contoh :
diberikan statement untuk mengakses data sebagai berikut :
SELECT * FROM emp
WHERE ROWID = 'AAAA7bAA5AAAA1UAAA';
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------
SELECT STATEMENT
TABLE ACCESS BY ROWID EMP
• Rule based optimization : Single Row by Cluster Join
This Access path ini hanya hanya tersedia jika statement's WHERE clause dipilih identitas baris atau dengan menambahkan dukungan tulisan SQL CURRENT OF CURSOR oleh Oracle Precompilers. Menjalankan statement, Oracle accesses tabel perbaris.
Contoh :
diberikan statement untuk mengakses data sebagai berikut :
SELECT * FROM emp
WHERE ROWID = 'AAAA7bAA5AAAA1UAAA';
The EXPLAIN PLAN output for this statement might look like this:
OPERATION OPTIONS OBJECT_NAME ----------------------------------
SELECT STATEMENT
TABLE ACCESS BY ROWID EMP
• Jika seset tujuan optimizer andauntuk dipilih, optimizer akan digunakan secara baku CBO tidak menghilangkan statistics anda. Bila berjalan dalam mode CBO, ini sangat penting untukkeakuratan statistik pada database (ANALYZE TABLE, or DBMS_STATS.Gather*).
• Relational Query Optimation
• Optimation query Planning
• Catalog system in query optimation
• Indexes using for query optimation
• Optimation query Planning
memperlihatkan representasi execution plan pada SQL statement berikut, untuk memilih name, job, salary, dan department name untuk semua employees yang tidak menerima gaji pada batas rentang gaji yang telah ditentukan :
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND NOT EXISTS
(SELECT * FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);
Database Trigger
• Trigger Type
• Using Database trigger
• The activate rule database trigger
• Deletion database trigger
Trigger Type
• INSERT
adalah event_trigger untuk penyisipan data record ke dalam tabel
• UPDATE
adalah event_trigger untuk perbaikan data record yang ada dalam tabel
• DELETE
adalah event_trigger untuk menhapus data record yang ada dalam tabel
Trigger Statement
CREATE OR REPLACE TRIGGER nm_trigger
[BEFORE | AFTER] event_trigger ON nama_tabel
FOR EACH ROW
[DECLARE]
Deklarasi_Variabel
BEGIN
statements list;
END;
Database Trigger
• Using Database trigger
MENGUPDATE DATA BARANG :
CREATE OR REPLACE TRIGGER tr_barang
AFTER update ON barang
FOR EACH ROW
BEGIN
dbms_output.put_line(‘Tabel barang telah di- update’);
END;
MENAMBAH DATA STOK BARANG :
CREATE OR REPLACE TRIGGER tr_insert_pasok
AFTER insert ON pasok
FOR EACH ROW
BEGIN
update stok_barang
set jum_stok= jum_stok + :new.jum_pasok ;
END;
MENGHAPUS DATA STOK BARANG :
CREATE OR REPLACE TRIGGER tr_delete_pasok
AFTER update ON pasok
FOR EACH ROW
DECLARE
Jml_lama integer;
Jml_baru integer;
Selisih integer;
BEGIN
select:old.jum_stok,:new.jum_pasok into jml_lama, jml_baru from pasok;
if (jml_lama>jml_baru) then
selisih := jml_lama - jml_baru;
update stok_barang
set jum_stok = jum_stok – selisih;
else
selisih := jml_baru - jml_lama;
update stok_barang
set jum_stok = jum_stok + selisih;
end if;
END;
