Pembuatan dan Manajemen Tabel pada PostgreSQL

Pembuatan dan Manajemen Tabel pada PostgreSQL - Sebelum kita memulai bagaimana cara membuat, mendesain, dan memanage database menggunakan aplikasi PostgreSQL 9.0, alangkah lebih baiknya bila kita mengenal dulu hal – hal yang terkait mengenainya.

Definisi Dasar Struktur Database 

Data : sekumpulan fakta mengenai objek tertentu, orang dan lain-lain yang dinyatakan dengan angka, huruf, gambar, film, suara dan sebagainya yang relevan dan belum mempunyai arti.

Informasi : hasil pengolahan data yang konkrit dan sudah mempunyai arti untuk mencapai suatu tujuan tertentu.

Tabel : merupakan hal yang paling mendasar dalam hal penyimpanan data yang terdiri dari field dan record.

Field (kolom) : merupakan elemen dari tabel yang berisikan informasi tertentu yang spesifik tentang subjudul tabel pada sebuah item data.

Syarat-syarat pembentukan Field Name pada tabel :

• Harus Unik atau Spesifik
• Boleh disingkat
• Pemisah sebagai pengganti spasi dalam pembentuk field adalah tanda lambang “_”

Contoh :

Kode Barang menjadi KdBarang, KodeBrg, Kd_Brg, Kd_Barang, Kode_Brg Tanggal Lahir menjadi TglLahir, Tgl_Lahir, Tgl_Lhr

Record (baris) : merupakan sekumpulan data yang saling berkaitan tentang sebuah subjek tertentu, misalnya data seorang siswa akan disimpan dalam record yang terdiri dari beberapa kolom/field.

Istilah-Istilah Dalam Database

• Variable yaitu container khusus digunakan untuk menyimpan data untuk sementara pada program
• Field adalah kategori informasi yang disimpan dalam database
• Data adalah satu satuan informasi
• Width yaitu menunjukkan lebar sebuah field
• File adalah kumpulan dari beberapa record yang sejenis
• Record adalah kumpulan dari suatu informasi orang atau suatu objek tertentu
• Field kunci berguna untuk mengidentifikasikan record

Konsep SQL

SQL (Structured Query Language) (baca: sequel) adalah salah satu bahasa generasi level ke-4 (4th GL) yang awalnya dikembangkan oleh IBM di San Jose Research Laboratory. Berbeda dengan bahasa pemrogramman level ke-3 (3rd GL), SQL adalah bahasa yang bersifat request oriented dan bersifat non-prosedural sehinggan lebih mudah untuk dipelajari karena sintaksis yang digunakan hampir menyerupai bahasa yang digunakan oleh manusia untuk berkomunikasi. Oleh karena itu, SQL lebih fleksibel dalam penggunaannya. Selain itu, SQL juga bersifat non case sensitif. Banyak vendor pembuat DBMS (Database Management System) yang saat ini menggunakan SQL sebagai standarisasi dalam produk mereka, seperti ORACLE, Microsoft SQL Server, PostGreSQL, dan MySQL.

SQL sendiri terbagi atas beberapa bagian, yaitu:

1. DDL (Data Definition Language)

Struktur/skema basis data yang menggambarkan/mewakili desain basis data secara keseluruhan didefinisikan dengan bahasa khusus yang disebut DDL. Dengan bahasa inilah kita dapat membuat tabel baru, membuat indeks, mengubah tabel, menentukan struktur penyimpanan tabel dan sebagainya. Hasil dari kompilasi perintah DDL adalah kumpulan tabel yang disimpan dalam file khusus yang disebut Kamus Data

DDL juga dapat diartikan sebagai bahasa yang memiliki kemampuan untuk mendefinisikan data yang berhubungan dengan pembuatan dan penghapusan objek seperti tabel, indeks, bahkan basis datanya sendiri. Misalnya, CREATE, DROP, dan ALTER.

2. DML (Data Manipulation Language)

Merupakan bentuk bahasa basis data yang berguna untuk melakukan manipulasi dan pengambilan data pada suatu basis data. DML terdiri dari beberapa bahasa yang berhubungan dengan proses manipulasi data pada tabel, record. Misalnya, INSERT, UPDATE, SELECT, dan DELETE.

Operasi Pembuatan dan Manajemen Tabel PostgreSQL

Membuat Tabel

Disini saya akan membuat sebuah tabel dengan nama identitas dan field sebagai berikut

Nama	Tipe Data	Keterangan
Ide_id	Int4	Not Null
Ide_namaDepan	Varchar(10)
Ide_namaBelakang	Varchar(10)
Ide_tgl_lahir	Date
Ide_lsnikah	Bool
Ide_alamat	Text

Querynya adalah sebagai berikut:

create table identitas (ide_id int4 not nul, ide_namadepan varchar(10), ide_namabelakang varchar(10) ide_tgl_lahir date, ide_isnikah bool, ide_alamat text );

Setelah itu lakukan pengecekan dengan \z, maupun \d identitas. lihat gambar:

Selanjutya saya akan membuat tabel kembali dengan nama pegawai dengan nama fiels yang sama seperti sebelumnya. Querynya adalah sebagai berikut:

create table pegawai (ide_id int4 not nul, ide_namadepan varchar(10), ide_namabelakang varchar(10) ide_tgl_lahir date, ide_isnikah bool, ide_alamat text );

Kembali lakukan pengecekan dengan \z, maupun \d pegawai. lihat gambar:

Ada beberapa hal yang harus diperhatikan dalam pembuatan tabel, yaitu:

• Tabel harus memiliki primary key, artinya dalam pembuatan tabel haruslah terdapat sekelompok kolom (field) yang menyebabkan setiap baris (record) dalam tabel tersebut tidak sama. 
• Deklarasi primary key tidak boleh kosong (null), jadi kita harus mendeklarasikan sebagai not null. Namun secara default PostgreSQL menganggapnya sebagai nullable (boleh kosong), jika waktu pengisian tidak menyebutkan null atau not null. 

Memanipulasi Tabel

PostgreSQL telah menyediakan beberapa fitur yang dapat digunakan untuk memanipulasi dan perlu diingat hal ini bukan manipulasi data melainkan manipulasi struktur tabelnya. Ada 5 struktur query yang dapat digunakan dalam memanipulasi struktur tabel

Menghapus kolom

Selanjutya saya akan menghapus kolom ‘ide_alamat” yang terdapat pada tabel pegawai dengan query sebagai berikut: 

alter table pegawai drop column ide_alamat;

Tetap lakukan pengecekan terhadap tabel pegawai. Lihat gambar:

Mengubah nama tabel dan kolom

untuk mengubah nama tabel pegawai menjadi pekerja, kita dapat menggunakan struktur query berikut: 

alter table “pegawai”  rename to pekerja;

Sedangkan untuk mengubah nama kolom ide pekerjaan menjadi ide_alamat, kita dapat menggunakan struktur query berikut: 

alter table pekerja rename column “ide_pekerjaan” to ide_alamat;

Jangan lupa untuk melakukan pengecekan, untuk lebih jelasnya mari kita lihat pada gambar:

Menambahkan primary key

untuk menambahkan primary keypada tabel pekerja kita dapat menggunakan struktur query berikut: 

alter table pekerja add primary key (ide_id);

Tetap lakukan pengecekan terhadap tabel terkait. untuk lebih jelasnya mari kita lihat pada gambar:

Jika muncul “INDEXES : “pekerja_pkey” PRIMARY KEY, btree (ide_id)”. Hal itu menandakan bahwa proses penambahan primary key pada tabel tersebut telah berhasil

Menghapus tabel identitas

Menghapus tabel identitas sama halnya seperti metode sebelumnya, yaitu dengan query sebagai berikut: 

drop table identitas;

Selanjutnya kembali lakukan pengecekan dengan mengetik \z. Selengkapnya lihat pada gambar:

Temporary tabel

Kemudian saya akan membuat temporary tabel dengan nama mahasiswa_sementara dengan Struktur query yang digunakan adalah sebagai berikut: 

create temporary table mahasiswa_sementara (mhs_nim int not null, mhs_nama varchar(30), mhs_jurusan text);

Selanjutnya tabel akan saya tampilkan dengan \d mahasiswa_sementara. Selengkapnya lihat pada gambar:

Catatan: Temporary tabel sifatnya hanya sementara artinya akan aktif hanya ketika kita sedang berada atau login ke database namun ketika kita logout dari psql database maka secara otomatis temporary tabel akan terhapus.

Grant dan Revoke

PostgreSQL juga menyediakan beberapa fitur yang dapat digunakan untuk memberikan atau menghapus hak akses pada user tertentu. Ada 4 jenis struktur query yang dapat digunakan dalam grant dan revoke:

Mencabut hak akses

Disini saya akan mencoba untuk mencabut hak akses tabel pekerja kepada user “sofi_dwi_p”. Berikut struktur penggunaan querynya : 

revoke all on table pekerja from sofi_dwi_p;

Selanjutnya lakukan pengecekan dengan mencoba untuk memberikan perintah terhadap tabel pekerja yang telah di revoke sebelumnya,

Nantinya akan memunculkan pesan: permission denied for relation pekerja yang Artinya user tersebut dilarang untuk mengakses tabel pekerja.

Kemudian kita juga bisa untuk mencabut salah satu hak akses pada perintah tertentu, misalnya SELECT. Dalam hal ini saya akan mengeksekusi pada user sofi_dwi_p. Struktur querynya adalah : 

revoke select on tabel pekerja from sofi_dwi_p;

 

Lakukan pengecekan dengan mengetikkan select * from nama pekerja

pesan: permission denied for relation pekerja menandakan bahwa user tersebut dilarang untuk mengakses tabel pekerja.

Memberi hak akses

Kita dapat membuka kembali hak akses yang telah di revoke tadi dengan menggunakan fungsi grant. Dalam hal ini saya akan memberikan kembali hak akses tabel pekerja terhadap user sofi_dwi_p dengan menggunakan struktur query sebagai berikut : 

grant all on table pekerja to sofi_dwi_p;

 

Setelah itu kembali lakukan pengecekan dengan mencoba memberikan perintah kembali terhadap tabel pekerja.

Nah, sekarang user sofi_dwi_p sudah dapat mengakses tabel peerja kembali.

Turunan (Inheritance)

Untuk mengaplikasikan fungsi turunan, disini saya akan membuat tabel baru dengan nama gaji yang merupakan turunan dari tabel pekerja, yang terdiri dari kolom gaji_kotor, pajak, dan gaji_bersih. Berikut Struktur penggunaan querynya : 

create table gaji (gaji_kotor int, pajak int, gaji_bersih int) inherits (pekerja);

Pengecekan dapat dilakukan melihat tabel gaji yaitu dengan mengetikkan \d gaji. Untuk selengkapnya lihat pada gambar berikut:

Berbagi :