08 R2 (Express
Edition) yang dapat diunduh secara gratis melalui situs resmi Microsoft
Corp., tepatnya di tautan: http://www.microsoft.com/en-
us/download/search.aspx?q=sql+server+2008+r2.
Gambar 4.1: Beberapa versi Ms. SQL Server 2008
4.1 Kebutuhan Sistem
Sebelum melakukan instalasi, perlu diketahui spesifikasi
sistemoperasi dan perangkat keras (hardware) minimal yang diperlukan
oleh SQL Server sehingga proses instalasi bisa berhasil.
Untuk sistem operasi, SQL Server 2008 Express Edition
mendukung beberapa versi Windows, yaitu: Windows 7, Windows
Server 2003, Windows Server 2008, Windows Server 2008 R2, dan
Windows Vista.
SQL Server R2 ini merupakan SQL Server versi ringkas, sehingga
memiliki berbagai keterbatasan jika dibandingkan dengan versi
lengkapnya. Salah satu keterbatasan yang penulis temukan saat
memakai SQL Server versi ini yaitu hanya bisa mengeksekusi
paket data kurang dari 10 GB (sepuluh giga byte). Namun demikian, SQL
Server Express Edition lebih dari memadai untuk dipakai sebagai
sistem basis data untuk aplikasi yang cukup kompleks namun tidak
begitu memerlukan tingkat keamanan yang tinggi.
Karena versi ringkas, tentunya kebutuhan perangkat keras tidak
sama dengan versi lengkapnya. Berikut yaitu spesifikasi perangkat
keras minimal yang diperlukan untuk bisa melakukan instalasi SQL
Server 2008 R2.
Prosesor : Pentium III atau prosesor setara lainnya dengan
kecepatan 1 GHz atau lebih tinggi.
Memori: Minimal 500 MB untuk SQL Server Express dengan
Tools dan Advanced Services, dan 4 GB untuk SQL Server dengan
Reporting Services.
Harddisk : 2.2 GB, belum termasuk space untuk sistem operasi
dan database aplikasi yang akan Anda kembangkan
memakai SQL Server.
4.2 Instalasi SQL Server 2008 R2
SQL Server 2008 ini berjalan di atas framework .NET 3.5 (baca:
dot NET versi 3.5),. Oleh karena itu, sebelum melakukan instalasi SQL
Server, Anda harus menginstal framework ini . File installer
framework .NET dapat diunduh dengan bebas di situs resminya
Microsoft. Selanjutnya, berikut yaitu langkah-langkah instalasi SQL
Server 2008.
1) Klik ganda (double clicked) file installer SQL Server, jangan lupa
sesuaikan dengan Windows Anda. Jika Windows OS Anda 32bit,
gunakan file ...x86.exe, dan ...x64.exe jika 64bit.
Gambar 4.2: File installer SQL Server untin Windows 32Bit dan 64Bit
2) Maka akan muncul jendela progress ekstraksi file installer.
Tunggu saja sampai muncul jendela SQL Server Installation
Center.
3) Klik menu Installation pada sebelah kiri jendela untuk melihat
beberapa opsi instalasi, kemudian klik opsi New installation or
add features to an existing installation.
Gambar 4.3: Jendela SQL Server Intallation Center
4) Tunggu lagi beberapa saat sampai muncul jendela Setup
Support Rules, klik tombol [OK]. Jika tombol [Show details]
diklik, maka akan muncul seperti Gambar 4.4 berikut.
Gambar 4.4: Jendela Setup Support Rules
5) Klik menu Installation Type yang ada di sebelah kiri jendela, pilih
opsi New installation or add shared features dan klik tombol
[Next]. INGAT! Selama proses instalasi, jendela SQL Server
Intallation Center jangan ditutup.
Gambar 4.5: Jendela Installtion Type
6) Jendela berikutnya yaitu License terms. Beri tanda centang
opsi I accept the license terms dan klik tombol [Next].
7) Pada jendela Setup Support Rules mungkin Anda akan
menemukan sepasang peringatan (warning) seperti yang
ditunjukkan Gambar 4.6.
Peringatan aplikasi .NET menunjukkan bahwa komputer
Anda tidak terhubung ke internet. Ini bukan masalah, jadi
bisa diabaikan.
Selanjutnya, peringatan Windows Firewall menunjukkan
bahwa perlu dilakukan pengaturan firewall sebelum
dilakukan instalasi SQL Server. Peringatan ini juga bisa
diabaikan.
Gambar 4.6: Sepasang peringatan pada jendela Setup Support Rules
8) Klik saja tombol [Next] untuk menuju ke jendela Feature
Selection.
9) Pada jendela Feature Selection, Anda bisa memilih semua fitur
atau hanya memberi tanda centang fitur apa saja yang ingin
diinstal. Tentunya, semakin banyak fitur yang diinstal akan
membutuhkan lebih banyak ruang penyimpanan. Namun
demikian, fitur SQL Server Management Tool harus dipilih.
10) Tentukan juga alamat direktori (path) fitur yang di-share melalui
kolom Shared feature directory. Anda bisa membiarkan default
path, atau menentukan drive yang diinginkan, pada contoh ini
dipakai drive “E”.
Gambar 4.7: Jendela Feature Selection
11) Klik tombol [Next], maka akan ditampilkan jendela Installation
Rules.
Gambar 4.8: Jendela Installation Rules
12) Pada jendela ini, Anda bisa mengklik tombol [Show details]
untuk melihat berbagai status rule yang diinstal atau langsung
mengklik tombol [Next] untuk melanjutkan.
13) Jendela berikutnya yang muncul yaitu Instance Configuration.
Pada sesi ini, Anda diminta untuk menentukan nama instance
serta root directory-nya. Anda dapat memilih opsi Default
instance jika Anda ingin tidak banyak melakukan konfigurai, atau
menentukan sesuai keinginan, seperti yang dicontohkan pada
buku ini. Jika Anda menentukan nama instance sendiri, harus
mengikuti aturan bahwa nama instance TIDAK BOLEH lebih dari
15 (lima belas) karakter.
14) Di bagian tengah yang ditandai dengan tanda kotak
menunjukkan direktori SQL Server dan Reporting Service hasil
penentuan nama instance dan root directory-nya.
Gambar 4.9: Jendela Instance Configuration
15) Klik tombol [Next], maka akan ditampilkan jendela Disk Space
Requirements.
Gambar 4.10: Jendela Disk Space Requirements
16) Pada jendela ini tidak ada konfigurasi apapun, langsung saja klik
tombol [Next] untuk menuju ke jendela Server Configuration.
Gambar 4.11: Jendela Server Configuration
17) Beberapa hal yang perlu dilakukan konfigurasi yaitu sebagai
berikut.
Jika remote SQL Server jalan di bawah akun Network
Service atau akun domain, maka Anda harus membuat
sebuah Service Principal Name (SPN) untuk layanan SQL
sebagaimana dideskripsikan di dalam artikel Microsoft
Knowledge Base, tepatnya di tautan
http://support.microsoft.com/default.aspx?scid=kb;en-
us;811889.
Di tab Service Account, ubah Startup Type untuk layanan
SQL Server Agent menjadi “Automatic”. Agent ini
dipakai untuk proses backup.
Jika diinginkan memakai akun yang sama untuk seluruh
layanan, klik tombol [Use the same account for all SQL
Server services]. Setelah muncul kotak dialog Use the same
account for all SQL Server 2008 R2 services, pada kolom
Account Name pilih “NT/AUTHORITY \SYSTEM”, kemudian
klik tombol [OK].
Pada tab Collation, biarkan apa adanya (default).
18) Klik tombol [Next] untuk menuju ke jendela Database Engine
Configuration.
Gambar 4.12: Tab Account Provisioning
pada Jendela Databse Engine Configuration
19) Beberapa hal yang perlu dilakukan pada jendela ini yaitu
sebagai berikut.
Pada tab Account Provisioning, Anda bisa memilih opsi
Windows authentication mode tanpa menentukan
password, atau memilih opsi Mixed Mode (SQL Server
authentication and Windows authemtication) dan
masukkan password untuk akun “sa”. Akun “sa” merupakan
akun administrator internal SQL Server.
Anda juga bisa menambahkan daftar user yang diinginkan
ke dalam kolom Specify SQL Server administrators dengan
mengklik tombol [Add Current User].
Pada tab Data Directories, Anda dapat mengubah lokasi
penyimpanan data SQL Server. Direkomendasikan untuk
TIDAK menyimpan data SQL Server di drive “C”. Pada
contoh in, folder “SQLData” yang sebelumnya telah dibuat
di drive “E” dipakai sebagai lokasi menyimpan Data root
directory. Folder “SQLData\Data” untuk User database
directory dan User database log directory, serta folder
“SQLData\Backup” untuk Backup directory.
Khusus untuk Temp DB directory dan Temp DB log
directory dibiarkan default.
Gambar 4.13: Tab Data Directories
pada Jendela Databse Engine Configuration
20) Pada tab FILESTREAM tidak perlu dilakukan konfigurasi dan
langsung klik tombol [Next] untuk menuju ke jendela Reporting
Service Configuration.
Gambar 4.14: Jendela Reporting Service Configuration
21) Pilih opsi Install the native mode default configuration dan klik
tombol [Next] untuk menuju ke jendela Error Reporting.
Gambar 4.15: Jendela Error Reporting
PERANCANGAN DATA BASE SISTEM INFORMASI MANAJEMEN PENDIDIKAN
DENGAN DBMS MICROSOFT (ACCES DAN SQL SERVER))
84
22) Jika tidak ditemukan kesalahan, langsung klik tombol [Next]
untuk menuju ke jendela Installation Configuration Rules.
Gambar 4.16: Jendela Installation Configuration Rules
23) Klik tombol [Show details] untuk melihat status instalasi rule
dan klik tombol [Next] sampai muncul jendela Ready to Install.
Gambar 4.17: Jendela Ready to Install
24) Klik tombol [Install] untuk melanjutkan proses instalasi. Jika
berhasil, maka akan muncul jendela Complete yang lebih kurang
seperti yang ditunjukkan Gambar 4.18.
Gambar 4.18: Jendela Complete
25) Proses instalasi SQL Server 2008 R2 selesai.
5
Administrasi Database
Microsoft SQL Server
Mengingat SQL Server tergolong RDBMS (relational database
management system) yang cukup besar dan kompleks, membahas fitur-
fiturnya secara lengkap tidak akan cukup dituangkan di dalam sebuah
buku, apalagi hanya satu bab. Oleh karena itu, pembahasan pada bab ini
hanya sebatas tentang langkah-langkah membuat database dan
komponennya (table, view, stored procedure, dan trigger), membuat
berbagai perintah query yang sering dipakai , dan proses administrasi
lain dianggap perlu dilakukan oleh seorang database enginer. Hal ini
dimaksudkan untuk menjembatani pembaca pemula yang mungkin
mengalami kesulitan dalam melakukan hal-hal dasar ini atau yang
kurang terbiasa dengan query di SQL Server. Bagi pembaca tingkat mahir
bisa mengabaikan bab ini dan langsung fokus ke bab selanjutnya yang
diinginkan.
Sebagai bahan latihan, pada bab ini akan dibahas rancangan
database untuk penjualan sederhana. Database ini akan diberi nama
“db_commerce” dan di dalamnya akan dibuat enam buah tabel, yaitu:
“dbo.products”, “dbo.orderdetail”, “dbo.orders”, “dbo.customer”,
“dbo.supply”, dan “dbo.supplier”.
5.1 Membuat Database
Untuk membuat database di SQL Server dapat dipakai dua
cara, yaitu dengan mengetik perintah SQL langsung melalui jendela
Query Editor yang tersedia atau dengan SSMS berbasis GUI. Berikut
yaitu perintah SQL yang dipakai untuk membuat database baru
lengkap dengan berbagai pengaturannya.
USE [master]
GO
/****** Object: Database [db_commerce]
Script Date: 12/24/2013 20:23:30 ******/
CREATE DATABASE [db_commerce] ON PRIMARY
( NAME = N'db_commerce', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_commerce.mdf' ,
SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'db_commerce_log', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db_commerce_log.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [db_commerce] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [db_commerce].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [db_commerce] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [db_commerce] SET ANSI_NULLS OFF
GO
ALTER DATABASE [db_commerce] SET ANSI_PADDING OFF
GO
ALTER DATABASE [db_commerce] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [db_commerce] SET ARITHABORT OFF
GO
ALTER DATABASE [db_commerce] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [db_commerce] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [db_commerce] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [db_commerce] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [db_commerce] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [db_commerce] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [db_commerce] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [db_commerce] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [db_commerce] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [db_commerce] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [db_commerce] SET DISABLE_BROKER
GO
ALTER DATABASE [db_commerce] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
PERANCANGAN DATA BASE SISTEM INFORMASI MANAJEMEN PENDIDIKAN
DENGAN DBMS MICROSOFT (ACCES DAN SQL SERVER))
88
ALTER DATABASE [db_commerce] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [db_commerce] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [db_commerce] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [db_commerce] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [db_commerce] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [db_commerce] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [db_commerce] SET READ_WRITE
GO
ALTER DATABASE [db_commerce] SET RECOVERY SIMPLE
GO
ALTER DATABASE [db_commerce] SET MULTI_USER
GO
ALTER DATABASE [db_commerce] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [db_commerce] SET DB_CHAINING OFF
GO
Sedangkan, berikut ini merupakan langkah-langkah yang perlu
dilakukan dalam membuat sebuah database baru memakai aplikasi
wizard berbasis GUI dengan SSMS.
1) Setelah berhasil masuk ke aplikas SSMS, pada bagian Object
Explorer, klik kanan Databases » New Database....
2) Setelah muncul jendela New Database, ketik “db_commerce”
pada kolom Database name.
3) Klik tombol telusur (browse – tombol dengan label tiga titik “...”)
yang ada di sebelah kanan kolom Owner, seperti Gambar 5.1.
Gambar 5.1: Bagian Object Explorer dan jendela New Database
4) Sampai muncul jendela Select Database Owner, klik tombol
[Browse...].
5) Pada jendela Browse for Objects, beri tanda centang objek [sa]
pada daftar Matching object dan klik tombol [OK], maka akan
dibawa kembali ke jendela Select Database Owner, klik tombol
[OK] lagi.
6) Apabila pada kolom Owner sudah berisi objek [sa], berarti sudah
benar.
Gambar 5.2: Proses memasukkan objek Owner
7) Setelah kembali ke jendela New Database (Gambar 5.1), klik
tombol [OK]. Jika berhasil, maka database “db_commerce” akan
muncul di bagian Object Explorer, tepatnya di dalam grup
Databases seperti Gambar 5.3.
Gambar 5.3: Database “db_commerce” yang baru dibuat
5.2 Membuat Tabel
Setelah membuat database, langkah berikutnya yaitu
membuat tabel. Tabel merupakan komponen utama database relasional
yang dipakai untuk menyimpan data dalam bentuk kolom (field) dan
baris (record).
5.2.1 Tabel [dbo].[products]
Tabel pertama yang akan dibuat yaitu “dbo.products”. Tabel
ini dipakai untuk menyimpan data produk atau komoditas jual.
Keterangan dari tabel “dbo.products” dituangkan dalam Tabel 5.1.
Tabel 5.1: Keterangan Tabel “dbo.products”
No. Nama Kolom Keterangan
1. serial dipakai untuk menyimpan data ID-Produk.
Kolom ini merupakan kunci primer (primary key)
pada tabel products. Bertipe data int dengan
atribut Is Identify=Yes, sehingga tanpa harus
menentukan nilai (value), kolom ini akan terisi
secara otomatis dengan nilai lebih besar satu
angka dari baris data sebelumnya.
2. name dipakai untuk menyimpan data nama
produk.
3. description dipakai untuk menyimpan data deskripsi
suatu produk, mulai dari merk, spesifikasi,
garansi, dan keterangan lain yang dianggap
perlu ditambahkan.
4. price dipakai untuk menyimpan data harga jual
suatu produk.
5. picture dipakai untuk menyimpan data lokasi file
(path) gambar suatu produk disimpan.
6. stock dipakai untuk menyimpan data stok suatu
produk.
Adapun langkah-langkah membuat tabel “dbo.products”
memakai SSMS yaitu sebagai berikut.
1) Pilih database “db_commerce”, kemudian klik kanan (right
clicked) objek Tables » New Table....
Gambar 5.4: Proses membuat tabel baru
2) Ketik nama kolom (field) yang diperlukan melalui bagian Column
Name, tentukan tipe data kolom melalui bagian Data Type, dan
status boleh kosong (null) suatu kolom melalui bagian Allow
Nulls seperti Gambar 5.5 berikut ini.
Gambar 5.5: Proses pembuatan tabel [dbo].[products]
3) Khusus untuk kolom serial, karena akan dibuat auto-number
atau auto-increment, maka perlu dilakukan pengaturan lebih
lanjut, yaitu dengan mengatur atribut Is Identity dengan nilai
“Yes” dan Identity Increment dengan nilai “1”.
Gambar 5.6 Properti untuk kolom serial pada tabel [dbo].[products]
4) Simpan tabel dengan mengklik ikon disket (tombol simpan) yang
ada di bagian toolbar SSMS dan beri nama “products”, maka
secara otomatis akan bernama “dbo.products”.
Gambar 5.7: Struktur akhir tabel [dbo].[products]
5.2.2 Tabel [dbo].[orderdetail]
Tabel “dbo.orderdetail” dipakai untuk menyimpan detail
order atau pemesanan terhadap suatu produk. Tabel 5.2 menunjukkan
keterangan dari tabel “dbo.orderdetail”.
Tabel 5.2: Keterangan Tabel “dbo.orderdetail”
No. Nama Kolom Keterangan
1. orderid dipakai untuk menyimpan data ID-Order. Kolom ini
merupakan kunci asing (foreign key) yang mengacu
pada kolom serial pada tabel “dbo.orders”. Kolom ini
bertipe data bigint, karena setiap kali order bisa lebih
dari satu produk, sehingga di tabel “dbo.orderdetail”
dimungkinkan akan menyimpan banyak record.
2. productid dipakai untuk menyimpan data ID-Produk yang
diorder. Kolom ini juga merupakan kunci asing (foreign
key) yang mengacu pada kolom serial di tabel
“dbo.products”.
3. quantity dipakai untuk menyimpan data kuantitas order
suatu produk yang yang diorder.
4. price dipakai untuk menyimpan data nilai order yang di
dapat dari harga jual (orderrdetail.price) dikalikan
dengan kuantitas (orderdetail.quantity). Kolom
orderdetail.price ini bisa mempunyai nilai (value) yang
sama dengan kolom products.price, namun juga bisa
mempunyai nilai yang berbeda. Sama jika nilai yang
tersimpan di kolom products.price belum mengalami
perubahan (naik atau turun) sejak pertama kali dientri
dan orderdetail.quantity bernilai satu, demikian
sebaliknya.
Adapun struktur tabel “dbo.orderdetail” hasil pembuatan
memakai SSMS ditunjukkan pada Gambar 5.8 berikut ini.
Gambar 5.8: Struktur tabel “dbo.orderdetail”
5.2.3 Tabel [dbo].[orders]
Tabel “dbo.orders” dipakai untuk menyimpan data order dari
konsumen tertentu. Jika tabel “dbo.orderdetail” berhubungan dengan
detail produk yang diorder, tabel “dbo.orders” berhubungan dengan
data konsumen yang melakukan order. Secara teknis “dbo.orderdetail”
merupakan tabel detail dari tabel “dbo.orders”. Keterangan tabel
“dbo.orders” ditunjukkan pada Tabel 5.3.
Tabel 5.3: Keterangan Tabel “dbo.orders”
No. Nama Kolom Keterangan
1. serial dipakai untuk menyimpan data ID-Order.
Kolom ini merupakan kunci primer (primary key)
pada tabel ini. Kolom ini bertipe data bigint
dengan atribut Is Identify=Yes.
2. date dipakai untuk menyimpan data tanggal
seorang konsumen melakukan order.
3. customerid dipakai untuk menyimpan data ID-Konsumen
yang melakukan order.
Sruktur tabel “dbo.orders” hasil pembuatan memakai SSMS
ditunjukkan pada Gambar 5.9.
Gambar 5.9: Struktur tabel “dbo.orderdetail”
5.2.4 Tabel [dbo].[customer]
Table “dbo.customer” dipakai untuk menyimpan data
konsumen yang pernah melakukan order. Keterangan dari tabel
“dbo.customer” dituangkan dalam Tabel 5.4.
Tabel 5.4: Keterangan Tabel “dbo.customer”
No. Nama Kolom Keterangan
1. serial dipakai untuk menyimpan data ID-Konsumen.
Kolom ini merupakan kunci primer (primary key).
Kolom ini juga bertipe data bigint dengan atribut
Is Identify=Yes.
2. name dipakai untuk menyimpan data nama
konsumen.
3. email dipakai untuk menyimpan data e-mail
konsumen.
4. address dipakai untuk menyimpan data alamat
konsumen, yang selanjutnya dipakai sebagai
alamat pengiriman barang yang diorder,
tentunya setelah konsumen mentransfer
sejumlah uang sesuai nilai ordernya.
5. phone dipakai untuk menyimpan data no. telepon
konsumen.
Gambar 5.10 berikut ini menunjukkan struktur tabel
“dbo.customer” hasil pembuatan memakai SSMS.
Gambar 5.10: Struktur tabel “dbo.customer”
5.2.5 Tabel [dbo].[supply]
Tabel “dbo.supply” dipakai untuk menyimpan data
pembelian/kulakan suatu produk. Keterangan dari tabel “dbo.supply”
dituangkan dalam Tabel 5.5.
Tabel 5.5: Keterangan Tabel “dbo.supply”
No. Nama Kolom Keterangan
1. serial dipakai untuk menyimpan data ID-Pembelian.
Kolom ini merupakan kunci primer (primary key).
Kolom ini juga bertipe data bigint dengan atribut
Is Identify=Yes.
2. productid dipakai untuk menyimpan data ID-Produk
yang diorder. Kolom ini merupakan kunci asing
(foreign key) yang mengacu pada kolom serial di
tabel “dbo.products”.
3. quantity dipakai untuk menyimpan data kuantitas
produk yang dibeli.
4. price dipakai untuk menyimpan data harga satuan
pembelian sutu produk.
5. supplierid dipakai untuk menyimpan data ID-Supplier
yang mnyuplai barang. Kolom ini juga
merupakan kunci asing (foreign key) yang
mengacu pada kolom serial di tabel
“dbo.supplier”.
6. date dipakai untuk menyimpan data tanggal
pembelian produk tertentu.
Gambar 5.11 berikut ini menunjukkan struktur tabel
“dbo.supply” hasil pembuatan memakai SSMS.
Gambar 5.11: Struktur tabel “dbo.supply”
5.2.6 Tabel [dbo].[supplier]
Tabel “dbo.supplier” dipakai untuk menyimpan data
penyedia produk (supplier). Keterangan dari tabel “dbo.supplier”
dituangkan dalam Tabel 5.6 berikut ini.
Tabel 5.6: Keterangan Tabel “dbo.supplier”
No. Nama Kolom Keterangan
1. serial dipakai untuk menyimpan data ID-Supplier.
Kolom ini merupakan kunci primer (primary key).
Kolom ini juga bertipe data int dengan atribut Is
Identify=Yes.
2. name dipakai untuk menyimpan data nama supplier
yang menyediakan produk tertentu.
3. address dipakai untuk menyimpan data alamat
supplier.
4. phone dipakai untuk menyimpan data no. Telepon
atau HP supplier tertentu.
5. contact dipakai untuk menyimpan data kontak person
supplier tertentu.
Gambar 5.12 berikut ini menunjukkan struktur tabel
“dbo.supplier” hasil pembuatan memakai SSMS.
Gambar 5.12: Struktur tabel “dbo.supplier”
5.3 Membuat Diagram Database
Diagram database dipakai untuk menampilkan relasi antar
tabel yang ada di dalam suatu database. Dengan adanya diagram ini,
informasi dependensi antara data di satu tabel dengan tabel lainnya
akan semakin jelas. Diagram ini juga sangat bermanfaat dalam membuat
view kategori kompleks, perancang database akan dengan mudah
melibatkan tabel mana saja dalam membuat sebuah view. Tentunya,
masih banyak lagi manfaat yang diperoleh dengan adanya sebuah
diagram database. Berikut ini disajikan langkah-langkah membuat
diagram database memakai SSMS.
1) Pada bagian Object Explorer, pilih Database Diagram, kemudian
pilih menu New Database Diagram.
Gambar 5.13: Proses pembuatan diagram database
2) Setelah muncul jendela Add Table, pilih semua tabel yang ada,
kemudian klik tombol [Add].
Gambar 5.14: Jendela Add table
3) Jika tidak terjadi kesalahan, maka keempat tabel yang dipilih tadi
akan muncul ke dalam kanvas Database Diagram.
Gambar 5.15: Proses merelasikan tabel products dengan orderdetail
4) Untuk merelasikan antara tabel satu dengan lainnya, cukup
drag-and-drop (klik tahan dan geser) kolom yang menjadi
primary key ke kolom pada tabel lain yang menjadi foreign key–
nya.
5) Setelah muncul jendela Tables and Columns, tentukan nama
relasi melalui kolom Relationship name, nama tabel dan primary
key-nya (sebelah kiri), serta nama tabel yang akan direlasikan
dan foreign key-nya (sebelah kanan), kemudian klik tombol [OK].
Gambar 5.16: Proses penentuan nama dan kunci relasinya
6) Jendela berikutnya yaitu Foreign Key Relationship. Pada
jendela ini, Anda diminta untuk menentukan aturan-aturan
mengenai tabel yang mempunyai foreign key (tabel orderdetail).
Gambar 5.17: Jendela Foreign Key Relationship
7) Aturan-aturan yang dimaksud diantaranya yaitu INSERT and
UPDATE Specification. Pada aturan ini, baik Delete Rule maupun
Update Rule sama-sama mempunyai empat opsi, yaitu:
No Action. Jika terjadi proses update atau delete pada data
di tabel yang mempunyai primary key (tabel products),
maka data-data yang bersesuaian di tabel yang mempunyai
foreign key (tabel orderdetail) tidak akan terjadi apap-apa.
Cascade. Jika terjadi proses update atau delete pada data di
tabel products, maka data-data yang bersesuaian di tabel
orderdetail juga akan terbarui (updated) atau terhapus
(deleted).
Set Null. Jika terjadi proses update atau delete pada data di
tabel products, maka data-data yang bersesuaian di tabel
orderdetail akan di set menjadi kosong (bernilai null).
Set Default. Jika terjadi proses update atau delete pada
data di tabel products, maka data-data yang bersesuaian di
tabel orderdetail akan dikembalikan ke nilai default yang
ditentukan saat pembuatan tabel.
8) Dengan cara yang sama, relasikan supplier.serial dengan
supply.supplierid, products.serial dengan supply.productid,
products.serial dengan orderdetail.productid, orders.serial
dengan orderdetail.orderid, dan yang terakhir customer.serial
dengan orders.customerid. Gambar 5.18 berikut merupakan
diagram database yang telah dibuat.
Gambar 5.18: Diagram relasi antar tabel dalam database db_commerce
Dari diagram database di atas dapat disimpulkan beberapa
hubungan dependensi sebagai berikut.
Tabel supply tergantung (depend) pada tabel supplier dan tabel
products.
Tabel orderdetail tergantung pada tabel orders dan tabel
products, sedangkan tabel orders tergantung pada tabel
customer. Hubungan dependensi antar tabel ini ditunjukkan
pada Gambar 5.19.
Gambar 5.19: Hubungan dependensi antar tabel dalam database
db_commerce
5.4 Persiapan Data
Persiapan data merupakan proses memasukkan data ke dalam
sebuah tabel sehingga tabel ini mempunyai data awal. Tujuannya
agar efeknya lebih representatif ketika sebuah tabel tadi dikenai suatu
perintah SQL. Berikut ini disajikan beberapa perintah SQL untuk
menyisipkan (insert) dan menampilkan (select) data ke/dari dalam
sebuah tabel.
Menyisipkan tujuh data ke dalam tabel products.
Gambar 5.20: Proses insert data ke dalam tabel dbo.products
Dengan perintah SQL serupa Anda dapat bereksperimen sendiri
untuk menyisipkan data ke dalam tabel customer, orders dan
orderdetail.
Menampilkan seluruh data yang ada dalam tabel products.
Gambar 5.21: Proses menampilkan data pada tabel dbo.products
Menampilkan seluruh data yang ada dalam tabel customer.
Gambar 5.22: Proses menampilkan data pada tabel dbo.customer
Menampilkan seluruh data yang ada dalam tabel orders.
Gambar 5.23: Proses menampilkan data pada tabel dbo.orders
Menampilkan seluruh data yang ada dalam tabel orderdetail.
Gambar 5.24: Proses menampilkan data pada tabel dbo.orderdetail
Menampilkan seluruh data yang ada dalam tabel supplier.
Gambar 5.25: Proses menampilkan data pada tabel dbo.supplier
Menampilkan seluruh data yang ada dalam tabel supply.
Gambar 5.26: Proses menampilkan data pada tabel dbo.supply
5.5 Membuat View
View juga termasuk dalam komponen database. Secara default,
view baru dibuat ke dalam database yang diaktifkan. Untuk membuat
secara eksplisit di dalam suatu database tertentu, maka buatlah nama
view dengan sintaks: db_name.view_name.
Untuk membuat view, Anda bisa memakai dua cara, yaitu:
memakai WIZARD dan CODING (mengetik langsung) ke editor
query. Pada bab ini akan dibahas pembuatan view memakai
WIZARD karena untuk membuat view melalui CODING, Anda cukup
mengetikkan perintah SQL ke dalam editor query kemudian
mengeksekusinya.
1) Pilih database “db_commerce”, klik kanan Views » New View....
Gambar 5.27: Proses pembuatan view baru
2) Setelah muncul kotak dialog Add Table, pilih tabel apa saja yang
ingin dilibatkan, kemudian klik tombol [Add].
Gambar 5.28: Kotak dialog Add table
3) Anda akan dibawa ke halaman utama pembuatan view. Halaman
utama view ditunjukkan pada Gambar 5.29.
Gambar 5.29: Proses pembuatan view
Keterangan:
[1] Daftar tabel yang ditambahkan ke dalam pembuatan view.
[2] View worksheet yang dipakai untuk menentukan kolom,
alias, nama tabel, status sebagai output, tipe pengurutan, status
pengurutan, status pengegrupan, filterisasi dan lainnya.
[3] Kode hasil generate dari view worksheet.
[4] Hasil eksekusi view berdasar atribut yang ditentukan di
view worksheet.
4) Untuk menampilkan hasil dari view yang dibuat, klik tombol
(tanda seru) yang ada di toolbar.
5) Untuk menyimpan, klik tombol disket yang ada di toolbar,
setelah muncul kotak dialog Choose Name, klik tombol [OK].
Gambar 5.30: Proses penyimpanan view
6) Dengan cara yang sama, buat view satu lagi dengan deskripsi
yang ditunjukkan pada Gambar 5.31 dan simpan dengan nama
“vdetail_order”.
Gambar 5.31: Proses pembuatan view “vdetail_order”
7) Jika tidak ada kesalahan, maka di dalam daftar view akan muncul
nama “vdetail_order” seperti Gambar 5.32 berikut ini.
Gambar 5.32: Daftar view yang telah dibuat
5.6 Membuat Stored Procedure
Stored Procedure merupakan kumpulan perintah SQL yang
didefinisikan oleh pengguna dan disimpan dengan nama tertentu di
dalam server database. Stored Procedure biasanya berisi perintah-
perintah umum yang berhubungan dengan database, baik perintah DDL
(data definition language) maupun DML (data manipulation language).
Berikut ini disajikan beberapa contoh pembuatan stored procedure
untuk menampilkan (select), menyisipkan (insert), memperbarui
(update) dan menghapus (delete) data. Secara umum sintaks Stored
Procedure sebagai berikut.
CREATE PROCEDURE [schema_name.][Procedure_Name]
<@Param_1> <Datatype_For_Param_1> =
<Default_Value_For_Param_1>,
<@Param_2> <Datatype_For_Param_2> =
<Default_Value_For_Param_2>,
...
<@Param_n> <Datatype_For_Param_n> =
<Default_Value_For_Param_n>
AS
SQL_STATEMENT;
Tabel 5.7: Keterangan sintaks stored procedure
Argumen Keterangan
[schema_name.][Procedure_Name] Mendeskripsikan nama skema
dan nama stored procedure.
<Param> <Datatype_For_Param> =
<Default_Value_For_Param>
Mendeskripsikan nama
parameter, tipe data parameter,
dan nilai default untuk
parameter. Nama parameter
harus diawali dengan tanda “@”.
Parameter ini bersifat opsional,
bisa terdiri satu atau lebih
parameter, namun juga tidak harus
ada.
SQL_STATEMENT Merupakan isi dari stored
procedure yang berisi kumpulan
perintah SQL.
Mungkin Anda bertanya, mengapa diperlukan stored procedure?
Berikut disajikan kelebihan dan kekurangan penerapan stored procedure
dalam sistem database. Terdapat banyak kelebihan diterapkan stored
procedure, diantaranya yaitu sebagai berikut.
Karena bisa menerapkan parameter, stored procedure lebih
fleksibel dalam melakukan berbagai operasi data.
Proses eksekusi stored procedure dilakukan di server database
sehingga prosesnya menjadi lebih cepat.
Lebih singkat kode program dalam mengembangan aplikasi,
karena aplikasi cukup memanggil stored procedure dan
mengirim parameter yang diperlukan sesuai dengan format
store procedure yang akan dieksekusi.
Bisa dipakai untuk membagi beban kerja antara server dan
klien saat aplikasi di jalankan. Jika semua query dibuat dan
dijalankan sisi klien (program aplikasi atau front end), maka
sumber daya yang terpakai pada sisi klien ini akan besar,
dengan adanya stored procedure sebagian beban kerja klien bisa
dialihkan ke server.
Stored procedure hanya ditulis sekali, namun bisa diakses oleh
banyak aplikasi.
Stored procedure berparameter yang memvalidasi seluruh input
user dapat dipakai untuk mencegah terjadinya SQL injection.
Jika Anda memakai perintah SQL dinamis, pastikan perintah
Anda berparameter, dan jangan pernah memasukkan nilai
parameter secara langsung ke dalam perintah query.
Stored procedure dapat mereduksi lalu lintas jaringan, yaitu
dengan mengombinasikan banyak operasi data ke dalam satu
pemanggilan procedure.
Sedangkan, kekurangan dalam penerapan stored procedure
diantaranya yaitu sebagai berikut.
Apabila semua permintaan operasi data ditangani oleh server
database memakai stored procedure, jika jumlah kliennya
sangat banyak, maka beban server akan sangat besar.
Kesulitan dalam melakukan migrasi server database, misalkan
dari SQL Server ke Oracle, SQL Server ke MySQL, atau
sebaliknya. Karena sintaks stored procedure pada umumnya
berbeda pada masing-masing DBMS..
5.6.1 Stored Procedure untuk Menampilkan Data
Berikut ini disajikan contoh stored procedure untuk
menampilkan data pada tabel dbo.products dengan kriteria tertentu.
Gambar 5.33: Stored procedure “getProducts”
Stored procedure “getProducts” dipakai untuk menampilkan
jumlah data (hasilnya ditampilkan dalam kolom “Total”) pada tabel
dbo.products dengan kriteria nama produk (kolom “name”)
mengandung kata dalam varibel @Kata.
Gambar 5.34: Proses dan hasil eksekusi procedure “getProducts”
Jika stored procedure “getProducts” menampilkan data dari
tabel, berikut ini disajikan contoh satu lagi stored procedure untuk
menampilkan data dari view.
CREATE PROCEDURE getDetailOrder (
@id bigint
) AS
SELECT * FROM vdetail_order WHERE orderid=@id;
Stored procedure “getDetailOrder” dipakai untuk
menampilkan data dari view “vdetail_order” dengan kriteria ID-Order
(kolom “orderid”) bernilai varibel @id.
Gambar 5.35: Proses dan hasil eksekusi procedure “getDetailOrder”
5.6.2 Stored Procedure untuk Menyisipkan Data
Berikut ini disajikan contoh stored procedure untuk menyisipkan
data ke dalam tabel dbo.products.
Gambar 5.36: Stored procedure “insProducts”
Stored procedure “insProducts” dipakai untuk menyisipkan
data melalui empat variabel input, yaitu: @vname (untuk kolom
“name”), @vdesc (untuk kolom “description”), @vprice (untuk kolom
“price”), dan @vpic (untuk kolom “picture”). Kolom dbo.products.serial
sengaja tidak didefinisikan karena sudah bertipe data IDENTIFY (1,1),
sehingga dapat terisi secara otomatis oleh sistem.
Gambar 5.37: Proses dan hasil eksekusi procedure “insProducts”
5.6.3 Stored Procedure untuk Memperbarui Data
Berikut ini disajikan contoh stored procedure untuk
memperbarui data pada tabel dbo.products.
Gambar 5.38: Stored procedure “updProducts”
Stored procedure “updProducts” dipakai untuk memperbarui
data melalui empat variabel edit dan satu variabel kondisi. Empat
variabel edit yang dimaksud yaitu @vname (untuk kolom “name”),
@vdesc (untuk kolom “description”), @vprice (untuk kolom “price”),
dan @vpic (untuk kolom “picture”). Sedangkan variabel kondisnya @id
(untuk kolom “serial”).
Gambar 5.39: Proses dan hasil eksekusi procedure “updProducts”
5.6.4 Stored Procedure untuk Menghapus Data
Berikut ini disajikan contoh stored procedure untuk menghapus
data dari tabel dbo.products.
Gambar 5.40: Stored procedure “delProducts”
Stored procedure “delProducts” dipakai untuk menghapus
data dengan satu variabel kondisi, yaitu @id (untuk kolom “serial”).
Gambar 5.41: Proses dan hasil eksekusi procedure “delProducts”
Dengan cara yang sama, ketik dan eksekusi skrip berikut ini
untuk membuat stored procedure pada tabel dbo.customer.
-- INSERT DATA
CREATE PROCEDURE insCust(
@vname nvarchar(30),
@vmail nvarchar(50),
@vaddr nvarchar(50),
@vphone nvarchar(20)
) AS
INSERT INTO dbo.customer(name, email, address, phone)
VALUES (@vname, @vmail, @vaddr, @vphone);
GO
-- UPDATE DATA
CREATE PROCEDURE updCust(
@id int,
@vname nvarchar(30),
@vmail nvarchar(50),
@vaddr nvarchar(50),
@vphone nvarchar(20)
) AS
UPDATE dbo.customer SET name=@vname, email=@vmail, address=@vaddr,
phone=@vphone
WHERE serial=@id;
GO
-- DELETE DATA
CREATE PROCEDURE delCust(
@id int
) AS
DELETE FROM dbo.customer WHERE serial=@id;
GO
Selanjutnya, ketik dan eksekusi skrip berikut ini untuk membuat
stored procedure pada tabel dbo.orders.
-- INSERT DATA
CREATE PROCEDURE insOrders(
@vdate date,
@vcust int
) AS
INSERT INTO dbo.orders (date, customerid) VALUES (@vdate,
@vcust);
GO
-- UPDATE DATA
CREATE PROCEDURE updOrders(
@id bigint,
@vdate date,
@vcust int
) AS
UPDATE dbo.orders SET date=@vdate, customerid=@vcust
WHERE serial=@id;
GO
-- DELETE DATA
CREATE PROCEDURE delOrders(
@id int
) AS
DELETE FROM dbo.orders WHERE serial=@id;
GO
Terakhir, ketik dan eksekusi skrip berikut ini untuk membuat
stored procedure pada tabel dbo.orderdetail.
-- INSERT DATA
CREATE PROCEDURE insOrderDetail(
@vorderid bigint,
@vproid int,
@vqty int,
@vprice float
) AS
INSERT INTO dbo.orderdetail(orderid, productid, quantity,
price)
VALUES (@vorderid, @vproid, @vqty, @vprice);
GO
-- UPDATE DATA
CREATE PROCEDURE updOrderDetail(
@vorderid bigint,
@vproid int,
@vqty int,
@vprice float
) AS
UPDATE dbo.orderdetail SET quantity=@vqty, price=@vprice
WHERE orderid=@vorderid AND productid=@vproid;
GO
-- DELETE DATA
CREATE PROCEDURE delOrderDetail(
@vorderid bigint,
@vproid int
) AS
DELETE FROM dbo.orderdetail
WHERE orderid=@vorderid AND productid=@vproid;
GO
Gambar 5.42 berikut ini menunjukkan daftar stored procedure
yang telah dibuat pada database “db_commerce”.
Gambar 5.42: Daftar stored procedure pada database “db_commerce”
5.7 Membuat UDF
Mungkin Anda sering melihat fungsi-fungsi SQL siap pakai
bawaan sistem, seperti MAX() untuk menampilkan data terbesar
(khusus untuk tipe DATE akan menampilkan data tanggal termuda) pada
kolom tertentu, SUM() untuk menampilkan jumlah seluruh data pada
kolom tertentu, AVG() untuk menampilkan rata-rata data yang ada di
kolom tertentu, demikian fungsi-fungsi bawaan lainnya. Dengan UDF
(user defined function – sering disebut Function saja), Anda dapat
mendefinisikan sendiri fungsi-fungsi sesuai selera sehingga bisa
dipakai selayaknya fungsi bawaan ini .
Pada SQL Server 2008 R2, UDF dikategorikan menjadi tiga, yaitu:
Inline Table-Valued Function, Scalar-Valued Function, dan Multi-
Statement Table-Valued Function.
5.7.1 Inline Table-Valued Function
Inline table-valued function (disingkat ITVF) merupakan bagian
dari UDF yang mengembalikan tipe data table. ITVF dapat dipakai
untuk meningkatkan fungsionalitas view yang berparameter. Berikut ini
yaitu aturan-aturan dalam ITVF:
Klausa RETURN hanya terdiri dari kata kunci table. Anda tidak
harus mendefinisikan format dari variabel return, karena
variabel ini diset oleh format result set yang dihasilkan dari
statemen SELECT dalam klausa RETURN.
TIDAK ADA function body yang diapit dengan BEGIN dan END.
Klausa RETURN berisi sebuah statemen SELECT tunggal yang
diapit tanda kurung. Result set yang dihasilkan oleh statemen
SELECT membentuk table yang dikembalikan oleh fungsi.
Statemen SELECT dipakai dalam sebuah fungsi inline
mengikuti aturan statemen SELECT yang dipakai dalam view.
ITVF hanya menerima konstanta atau argumen @local_variable.
Skrip berikut ini merupakan sintaks umum dari ITVF.
CREATE FUNCTION <Inline_Function_Name, sysname,
FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, ,
int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, ,
char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references
here
SELECT 0
)
Pada contoh kali ini akan dibuat ITVF untuk menampilkan
seluruh data yang ada pada tabel tertentu namun hanya kolom-kolom
tertentu.
Gambar 5.43: Fungsi “allProduct”
Fungsi allProduct() di atas dipakai untuk menampilkan
seluruh data yang ada di kolom serial, name, description, dan price yang
ada di tabel dbo.products. sebagaimana aturan tentang ITVF variabel
return hanya berupa tipe data TABLE.
Gambar 5.44: Proses dan hasil eksekusi fungsi “allProduct”
Perintah SQL di atas merupakan contoh eksekusi ITVF yang telah
dibuat, yaitu fungsi allProdutc(). Karena pemanggilan fungsinya
ditambahkan klausa WHERE name LIKE ‘%LG%’ maka hasil yang
dikembalikan berupa data produk yang mengandung kata “LG”.
5.7.2 Scalar-Valued Function
Scalar-Valued function (disingkat SVF) bisa dibuat dengan atau
tanpa parameter. SVF dapat dipakai untuk mengoperasikan kolom-
kolom suatu tabel atau view. Operasi yang dimaksud dapat berupa
operasi matematika, penggabungan string, pencarian, dan operasi
sejenis lainnya. Adapun sintaks umum dari SVF yaitu sebagai berikut.
CREATE FUNCTION <Scalar_Function_Name, sysname,
FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result>
<Function_Data_Type, ,int>
-- Add the T-SQL statements to compute the return
value here
SELECT <@ResultVar, sysname, @Result> = <@Param1,
sysname, @p1>
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
END
Pada contoh kali ini akan dibuat SVF untuk mendapatkan nilai
pada kolom (field) baru yang dihasilkan dari perkalian dua kolom. Dua
kolom ini dapat ditentukan secara langsung saat eksekusi SVF.
Gambar 5.45: Fungsi subtotal()
Fungsi subtotal() di atas dipakai untuk menampilkan hasil kali
dua kolom yang di-parsing melalui parameter @qty dan @price. Fungsi
ini mengembalikan nilai bertipe data float.
Gambar 5.46: Proses dan hasil eksekusi fungsi subtotal()
Perintah SQL di atas merupakan contoh eksekusi SVF yang telah
dibuat, yaitu fungsi subtotal(). Karena pemanggilan fungsinya
subtotal(quantity, price) maka hasil yang dikembalikan berupa hasil
perkalian dari kolom quantity dan price.
5.7.3 Multi-Statement Table-Valued Function
Secara umum multi-statement table-valued function (disingkat
MTVF) mirip dengan inline table-valued function (ITVF), perbedaanya
hanya pada poin-poin berikut ini.
Variabel table yang akan dikembalikan oleh fungsi harus
didefinisikan di awal fungsi.
MTVF harus mempunyai blok BEGIN/END.
Di dalam blok BEGIN/END harus ada kode yang menghasilkan
variabel table.
MTVF harus mengembalikan nilai.
ITVF hanya mengembalikan statemen SELECT, tidak ada variabel
table di dalamnya, tidak ada statemen INSERT, dan tidak ada
blok kode. Skrip berikut ini merupakan sintaks umum MTVF.
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <data_type_for_param1, ,
int>,
<@param2, sysname, @p2> <data_type_for_param2, ,
char>
)
RETURNS
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column definitions for the TABLE variable
here
<Column_1, sysname, c1> <Data_Type_For_Column1, ,
int>,
<Column_2, sysname, c2> <Data_Type_For_Column2, ,
int>
)
AS
BEGIN
-- Fill the table variable with the rows for your
result set
RETURN
END
Untuk contoh MTVF akan dipakai contoh yang telah dibahas
pada ITVF dengan sedikit modifikasi.
Gambar 5.47: Fungsi insCrossTable()
Fungsi insCrossTable() di atas dipakai untuk menampilkan
data dari tabel dbo.products untuk selanjutnya data hasil SELECT
ini disisipkan (insert) ke dalam tabel baru.
Gambar 5.48: Proses dan hasil eksekusi fungsi insCrossTable()
Perintah SQL di atas merupakan contoh eksekusi MTVF yang
telah dibuat, yaitu fungsi insCrossTable(). Karena pemanggilan fungsinya
ditambahkan klausa WHERE nama LIKE ‘%LG%’ maka hasil yang
dikembalikan berupa data produk yang mengandung kata “LG”. Data
yang dikembalikan oleh fungsi insCrossTable() ini selanjutnya disisipkan
ke dalam tabel baru dengan nama produkLG. Gambar 5.48 (kanan)
menunjukkan terciptanya tabel baru dengan nama dbo.produkLG hasil
eksekusi fungsi insCrossTable().
5.8 Membuat Trigger
Trigger merupakan sebuah bentuk khusus dari stored procedure
yang secara otomatis melakukan eksekusi ketika sebuah event terjadi di
dalam server database. Trigger dapat mencegah akses terhadap data
yang spesifik, melakukan logging atau melakukan audit perubahan data.
Berikut yaitu beberapa manfaat penggunaan trigger:
Sebagai salah satu mekanisme alternatif untuk menjalankan
aturan bisnis dan menjaga integritas data dalam sebuah
database. Mekanisme yang lainnya yaitu penggunaan
constraint.
Dapat mengevaluasi status dari sebuah tabel sebelum dan
sesudah terjadi perubahan data dan melakukan aksinya sesuai
perbedaan yang terjadi.
Dapat melakukan perubahan secara cascade melalui relasi tabel
pada database, akan namun perubahan ini akan lebih efisien jika
dieksekusi memakai referential integrity constraints.
Dapat melakukan pencegahan akses atas data yang kompleks
dibanding dengan memakai check constraint.
Dapat mereferensikan kolom (field) dalam tabel yang berbeda.
Pada SQL Server 2008 R2, trigger dikelompokkan menjadi tiga,
yaitu: Trigger DML (data manipulation language), Trigger DDL (data
definition language), dan Trigger LOGON.
5.8.1 Trigger DML
Trigger DML melakukan eksekusi ketika ada user mencoba
melakukan modifikasi data yang behubungan dengan DML. Kejadian
(event) DML dalam hal ini yaitu statemen INSERT, UPDATE, dan
DELETE data pada sebuah tabel atau view. Namun demikian, trigger
tidak dapat dipakai dalam kejadian yang dihasilkan oleh statemen
SELECT. Trigger-trigger ini aktif ketika ada satu lebih valid event
diaktifkan, terlepas dari ada tidaknya baris data dalam tabel yang
terpengaruh (affected). Berikut ini merupakan sintaks umum pembuatan
trigger DML.
Trigger on an INSERT, UPDATE, or DELETE statement to a table
or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method
specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
Tabel 5.8: Keterangan sintaks Trigger DML
Argumen Deskripsi
[schema_name.]trigger_na
me
Mendeskripsikan nama skema dan trigger
DML.
{table|view} Nama tabel atau view dimana trigger
DML akan diterapkan.
<dml_trigger_option>[,..
.n]
Menentukan opsi trigger DML, termasuk
klausa ENCRYPTION dan EXECUTE AS.
{[INSERT][,][UPDATE][,][
DELETE]}
Opsi event untuk trigger DML. Untuk
menentukan pada event apakah (bisa
lebih dari satu) trigger DML dieksekusi.
[NOT FOR REPLICATION] Menentukan apakah trigger akan
dieksekusi saat proses replikasi pada
tabel yang bersangkutan terjadi.
{sql_statement[;][,...n]
| EXTERNAL NAME <method
specifier [;]>}
Perintah-perintah SQL yang akan
dieksekusi saat terjadi operasi DML.
5.8.1.1 Trigger untuk Logging
Contoh pertama trigger DML kali ini yaitu membuat
pencatatan (logging) terhadap aktifitas tabel “dbo.products”. Hasil
pencatatan ini nantinya disimpan ke dalam tabel “dbo.log_products”
dengan struktur sebagai berikut.
Gambar 5.49: Struktur tabel “dbo.log_products”
Selanjutnya, buat trigger DML dengan nama “logInsProduct”
dengan kode seperti berikut.
Gambar 5.50: Proses pembuatan trigger “logInsProducts”
Trigger “logInsProducts” ini akan terpicu (menjalankan aksi) jika
pada tabel “dbo.products” dikenai kejadian insert atau update. Untuk
menguji apakah trigger “logInsProducts” berjalan sebagaimana
mestinya, akan dibuat perintah SQL untuk menyisipkan (insert) data ke
dalam tabel “dbo.products” seperti berikut.
Gambar 5.51: Pengujian trigger “logInsProducts” untuk kejadian insert
Gambar 5.51 menunjukkan bahwa trigger “logInsProducts”
terpicu setelah kejadian insert dikenakan pada tabel “dbo.products”.
Bukti trigger “logInsProducts” telah terpicu yaitu berhasil
disisipkannya sebuah record ke dalam tabel “dbo.log_products”.
Selanjutnya, trigger “logInsProducts” akan diuji dengan kejadian update
dengan memberikan perintah SQL berikut ini.
Gambar 5.52: Pengujian trigger “logInsProducts” untuk kejadian update
Gambar 5.52 menunjukkan bahwa trigger “logInsProducts” juga
terpicu setelah kejadian update dikenakan pada tabel “dbo.products”.
Bukti trigger “logInsProducts” telah terpicu yaitu berhasil
disisipkannya lagi sebuah record dengan keterangan “Insert/update
data…” ke dalam tabel “dbo.log_products”.
Setelah membuat trigger DML untuk kejadian insert dan update,
sekarang akan diberikan contoh pembuatan trigger untuk kejadian
delete dengan nama “logDelProducts” seperti berikut ini.
Gambar 5.53: Proses pembuatan trigger “logDelProducts”
Trigger “logDelProducts” ini akan terpicu jika pada tabel
“dbo.products” dikenai kejadian delete. Untuk menguji apakah trigger
“logInsProducts” berjalan sebagaimana mestinya, akan dibuat perintah
SQL untuk menghapus (delete) data dari dalam tabel “dbo.products”
seperti berikut.
Gambar 5.54: Pengujian trigger “logDelProducts” untuk kejadian delete
Gambar 5.54 menunjukkan bahwa trigger “logDelProducts” juga
terpicu setelah kejadian delete dikenakan pada tabel “dbo.products”.
Bukti trigger “logDelProducts” telah terpicu yaitu berhasil
disisipkannya lagi sebuah record dengan keterangan “Delete data…” ke
dalam tabel “dbo.log_products”.
5.8.1.2 Trigger untuk Perbaruan Data
Selanjutnya akan diberikan contoh trigger DML dengan operasi
yang sedikit lebih kompleks dari sekadar logging, yaitu perbaruan data
antar tabel yang terrelasi. Contoh trigger kali ini diberi nama
“tambahStokProduk” pada kejadian insert data.
Gambar 5.55: Pembuatan trigger “tambahStokProduk”
Trigger “tambahStokProduk” ini dipicu saat ada proses sisip
(insert) data ke dalam tabel “dbo.supply”. Adapun aksi yang dijalankan
oleh trigger ini yaitu memperbarui data “stock” dan “price” yang ada
di tabel “dbo.products”. Data “stock” akan diperbarui dengan nilai stok
lama ditambah kuantitas pembelian baru (kolom “dbo.supply.qty”),
sedangkan data “price” diperbarui dengan nilai harga pembelian baru
ditambah 10%-nya. Untuk lebih jelasnya, perhatikan proses sisip data ke
dalam tabel “dbo.supply” berikut ini.
Gambar 5.56: Proses sisip data ke dalam tabel “dbo.supply”
Sekarang, perhatikan perubahan data yang terjadi pada tabel
“dbo.products” berikut ini.
Gambar 5.57 Hasil eksekusi trigger “tambahStokProduk”
(a) Tabel “dbo.supply” (b) Tabel “dbo.products” sebelum perubahan;
(c) Tabel “dbo.products” setelah perubahan.
Perhatikan perubahan yang terjadi pada tabel “dbo.products” di
atas. Untuk data produk dengan ID=31 (Astech Mouse), data stok (stock)
menjadi 105 (seratus lima) dan data harga (price) menjadi 68750 (enam
puluh delapan ribu tujuh ratus lima puluh). Data stok 105 didapatkan
dari 100 (stok lama) ditambah dengan 5 (kuantitas pembelian baru),
sedangkan 68750 didapat dari 62500 (harga baru) ditambah dengan
6250 (atau 10% dari harga baru ini ). Berikut ini disajikan contoh
terakhir untuk trigger DML, yaitu “ubahStokProduk”.
Gambar 5.58: Pembuatan trigger “ubahStokProduk”
Trigger “ubahStokProduk” ini akan dipicu saat terjadi proses
penghapusan (deleting) data produk tertentu pada tabel “dbo.supply”.
Aksi yang dilakukan trigger ini yaitu mengurangi stok yang ada di
dalam tabel “dbo.products” sebesar kuantitas data produk yang dihapus
pada tabel “dbo.supply”. Untuk lebih jelasnya, perhatikan proses hapus
data dari dalam tabel “dbo.supply” berikut ini.
Gambar 5.59: Proses hapus data pada tabel “dbo.supply”
Perhatikan perubahan yang terjadi pada tabel “dbo.products” di
atas. Untuk data produk dengan ID=31 (Astech Mouse), data stok (stock)
menjadi 100 (seratus lima). Data stok 100 didapatkan dari 105 (stok
lama) dikurangi dengan 5 (kuantitas data pada tabel “dbo.supply”).
5.8.2 Trigger DDL
Trigger DDL melakukan eksekusi dalam menanggapi berbagai
event DDL. Event-event yang tergolong DDL yaitu statemen CREATE,
ALTER, dan DROP, termasuk stored procedure bawaan sistem yang
melakukan operasi sejenis DDL. Berikut ini merupakan sintaks umum
pembuatan trigger DDL.
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or
UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method
specifier > [ ; ] }
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
Di bawah ini diberikan contoh pembuatan trigger DDL dengan
“log” yang akan dipakai untuk melakukan pencatatan (log) terhadap
seluruh kejadian DDL (DDL event), seperti pembuatan (create),
perbaruan struktur (alter) dan penghapusan (drop) seluruh tabel yang
ada di dalam database “db_commerce”. Pertama, dibuat sebuah tabel
“ddl_log” untuk menyimpan pencatatan yang dihasilkan trigger “log”.
Selanjutnya, perhatikan kode SQL pembuatan trigger “log” berikut ini.
Gambar 5.60: Proses pembuatan trigger “log”
Untuk melihat hasil pencatatan yang dihasilkan trigger “log”,
berikut diberikan contoh kode SQL untuk membuat (create) dan
menghapus (drop) sebuah tabel dengan nama “TestTable” di dalam
database “db_commerce”.
Gambar 5.61: Proses pengujian (testing) trigger “log”
Pada Gambar 5.61 di atas menunjukkan bahwa seluruh event
DDL yang terjadi pada seluruh komponen database “db_commerce”,
baik berupa tabel atau view, maka trigger “log” akan terpicu dan
melakukan pencatatan sesuai event yang terjadi.
5.8.3 Trigger LOGON
Trigger LOGON melakukan eksekusi stored procedure (bawaan
sistem) dalam menanggapi event LOGON yang dibangkitkan ketika
sebuah sesi pengguna (user session) berhasil dibentuk dengan sebuah
instan dari SQL Server. Trigger LOGON aktif setelah fase otentikasi login
dalam tahap akhir, namun sebelum sesi pengguna benar-benar
terbentuk. Oleh karena itu, semua pesan yang dihasilkan oleh trigger
yang biasanya sampai ke pengguna, seperti pesan error dan pesan dari
statemen PRINT, dialihkan ke catatan kesalahan (error log) SQL Server.
Trigger LOGON tidak aktif jika otentikasi gagal.
Anda dapat memakai trigger LOGON untuk mengaudit dan
mengontrol sesi server, seperti penulusuran aktifitas login, membatasi
login ke SQL Server, atau pembatasan jumlah sesi untuk login tertentu.
Berikut ini merupakan sintaks umum pembuatan trigger LOGON.
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method
specifier > [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
Untuk contoh kali ini akan dibuat sebuah trigger untuk
membatasi waktu login user tertentu. Batasan yang dimaksud yaitu
jam login, misalkan user tertentu, anggap saja “TestUser”, bisa login ke
SQL Server hanya pada pukul 10:00 WIB sampai dengan 18:00 WIB.
Pertama kali yang perlu dibuat yaitu membuat login (user)
baru yang akan dibatasi jam loginnya. Ada 2 cara untuk membuat login
baru, yaitu: dengan dengan mengetikkan baris perintah SQL langsung
melalui query editor yang tersedia atau memakai wizard melalui
aplikasi GUI. Berikut yaitu perintah SQL untuk membuat login baru
dengan nama “TestUser”.
USE [master]
GO
--Create the login on your servel called "TestUser"
CREATE LOGIN [TestUser] WITH PASSWORD=N'Admin2345'
,DEFAULT_DATABASE=[master]
,DEFAULT_LANGUAGE=[us_english]
,CHECK_EXPIRATION=OFF
,CHECK_POLICY=OFF
GO
Adapun dengan wizard melalui aplikasi GUI langkah-langkahnya
yaitu sebagai berikut.
1) Setelah berhasil masuk ke SQL Server, klik kanan submenu
Security » Logins, pilih New Login....
Gambar 5.62: Proses membuat login baru
2) Setelah muncul jendela Login – New, ketik “TestUser” pada
kolom Login name.
Gambar 5.63: Jendela Login - New
3) Pilih opsi SQL Server authentication dan tentukan kata sandinya
melalui kolom Password dan Confirm password, misalkan
“Admin2345” (tanpa tanda kutip).
4) Hilangkan tanda (uncheck) pada opsi Enforce password
expiration.
5) Tentukan nama default database untuk user “TestUser” melalui
kolom Default database, misalkan “master”.
6) Klik tombol [OK], jika tidak muncul pesan kesalahan, berarti
proses pembuatan login baru berhasil.
Langkah selanjutnya yaitu memutuskan koneksi dari SQL
Server dan mencoba login lagi memakai user baru yang telah
dibuat, yaitu: “TestUser”.
1) Klik tombol (disconnect) untuk memutuskan koneksi dengan
SQL Server.
2) Setelah muncul jendela Connect to Server, pilih nama server
melalui kolom Server name.
3) Pilih opsi SQL Server Authentication pada kolom
Authentication.
4) Pilih TestUser pada kolom Login dan masukkan password
“Admin2345” sebagaimana yang telah dibuat sebelumnya.
Gambar 5.64: Jendela Connect to Server
5) Mestinya Anda akan berhasil masuk ke SQL Server dan dibawa
ke halaman utama SSMS dengan bagian Object Explorer
menampilkan daftar database yang ada di dalamnya seperti
Gambar 5.65 berikut ini.
Gambar 5.65: Bagian Object Explorer pada halaman utama SSMS
Sekarang saatnya membuat trigger untuk membatasi jam
koneksi, misalkan diberi nama “connection_limit_trigger”, dengan kode
SQL seperti berikut ini.
Gambar 5.66: Proses pembuatan trigger “connection_limit_trigger”
Untuk mengujinya, putuskan koneksi SQL Server dan coba lagi
login memakai “TestUser” sebagaimana yang diilustrasikan pada
Gambar 5.64. Jika Anda melakukan login tidak di antara pukul 10:00WIB
dan 18:00WIB, maka akan muncul pesan kesalahan seperti berikut ini.
Gambar 5.67: Pesan kesalahan yang dihasilkan oleh
trigger “connection_limit_trigger”
Selanjutnya, Anda coba lagi login memakai user lain yang
tidak dibatasi jam loginnya, misal “sa”. Setelah berhasil masuk ke SQL
Server, coba akses menu Management yang ada pada Object Explorer,
kemudian klik catatan (log) terakhir yang ada dalam menu SQL Server
Log.
Gambar 5.68: Submenu SQL Server Log pada Object Explorer
Gambar 5.69 berikut ini menunjukkan catatan yang dihasilkan
oleh trigger “connection_limit_trigger”.
Gambar 5.69: Catatan yang dihasilkan oleh
trigger “connection_limit_trigger”
Perhatikan baris paling bawah pada Gambar 5.67, pesan yang
ditampilkan yaitu “Tidak diijinkan login dengan “TestUser” di luar jam
kerja...”, pesan ini sebagaimana yang telah didefinisikan saat pembuatan
trigger “connection_limit_trigger”.
Kesimpulan yang dapat diambil tentang trigger Logon yaitu
trigger Logon sangat berguna untuk melacak dan mengendalikan
aktivitas login SQL Server. Informasi yang ditangkap oleh trigger Logon
membantu kita mengidentifikasi dan mencegah akses yang tidak sah dari
SQL Server.
5.9 Backup Database
Biasanya backup database dilakukan sebagai tindakan antisipatif
agar aman dari kerusakan atau kehilangan data (termasuk fisik
servernya), baik yang diakibatkan oleh manusia (seperti: cracking,
pencurian, dan tindakan sabotase) maupun alam (seperti: banjir,
kebakaran, dan bencana alam lainnya). Backup database dapat
dilakukan secara langsung atau periodik. Berikut ini yaitu langkah-
langkah untuk melakukan backup database melalui SSMS.
1) Pada bagian Object Explorer, klik kanan nama database yang
akan di-backup, kemudian akses menu Tasks » Back Up....
Gambar 5.70: Menu Tasks » Back Up...
2) Setelah muncul jendela Back Up Database, tentukan nama
database yang akan di-backup, misal “db_commerce” pada
kolom Database dan pada kolom Backup type, pilih opsi “Full”
jika diinginkan backup keseluruhan.
Gambar 5.71: Jendela Back Up Database
3) Klik tombol [Add...] untuk menentukan nama file tujuan (hasil
backup) dan alamat (path) file hasil backup ini akan
disimpan.
4) Setelah muncul jendela Select Backup Destination, klik tombol
[...] (baca: browse) sampai muncul jendela Locate Database
Files, tentukan direktori penyimpanan (misal: “I:\backup-
commerce”) dan nama file hasil backup yang diinginkan (misal:
“commerce-05012014.bak”), klik tombol [OK].
Gambar 5.72: Jendela Select Back Up Destination
dan Locate Database Files
5) Setelah kembali ke jendela Select Backup Destination dan Back
Up Database (Gambar 5.71) klik tombol [OK]. Jika berhasil, maka
akan muncul pesan sukses seperti Gambar 5.73.
Gambar 5.73: Pesan sukses backup database
6) Gambar 5.74 berikut ini merupakan file hasil backup database
yang berekstensi *.bak.
Gambar 5.74: File hasil backup database
5.10 Restore Database
Restore database merupakan tindakan mengembalikan seluruh
komponen database melalui file hasil proses backup, tujuannya agar
konten database sama dengan data yang ada di dalam file hasil backup.
Berikut yaitu langkah melakukan restore database melalui SSMS.
1) Pada bagian Object Explorer, klik kanan nama database yang
akan di-backup, kemudian akses menu Restore Database....
Gambar 5.75: Menu Restore Database
2) Setelah muncul jendela Restore Database, pada bagian Source
to restore pilih opsi From device dan klik tombol telusur (tiga
titik).
Gambar 5.76: Jendela Restore Database
3) Pada jendela Specify Backup, klik tombol Add. Setelah muncul
jendela Locate Backup File, tentukan file backup yang akan di-
restore, klik tombol [OK]. Setelah kembali ke jendela Specify
Backup, klik tombol [OK].
4) Pada jendela Restore Database, pada bagian Destination to
restore, pilih “db_commerce” yang muncul pada kolom To
database.
Gambar 5.77: Bagian Destination for restore pada jendela Restore
Database
5) Jangan lupa beri tanda centang file backup pada bagian Select
the backup sets to restore, klik tombol [OK]. Jika berhasil, maka
akan muncul pesan sukses seperti Gambar 5.78.
Gambar 5.78: Pesan sukses restore database
5.11 Generate Skrip
Generate skrip dapat dikatakan sebagai proses backup dalam
format *.SQL, sehingga file hasil generate ini mudah dibaca dan
dipahami isinya karena berupa kumpulan perintah-perintah SQL.
Keuntungan lain dari proses ini yaitu dapat melakukan backup
berdasar komponen database tertentu, misalkan tabel, trigger, atau
stored procedure tertentu saja. Untuk lebih jelasnya, perhatikan langkah-
langkahnya berikut ini.
1) Pada bagian Object Explorer, klik kanan nama database yang
akan di-generate, kemudian akses menu Tasks » Generate
Scripts....
Gambar 5.79: Proses generate skrip
2) Setelah muncul jendela Generate and Publish Scripts (disingkat
GPS) - Introduction, klik tombol [Next].
3) Pada jendela GPS – Choose Objects, pilih objek database mana
saja yang ingin di-generate skripnya, kemudian klik tombol
[Next].
Gambar 5.80: Jendela GPS Introduction dan GPS Choose Objects
4) Pada jendela GPS – Set Scripting Options, pilih opsi Save to file.
Klik tombol [Advanced] untuk melakukan pengaturan tingkat
lanjut.
Gambar 5.81: Jendela GPS Set Scripting Options
5) Setelah muncul jendela Advanced Scripting Options, lakukan
pengaturan yang dianggap perlu, misalkan pengaturan untuk
opsi Type of data to script diset dengan Schema and data. Jika
selesai, klik tombol [OK].
Gambar 5.82: Jendela Advanced Scripting Options
6) Pada jendela GPS - Summary, langsung klik saja tombol [Next],
sampai muncul jendela GPS – Save or Publish Scripts klik tombol
[Finish].
Gambar 5.83: Jendela GPS – Summary dan GPS – Save and Publish
Scripts
7) Jika berhasil, maka file “scrrip.sql” akan terbentuk di dalam
direktori yang ditentukan.
Gambar 5.84: File hasil generate skrip
Demikian sedikit tentang administrasi database Ms. SQL Server, Anda
dapat mencari informasi dari internet atau media lain untuk membantu
Anda memperdalam bahasan materi pada bab ini.
0 Komentar