Tugas Basis Data (view, Trigger, Store Procedure)

            1. View

Dalam sistem relasional, sebuah view adalah sebuah relasi virtual. Definisi view adalah  hasil (result) dari sebuah Query terhadap relasi- relasi dasar (atau relasi real). Hasil (view) ini tidak disimpan dalam database seperti relasi dasar. Sebuah view adalah sebuah jendela dinamik, dalam artian bahwa ia mencerminkan semua update yang dilakukan terhadap database. Disamping pemakaiannya di dalam skemaexternal, view juga berguna untuk menjamin data- security dengan cara yang sederhana. Dengan memilih subset dari database, view dapat menyembunyikan beberapa data. Jika user mengakses database melalui view, mereka tak dapat melihat atau memanipulasi hidden-data, dengan demikian data akan menjadi secure. Perhatikan bahwa, dalam sistem DBMS terdistribusi, sebuah view dapat diturunkan dari relasi-relasi terdistribusi. Akses ke sebuah view akan memer lukan eksekusi dari kueri terdistribusi yang berhubungan dengan definisi view tersebut.

Contoh penggunaan view dalam SQL :

set pagesize 500
alter session set nls_date_format = 'DD.MM.YYYY';

create table prices_ (
sku        varchar2(38),
price      number,
valid_from date
);

insert into prices_ values ('4711', 18, '08.01.2003');
insert into prices_ values ('4711', 19, '01.05.2000');
insert into prices_ values ('4711', 20, '18.01.2001');
insert into prices_ values ('4711', 21, '09.01.2000');

insert into prices_ values ('beer', 14, '07.03.2000');
insert into prices_ values ('beer', 15, '10.01.2003');
insert into prices_ values ('beer', 16, '18.01.2001');
insert into prices_ values ('beer', 19, '16.11.2001');
insert into prices_ values ('beer', 17, '19.02.2002');


create view prices_today_ as
select
  sku,
  price,
  valid_from
from
  (select
     sku,
     price,
     valid_from,
     rank() over (partition by sku order by valid_from desc) r
   from
     prices_
  )
where r=1;


select * from prices_today_;

drop table prices_;
drop view prices_today_;

         2. Trigger

Trigger adalah blok PL/SQL atau prosedur yang berhubungan dengan table, view, skema atau database yang dijalankan secara implicit pada saat terjadi sebuah event. Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE. INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

Tipe dari trigger adalah :

· Application trigger : diaktifkan pada saat terjadi event yang berhubungan dengan sebuah aplikasi

· Database trigger : diaktifkan pada saat terjadi event yang berhubungan dengan data (seperti operasi DML) atau event yang berhubungan dengan sistem (semisal logon atau shutdown) yang terjadi pada sebuah skema atau database.

Trigger perlu dibuat pada saat :

· Membentuk sebuah aksi tertentu terhadap suatu event
· Memusatkan operasi global

Trigger tidak perlu dibuat, jika :

· Fungsionalitas yang diperlukan suatu ada pada Oracle server
· Duplikat atau sama dengan fungsi trigger yang lain.

Sintak penulisan dari database trigger, berisi komponen berikut :

1. Trigger timing :
a. Untuk tabel : BEFORE, AFTER
b. Untuk view : INSTEAD OF
2. Trigger event : INSERT, UPDATE atau DELETE
3. Nama tabel : yaitu nama tabel atau view yang berhubungan dengan trigger
4. Tipe trigger : Baris atau Pernyataan (statement)
5. klausa WHEN : untuk kondisi pembatasan
6. trigger body : bagian prosedur yang dituliskan pada trigger

Trigger timing adalah waktu kapan trigger diaktifkan. Ada tiga macam trigger timing, yaitu :

· BEFORE : trigger dijalankan sebelum DML event pada tabel
· AFTER : trigger dijalankan setelah DML event pada tabel
· INSTEAD OF : trigger dijalankan pada sebuah view.

Trigger event ada 3 kemungkinan : INSERT, UPDATE atau DELETE.
Pada saat trigger event UPDATE, kita dapat memasukkan daftar kolom untuk mengidentifikasi kolom mana yang berubah untuk mengaktifkan sebuah trigger (contoh : UPDATE OF salary ... ). Jika tidak ditentukan, maka perubahannya akan berlaku untuk semua kolom pada semua baris.

Tipe trigger ada 2 macam, yaitu :

· Statement : trigger dijalankan sekali saja pada saat terjadi sebuah event. Statement trigger juga dijalankan sekali, meskipun tidak ada satupun baris yang dipengaruhi oleh event yang terjadi.

· Row : trigger dijalankan pada setiap baris yang dipengaruhi oleh terjadinya sebuah event. Row trigger tidak dijalankan jika event dari trigger tidak berpengaruh pada satu baris pun.

Trigger body mendefinisikan tindakan yang perlu dikerjakan pada saat terjadinya event yang mengakibatkan sebuah trigger menjadi aktif.

2.    Sintak SQL  dalam Triger.

Bentuk dasar perintah dari Triger : a. Sintak Membuat Trigger

CREATE TRIGGER nama_trigger

ON nama_tabel

FOR INSERT, UPDATE, DELETE

AS

isi statement-statement Anda disini.

GO

b. Sintak Membuat dan Mengisi Tabel pada Trigger

           3. Store Procedure

  1.  Definisi Store Procedure

Procedure Adalah program yang disimpan dalam database seperti halnya data. Hal ini sebenanya cukup tidak umum, karena kita mengharapkan yang disimpan dalam database adalah data bukannya program.

Adanya dukungan Stored Procedure akan membuat program anda lebih ringkas dan mudah untuk dikembangkan.

Stored procedure akan membantu anda dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali tabel2 pembantu. . Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja.

2.      Sintak SQL dalam Store Procedure

CREATE PROCEDURE nama_stored_procedurenya @parameter1 tipe_data, @parameter2 2 tipe_data AS isi procedurenya.

Penjelasan
1. nama stored_procedure tidak boleh sama dengan nama fungsi internal, misal CREATE PROCEDURE SUM, tidak boleh ada spasi, tapi bisa menggunakan karakter _
2. Untuk membuat stored procedure gunakan perintah CREATE, untuk mengedit gunakan ALTER , untuk menghapus gunakan DROP.
perintah CREATE, ALTER, DROP dapat digunakan juga untuk membuat TABLE, VIEW, TRIGGER, FUNCTION, misal CREATE VIEW, ALTER FUNCTION,dsb
3. SQL Server mengenali parameter/variabel karena ada tanda @, contoh : @nama_barang char(50), @tanggal datetime, dsb
4. Untuk deklarasi parameter di Stored Procedure gunakan DECLARE
contoh : DECLARE @StartDate datetime, @EndDate datetime, dst..
5. Untuk memasukkan nilai ke sebuah parameter, gunakan SET atau SELECT, contoh :
SET @nama='Itanium' 
SET @Web='Klik-kanan' (perintah SET hanya bisa untuk 1 variabel saja)
SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisa digunakan untuk banyak variabel)

6. Untuk mengambil nilai dari sebuah field dari tabel ke dalam variabel dapat juga menggunakan SET / SELECT , misal : SET @nama= SELECT nama FROM user WHERE login='Itanium') selalu gunakan anda(),perintah ini valid bila data yang ditemukan hanya 1, bila lebih, maka varibale @nama tidak akan ada nilai nya.
7. Untuk mengambil data dalam jumlah banyak, misal seperti array atau StringList, gunakan temporary tabel.untuk pembahasan lebih lengkap, tunggu posting berikut nya.
Contoh pembuatan Stored Procedure. Kita akan membuat sebuah Stored procedure yang berfungsi untuk menggantikan perintah SELECT yang rumit, misal untuk laporan stok barang.

3.      Contoh Store Procedure.

Dalam contoh ini ada 3 tabel yang digunakan :

1. Barang (IDBarang, NamaBarang, IDSatuan).

2. Satuan (IDSatuan, Satuan).

3. StockBarang (Tanggal, IDBarang, SAwal,Masuk,Keluar,SAkhir) laporan yang diminta adalah untuk menampilkan stock sesuai dengan periode tertentu (bisa per hari, bisa juga per minggu, tergantung inputan StartDate dan EndDate). SQL untuk pembuatan Stored Procedure nya :

CREATE PROCEDURE LapStockBarang1 @StartDate varchar(10), @EndDate varchar(10) AS

DECLARE @tgl1 datetime, @tgl2 datetime 

**(variabel StartDate tidak bisa langsung datetime karena perintah EXEC LapStockBarang dalam bentuk string)

CREATE TABLE #TStock (IDBarang varchar(5), SAwal real, Masuk real, Keluar real)

CREATE TABLE #TStock2 (IDBarang varchar(5), SAwal real, Masuk real, Keluar real, SAkhir real)

** buat temporary table , tanda # menandai bahwa tabel ini hanya akan ada saat proses stored procedure.



SELECT@tgl1=CONVERT(datetime,@StartDate,103),@tgl2=CONVERT(datetime,@EndDate,103)

** convert varchar(string) ke tipe datetime, gunakan perintah CONVERT(tipe,variabel,format). Format 103 adalah format dd/mm/yyyy

INSERT INTO #TStock (IDBarang, SAwal)

SELECT (IDBarang, SAwal)

FROM StockBarang 

WHERE tanggal= @tgl1

** masukkan saldo awal pada tanggal bulan itu

INSERT INTO #TStock (IDBarang, Masuk, Keluar)

SELECT IDBarang, SUM(Masuk), SUM(Keluar)

FROM StockBarang

WHERE tanggal BETWEEN @tgl1 AND @tgl2

GROUP BY IDBarang

** masukkan JUMLAH dari masuk dan keluar

INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar)

SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar)

FROM #TStock

GROUP BY IDBarang

** sekarang gabungkan data2 nya

UPDATE #TStock2 SET SAkhir= SAwal + Masuk - Keluar

SELECT t.*, b.NamaBarang, s.Satuan

FROM #TStock2 AS t, Barang AS b, Satuan AS s

WHERE t.IDBarang=b.IDBarang

ND b.IDSatuan=s.IDSatuan

ORDER BY b.IDBarang

Tugas Basis Data (Selasa, 28-06-2011)

1. Menampilkan data produk bacaan yang jenis kategorinya terbanyak

a. Membuat view_all_bacaan

b. membuat view_Summary_bacaan

c. membuat v_cari_produk_terbanyak

2. Menampilan data supplier yang sering mensupply barang/produk bacaan (bukan di lihat dari jumlahnya) tapi di lihat dari seringnya melakukan transaksi (lihat table pembelian)

a. membuat view_all_supplier

b. membuat view_summary_supplier

c. membuat v_cari_supplier_terbanyak   (dilihat dari sering supplier)

3. Menampilan data customer yang sering belanja barang/produk bacaan (bukan di lihat dari jumlahnya) tapi di lihat dari seringnya melakukan transaksi (lihat table penjualan)

a. membuat view_all_costumer

b. membuat view_summary_costumer_terbanyak

c. membuat v_cari_costumer_terbanyak

4. Tampilkan data penerbit yang terbanyak koleksi barang/buku yang ada di table produk_bacaan

a. membuat view_all_penerbit

b. membuat view_summary_penerbit_terbanyak

c. membuat v_cari_penerbit_terbanyak  

Tugas Basis Data (Single Identity Number)

Pentingnya Single Identifikasi Number Bagi Indonesia

Indonesia belum menerapkan sepenuhnya Single Identification Number (SIN) untuk administrasi kependudukannya. Sudah menjadi rahasia umum, bahwa untuk membuat berbagai kartu identitas diri seperti Kartu Tanda Penduduk (KTP) sangat mudah dilakukan di Indonesia. Dengan membayar sejumlah uang kita dapat memperoleh KTP tersebut. Banyak orang memiliki lebih dari satu KTP. Hal ini mencerminkan belum rapinya administrasi kependudukan di Indonesia. Keadaan ini menimbulkan berbagai kesulitan di berbagai sektor, seperti perbankan, asuransi, keimigrasian, penegakan hukum dan kepegawaian. Apakah benar penerapan SIN merupakan satu-satunya jalan keluar untuk mengatasi masalah ini? Untuk apa kita memiliki single identification number ?

Berbagai Masalah Kurang rapinya administrasi kependudukan menimbulkan banyak masalah. Misalnya Penyedia Jasa Keuangan (PJK) seperti bank dan asuransi dalam rangka menerapkan ketentuan tentang prinsip mengenal nasabah (know your customer) mengalami kesulitan melakukan verifikasi kebenaran dokumen identitas diri yang diserahkan oleh calon nasabahnya. Seringkali bank tanpa sadar menerima nasabah yang menggunakan identitas palsu atau asli tapi palsu (ASPAL).

Dari Laporan Transaksi Keuangan Mencurigakan (LTKM) yang disampaikan kepada Pusat Pelaporan dan Analisis Transaksi Keungan (PPATK) banyak yang menginformasikan dipergunakan identitas palsu dari para pelaku Transaksi Mencurigakan Tersebut. Pada tahun 2005 dan 2006 LTKM yang mengindikasikan penggunaan identitas palsu tetap tinggi. Selanjutnya apabila hasil analisis terhadap laporan ini (kasus) diserahkan kepada penegak hukum, mereka kesulitan untuk melakukan penyelidikan dan penyidikan karena orang yang menggunakan identitas palsu tersebut sulit untuk dicari. Begitu juga dengan urusan keimigrasian dan perpajakan, jaminan sosial juga mengalami kesulitan besar karena belum terlaksananya SIN ini.

Kondisi di berbagai Negara 

Banyak negara sudah memiliki SIN seperti Singapura, Malaysia, Thailand, Amerika Serikat dan Canada. Di Amerika dan Canada kita kenal dengan nama Social Security Number. Di negara ini, setiap penduduk sejak lahir sampai meninggal memiliki satu SIN. Bahkan di banyak negara Eropa hewan piaraan pun sudah memiliki SIN yang dimasukkan ke dalam tubuhnya dengan suatu piranti yang dapat dibaca dengan alat scanner. Hal ini mempermudah berbagai macam urusan. Kalau kita ingin mengetahui apakah seseorang sudah meninggal akan lebih mudah diketahui dengan adanya SIN. Dengan adanya SIN akan sulit dilakukan pemalsuan identias untuk melakukan tindak pidana. Jaminan sosial, urusan keimigrasian dan perpajakan juga lebih mudah dilaksanakan. Penegakan hukum dan keamanan juga dapat dilakukan dengan lebih efisien dan efektif.

Indonesia Sudah Memiliki SIN 

Dalam kaitan dengan pelaksanaan Anggaran Pendapatan dan Belanja Negara (APBN) Pemerintah pernah mengeluarkan Keputusan Presiden No. 72 Tahun 2004, tetapi ketentuan itu belum terlaksana sebagaimana diharapkan. Kemudian pada tanggal 29 Desember 2006 Presiden Republik Indonesia mengundangkan UU No. 23/2006 tentang Administrasi Kependudukan. Administrasi Kependudukan diartikan dengan rangkaian kegiatan dan penertiban dalam penerbitan dokumen dan Data Kependudukan, pengelolaan informasi Administrasi Kependudukan serta penggunaan hasilnya untuk pelayanan pulik dan sektor lain.

Menurut Pasal 13 disebutkan, setiap penduduk wajib memiliki Nomor Induk Kependudukan (NIK) yang berlaku seumur hidup dan selamanya. NIK adalah nomor identitas Penduduk yang bersifat unit atau khas, tunggal dan melekat pada seseorang yang terdaftar sebagai Penduduk Indonesia. Tampaknya NIK ini dapat disamakan dengan SIN. Undang-undang ini diterbitkan dengan pertimbangan untuk memberi perlindungan dan pengakuan dan penentuan status pribadi dan status hukun atas setiap Peristiwa Kependudukan. Pada tanggal 7 Februari 2007 yang lalu dalam Rapat Koordinasi Nasional Pencegahan dan Pemberantasan Tindak Pidana Pencucian Uang yang dipimpin Menteri Koordinator Bidang Politik, Hukum dan Keamanan disepakati untuk menetapkan delapan strategi nasional dalam rangka pencegahan dan pemberantasan Tindak Pidana Pencucian Uang. Salah satu strategi nasional itu ádalah mengatur dan mengimplementasikan Single Identification Number atau SIN terhadap seluruh penduduk Indonesia. Dalam hal ini sebagai pelaksanaan UU No. 23 tahun 2006 tentang Administrasi Kependudukan perlu ditetapkan peraturan pelaksanaan dan implementasi yang menyeluruh dan profesional.

Walaupun UU yang mengatur NIK sudah ada, yang paling penting adalah implementasinya secara konsisten. Diharapkan UU Administrasi Kependudukan ini dapat bermanfaat bagi berbagai kegiatan kependudukan, kepegawaian, perpajakan, imigrasi, perbankan dan upaya penegakan hukum serta mempermudah kerjasama antar lembaga dalam rangka pelaksanaan tugasnya.

Selasa, 21 Juni 2011

GRAMEDIA - SQL Server 2008 R-2

Tugas BASIS DATA (Perintah Dasar Query SQL)

1. Create
    membuat Database atau tabel
    Contoh : create database Mahasiswa;

2. Drop
    Untuk menghapus database atau table
    Contoh :drop database Mahasiswa

3. Select
    Untuk menampilkan data yang sudah ada
    contoh : SELECT * FROM  Mahasiswa;

4. SELECT LIMIT

     digunakan untuk menampilkan rocord mulai dari yg ke berapa sampai yang ke           berapa. 

Contoh : select nama, alamat from Mahasiswa limit 0,3;

5. SELECT … AS

    digunakan untuk mengganti nama tabel

    Contoh :select nama as nama_mahasiswa, alamat, telepon from Mahasiswa limit 0,3;

6. Where

   digunakan untuk memilih record yang akan ditampilkan sesuai dengan kriteria tertentu

   Contoh : Select * from Mahasiswa where npm='1103037';

7. WHERE .. LIKE

   kriteria pada where dapat diseleksi dengan kata kunci like

  Contoh : Select kd_dosen, nama_dosen, alamat from table_dosen where nama_dosen like 's%';

8. Updete

    Untuk mengubah record

   Contoh : UPDATE cuaca SET max_suhu = 36 WHERE kota = 'Yogyakarta';

9. Delete

   Untuk meghapus record

   Contoh : DELETE FROM cuaca WHERE kota='Yogyakarta';

10. Insert

   Untuk memasukan record

    Contoh : INSERT INTO friend VALUES (  'Mike',  'Nichols', 'Tampa', FL', 19 );

11. Order By

   Digunakan untuk mengurutkan hasil query berdasarkan field yang dipilih.

   ESC = diurutkan dari nilai paling kecil

   DESC = diurutkan dari nilai paling besar

   Contoh : - SELECT * FROM friend ORDER BY state;

                - SELECT * FROM friend ORDER BY age DESC;

12. IN

    digunakan untuk memilih record yang memiliki nilai yang disebutkan dalam kriteria IN

    Contoh : select npm, nama as nama_mahasiswa from tbl_mahasiswa where npm In ('1104047', '1103048', '1103049');

13. AND dan OR

     Contoh : - SELECT * FROM friend  WHERE firstname = 'Sandy' AND lastname = 'Gleason';

                   - SELECT * FROM friend  WHERE state = 'NJ' OR state = 'PA' 0RDER BY firstname;

14. COUNT

      digunakan untuk menghitung record

      Contoh : SELECT COUNT(*) FROM friend;

15. SUM

     digunakan untuk menjumlahkan record-record yang dipilih

     Contoh : SELECT SUM(age) FROM friend;

16. AVG

     digunakan untuk menghitung rata-rata

     contoh : SELECT AVG(age) FROM friend;

17. MAX dan MIN

      digunakan untu mencari nilai terbesar dan terkecil dari suatu record

     Conoh : - SELECT MAX(age) FROM friend;

                 - SELECT MIN(age) FROM friend;

18. Query bersarang ( Nested Query)

      Dalam SQL dikenal Query bersarang, Query dalam Query

     Contoh : select npm, nama, kd_jurusan from tbl_mahasiswa where kd_jurusan IN ( select kd_jurusan from table jurusan where kd_jurusan='TI0004';

19. Inner join

     digunakan unutuk menggabungkan 2 atau lebih tabel dimana field yang dijadikan referensi join memiliki nilai yang sama.

Contoh : select t1.npm, t1.nama, t1.nama_jurusan from tbl_mahasiswa t1 Inner Join tbl_jurusan t2 on t1.kd_jurusan=t2.kd_jurusan;

20. Left Join

     menggabungkan 2 atau lebih tabel dimana field yang digunakan sebagai referensi pada tabel sebelah kiri akan ditampilkan walaupun tidak hadir pada tabel sebelah kanan.

Contoh : select t1.npm, t1.nama, t1.kd_krs from table mahasiswa t1 Left Join tbl_krs t2 on t1.npm=t2.npm;

21. Having

     Contoh : SELECT state, COUNT(*)  FROM friend GROUP BY state HAVING COUNT(*) > 1  ORDER BY state;

22. Alter Table

     digunakan untuk merubah struktur table

     Contoh : - ALTER TABLE MHS ADD (jkel char (1));

23. GRANT

     Contoh : GRANT Select (final) ON NILAI TO AVI;

24. REVOKE 

     Contoh : Revoke select(final) ON NILAI FROM AVI;

25. Recover Table
     mengembalikan keadaan data seperti sebelum terjadi kerusakan.
     Contoh : RECOVER TABLE MHS;

26. Between
     Mencari data mahasiswa yang nimnya diantara angka 125 sampai 135
     Contoh : SELECT * FROM mahasiswa WHERE nim BETWEEN 125 AND 135;

Tugas Rabu 08/06/2011

CDM (Conceptual Data Model)

 

PDM (Physical Data model)

ERD, CDM, dan PDM pada Penjualan, Pembelian, dan Persediaan Koperasi

1. CDM (Conseptual Data Model)

2. PDM (Physical Data Model)

3. ERD (Entity Relationship Diagram)