SQL ( Structured Query Language )

Structured Query Language

Sejarah

• Tahun 1986, ANSI (American National

Standards Institute) dan ISO mengumumkan standard SQL, SQL-86

• IBM merilis Systems Application Architecture) SAA-SQL tahun 1987
• Berturut-turut ANSI merilis SQL-89, SQL- 92, dan SQL- 99

SQL (Structured query language)

• SQL adalah bahasa yang digunakan untuk mengelola database relasional
• SQL adalah bahasa standard untuk sistem manajemen database relasional
• Sistem database yang menggunakan SQL :
  1. Oracle
  2. DB2
  3. Sybase
  4. MS SQL
  5. MS Access
  6. My SQL

Type Data

Dibedakan menjadi :

1. Tipe data numerik

semua data bilangan yang dapat diperhitungkan bukan angka yang bersifat keterangan, mis jumlah komputer

jenis tipe data numerik : integer, float, single, double, currency.

          2. Tipe data karakter

semua data huruf, angka dan tanda baca jenis tipe data karakter : char, string, text, memo.

          3. Tipe data tanggal

mendefinisikan waktu

jenis tipe data waktu : date, datetime, time, timestamp.

          4. Tipe data boolean ; tipe data khusus untuk menyatakan status benar atau salah, ya atau tidak.

kebanyakan mereka memiliki perintah tambahan yang proprietary.

Jenis perintah SQL :

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)

Skema Contoh

Struktur Dasar

• Select, berkaitan dengan operasi proyeksi

pada aljabar relasional. Digunakan untuk mendaftar atribut yang ingin dikeluarkan sebagai hasil query

• From, berkaitan dengan operasi produk

kartesian (relasi mana yang akan di-scan)

• Where, berkaitan dengan predikat seleksi.

Operasi SELECT

Operasi select digunakan untuk mengambil sebagian atau seluruh isi tabel dari suatu basisdata.

Contoh : “Tentukan nama-nama dari semua cabang bank dalam relasi loan “

Query-nya :

SELECT branch-name FROM loan

 

Operasi WHERE

Klausa where menspesifikasi kondisi yang harus dipenuhi oleh hasil query

– Berkaitan dengan predikat seleksi pada aljabar relasional.

Contoh : “Temukan semua loan number untuk pinjaman-pinjaman yang dibuat pada cabang Perryridge dengan jumlah lebih besar dari $1200”.

Query-nya ditulis sebagai berikut :

SELECT loan-number FROM loan WHERE branch-name = “Perryridge” and amount >1200;

• Perbandingan dapat dikombinasikan dengan

menggunakan operasi logika and, or, dan not.

• Operand hubungan logika dapat menggunakan

operasi perbandingan <,<=,>,>=,=, dan <>

• Contoh:

SELECT loan-number FROM loan WHERE amount <=100000 and amount >=90000;

• SQL juga memasukkan perintah between
  • untuk menentukan apakah suatu nilai lebih kecil daripada atau sama dengan suatu nilai lain dan lebih besar daripada atau sama dengan suatu nilai lain.
  • Contoh : “jika diinginkan menemukan loan-number

yang jumlah pinjamannya antara $90000 dan $100000”

Query ditulis sebagai berikut :

SELECT loan-number FROM loan WHERE amount between 90000 and 100000

• Klausa from menunjukkan daftar relasi

yang dilibatkan dalam query

– Berkaitan dengan operasi produk kartesian pada aljabar relasional

• Contoh: Produk Kartesian dari borrower x loan

select * from borrower, loan;

Contoh query : “Untuk semua customer yang mempunyai sebuah pinjaman dari bank, temukan nama dan loan number mereka”.

Dalam SQL ditulis :

SELECT distinct customer-name,borrower.loan-number FROM borrower, loan WHERE borrower.loan-number = loan.loan-number

Contoh: “Tampilkan nama,loan number and loan amount dari semua customer yang memiliki pinjaman di cabang Perryridge”

select customer-name, borrower.loan-number, amount from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’

DDL (data definition language)

Merupakan kelompok perintah yang digunakan untuk melakukan pendefinisian tabel.

Kelompok perintah DDL dapat membuat :

1. Tabel
2. Mengubah struktur
3. Menghapus tabel
4. Membuat index

DML (Data manipulation Language)

Digunakan untuk melakukan manipulasi data dalam database, menambahkan (insert), mengubah (update), menghapus (delete), mengambil dan mencari data (query).

Perintah SQL standar tsb dapat digunakan untuk menyelesaikan tugas yang diberikan berhubungan dengan data suatu database.

Dcl (data control language)

Perintah untuk melakukan pendefinisian pemakai yang boleh mengakses database dan apa saja privilegenya.

Fasilitas ini tersedia pada sistem manajemen database yang memiliki fasilitas keamanan dengan membatasi pemakai dan kewenangannya.

Data definision language (DDL)

digunakan untuk melakukan pembuatan struktur database, mulai dari mendefinisikan database, tabel-tabel dan indexnya, view, dan perintah-perintah berkenaan dengan maintenance dari strukture database itu sendiri.

Membuat database

Perintah CREATE DATABASE namadatabase

Perintah ini digunakan pertama kali sebelum membuat tabel, view, fungsi, prosedur atau pun komponen lain suatu sistem database.

Contoh :

create database datamahasiswa;

Membuat tabel

Perintah CREATE TABLE namatabel(Field1 TipeData1 [, field2 tipedata2[, ...] ]

);

perintah ini diberikan untuk membuat tabel dalam suatu database

contoh :

create table kota(kodekota char(3) not null,namakota varchar(35) null,primary key (kodekota));

Menambah Field baru tabel

Perintah ALTER TABLE namatable

ADD fieldbaru tipenya;

namatabel adalah nama dari tabel yang akan ditambah fieldnya

fieldbaru adalah nama field yang akan ditambahkan .

Contoh :

alter table bukualamat add foreign key (kodekota) reference kota (kodekota);

Mengubah lebar field tabel Perintah ALTER TABLE namatabel

MODIFY fieldnya tipenya panjangbaru namatabel adalah nama dari table yang akan diubah salah satu fieldnya.

fieldnya adalah nama field yang akan diubah lebar fieldnya.

tipenya dan panjangbaru merupakan berubahan yang akan diterapkan kepada tabel tsb.

contoh :

alter table dbmahasiswa modify (nama_mahasiswa char(45));

Menghapus tabel

Perintah DROP TABLE namatabel

namatabel adalah nama dari tabel yang akan dihapus secara fisik.

penghapusan menyebabkan struktur dan data yang dibuat akan hilang

Menghapus database

Perintah DROP DATABASE namadatabase;

namadatabase adalah nama dari database yang akan dihapus.

penghapusan database akan menyebabakan seluruh struktur dan data yang ada didalamnya menjadi hilang

Membuat index

Perintah

CREATE INDEX namaindeks ON namatabel (namakolom1[,namakolom2, ...])

namaindeks adalah nama yang diacu untuk mendapatkan data index dari suatu kolom dalam tabel.

namatabel adalah nama dari tabel yang kolom-kolomnya akan dibuatkan indexnya.

contoh :

create index kotaonbukualamat on bukualamat (kodekota);

Menghapus index

Perintah DROP INDEX namaindex ON namatabel

penghapusan index tidak menyebabkan terhapusnya tabel.

penghapusan index tabel suatu kolom hanya menyebabkan prosees pencarian data pada kolom tersebut bisa lebih lambat.

Data manipulation language (DML)

Merupakan bagian dari SQL yang digunakan untuk melakukan manipulasi dalam database (tambah, ubah, hapus, cari)

Contoh Dataset Loan

loan-number	branch-name	amount
L-170	Downtown	3000
L-230	Redwood	4000
L-260	Perryridge	1700

Borrower

customer-name	loan-number
Jones	L-170
Smith	L-230
Hayes	L-155

Insert

Perintah INSERT INTO namatabel (field1 [,field2 [,...]]) VALUE (nilai1 [,nilai2 [,...]]; atau INSERT INTO namatabel VALUES (nilai1 [,nilai2[,...]]); 

namatabel adalah tabel yang akan diisi data.

field1,field2 ... Adalah field-field (kolom) dari tabel yang akan diisi

nilai1,nilai2 ... Adalah data yang akan dimasukkan dalam tiap kolom yang disebutkan pada bagian field.

Untuk menambahkan satu tuple dalam relasi digunakan statement

insert.

Contoh :

INSERT INTO account values (“Perryridge”,”A-9732”,1200)

Query ini identik dengan

INSERT INTO account (branch-name, account-number,balance) values (“Perryridge”,”A-9732”,1200)

Insert juga dapat dilakukan untuk suatu hasil dari query yang lain.

Contoh :

INSERT INTO account SELECT branch-name, loan-number, 200

FROM loan WHERE branch-name = “Perryridge”

Update

Perintah UPDATE namatabel

SET field1 = nilai1 [, field2= nilai2 [,...]] [WHERE kondisi];

namatabel adalah nama dari tabel yang akan diperbaiki datanya

field1 adalah nama field dalam tabel yang akan diubah.

nilai1 adalah data yang akan dimasukan ke dalam field1

field2 dan nilai 2 adalah nama field dan datanya dst.

kondisi adalah kriteria data dalam tabel yang akan diperbaiki

perintah update digunakan untuk memperbaiki data dalam suatu record (baris) dalam suatu tabel, perbaikan dapat dilakukan untuk satu record, beberapa atau seluruh record.

Contoh :

untuk menaikkan saldo para nasabah sebesar 5% ditulis

query sebagai berikut :

UPDATE account SET balance = balance * 1.05

Untuk menaikkan saldo nasabah sebesar 6% bagi nasabah yang saldonya lebih dari $10000

UPDATE account SET balance = balance *1.06 WHERE balance >10000

question :

Apa yang akan terjadi jika dalam pengupdate-an suatu record apabila lupa menulis kondisinya ?

Query yang sama dengan sebelumnya: Naikkan semua account dengan saldo di atas $10,000 sebesar 6%, account yang lain sebesar 5%.

update account set balance = case when balance <= 10000 then balance *1.05 else balance * 1.06 end

delete

Perintah DELETE FROM namatabel [WHERE kondisi];

namatabel adalah nama dari tabel yang akan dihapus datanya.

kondisi adalah kriteria data dalam tabel yang akan dihapus.

perintah DELETE digunakan untuk melakukan penghapusan record dari suatu tabel yang memiliki kondisi yang dinyatakan dalam pernyataan kondisi.

“Hapus semua account pada cabang Perryridge”

delete from account where branch-name = ‘Perryridge’

“Hapus semua account di setiap cabang yang berlokasi di Needham city”

delete from account where branch-name in (select branch-name

from branch where branch-city = ‘Needham’) delete from depositor

where account-number in (select account-number from branch, account

where branch-city = ‘Needham’ and branch.branch-name = account.branch-name)

Apa yang akan terjadi jika dalam DELETE suatu record apabila lupa menulis kondisinya ?

Data mahasiswa

NIM	Nama	Jurusan
2901	Anjar	Sipil
2902	Jasmin	Hukum
2903	Bayu	Hukum

Ingin menghapus data Mahasiswa dengan NIM = 2902 :

DELETE from Mahasiswa Where NIM = 2902;

Hasil

NIM	Nama	Jurusan
2901	Anjar	Sipil
2903	Bayu	Hukum

Jika ingin menghapus record mahasiswa bernama Anjar dan Bayu bersamaan, dapat digunakan perintah :

DELETE from Mahasiswa

where Nama =‘Anjar’ or Nama=‘Bayu’;

Beberapa hal yang patut diperhatikan dalam penulisan perintah SQL adalah:

1. Perhatikan huruf besar - huruf kecil. Agus tidak sama dengan agus. Gaji_Pegawai tidak sama dengan GajiPegawai.
2. Jangan lupa untuk membubuhi tanda titik koma ( ; ) di setiap akhir penulisan perintah.

Select

Perintah

SELECT (* | field1 [,field2 [,...]]) FROM namatabel [WHERE kondisi]

namatabel adalah nama dari tabel yang akan ditampilkan datanya

field1,field2 ... Adalah nama field yang akan ditampilkan datanya.

* digunakan untuk menampilkan seluruh field dari tabel

kondisi adalah kriteria data dalam table yang akan ditampilkan

Perintah SELECT digunakan untuk menampilkan isi dari suatu tabel

Kondisi

LIKE

merupakan kata kunci dalam SQL yang digunakan untuk mendefinisikan suatu kriterisa yang lebih fleksibel.

Kondisi yang dinyatakan dengan menggunakan LIKE dapat memfilter data sehingga dapat menampilkan suatu kriteria seolah dengan menggunakan bahasa inggris.

Perintah

SELECT * FROM namatabel WHERE namafield LIKE ‘datadicari’;

perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan dengan “datadicari”

Perintah

SELECT * FROM namatabel WHERE namafield LIKE ‘datadicari%’;

perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan diawali dengan “datadicari”

View

Perintah

CREATE VIEW namaview

AS ekspresiQuery

namaview adalah nama dari view yang akan dibuat

ekspresiQuery adalah perintah select dan kondisi query yang ditentukan sama seperti halnya pada saat melakukan perintah select dengan menggunakan kondisi.

Data control language (DCL)

Terdiri atas sekelompok perintah SQL untuk memberikan hak otorisasi mengakses database, mengalokasikan space, pendefinisian space, pengauditan penggunaan database.

Secara umum DCL merupakan bahasa yang digunakan untuk melakukan pengelolahan pemakai yang dapat melakukan akses dan manipulasi database terutama perintah GRANT dan REVOKE

Perintah COMMIT dan ROLLBACK merupakan kelengkapan fasilitas dalam pembuatan aplikasi yang memungkinkan suatu transaksi yang terjadi untuk dapat segera disimpan atau dibatalkan transaksinya.

Fungsi Agregat

Fungsi yang disediakan oleh SQL untuk melakukan ringkasan (summary) data, bukan menampilkan data baris per baris.

Fungsi Agregat di SQL :

1. Sum()
2. Avg()
3. Max()
4. Min()
5. Count()

Fungsi agregat dapat disisipkan pada perintah SELECT, yang digunakan untuk melakukan manipulasi sederhana ataupun untuk mendapatkan informasi dari suatu tabel.

SUM

sum(namafield)

merupakan fungsi agregat yang digunakan untuk melakukan penjumlahan isi field yang bertipe numerik yang namanya disebutkan padan namafield yang dijadikan parameter pada fungsi sum()

Tabel Karyawan

Kode	Nama	Gaji
KP01	Amrin	200000
KP02	Camelia	300000
KP03	Bembi	100000

SELECT SUM(gaji) From Karyawan

Hasil

Gaji

600000

AVG

avg(namafield)

fungsi ini digunakan untuk mendapatkan nilai rata-rata suatu field yang bertipe numerik yang namanya disebutkan sebagai parameter pada fungsi avg().

SELECT AVG(gaji) From Karyawan

Hasil

Gaji

200000

Max

max(namafield)

fungsi ini digunakan untuk mendapatkan nilai terbesar(maximum) dari field bertipe numerik yang nama fieldnya dijadikan parameter pada fungsi min

Tabel Karyawan

Kode	Nama	Gaji
KP01	Amrin	200000
KP02	Camelia	300000
KP03	Bembi	100000

select MAX(gaji) from Karyawan;

Hasil :

Gaji

300000

Min

min(namafield)

fungsi ini digunakan untuk mendapatkan nilai terkecil (minimum) dari field bertipe numerik yang nama fieldnya dijadikan parameter pada fungsi min

select MIN(gaji) from Karyawan;

Gaji

100000

count(namafield)

digunakan untuk mengetahui jumlah record dari suatu tabel.

Jumlah record yang ditampilkan adalah jumlah record berdasarkan perintah SELECT

sql> use dbmahasiswa;

database changed

sql>select count(*) from bukumahasiswa

+----------------+

| count(*) |

|-----------------|

| 2 |

+-----------------+

Group by

Perintah Group By memiliki kegunaan untuk melakukan perhitungan berdasarkan kriteria tertentu.

Pegawai_baru

Kode	Nama	Asal	Pendidikan	Gaji
PB01	Ronald	Jakarta	S1	400000
PB02	Made	Bali	S1	300000
PB03	Aziz	Semarang	S1	300000
PB04	Mustofa	Semarang	D3	250000
PB05	Eka	Jakarta	S1	275000
PB06	Gozali	Yogya	D3	200000
PB07	Dani	Jakarta	S1	350000

Dari tabel Pegawai_baru, kita ingin menampilkan gaji tertinggi / maksimum yang diperoleh pegawai berdasarkan pendidikannya.

select Pendidikan,max(Gaji)
from Pegawai_baru
GROUP BY Pendidikan;

Hasil

D3	250000
S1	400000

select Asal,count(Asal) from Pegawai_baru GROUP BY Asal;

Bali	1
Jakarta	3
Semarang	2
Yogya	1

select Pendidikan,count(Pendidikan),sum(Gaji) from Pegawai_baru GROUP BY Pendidikan;

D3	2	162500
S1	5	450000

Read More

Perancangan Basis Data

Proses Perancangan Basis Data
BAB III

Tujuan

• Mengerti yang dimaksud dengan Sistem Basis Data dan komponen-komponennya
• Mengetahui perbedaan Data dan DBMS
• Mengetahui Life Cycle Database
• Mengetahui langkah men-design database.

Sistem Basis Data

adalah suatu sistem menyusun dan mengelola record-record menggunakan computer untuk menyimpan atau merekam serta memelihara data operasional lengkap sebuah organisasi/perusahaan sehingga mampu menyediakan informasi yang optimal yang diperlukan pemakai untuk proses mengambil keputusan.

Perancangan Basis data

Untuk mengelola sumber informasi yang pertama kali dilakukan adalah merancang suatu sistem database agar informasi yang ada pada organisasi tersebut dapat digunakan secara maksimal.

Tujuan Perancangan Database

1. Untuk memenuhi kebutuhan akan informasi dari pengguna dan aplikasi
2. Menyediakan stuktur informasi yang natural dan mudah dimengerti oleh pengguna.
3. Mendukung kebutuhan pemrosesan dan beberapa obyek kinerja dari suatu sistem database

Siklus Kehidupan Sistem Informasi (Macro Life Cycle)

Tahapan-tahapan yang ada pada siklus kehidupan sistem informasi yaitu :

1. Analisa Kelayakan

Tahapan ini memfokuskan pada

1. penganalisaan areal aplikasi yang unggul,
2. mengidentifikasi pengumpulan informasi dan penyebarannya
3. mempelajari keuntungan dan kerugian, penentuan kompleksitas data dan proses,
4. menentukan prioritas aplikasi yang akan digunakan

1. Analisa dan Pengumpulan Kebutuhan Pengguna
   1. Kebutuhan-kebutuhan yang detail dikumpulkan dengan berinteraksi pada kelompok pemakai atau pemakai individu.
   2. Mengidentifikasikan masalah yang ada dan kebutuhan-kebutuhan.
   3. Ketergantungan antar aplikasi, komunikasi dan prosedur laporan.
2. Perancangan
   1. Perancangan sistem database
   2. Sistem aplikasi
3. Implementasi

Mengimplementasikan sistem informasi dengan database yang ada

1. Pengujian dan Validasi

Pengujian dan validasi sistem database dengan kriteria kinerja yang diinginkan oleh pengguna

1. Pengoperasian dan Perawatan

Pengoperasian sistem setelah di validasi disertai dengan pengawasan dan perawatan sistem.

Daur Hidup (Life Cycle) yang Umum dari Aplikasi Basis Data

• Definisi Sistem
• Database Design
• Implementasi
• Loading/Konversi Data
• Konversi Aplikasi
• Testing & Validasi
• Operations
• Control & Maintenance

Daur Hidup (Life Cycle) dari Aplikasi Basis Data

• Definisi Sistem:

Ø ruang lingkup basis data

Ø pemakai

Ø aplikasi

• Design:

Ø logical design à ER/EER

Ø physical design untuk suatu DBMS

• Implementasi:

Ø membuat basis data (kosong)

Ø membuat program aplikasi

• Loading/ Konversi Data:

Ø memasukkan data ke dalam basis data

Ø mengkonversi file yang sudah ada ke dalam format basis data dan kemudian memasukkannya dalam basis data

• Konversi Aplikasi:

Semua aplikasi dari sistem sebelumnya dikonversikan ke dalam sistem basis data.

• Testing dan Validasi:

Sistem yang baru harus ditest dan divalidasi (diperiksa keabsahannya).

• Operasi:

Pengoperasian basis data dan aplikasinya.

• Monitoring dan Maintenance:

Selama operasi, sistem dimonitor dan diperlihara. Baik data maupun program aplikasi masih dapat terus tumbuh dan berkembang.

Basis Data biasanya merupakan salah satu bagian dari suatu sistem informasi yang besar yang antara lain terdiri dari:

• Data
• Perangkat lunak DBMS
• Perangkat keras komputer
• Perangkat lunak dan sistem operasi komputer
• Program-program aplikasi
• Pemrogram, dll

Proses Design Basis Data

1. Pengumpulan dan analisa requirement
2. Design basis data conceptual
3. Pemilihan DBMS

4. Mapping dari conceptual ke logical
5. Physical Design
6. Implementasi

Keenam phase dalam proses design tidak perlu dilaksanakan secara mutlak, mungkin ada umpan balik antar phase dan dalam masing-masing phase

Proses Design Paralel

Proses design terdiri dari dua proses yang paralel yaitu:

• Proses design dari data dan struktur dari basis data (data driven)
• Proses design dari program aplikasi dan pemrosesan basis data (process driven)

Mengapa Harus Paralel

Karena kedua proses tersebut saling bergantungan.

Contoh:

1. Menentukan data item yang akan disimpan dalam basis data tergantung dari aplikasi basis data tersebut, juga dalam menentukan struktur dan akses path.

2. Design dari program aplikasi tergantung dari struktur basis datanya.

3. Biasanya condong ke salah satu.

Proses design

Phase 1: Pengumpulan Data & Analisa Requirement

• Pengidentifikasian group pemakai dan area aplikasi
• Penelitian kembali dokumen-dokumen yang sudah ada yang berhubungan dengan aplikasi à form, report, manual, organization chart, dsb
• Analisa lingkungan operasi dan kebutuhan dari pemrosesan, seperti tipe transaksi, input/output, frekuensi suatu transaksi, dsb
• Transfer informasi informal ke dalam bentuk terstruktur menggunakan salah satu bentuk formal dari requirement specification (bentuk diagram) seperti Flow Chart, DFD, UML Diagram, dll. Hal ini dilakukan untuk mempermudah pemeriksaan kekonsistenan, ketepatan, dan kelengkapan dari spesifikasi.

Phase 2A: Design Conceptual Schema

• High level data model, bukan implementation-level data model
• Memberikan gambaran yang lengkap dari struktur basis data yaitu arti, hubungan, dan batasan-batasan.
• Conceptual schema bersifat tetap
• Alat komunikasi antar pemakai basis data, designer, dan analis

Harus bersifat:

• Mampu menyatakan relationship, batasan-batasan
• Diagram
• Formal, minimum dalam menyatakan spesifikasi data (tidak ada duplikasi)
• Simple
• Conceptual data model harus DBMS independent à ER/EER

Phase 2b: Design Transaksi

• Pada saat suatu basis data di-design, aplikasi dari transaksi utama harus sudah diketahui
• Transaksi-transaksi baru dapat didefinisikan kemudian
• Tentukan karakteristik dari transaksi dan periksa apakah basis data sudah memuat semua informasi untuk melaksanakan transaksi

• Transaksi dapat dibagi dalam 3 bagian yaitu:

- retrieval

- update

- mixed

• Phase 2a dan 2b sebaiknya dilaksanakan secara paralel dengan menggunakan umpan balik agar didapat design schema dan transaksi yang stabil

Phase 3: Pemilihan DBMS

• Pemilihan DBMS ditentukan oleh sejumlah faktor antara lain:
  • faktor teknis: storage, akses path, user interface, programmer, bahasa query
  • faktor ekonomi: software, hardware, maintenance, training, operasi, konversi, teknisi, dll
  • faktor organisasi: kompleksitas, data, sharing antar aplikasi, perkembangan data, pengontrolan data

Phase 4: Mapping dari Data Model

• Memetakan conceptual model ke dalam DBMS
• Menyesuaikan schema dengan DBMS pilihan
• Hasil pemetaan biasanya berupa DDL

Phase 5: Physical Design

• Struktur storage, akses path untuk mendapatkan performance yang baik
• Kriteria baik dapat dilihat dari:

- response time

- pemakaian storage

- throughput (jumlah transaksi per unit waktu)

• Perlu tuning untuk memperbaiki performance berdasarkan statistik pemakaian

Phase 6: Implementasi Sistem Basis Data

• DDL dan SDL dari DBMS dikompilasi membentuk schema basis data dan basis data yang masih kosong
• Basis data dapat dimuati (di-load) dari sistem yang lama
• Transaksi dapat diimplementasikan oleh program aplikasi dan dikompilasi
• Siap dioperasikan

Kesimpulan

Pengetahuan mengenai model data dan teknik design database adalah penting bagi praktisi database dan pengembang aplikasi.

Database life cycle menggambarkan langkah yang dibutuhkan dalam metode pendekatan untuk mendesign database dari logical design ke physical design.

Studi Kasus

Di bawah ini deskripsi mengenai suatu perusahaan yang akan di representasikan dalam database dan buat sesuai dengan proses perancangan database dari tahap 1 s/d tahap 4.

1. Suatu perusahaan terdiri atas bagian–bagian, masing–masing bagian mempunyai nama, nomor bagian dan lokasi . Setiap bagian mempunyai seorang pegawai yang mempunyai seorang pimpinan yang memimpin bagian tersebut.
2. Setiap bagian mengontrol sejumlah proyek dimana masing–masing proyek mempunyai nama, nomor proyek dan lokasi .
3. Setiap pegawai menjadi anggota pada salah satu bagian tapi dapat bekerja di beberapa proyek . Untuk setiap pegawai yang bekerja di proyek mempunyai jam kerja per-minggu . Seorang pegawai mempunyai nama, nomor pegawai, alamat, jenis kelamin, tanggal lahir dan usia serta supervisor / penyelia langsung. Pegawai juga mempunyai tanggungan yang terdiri atas nama, jenis kelamin dan hubungannya dengan si pegawai.

Read More

Distribusi Database

Distribusi Database

Pokok Bahasan

• Pendahuluan
• Tipe Basis Data Terdistribusi
• Arisitektur Basis Data Terdistribusi
• Penyimpanan Data pada Sistem Terdistribusi
• Manajemen Katalog Terdistribusi
• Qery Terdistribusi
• Joins pada DBMS Terdistribusi
• Mengubah Data Terdistribusi
• Locking pada Sistem Terdistribusi
• Distribusi Recovery

Tujuan

Setelah mempelajari materi bab ini, mahasiswa diharapkan mampu :

1. Memahami perbedaan DBMS terdisribusi dan DMBS terpusat.
2. Memahami arsistektur basis data terdistribusi
3. Memahami penyimpanan data, catalog data pada system terdistribusi
4. Memahami query, join dan optimasi query pada DBMS terdistribusi
5. Memahami bagaimana mengubah data, melakukan locking data pada DBMS terdistribusi.
6. Memahami bagaimana menangani kegagalan pada sistem terdistribusi

Pendahuluan

Pada basis data terdistribusi (distributed database), data disimpan pada beberapa tempat (site), setiap tempat diatur dengan suatu DBMS (Database Management System) yang dapat berjalan secara independent.

Properti yang terutama terdapat pada basis data

terdistribusi :

• Independensi data terdistribusi : pemakai tidak perlu mengetahui dimana data berada (merupakan pengembangan prinsip independensi data fisik dan logika).
• Transaksi terdistribusi yang atomic : pemakai dapat menulis transaksi yang mengakses dan mengubah data pada beberapa tempat seperti mengakses transaksi local.

Kedua property tersebut harus mendukung system secara efisien. Untuk system terdistribusi yang bersifat global, properti-properti tersebut kemungkinan tidak tepat karena adanya administrasi yang terlalu berlebihan dalam membuat lokasi data yang transparan.

Tipe Basis Data Terdistribusi

Terdapat dua tipe basis data terdistribusi :

• Homogen : yaitu sistem dimana setiap tempat menjalankan tipe DBMS yang sama
• Heterogen : yaitu sistem dimana setiap tempat yang berbeda menjalankan DBMS yang berbeda, baik Relational DBMS (RDBMS) atau non relational DBMS.

Arsitektur Basis Data Terdistribusi

Terdapat tiga pendekatan alternatif untuk membagi fungsi pada proses DBMS yang berbeda. Dua arsitektur alternatif DBMS terdistribusi adalah Client/Server dan Collaboration Server.

1. Client-Server

Sistem client-server mempunyai satu atau lebih proses client dan satu atau lebih proses server, dan sebuah proses client dapat mengirim query ke sembarang proses server . Client bertanggung jawab pada antar muka untuk user, sedangkan server mengatur data dan mengeksekusi transaksi. Sehingga suatu proses client berjalan pada sebuah personal computer dan mengirim query ke sebuah server yang berjalan pada mainframe.

Arsitektur ini menjadi sangat popular untuk beberapa alasan.

Pertama, implementasi yang relatif sederhana karena pembagian fungis yang baik dank arena server tersentralisasi.

Kedua, mesin server yang mahal utilisasinya tidak

terpengaruh pada interaksi pemakai, meskipun mesin client tidak mahal.

Ketiga, pemakai dapat menjalankan antarmuka berbasis grafis sehingga pemakai lebih mudah dibandingkan antar muka pada server yang tidak user-friendly.

2.Collaboration Server

Arsitektur client-server tidak mengijinkan satu query mengakses banyak server

karena proses client harus dapat membagi sebuah query ke dalam beberapa subquery untuk dieksekusi pada tempat yang berbeda dan kemudian membagi jawaban ke subquery.

Proses client cukup komplek dan terjadi overlap dengan server; sehingga

perbedaan antara client dan server menjadi jelas. Untuk mengurangi perbedaan digunakan alternatif arsitektur client-server yaitu sistem Collaboration Server.

Pada sistem ini terdapat sekumpulan server basis data, yang menjalankan transaksi data lokal yang bekerjasama mengeksekusi transaksi pada beberapa server .

Jika server menerima query yang membutuhkan akses ke data pada server lain,

sistem membangkitkan subquery yang dieksekusi server lain dan mengambil

hasilnya bersama-sama untuk menggabungkan jawaban menjadi query asal.

Sistem basis data

Ada 2 sistem basis data :

1. Terpusat (Centralized)
2. Terdistribusi (Distributed)

Perbedaan utama ke dua sistem tersebut :

• Pada sistem terpusat data ditempatkan di satu lokasi dan semua lokasi lain mengakses basis data di lokal tsb.
• Pada sistem terdistribusi data ditempatkan dibanyak lokasi, tetapi menerapkan suatu mekanisme tertentu untuk membuatnya menjadi satu kesatuan basis data.

Struktur Basis Data Terdistribusi

Sebuah sistem basis data terdistribusi hanya mungkin dibangun dalam sebuah sistem jaringan komputer (topologi).

Sistem topologi yang dapat digunakan :

1. Topologi star (bintang)
2. Topologi Ring (cincin)
3. Topoogi Bus

Perbedaan utama dari ke tiga topologi tsb :

1. Biaya Instalasi

Biaya dalam membangun hubungan (link) antar simpul.

2. Biaya Komunikasi

Waktu dan biaya dalam pengoperasian sistem berupa pengirim data dari satu simpul ke simpul lain.

3.Kehandalan

Frekuensi/tingkat kegagalan komunikasi yang terjadi.

4.Ketersediaan

Tingkat kesiapan data yang dapat diakses sebagai antisipasi kegagalan komunikasi.

Jenis Transaksi sistem basis data terdistribusi

Dalam sebuah sistem basis data terdistribusi, ada 2 jenis transaksi yang terjadi :

1. Transaksi lokal

Transaksi yang mengakses data pada suatu simpul (mesin/server) yang sama dengan simpul.

2.Transaksi Global

Transaksi yang membutuhkan pengaksesan data di simpul yang berbeda dengan simpul dimana transaksi tsb dijalankan, atau transaksi dari sebuah simpul yang membutuhkan pengaksesan data ke sejumlah simpul yang lain.

Keuntungan dan Kerugian

Keuntungan dari Basis Data Terdistribusi :

1. Pembagian (pemakaian bersama) Data dan Kontrol yang Tersebar.

Setiap user pada suatu lokasi (simpul) dapat mengakses data yang berada di lokasi lainnya.

2. Kehandalan dan Ketersediaan.

Jika ada sebuah simpul mengalami kerusakan, simpul/lokasi yang lain akan tetap dapat beroperasi.

3.Kecepatan Query

Sebuah query melibatkan data di sejumlah lokasi/simpul, maka query tsb dapat dipilah ke sejumlah subquery yang akan dijalankan di simpul yang bersesuaian.

Kelemahan dari sistem basis data terdistribusi :

1. Biaya pembangunan perangkat lunak.

perlu biaya besar untuk implementasi sistem basis data.

2.Potensi Bug yang lebih Besar.

Akan lebih sulit menjamin kebenaran algoritma/program karena beroperasi secara paralel.

3.Peningkatan Waktu Proses (Overhead).

Waktu untuk pertukaran data dan tambahan komputasi yang diperlukan untuk mengupayakan koordinasi antar simpul merupakan beban tambahan (overhead) yang tidak dijumpai dalam sistem terpusat.

Desain Basis Data Terdistribusi

Ada beberapa pendekatan yang berkaitan dengan penyimpanan data/tabel dalam sebuah sistem basis data terdistribusi.

1. Fragmentasi

Data dalam tabel dipilah dan disebarkan ke dalam sejumlah fragmen. Tiap fragmen disimpan di sejumlah simpul yang berbeda-beda.

2.Replikasi

Sistem memelihara sejumlah salinan/duplikat tabel data. Setiap salinan tersimpan dalam simpul yang berbeda, yang menghasilkan replikasi data.

3. Replikasi dan Fragmentasi

Merupakan kombinasi dari kedua hal sebelumnya. Data/tabel dipilah dalam sejumlah fragmen. Sistem lalu mengelola sejumlah salinan dari masing-masing fragmen tadi di sejumlah simpul.

Fragmentasi Data

Rekontruksi ini dapat dilakukan melalui sebuah penerapan operasi Union (untuk menggabungkan baris data) ataupun operasi Natural Join (penggabungan field data) terhadap fragmen-fragmen tersebut.

Ada 2 jenis pembentukan fragmentasi :

1. Fragmentasi Horizontal
2. Fragmentasi Vertikal
   
   

Fragmentasi Horizontal

Fragmentasi Horizontal sebuah tabel r di partisi ke dalam sejumlah fragmen r_1, r₂, r₃ ... r_n yang merupakan pemilahan baris data. Setiap baris data pada tabel r harus berada minimal di sebuah fragmen, sedemikian hingga tabel awalnya dapat dibentuk kembali jika diperlukan.

Kita dapat melakukan rekonstruksi dari tabel r dengan menerapkan operasi Union dari semua fragmen, dengan ekspresi :

r = r₁ U r₂ U r₃ ... Ur_n

Menurut ekspresi diatas maka nasabah1 dan nasabah2 dapat digabungkan dengan operasi Union untuk mendapatkan kembali tabel Nasabah awal.

Nasabah = Nasabah₁ U Nasabah₂

Fragmentasi Vertikal

Fragmentasi Vertikal sama dengan dekomposisi (penguraian) tabel yang merupakan pemilahan field.

Bagaimana cara untuk memutuskan field apa saja yang akan ditempatkan di fragmen pertama, di fragmen kedua dan fragmen-fragmen selanjutnya ?

Answer :

Dengan mempertimbangkan fungsinya atau perkiraan frekuensi pemakaian.

Ekspresi :

nasabah₁ = Π no_nasabah, nama, alamat, kota (nasabah)

nasabah₂ = Π no_nasabah, saldo_simpan(nasabah)

nasabah₃= Π no_nasabah, saldo_pinjam(nasabah)

Satu hal yang paling penting diperhatikan dalam Fragmentasi adalah jaminan bahwa kita dapat mengembalikan semua fragmen itu ketabel semula dengan tepat.

r = r₁ |Χ| r₂ |Χ|r₃ ... |Χ|r_n

Replikasi Data

berarti bahwa kita menyimpan beberapa copy sebuah relasi atau fragmen relasi. Keseluruhan relasi dapat direplikasi pada satu atau lebih tempat.

contoh :

jika relasi R difragmentasi ke R1, R2 dan R3, kemungkinan terdapat hanya satu copy R1, dimana R2 adalah replikasi pada dua tempat lainnya dan R3 replikasi pada semua tempat.

Motivasi untuk replikasi adalah :

• Meningkatkan ketersediaan data

Jika sebuah tempat yang berisi replika melambat, kita dapat menemuka data yang sama pada tempat lain. Demikian pula, jika copy lokal dari relasi yang diremote tersedia, maka tidak terpengaruh saluran komunikasi yang gagal.

• Evaluasi query yang lebih cepat

query dapat mengeksekusi lebih cepat menggunakan copy local dari relasi termasuk ke remote site.

• Memperbaiki performansi dari operasi query

Manajemet Katalog Terdistribusi

Menyimpan data terdistribusi pada beberapa tempat dapat menjadi sangat kompleks. Kita harus menyimpan data bagaimana relasi difragmentasi dan replikasi, bagaimana fragmen relasi didistribusikan ke beberapa tempat dan dimana kopi dari fragmen disimpan. Nama setiap replika dari setiap fragmen harus ada. Untuk menyediakan otonomi lokal digunakan format sebagai berikut :

<local-name, birth-site>

Katalog setiap tempat menggambarkan semua obyek (fragmen, replika) pada suatu tempat dan menyimpan data replika dari relasi yang dibuat pada tempat tersebut.

Untuk menemukan relasi, lihat pada katalog birth-site. Birth-site tidak pernah berubah meskipun relasi dipindahkan.

Query Terdistribusi

Misalnya pada dua relasi :

Sailors(sid: integer, sname: string, rating: integer, age: real)

Reserves(sid: integer, bid: integer, day: date, rname: string)

Kemudian dilakukan query berikut :

SELECT AVG(S.age) FROM Sailors S WHERE S.rating > 3 AND

S.rating < 7

• Fragmentasi horisontal : tupel dengan rating < 5 pada Shanghai, >= 5 pada Tokyo. Harus menghitung SUM(age), COUNT(age) pada kedua tempat. Jika WHERE berisi hanya S.rating>6, maka hanya satu tempat.
• Fragmentasi vertikal : sid dan rating pada Shanghai, sname dan age pada Tokyo, tid pada kedua tempat. Harus melakukan rekonstruksi relasi dengan join pada tid kemudian mengevaluasi query.
• Replikasi : Sailor di-copy kan pada kedua tempat.

Joins pada DBMS Terdistribusi

Sebagai contoh, London menyimpan 500 halaman Sailor dan Paris mempunyai 1000 halaman Reserves seperti Gambar berikut

LONDON PARIS

500 halaman 1000 halaman

contoh sistem terdistribusi

Optimasi Query pada DBMS Terdistribusi

Untuk optimasi query pada sistem terdistribusi, menggunakan pendekatan biaya, misalnya pada semua plan, mengambil yang termurah, sama dengan optimasi tersentralisasi. Perbedaan optimasi query pada sistem terdistribusi dan sistem tersentralisasi,

1. biaya komunikasi harus dipertimbangkan.
2. otonomi tempat lokal harus diperhatikan.
3. menggunakan metode join terdistribusi yang baru.

Query site membangun daerah global, dengan daerah local menggambarkan pemrosesan pada setiap tempat. Jika sebuah tempat dapat melakukan improvisasi pada daerah lokal, dapat dilakukan dengan bebas.

Mengubah Data Terdistribusi

Untuk melakukan pengubahan data terdistribusi, dilakukan replikasi transaksi yang dapat dilakukan dengan cara :

1. Synchronous Replication

semua copy dari relasi yang dimodifikasi (fragmen) harus diubah sebelum modifikasi transaksi commit. Distribusi data dibuat transparan ke pemakai.

1. Asynchronous Replication

Copy dari sebuah relasi yang dimodifikasi hanya diubah secara periodik, copy yang berbeda akan keluar dari sinkronisasi. User harus waspada pada distribusi data. Produk saat ini mengikuti pendekatan ini.

Synchronous Replication

Terdapat dua teknik dasar untuk menjamin transaksi terlihat nilai yang sama dengan copy, yaitu :

• Voting

transaksi harus menulis mayoritas copy untuk memodifikasi sebuah obyek, harus membaca cukup copy untuk meyakinkan bahwa terlihat setidaknya satu dari copy saat itu. Misalnya terdapat 10 copy, 7 penulisan untuk perubahan dan 4 copy untuk pembacaan. Setiap copy mempunyai nomor versi. Teknik ini biasanya tidak atraktif karena pembacaan adalah hal yang biasa.

• Read-any Write-all

penulisan lebih lambah dan pembacaan lebih cepat daripada teknik Voting. Teknik ini banyak digunakan pada synchronous replication

Pemilihan teknik synchronous replication akan menentukan tempat mana yang terkunci untuk seting.

Data Warehousing : Sebuah contoh Replication

Trend yang berkembang saat ini adalah membangun “warehouses” data yang sangat besar dari beberapa tempat. Hal ini memungkinkan untuk query pendukung keputusan yang kompleks dari data pada keseluruhan organisasi. Warehouse dapat dipandang sebagai instance dari asynchronous replication. Data sumber biasanya dikontrol dengan DBMS yang berbagi, penekanannya pada cleaning data dan menghapus kesalahan pada pembuatan replikasi. Prosedur Capture dan aplikasi Apply baik untuk lingkungan ini.

LOCKING PADA SISTEM TERDISTRIBUSI

Untuk menangani penguncian obyek pada beberapa tempat digunakan cara :

• Sentralisasi :

satu tempat melakukan semua penguncian dan membuka kunci untuk semua obyek

• Primary Copy :

semua penguncian untuk suatu obyek dikerjakan pada tempat primary copy dari obyek tersebut. Untuk pembacaan membutuhkan akses ke tempat terkunci sebaik tempat dimana obyek disimpan.

• Terdistribusi penuh :

penguncian untuk suatu copy dilakukan pada tempat dimana copy disimpan. Hal in akan mengunci semua tempat pada saat menulis obyek.

Distribusi Recovery

Proses pemulihan pada DBMS terdistribusi lebih kompleks daripada pada DBMS tersentralisasi karena sebab berikut :

• Terjadi kegagalan yang baru, misalnya saluran komunikasi dan remote site.
• Jika sub transaksi dari suatu transaksi mengeksekusi tempat yang berbeda, semua atau tidak ada yang harus commit. Hal ini memerlukan commit protocol untuk menangani hal tersebut.

Suatu log ditangani pada setiap tempat, sebagaimana pada DBMS tersentralisasi dan aksi commit protocol ditambahkan pada log.

Ringkasan

• Pada basis data terdistribusi, data disimpat pada beberapa lokasi dengan tujuan untuk membuat distribusi yang transparan. Pada basis data terdistribusi, distributed data independence (pemakai tidak perlu mengetahui lokasi data ) dan distributed transaction atomicity (dimana tidak ada perbedaan antara transaksi terdistribusi dan transaksi local). Jika semua lokasi menjalankan perangkat lunak DBMS yang sama, system disebut homogen, selain itu disebut heterogen.
• Arsitektur sistem basis data terdistribusi terdapat tiga tipe. Pada system Client-Server, server menyediakan fungsi DBMS dan client menyediakan antar muka pemakai. Pada Collaboration system system, tidak terdapat perbedaan antara proses client dan server.
• Pada DBMS terdistribusi, suatu relasi difragmentasi dan direplikasi pada beberapa tempat. Dalam fragmentasi horizontal, setiap partisi terdiri dari himpunan baris dari relasi asal. Dalam fragmentasi vertika, setiap partisi terdiri dari himpunan kolom pada relasi asal. Pada replikasi, disimpan beberapa copy dari relasi atau suatu partisi pada beberapa tempat.
• Jika suatu relasi difragmen dan direplika, setiap partisi memerlukan nama global yang unik yang disebut relation name. Manajemen catalog terdistribusi diperlukan untuk menyimpan rekaman dimana data disimpan.
• Jika suatu transaksi melibatkan aktivitas pada tempat yang berbeda, maka memanggil aktivitas sub transaksi.
• Pada DBMS terdistribusi, manajemen lock berupa lokasi sentral, primary copy atau terdistribusi penuh. Deteksi deadlock pada system terdistribusi dibutuhkan.
• Pada pemrosesan query dalam DBMS terdistribusi, lokasi partisi dari relasi perlundihitung. Join dua relasi dapat dilakukan dengan mengirim satu relasi ke tempat lain dan membentuk local join. Jika join melibatkan kondisi seleksi, jumlah tupel yang diperlukan kemungkinan kecil. Semijoin dan Bloomjoin mengurangi jumlah tupel yang dikirim ke jaringan dengan mengirim informasi terlebih dahulu yang mengijinkan mem-filter tupel yang tidak relevan. Optimasi query pada system terdistribusi harus mempertimbangkan komunikasi dengan model biaya.
• Pada synchronous replication, semua copy dari relasi replica diubah sebelum transaksi commit. Pada asynchronous replication, copy hanya diubah secara periodic. Terdapat dua teknik untuk menjamin synchronous replication. Secara voting, perubahan harus menulis mayoritas copy dan membaca harus mengakses cukup copy untuk menjamin bahwa satu copy sudah tersedia. Pada replikasi peerto-

peer, lebih dari satu copy dapat diubah dan strategi conflict resolution dapat mengubah konflik yang terjadi. Pada replikasi primary site, terdapat satu primary copy yang dapat diubah, copy sekunder lain tidak dapat diubah. Pengubahan pada primary copy dipropaganda menggunakan capter dan kemudian apply ke tempat lain.

• Pemulihan pada DBMS terdistribusi dilakukan menggunakan commit protocol yang mengkoordinasi aktivitas pada tempat yang berbeda yang dilibatkan pada transaksi. Pada Two-Phase Commit, setiap transaksi didesain oleh tempat coordinator. Sub transaksi dieksekusi pada tempat sub ordinat. Protokol menjamin bahwa perubahan dibuat oleh beberapa transaksi dapat dipulihkan. Jika tempat coordinator bertabrakan, sub ordinat di blok, dan sub ordinat harus menunggu coordinator pulih.

Read More