BASIS DATA BERBASIS SQL
Gambar
12.1 di samping ini adalah gambar sekumpulan komputer server yang
biasanya ada pada perusahaan penyedia jasa internet atau pada bagian
teknologi informasi perusahaan-perusahaan besar. Salah satu fungsi
peralatan ini adalah sebagai pusat data yang dibutuhkan penggunanya.
Peran sebagai pusat data ini membutuhkan DBMS dan aplikasi basis data
yang kuat. Perangkat DBMS seperti Microsoft SQL Server, Oracle, MySQL,
PostgreSQL biasanya menjadi pilihan. Fungsi-fungsi lanjut yang
disediakan oleh DBMS tersebut sangat membantu kecepatan kerja, keamanan,
dan keakuratan data yang disampaikan.
Bab ini membahas tiga standar
kompetensi yaitu melakukan pemrograman data deskripsi (SQL) tingkat
dasar, mengoperasikan bahasa pemrograman data deskripsi (SQL) tingkat
lanjut dan membuat program basis data dengan Microsoft SQL Server. Kedua
standar kompetensi ini disatukan karena kedekatan materi bahasan.
Susunan sub bab tidak langsung mengacu pada kompetensi dasar, tapi lebih
pada kecocokan materi. Ringkasan akan disampaikan di akhir bab kemudian
dilanjutkan dengan soal-soal latihan. Sebelum kalian mempelajari bab
ini buka kembali bab-bab tentang pemecahan masalah, sistem operasi,
algoritma lanjutan, dasar-dasar basis data, dan aplikasi basis data
berbasis Microsoft Access pada bab-bab sebelumnya.
SEKILAS TENTANG SQL
Dalam DBMS biasanya tersedia paket bahasa yang digunakan untuk
mengorganisasi basis data yang ada, yaitu Data Definition Language (DDL)
dan Data Manipulation Language (DML).
Data Definition Language (DDL)
Data Definition Language (DDL) adalah satu paket bahasa DBMS yang
berguna untuk melakukan spesifikasi terhadap skema basis data. Hasil
kompilasi dari DDL adalah satu set tabel yang disimpan dalam file khusus
yang disebut Data Directory/Dictionary. Secara umum perintah perintah
dalam DDL berhubungan dengan operasi-operasi dasar seperti membuat basis
data baru, menghapus basis data, membuat tabel baru, menghapus tabel,
membuat indeks, mengubah struktur tabel. Contoh perintah DDL misalnya,
Create Table, Create Index, Alter, dan Drop Database.
Data Manipulation Language
Data Manipulation Language (DML) adalah satu paket DBMS yang
memperbolehkan pemakai untuk mengakses atau memanipulasi data
sebagaimana yang telah diorganisasikan sebelumnya dalam model data yang
tepat. Dengan DML dapat dilakukan kegiatan :
- Mengambil informasi yang tersimpan dalam basis data.
- Menyisipkan informasi baru dalam basis data.
- Menghapus informasi dari tabel.
Terdapat
dua tipe DML yaitu prosedural dan non prosedural. Prosedural DML
membutuhkan pemakai untuk menspesifikasikan data apa yang dibutuhkan dan
bagaimana cara mendapatkannya, sedang non prosedural DML membutuhkan
pemakai untuk menspesifikasikan data apa yang dibutuhkan tanpa tahu
bagaimana cara mendapatkannya.
SQL merupakan kependekan dari
Structured Query Language (Bahasa Query Terstruktur). SQL lebih dekat
dengan DML dari pada DDL. Namun tidak berarti SQL tidak menyediakan
perintah DDL. SQL lebih menekankan pada aspek pencarian dari dalam
tabel. Aspek pencarian ini sedemikian penting karena di sinilah
sebenarnya inti dari segala upaya kita melakukan pengelolaan data. Data
dalam basis data diorganisasi sedemikian rupa dengan tujuan untuk
memudahkan pencarian di kemudian hari.
Sebagai sebuah bahasa, SQL
telah distandarisasi dan mengalami beberapa perubahan atau
penyempurnaan. SQL muncul pertama kali pada tahun 1970 dengan nama
Sequel (nama yang masih sering digunakan hingga saat ini). Standarisasi
yang pertama dibuat pada tahun 1986 oleh ANSI (American National
Standards Institute) dan ISO (International Standard Organization), yang
disebut SQL-86. Pada tahun 1989 SQL-86 diperbaharui menjadi SQL-89.
Standar terakhir yang dibuat adalah SQL-92.
Pernyataan-pernyataan SQL
digunakan untuk melakukan beberapa tugas seperti : update data pada
basis data, atau menampilkan data dari basis data.
Beberapa software
RDBMS yang dapat menggunakan SQL, seperti : Oracle, Sybase, Microsoft
SQL Server, MySQL, Microsoft Access, Ingres, dsb. Setiap software basis
data mempunyai mungkin bahasa perintah / sintaks yang berbeda, namun
pada prinsipnya mempunyai arti dan fungsi yang sama. Perintah utama
dalam SQL adalah select. Struktur utama perintah adalah sebagai berikut:
Select <kolom>
From <table>
Where <kondisi>
Kita akan menggunakan perintah-perintah ini pada bagian-bagian berikut.
MEMPERSIAPKAN PERANGKAT LUNAK BERBASIS SQL
Seperti disebutkan di bagian awal bab, perangkat lunak yang akan
kita gunakan adalan Microsoft SQL Server. Pada kesempatan ini versi yang
dipakai adalah versi 2005 Developer Edition. Penjelasan sekilas tentang
Microsoft SQL Server dapat dilihat pada bab 10.
Kebutuhan Operasi
Microsoft SQL Server adalah perangkat lunak yang berjalan pada
platform Microsoft Windows, sehingga kebutuhan utama dari perangkat
lunak ini adalah tersedianya komputer yang telah terinstal sistem
operasi Windows, terutama versi Server. Hal ini karena Windows versi
Server memberikan integrasi yang lebih baik. Selain itu dari sisi
keamanan dan alokasi kerja model client-server, versi server memberikan
unjuk kerja yang lebih baik.
Microsoft SQL Server adalah perangkat
lunak yang harus diaktifkan sebagai service pada Windows. Jika belum
dijalankan maka kita tidak akan dapat menggunakannya. Default-nya,
ketika Microsoft SQL Server diinstal maka secara otomatis, service ini
akan dibuat dan dijalankan setiap kali Windows dihidupkan. Namun untuk
memastikan kalian dapat memeriksanya dengan membuka Control Panel,
kemudian pilih Administrative Tool. Pada jendela Administrative Tool
(Gambar 12.2) klik ganda pada ikon Service sehingga terbuka jendela
seperti pada Gambar 12.3.
Pada
jendela Service carilah nama service SQL Server kemudian periksalah di
kolom status, apakah sudah started atau belum. Jika belum jalankan
(start) service ini.
12.2.2.Menjalankan Perangkat Lunak Basis Data Berbasis SQL
Setelah terinstal maka kita dapat menggunakan Microsoft SQL
Server dengan memanggil GUI client dari Microsoft SQL Server yang
disebut sebagai SQL Server Management Studio. Ketika pertama kali
dipanggil maka tampilan akan tampak seperti pada Gambar 12.4.
Setiap
kali kita membuka SQL Server Management Studio, kita akan diperiksa
apakah kita berhak menggunakan SQL Server atau tidak. Jika SQL Server
dijalankan pada mesin lokal dan menggunakan Windows Authentication maka
kita dapat langsung menekan tombol Connect untuk masuk ke lingkungan SQL
Server. Tetapi bila SQL Server dijalankan dari komputer lain (server)
kita akan diminta memasukkan user name dan password yang telah
didaftarkan.
Ada informasi penting dari Gambar 12.4 di atas yang
kalian perlu ketahui. Pada gambar tersebut terlihat bahwa kita akan
mengakses tipe server Database Engine, yaitu server yang berhubungan
dengan masalah aplikasi basis data. SQL Server menyediakan beberapa tipe
server yang dapat diakses. Namun tidak dijelaskan pada buku ini. Selain
itu nama server yang akan diakses adalah ARMBLACKMATE. Hal ini karena
SQL Server diinstal pada komputer dengan nama ARM-BLACKMATE sehingga
kita perlu nama server sama dengan nama dimana SQL Server berjalan.
MENU / FITUR UTAMA
Setelah kita berhasil masuk ke dalam lingkungan SQL Server maka
kita akan disuguhi tampilan awal dari SQL Server Management Studio
seperti pada Gambar 12.5.
Ada
dua bagian penting yang ada pada bagian awal ini yaitu Object Explorer
dan Summary Panel. Object Explorer adalah tempat kita melihat obyekobyek
apa saja yang ada di dalam SQL Server. Pada gambar di atas kalian bisa
melihat obyek-obyek yang tersedia. Untuk sementara yang paling penting
adalah Databases. Sedangkan Summary panel merupakan tempat keterangan
atau ringkasan yang ada pada Object Explorer. Coba klik pada salah satu
obyek di Object Explorer, maka isi Summary panel juga akan berubah
sesuai dengan obyek yang kita pilih.
Kita akan fokus pada obyek
Databases. Coba klik tanda + (atau tanda node) di depan obyek Databases,
sehingga akan tampak tampilan seperti pada Gambar 12.6 di samping ini.
Pada Gambar tersebut tampak ada empat basis data yang sudah ada pada SQL
Server, yaitu AdventureWorks, AdventureWorksDW, latihan01SQL dan
Northwind2007SQL. Keempat basis data ini termasuk user object karena
merupakan buatan pengguna. SQL Server juga membuat sendiri basis data
ketika proses instalasi berlangsung. Basis data ini biasa disebut
sebagai system object. Biasanya ada beberapa system object yang tersedia
yaitu Master, Model, MSDB, Resource, TempDB, dan Distribution. Basis
data ini tidak boleh dihapus karena akan membuat sistem menjadi tidak
bekerja normal. Untuk memeriksanya, coba klik node di depan System
Databases.
Sementara ini kita tidak akan berhubungan dengan System
Databases. Kita akan melihat pada user object. Klik pada salah satu
basis data maka tampilan pada Summary Panel akan berubah. Perhatikan
Gambar 12.7. Pada gambar tersebut tampak bahwa basis data AdventureWorks
berisi berbagai komponen antara lain Database Diagrams, Tables, Views
dan yang lainnya. Setiap basis data yang kita buat di SQL server akan
punya komponen-komponen ini secara default. Meskipun kita tidak membuat
Database Diagrams misalnya, komponen Database Diagrams tetap disediakan.
12.4. PEMBUATAN DAN PENGISIAN TABEL
Pembuatan Basis Data
Sebelum kita membuat tabel, kita harus membuat lebih dahulu basis
datanya. Untuk membuat basis data baru, klik kanan pada Databases dan
pilih New Database. Jendela untuk membuat basis data baru akan terbuka
(Gambar 12.8). Pada textbox di depan Database name, ketikkan nama basis
data yang akan dibuat. Pada contoh ini namanya adalah Lat-01. Kemudian
klik OK. Perhatikan pada Object Explorer, basis data baru sudah
terbentuk dan memiliki komponen-komponen yang sama dengan basis data
lainnya.
Pembuatan Tabel
Setelah basis data terbentuk kita baru bisa membuat tabel. Klik
kanan pada komponen Tables di basis data Lat-01 dan pilih New Table…
Bagian Summary panel akan berubah menjadi seperti pada Gambar 12.9.
Kalian bisa mulai memasukkan atribut atau kolom dari tabel yang akan
dibuat. Pembuatan tabel ini sama persis dengan apa yang telah kalian
lakukan dengan Microsoft Access. Penentuan tipe data, lebar data dan
primary key juga tidak jauh berbeda. Yang agak berbeda adalah SQL Server
menyediakan tipe data yang lebih banyak daripada Microsoft Access.
Untuk menentukan primary key, pilih atribut kemudian tekan tanda kunci
pada toolbar.
Pada
gambar di atas, kita membuat tabel dengan 5 kolom yaitu NoInduk, Nama,
Alamat, Kota, NoTelepon. Masing-masing bertipe nchar dengan lebar data
yang bervariasi. Untuk menentukan lebar data, lihatlah pada bagian
property dari kolom. Setelah selesai simpan tabel dengan nama tertentu.
Pada contoh di atas tabel disimpan dengan nama siswa. SQL server akan
memberikan awalan nama (prefix) dbo secara default. Tetapi ini bisa kita
rubah. Jadi nama tabel yang kita buat di atas akan menjadi dbo.siswa.
Pengisian Data pada Tabel
Setelah semua tabel terbentuk, kita dapat mengisi tabel dengan
data-data yang kita inginkan. Dengan menggunakan GUI SQL Server
Management Studio kita dapat mengisi data seperti halnya pada Access.
Klik kanan pada nama tabel kemudian pilih Open Table. Jendela baru pada
Summary panel akan terbuka dan siap untuk diisi datanya. Gambar 12.10
menunjukkan bagaimana kita mengisi data pada sebuah tabel. Setiap kali
kalian mengisi untuk satu baris harus diikuti dengan menekan Enter. Jika
tidak maka data tidak tersimpan. Cobalah isi tabel-tabel yang lain.
OPERASI PADA TABEL DAN VIEW
Relasi Antar Tabel
Tabel-tabel yang telah kalian buat dan isi di atas masih merupakan
tabel yang berdiri sendiri tidak ada hubungan antar tabel. Padahal kalau
dilihat dari tabel-tabel yang ada kita bisa membuat model hubungannya
(lihat bab 10 dan 11 tentang relasi antar tabel). Tabel siswa
berhubungan dengan tabel program, tabel guru berhubungan dengan tabel
bidang dan tabel program. Untuk membuat hubungan antar tabel ini kita
bisa menggunakan komponen Database Diagrams. Klik kanan pada Database
Diagrams kemudian pilih New Database Diagram. Jendela baru akan terbuka
seperti tampak pada Gambar 12.11.
Pada
jendela Add Table klik Add untuk memilih tabel yang akan kita
relasikan. Pada contoh ini kita akan pilih semua tabel karena semua
saling terkait. Klik Add beberapa kali sampai tidak ada nama tabel di
jendela Add Table. Setelah semua tabel terpilih buat relasi seperti
tampak pada Gambar 12.12. Cara menghubungkan tabel satu dengan tabel
yang lain sama persis dengan yang kita lakukan di Microsoft Access.
Simpan diagram ini.
Membuat View
View sama dengan Query pada Microsoft Access. SQL Server juga
menyediakan fitur GUI untuk membuat View seperti halnya GUI Query pada
Microsoft Access. Untuk membuat View, klik kanan pada View kemudian
pilih New View sehingga jendela seperti Gambar 12.13 terbuka. Pilih Add
Table untuk menambahkan satu atau lebih tabel yang akan kita buat
View-nya. Misalnya kita pilih tabel siswa. Klik Close untuk menutup
jendela tersebut. Sehingga akan muncul tampilan seperti tampak pada
Gambar 12.14.
Ada empat bagian penting pada Gambar 12.14 yaitu
jendela Diagram, jendela Criteria, jendela SQL dan jendela Result.
Jendela Diagram digunakan sebagai tempat tabel-tabel sumber yang akan
kita buat View-nya. Jendela Criteria adalah tempat kita menentukan
kriteria-kriteria pada View. Jendela SQL adalah tempat menuliskan
perintah SQL dari View yang kita buat. Jendela Result adalah tempat
untuk menampilkan hasil dari View.
Pada
Gambar 12.14, kita telah memilih tabel Siswa sebagai sumber View dan
muncul pada jendela Diagrams. Kita akan membuat View sederhana
berdasarkan tabel ini. Klik *(All Columns) di bawah nama tabel Siswa.
Isi jendela SQL otomatis akan berubah. Isi jendela SQL akan berubah
sesuai dengan apa yang kalian lakukan pada jendela Diagram dan jendela
Criteria. Kemudian klik tanda seru (!) pada toolbar untuk mengeksekusi
View tersebut atau klik kanan di salah satu bagian pada Gambar 12.14
(boleh di jendela Diagram/Criteria/SQL/Result) kemudian pilih Execute
SQL. Kalian akan memperoleh tampilan seperti pada Gambar 12.15. Simpan
View ini dengan cara klik pada ikon Disket pada Toolbar kemudian
masukkan nama View.
Kalau
kalian perhatikan, langkah-langkah membuat View di atas sama persis
dengan apa yang kalian lakukan ketika membuat Query pada Microsoft
Access. Kita akan buat beberapa contoh yang lebih kompleks. Perhatikan
contoh-contoh berikut.
Contoh 12.1. View untuk menampilkan nama Guru yang alamat rumahnya di luar kota Malang.
Pada
contoh ini kita menggunakan tabel Guru sebagai sumber View. Buat View
baru kemudian tampilkan jendela seperti pada Gambar 12.13. Pilih tabel
Guru kemudian Close. Klik Nama, Alamat dan Kota pada kotak tabel Guru.
Kemudian pada jendela Criteria, pilih baris Kota dan pada kolom Filter
ketikkan pernyataan <> ‘Malang’. Jalankan View ini, kalian akan
mendapatkan hasil seperti pada Gambar 12.16.
Kolom
Filter pada jendela Criteria digunakan untuk memilih baris yang sesuai
dengan keinginan kita. Pada contoh di atas, digunakan untuk memilih
baris yang isi kolom Kotanya bukan ‘Malang’. Kita menggunakan tanda
<> untuk menyatakan ketidaksamaan.
Contoh 12.2. View untuk menampilkan nama, nomor induk, dan program keahlian Siswa secara urut abjad nama.
Contoh
ini membutuhkan minimal 2 tabel, yaitu tabel Siswa dan tabel Program.
Perhatikan relasi antar tabel pada Gambar 12.12. Tabel Siswa berhubungan
langsung dengan tabel Program. Dengan cara yang sama seperti Contoh
12.13, pilih tabel Siswa dan tabel Program. Pada kotak Siswa di jendela
Diagram pilih Nama dan NoInduk sedangkan pada kotak Program pilih
NamaProgram. Pada jendela Criteria, pilih baris Nama dan klik pada kolom
Sort Type kemudian pilih Ascending. Kemudian jalankan View tersebut.
Kalian akan mendapat hasil seperti pada Gambar 12.17. Perhatikan pada
jendela Result, kolom Nama ditampilkan berurutan sesuai dengan abjad.
Contoh 12.3. View untuk menampilkan nama Guru dan bidang keahliannya secara urut abjad nama.
Berdasarkan
relasi tabel pada Gambar 12.12, maka View pada contoh ini membutuhkan
tiga buah tabel yaitu tabel Guru, Guru_Bidang dan Bidang. Kolom Nama ada
di tabel Guru sedangkan kolom NamaBidang ada pada tabel Bidang. Pilih
tiga tabel tersebut. Pada kotak Guru di jendela Diagram pilih Nama
kemudian pada kotak Bidang pilih NamaBidang. Pada jendela Criteria,
pilih baris Nama dan klik pada kolom Sort Type kemudian pilih Ascending.
Jalankan View ini. Perhatikan hasil yang diperoleh.
MENGGUNAKAN T-SQL
Transact-SQL (disingkat T-SQL) adalah varian dari bahasa basis
data SQL yang dikeluarkan oleh perusahaan Microsoft dan Sybase.
Microsoft SQL Server hanya mengenali bahasa SQL tipe T-SQL ini. Oleh
karena itu jika kalian ingin menggunakan Microsoft SQL Server, maka
kalian harus memahami bahasa ini.
Secara umum T-SQL tidak berbeda
jauh dengan SQL standard. Hal ini karena T-SQL sebenarnya adalah bahasa
SQL standar yang diberi tambahan perintah-perintah khusus untuk membantu
kerja dalam SQL Server. Perintah SELECT, FROM dan WHERE yang telah kita
singgung di awal bab, tetap menjadi perintah utama di T-SQL.
Untuk
membuat perintah-perintah T-SQL ini SQL Server mempunyai jendela Query
yang dapat kita buka dengan cara klik pada tombol New Query di bawah
Menu Bar (lihat Gambar 12.19). Sebelumnya pilih terlebih dahulu
basisdata yang ingin kita gunakan dengan cara klik pada nama basis data
di Object Explorer. Setelah jendela Query terbuka kalian dapat mulai
mengetikkan perintah-perintah SQL.
Definisi Tabel dengan T-SQL
Hampir semua aktivitas di dalam SQL Server dapat dilakukan dengan
menggunakan perintah-perintah T-SQL. Termasuk membuat tabel. Pada sub
bab sebelumya kalian telah membuat tabel-tabel dengan menggunakan
fasilitas GUI. Kalian juga bisa membuat tabel-tabel tersebut dengan
perintah-perintah SQL. Perintah-perintah yang berhubungan dengan
definisi tabel termasuk dalam kategori DDL. Perintah untuk pendefinisian
atau pembuatan tabel baru adalah CREATE TABLE. Sedangkan untuk
menghapus kita menggunakan perintah DROP TABLE
Buat basis data baru
dengan nama Lat-01_SQL. Kemudian pilih basis data tersebut. Buka jendela
Query seperti pada Gambar 12.18. Kiata akan membuat tabel-tabel yang
sama seperti pada Lat-01 tetapi dengan perintah SQL. Tabel pertama yang
kita buat adalah tabel Siswa (lihat kembali struktur tabel ini pada
Tabel 12.1 di atas). Ketikkan perintah berikut ini, kemudian jalankan
dengan klik tanda seru (!).
CREATE TABLE [dbo].[Bidang](
[IdBidang] [smallint] NOT NULL,
[NamaBidang] [nchar](20)NULL,
[Deskripsi] [nchar](100)NULL,
CONSTRAINT [PK_Bidang] PRIMARY KEY CLUSTERED
(
[IdBidang] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Perintah CREATE TABLE diikuti dengan nama tabel yang akan kita
buat ([dbo].[Bidang]) kemudian diikuti dengan daftar kolom yang ada pada
tabel tersebut. Pada daftar kolom ini, tipe data, lebar data dan
kondisi lainnya (misalnya NOT NULL atau NULL) harus dicantumkan. Setelah
itu baru bagian CONSTRAINT dari tabel tersebut dituliskan. Bagian
CONSTRAINT ini biasanya berisi pendefinisian Primary Key dari tabel
tersebut. Perhatikan cara penulisan perintah-perintah di atas.
Setelah
kalian jalankan, periksalah pada bagian node Tables apakah tabel kalian
sudah terbentuk atau belum. Klik kanan pada Tables basis data kalian di
Object Explorer kemudian pilih Refresh. Tabel baru yang kalian buat
akan muncul di bawah Object Tables. Buatlah tabel-tabel lainnya dengan
cara yang sama. Berikut ini perintah-perintah pembuatan masing-masing
tabel.
Tabel Program
CREATE TABLE [dbo].[Program](
[IdProgram] [smallint] NOT NULL,
[NamaProgram] [nchar](20) NULL,
[Deskripsi] [nchar](100) NULL,
CONSTRAINT [PK_Program] PRIMARY KEY CLUSTERED
(
[IdProgram] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Tabel Guru
CREATE TABLE [dbo].[Guru](
[NIP] [nchar](15) NOT NULL,
[Nama] [nchar](20) NULL,
[Alamat] [nchar](30) NULL,
[Kota] [nchar](20) NULL,
[Telepon] [nchar](15) NULL,
CONSTRAINT [PK_Guru] PRIMARY KEY CLUSTERED
(
[NIP] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Tabel Siswa
CREATE TABLE [dbo].[Siswa](
[NoInduk] [nchar](10) NOT NULL,
[Nama] [nchar](20) NULL,
[Alamat] [nchar](30) NULL,
[Kota] [nchar](20) NULL,
[Telepon] [nchar](15) NULL,
[IdProgram] [smallint] NULL,
CONSTRAINT [PK_Siswa] PRIMARY KEY CLUSTERED
(
[NoInduk] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Tabel Guru_Program
CREATE TABLE [dbo].[Guru_Program](
[NIP] [nchar](15) NOT NULL,
[IdProgram] [smallint] NOT NULL,
CONSTRAINT [PK_Guru_Program] PRIMARY KEY CLUSTERED
(
[NIP] ASC,
[IdProgram] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Tabel Guru_Bidang
CREATE TABLE [dbo].[Guru_Bidang](
[IdBidang] [smallint] NOT NULL,
[NIP] [nchar](15) NOT NULL,
CONSTRAINT [PK_Guru_Bidang] PRIMARY KEY CLUSTERED
(
[IdBidang] ASC,
[NIP] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Pada
perintah-perintah pembuatan tabel di atas, kita belum membuat relasi
antar tabel. Relasi antar tabel pada SQL biasanya dinyatakan dalam
hubungan FOREIGN KEY (lihat kembali pengertian FOREIGN KEY pada bab 10
dan 11). Kita perlu memodifikasi tabel dengan menambahkan CONSTRAINT
agar tabel dapat mengerti relasi antar tabel. Buka kembali jendela Query
kemudian ketikkan perintah-perintah berikut ini.
ALTER TABLE [dbo].[Siswa] WITH CHECK ADD CONSTRAINT
[FK_Siswa_Program] FOREIGN KEY([IdProgram])
REFERENCES [dbo].[Program] ([IdProgram])
ALTER TABLE [dbo].[Guru_Program] WITH CHECK ADD
CONSTRAINT [FK_Guru_Program_Guru] FOREIGN KEY([NIP])
REFERENCES [dbo].[Guru] ([NIP])
ALTER TABLE [dbo].[Guru_Bidang] WITH CHECK ADD CONSTRAINT
[FK_Guru_Bidang_Bidang] FOREIGN KEY([IdBidang])
REFERENCES [dbo].[Bidang] ([IdBidang])
ALTER TABLE [dbo].[Guru_Bidang] WITH CHECK ADD CONSTRAINT
[FK_Guru_Bidang_Guru] FOREIGN KEY([NIP])
REFERENCES [dbo].[Guru] ([NIP])
Perintah untuk memodifikasi atau merubah struktur tabel adalah ALTER
TABEL
kemudia diikuti dengan nama tabel yang ingin dirubah. Perhatikan pada
baris ALTER TABLE [dbo].[Siswa] WITH CHECK ADD CONSTRAINT
[FK_Siswa_Program]
FOREIGN KEY([IdProgram]). Tabel yang ingin dimodifikasi adalah
dbo.Siswa dan tabel ini berhubungan dengan tabel Program
sehingga
dituliskan FK_Siswa_Program. Kolom IdProgram pada tabel Siswa merupakan
FOREIGN KEY sehingga dituliskan sebagai FOREIGN KEY([IdProgram]). Kolom
IdProgram ini berasal dari tabel program sehingga pada bagian akhir
perintah harus dituliskan referensi tabelnya (ditulis dengan REFERENCES
[dbo].[Program] ([IdProgram])). Dengan cara yang sama relasi-relasi
antar tabel di atas dibuat.
Perintah DROP TABEL sangat mudah
dilakukan yaitu tinggal menambahkan nama tabel didepan perintah
tersebut. Misalnya DROP TABLE [dbo].[Bidang].
Pengisian
Perubahan dan Penghapusan Isi Tabel dengan SQL Setelah semua tabel
selesai dibuat, maka kita dapat mengisi data pada masing-masing tabel
dengan menggunakan perintah INSERT. Perintah ini termasuk dalam kelompok
DML. Kita akan mencoba menggunakan perintah ini pada tabel Bidang. Kita
periksa dulu isi tabel Bidang dengan cara klik kanan pada nama tabel
kemudian pilih Open Table. Isi tabel akan muncul pada jendela Summary
(Gambar 12.20). Tutup kembali jendela Open Table tersebut.
Buka jendela Query kemudian ketikkan perintah berikut.
INSERT INTO [Lat-01].[dbo].[Bidang]
([IdBidang]
,[NamaBidang]
,[Deskripsi])
VALUES
(8, 'Jaringan Wireless', NULL)
Perintah
INSERT diikuti dengan nama basis data dan nama tabel
([Lat-01].[dbo].[Bidang])kemudian diikuti dengan nama kolom yang ada
pada tabel tersebut. Kata kunci VALUES digunakan untuk memberi petunjuk
bahwa bagian setelah kata kunci ini adalah data yang akan dimasukkan.
Perhatikan,
karena ada 3 kolom di tabel Bidang, maka kita juga memasukkan 3 buah
data. Data pertama (8) harus terisi karena pada definisi tabel Bidang,
kolom IdBidang dinyatakan NOT NULL. Data ketiga yaitu untuk kolom
Deskripsi kita isi NULL karena kita ingin mengosongkan isi kolom ini.
Jalankan
perintah di atas. Kemudian klik kanan pada tabel Bidang kemudian pilih
Refresh. Kemudian klik kanan lagi pada tabel Bidang dan pilih Open
Table. Data yang kalian masukkan sudah berada pada tabel Bidang (Gambar
12.21).
Kalau
kalian perhatikan, perintah INSERT di atas terlalu panjang. Coba hapus
bagian daftar nama kolom pada perintah tersebut. Kemudian ganti data di
bawah VALUES menjadi (9, ’Digital Animation’, NULL). Kemudian jalankan
dan periksalah hasilnya. Apa yang terjadi? Setelah Refresh, kalian akan
menjumpai bahwa data kalian juga dapat dimasukkan tanpa harus menyebut
daftar nama kolom pada perintah INSERT.
Seringkali dalam pengisian
data pada suatu tabel, kita melakukan kesalahan. Kesalahan dapat berupa
kesalahan ketik atau kesalahan pembacaan data. Sehingga ketika
diperiksa, kita menginginkan untuk merubah/memperbaiki data tersebut.
Proses ini biasa disebut sebagai update data. SQL menyediakan perintah
UPDATE untuk melakukan proses ini. Perintah ini masih termasuk dalam
kelompok DML.
Misalnya kita ingin merubah isi kolom NamaBidang pada
IdBidang yang ke-5 (lihat Gambar 12.21). Dari ‘Fotografi’ kita rubah
menjadi ‘Fotografi Digital’ maka kita ketikkan perintah seperti berikut.
UPDATE [Lat-01].[dbo].[Bidang]
SET [NamaBidang] = 'Fotografi Digital'
WHERE [IdBidang] = 5
Periksa kembali isi tabel Bidang. Jangan lupa untuk me-refresh dulu tabel Bidang sebelum perintah Open Table dijalankan.
Penghapusan data dengan SQL dilakukan dengan perintah DELETE. Perlu
diperhatikan bahwa perintah DELETE akan menghapus isi seluru baris.
Kalau kalian hanya ingin mengosongkan isi satu bagian dari baris (atau
satu sel) saja gunakan perintah UPDATE. Misalnya kita ingin kita
menghapus baris yang IdBidangnya sama dengan 8 maka kita ketikkan dengan
perintah seperti berikut.
DELETE FROM [Lat-01].[dbo].[Bidang]
WHERE [IdBidang] = 8
Mencari dan Menampilkan Data (View) dengan SQL
Seperti telah disinggung di awal bab, perintah utama dalam SQL
adalah SELECT, FROM dan WHERE. Sebagian besar aktivitas kita menggunakan
SQL berhubungan dengan perintah-perintah ini. Untuk menggunakan
perintahperintah ini, buka jendela Query kemudian ketikkan perintah yang
kalian inginkan. Jalankan dengan menekan tombol tanda seru (!).
Perintah
SELECT digunakan untuk memilih kolom mana saja yang akan ditampilkan.
Jika kita ingin menampilkan semua kolom, kita cukup menggunakan tanda *.
Perintah FROM digunakan untuk menentukan asal kolom yang ingin kita
tampilkan datanya. Perintah WHERE digunakan untuk membatasi baris-baris
yang akan kita tampilkan agar sesuai dengan kriteria yang kita inginkan.
WHERE bisa dari tabel atau hasil dari View yang lain. Perhatikan
contoh-contoh penggunaan SQL berikut ini.
Contoh 12.4. Menampilkan semua data pada tabel Bidang.
Contoh
ini kita membutuhkan tabel Bidang. Sedangkan kolom yang kita ingin
tampilkan adalah semua kolom. Kita dapat menggunakan tanda * atau
mendaftar semua kolom yang ada pada tabel Bidang. Berikut adalah
perintah SQL dan hasil eksekusinya.
Jika
kalian ingin menyimpan dalam bentuk View perintah di atas, tambahkan
sebelum pernyataan SELECT perintah CREATE VIEW <nama_view> AS.
Ganti <nama_view> dengan nama yang kalian inginkan.
Contoh 12.5. Menampilkan data nama dan alamat siswa.
Pada contoh
ini kita membutuhkan tabel Siswa. Sedangkan kolom yang kita inginkan
adalah kolom nama, alamat, dan kota. Kita tidak bisa menggunakan tanda
*, tetapi harus mendaftarkan nama kolom nama dan alamat setelah perintah
SELECT. Perhatikan perintah SQL dan outpunya berikut ini.
Contoh 12.6. Menampilkan data nama siswa yang rumahnya di Batu.
Pada
contoh ini kita membutuhkan pernyataan WHERE untuk membatasi baris yang
ingin kita tampilkan. Tabel yang dipakai adalah tabel Siswa dan kolom
yang ingin ditampilkan adalah kolom Nama. Kalau kalian perhatikan hasil
eksekusi contoh 12.2 di atas, yang alamat rumahnya di Batu ada dua orang
yaitu Pepe Ricard dan Bubi Red. Pernyataan WHERE diikuti dengan kondisi
yang harus dipenuhi. Pada kasus ini adalah Kota=’Batu’. Berikut ini
perintah SQL secara lengkap dan outputnya. Perhatikan cara penulisannya.
Contoh 12.7. Menampilkan data nama, alamat dan kota dari guru yang rumahnya tidak di Malang.
Pada
bagian ini kita juga menggunakan perintah WHERE untuk membatasi data
yang ingin kita tampilkan. Tabel yang kita gunakan adalah tabel guru dan
kolom yang kita butuhkan adalah kolom nama, alamat dan kota. Pernyataan
yang rumahnya tidak di Malang merupakan kondisi yang harus dipenuhi.
Sehinga secara lengkap pernyataan akan tampak seperti berikut.
Contoh 12.8. Menampilkan data nama siswa dan program keahlian yang diikuti.
Pada
contoh ini kita melibatkan dua buah tabel yang saling berhubungan yaitu
tabel Siswa dan tabel Program. Yang kita ingin tampilkan adalah kolom
Nama pada tabel Siswa dan kolom NamaProgram pada tabel Program. Untuk
kasus seperti ini pada perintah SELECT pemanggilan nama kolom harus
didahului dengan nama tabelnya. Sedangkan pada FROM kita menggunakan
perintah INNER JOIN untuk menggabung dua tabel. Perlu kalian cermati
(lihat Tabel 12.1 di atas), pada tabel Siswa terdapat Foreign Key yaitu
IdProgram yang merupakan Primary Key pada tabel Program. IdProgram ini
merupakan penghubung yang dapat kita gunakan untuk menggabung dua tabel.
Perhatikan pernyataan SQL berikut ini dan cermati outputnya.
Contoh 12.9. Menampilkan data nama siswa yang mengikuti program keahlian RPL.
Contoh
ini merupakan kelanjutan dari contoh 12.4. Kita menambahkan pernyataan
WHERE untuk menampilkan nama yang ikut program keahlian RPL.
Selain itu kita hanya ingin menampilkan kolom Nama dari tabel Siswa. Perhatikan pernyataan SQL berikut dan hasil eksekusinya.
Contoh 12.10. Menampilkan data nama guru dan bidang keahliannya.
Contoh
ini lebih kompleks dari contoh-contoh di atas. Kita melibatkan tiga
buah tabel, yaitu tabel Guru, tabel Guru_Bidang dan tabel Bidang.
Perhatikan relasi antar tabel tersebut pada Gambar 12.11. Ada dua INNER
JOIN yang kita gunakan, yaitu INNER JOIN tabel Guru dengan tabel
Guru_Bidang dan INNER JOIN Guru_Bidang dengan tabel Bidang. Perhatikan
bagaimana membuat pernyataan INNER JOIN.
Contoh
Contoh 12.11. Menampilkan data nama guru yang bidang keahliannya Pemrograman Web atau Basis Data.
Contoh
ini merupakan pengembangan dari contoh 12.10. Kita ingin menampilkan
guru yang bidang keahliannya Pemrograman Web atau Basis Data.
Kita
perlu menambahkan perintah WHERE untuk membatasi baris. Pada WHERE kita
juga perlu operator OR. Perhatikan perintah SQL dan hasil eksekusinya
berikut ini.
Bandingkan
dengan hasil eksekusi contoh 12.10. Cobalah ganti OR dengan AND dan
jalankan kembali perintah SQL tersebut. Bagaimanakah hasilnya?
Contoh-contoh di atas dapat dikembangkan lagi dengan banyak variasi.
Dengan banyak mencoba dan berlatih maka kalian akan dapat memahami dengan baik penggunaan perintah-perintah SQL.
FUNGSI, PROCEDURE DAN TRIGGER
Microsoft SQL Server menyediakan fasilitas-fasilitas tingkat
lanjut yang tidak dimiliki oleh Microsoft Access seperti kemampuan
membuat fungsi, mendefinisikan store procedure dan trigger. Hal ini
karena SQL Server diperuntukkan sebagai basis data server yang
membutuhkan kinerja yang lebih kuat dari Microsoft Access.
12.7.1.FUNGSI
T-SQL menyediakan banyak fungsi yang digunakan untuk mempermudah
tugas-tugas dalam pengelolaan basis data. Beberapa fungsi penting akan
disampaikan di sini. Untuk lebih lengkapnya silahkan baca manual atau
onlinehelp dari SQL Server.
- Fungsi-fungsi yang berhubungan dengan numeric
Fungsi-fungsi
penting yang berhubungan dengan numeric (angka) adalah isNumeric dan
Round. Fungsi isNumeric digunakan untuk memeriksa apakah isi suatu data
berupa data angka atau tidak. Buka jendela Query kemudian ketikkan
perintah seperti pada contoh 12.12 dan periksalah hasilnya. Tabel Siswa
mempunyai kolom Telepon yang isi datanya sekilas berupa angka. Kita
dapat memeriksa dengan menggunakan fungsi isNumerik. Pada hasil eksekusi
di bawah ini terlihat nilai hasil pemeriksaan menghasilkan angka 0
untuk seluruh data. Angka 0 berarti false. Berarti, semua data pada
kolom Telepon bukan data numeric.
Contoh 12.12. Menggunakan fungsi isNumeric.
Fungsi
ROUND digunakan untuk membulatkan bilangan pecahan ke bilangan bulat
terdekat. Misalnya 13.58 akan menjadi 14.00. Perhatikan contoh berikut
ini.Fungsi ROUND digunakan untuk membulatkan bilangan pecahan ke
bilangan bulat terdekat. Misalnya 13.58 akan menjadi 14.00. Perhatikan
contoh berikut ini.
- Fungsi-fungsi yang berhubungan dengan string
Fungsi-fungsi
penting yang berhubungan dengan karakter (string) antara lain adalah
LEFT, RIGHT, LEN, LOWER, UPPER, LTRIM, dan RTRIM.
LEFT digunakan
untuk memilih sejumlah karakter tertentu dari sebelah kiri sedangkan
RIGHT dari sebelah kanan. Hasil dari RIGHT tergantung dari lebar data
yang kalian tetapkan pada pembuatan table. LEN digunakan mengetahui
panjang karakter pada data. Perhatikan contoh berikut.
Contoh 12.14. Menggunakan LEFT, RIGHT dan LEN.
LOWER
digunakan untuk merubah karakter menjadi huruf kecil, sedangkan UPPER
sebaliknya. LTRIM digunakan untuk menghilangkan space di sebelah kiri
data string, sedangkan RTRIM di sebelah kanan.
Lihat contoh berikut.
Contoh 12.15. Menggunakan fungsi UPPER dan LOWER.
- Fungsi-fungsi yang berhubungan dengan waktu
Fungsi-fungsi yang berhubungan dengan waktu yang penting antara lain:
GETDATE, MONTH, DAY, YEAR, DATENAME, DATEADD, and DATEDIFF.
GETDATE
digunakan untuk mendapatkan tanggal sekarang dari system computer.
MONTH digunakan untuk mengambil bagian bulan dari data tanggal. DAY
digunakan untuk mengambil bagian tanggal dari data tanggal. YEAR
digunakan untuk mengambil bagian tahun dari data tanggal. DATENAME
digunakan untuk mendapatkan nama hari dari suatu tanggal. DATEADD
digunakan untuk menambah atau mengurangi data tanggal. DATEDIFF
digunakan untuk melihat selisih antara dua buah data tanggal. Perhatikan
contoh berikut.
12.7.2.Procedure dan Stored Prosedure
Selain fungsi-fungsi yang tersedia di atas, SQL Server juga
memperkenalkan user-defined function. Fungsi ini adalah fungsi yang
dapat kita buat sendiri untuk mempercepat pengelolaan basis data.
Kadang-kadang fungsi jenis ini disebut juga procedure. Ada dua tipe user
defined function yaitu scalar dan inline table-value. Fungsi scalar
menghasilkan satu nilai keluaran melalui kata kunci Return. Inline
Table-Value menghasilkan suatu table baru ketika dieksekusi. Perhatikan
contoh berikut.
Contoh 12.17. Membuat fungsi scalar.
Fungsi yang ada di samping ini diberi nama dbo.FullName.
Parameter
yang digunakan ada dua yaitu @FirstName dan @LastName. Perhatikan
bagaimana mendefinisikan suatu fungsi dan parameternya. Output dari
fungsi akan bertipe data nVarChar dengan lebar data 35. Fungsi ini akan
memberikan output satu nilai yaitu gabungan dari @LastName dan
@Firstname. Sehingga kita bisa
nyatakan ini termasuk dalam fungsi scalar.
Pada
kode yang di blok, terlihat bagaimana fungsi tersebut dipanggil pada
suatu pernyataan query. Perhatikan output yang dihasilkan. Terbentuk
kolom baru yang berisi gabungan kolom LastName dan FirstName. Stored
procedure adalah potongan kode program yang dapat menerima parameter
input dan menghasilkan satu atau lebih parameter output. Stored
procedure umumnya digunakan untuk operasi-operasi pada basis data.
Biasanya suatu perintah SQL yang rumit, panjang dan kompleks disimpan
sebagai stored procedure. Jika kita ingin melakukan operasi tersebut
kita tidak perlu mengetik ulang, cukup kita panggil nama stored
procedure dan kita eksekusi langsung.
Perintah untuk membuat stored
procedure adalah CREATE PROCEDURE kemudian diikuti dengan nama
procedure-nya. Perhatikan contoh berikut ini.
Contoh 12.18. Membuat stored procedure.
Buka jendela Query, kemudian ketikkan kode berikut ini.
CREATE PROCEDURE hapusBaris
@IdNumber smallint
AS
DELETE
FROM dbo.Bidang Where dbo.Bidang.IdBidang = @IdNumber
Procedure
yang kita buat ini bernama hapusBaris dengan satu parameter yaitu
@IdNumber dengan tipe data smallint. Pernyataan setelah AS adalah
pernyataan SQL yang akan dikerjakan ketika stored procedure di atas
dijalankan.
Trigger
Trigger adalah tipe khusus dari stored procedure yang akan
dieksekusi ketika suatu kejadian muncul. Kejadian tersebut misalnya
ketika ada penambahan data (INSERT), penghapusan data (DELETE) atau
perubahan data (UPDATE). Trigger biasanya merupakan komponen dari suatu
table.
Cara membuatnya adalah klik node di depan table yang anda pilih.
Setelah
muncul daftar komponen table tersebut klik kanan pada Trigger dan pilih
New Trigger. SQL Server akan menampilkan jendela baru yang isinya
adalah template dari Trigger. Hapus semua teks pada jendela tersebut
kemudian ketikkan contoh berikut ini.
Contoh 12.19. Membuat trigger.
CREATE TRIGGER coba_trigger
ON dbo.Bidang FOR INSERT
AS
DECLARE @varNama Varchar(20)
SELECT @varNama = NamaBidang FROM INSERTED
PRINT 'Anda baru memasukan data : ' + @varNama
Trigger
di atas berada pada table Bidang. Nama triggernya adalah coba_trigger
dan akan dijalankan ketika event pemasukkan data (INSERT) pada table
dbo.Bidang terjadi.
ADMINISTRASI SQL SERVER
Security dan Authentication.
Security atau keamanan basis data merupakan komponen yang sangat
penting. Karena sifatnya yang berperan sebagai server maka sebenarnya
SQL Server secara teori dapat diakses oleh siapa saja. Hal ini tentu
akan sangat merugikan jika ada pihak-pihak yang tidak bertanggung jawab
melakukan perubahan, perusakan atau bahkan menghapus basis data yang
kita buat dengan susah payah.
SQL Server menyediakan mekanisme
Authentication untuk membatasi siapa yang berhak masuk ke dalam sistem
SQL Server. Ada dua model authentication yaitu Windows Authentication
dan SQL Server Authentication.
- Windows Authentication. Pada model
ini SQL Server tidak melakukan pengecekan pada user name dan password
yang diberikan namun dipercayakan pada system operasi Windows. Artinya
sebenarnya hanya user yang terdaftar pada computer tersebut yang mungin
bisa masuk pada SQL Server.
- SQL Server Authentication. Informasi
user name dan password disimpan dalam SQL Server sehingga setiap kali
ada user yang akan masuk koneksi ke SQL Server, SQL Server akan
memeriksanya.
Untuk melihat model authentication apa yang ada di SQL
Server, klik kanan pada nama server di jendela Object Explorer kemudian
pilih Properties.
Pada jendela Server Properties pilih bagian Security sehingga tampilan akan tampak seperti pada Gambar 12.22.
Untuk
menambahkan user yang bisa masuk ke SQL Server, klik node pada Security
di Object Explorer, kemudian klik kanan Logins. Pilih New Logins untuk
membuka jendela Login seperti pada Gambar 12.23. Buat user baru yang
kalian inginkan.
12.8.2.Permissions
Permissions berhubungan dengan hak akses seorang user pada suatu
basis data. Seorang yang sudah terdaftar sebagai user pada SQL Server
tidak secara otomatis bisa menggunakan basis data yang ada jika belum
diberi hak. Untuk mengubah hak user pada basis data tertentu, klik node
pada Logins. Klik kanan pada nama user yang terdaftar di bawah Logins
kemudian pilih Properties. Pada jendela Login Properties, pilih bagian
User Mapping sehingga tampilan akan tampak seperti pada Gambar 12.24.
Gambar
di atas menunjukka user dengan nama sa merupakan pemilik (db_owner)
dari beberapa basis data (lihat basis data yang dicentang). Kita dapat
mengatur peran (role) seorang member pada basis data dengan mencentang
atau tidak pada bagian Database role membership.
RINGKASAN
- Data Definition Language (DDL) adalah satu paket bahasa DBMS
yang berguna untuk melakukan spesifikasi terhadap skema basis data
sedangkan Data Manipulation Language (DML) adalah satu paket DBMS yang
memperbolehkan pemakai untuk mengakses atau memanipulasi data
sebagaimana yang telah diorganisasikan sebelumnya dalam model data yang
tepat.
- SQL lebih menekankan pada aspek pencarian dari dalam tabel hal ini karena pencarian adalah inti dari pengelolaan data.
- Pembuatan Tabel dan View pada SQL server dapat dilakukan dengan cara GUI atau dengan menggunakan perintah-perintah SQL.
- Perintah utama SQL adalah SELECT, FROM dan WHERE.
- Microsoft SQL Server menyediakan fasilitas fungsi built-in dan user-defined function.
-
Stored procedure adalah potongan kode program yang dapat menerima
parameter input dan menghasilkan satu atau lebih parameter output.
Trigger adalah tipe khusus dari stored procedure yang akan dieksekusi ketika suatu kejadian muncul
-
SQL Server menyediakan mekanisme Authentication untuk membatasi siapa
yang berhak masuk ke dalam sistem SQL Server dengan dua model yaitu
Windows Authentication dan SQL Server Authentication. Selain itu
keamanan data juga diatur dengan cara pemberian permissions.
REFERENSI :
smkibukartini.sch.id
