Modul Praktikum Basis Data

Modul Praktikum Basis Data

Oleh:

Bambang Purnomosidi D. P., S.E., Ak., S.Kom., MMSI

SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTER AKAKOM YOGYAKARTA

MODUL I Pengenalan dan Lingkungan Kerja PostgreSQL I.1 I.1.1

I.1.2

Maksud dan Tujuan Maksud Membahas tentang pengertian basis data secara umum, pemahaman umum tentang PostgreSQL, serta dasar-dasar pengoperasian PostgreSQL. Tujuan 1. Mahasiswa memahami pengertian basis data secara umum dan peranannya. 2. Mahasiswa memahami kegunaan software PostgreSQL serta kemungkinan penggunaannya. 3. Mahasiswa memahami cara menjalankan server basis data PostgreSQL. 4. Mahasiswa memahami berbagai software yang bisa digunakan untuk mengakses database PostgreSQL. 5. Mahasiswa memahami cara mengkonfigurasi server basis data PostgreSQL. 6. Mahasiswa memahami cara menggunakan perintah baris psql untuk mengakses basis data serta mencari dan menggunakan berbagai perintah SQL maupun non-SQL.

I.2

Dasar Teori Suatu software basis data merupakan software yang digunakan untuk menyimpan dan memungkinkan data yang telah disimpan untuk diakses dengan perintah-perintah tertentu. Software basis data sering dikenal dengan sebutan DBMS (Database Management System). Perintah-perintah yang digunakan untuk mengelola basis data mempunyai standar yang disebut dengan SQL (Structured Query Language). Standar ini dibuat oleh suatu badan yang berwenang (ANSI) sehingga sering disebut juga dengan istilah ANSI SQL. Saat ini standar SQL yang diacu kebanyakan software adalah SQL92 dan SQL99. Pada umumnya, data yang tersimpan merupakan data relasional (data yang saling terhubung). Proses untuk merancang suatu basis data disebut dengan istilah normalisasi. Pada dasarnya, suatu basis data terdiri atas berbagai tabel yang saling terhubung. Tabel itu sendiri terdiri atas data rinci untuk suatu entitas dan terdiri atas row dan column (pada software non-SQL dan software basis data tertentu sering disebut record dan field). Berikut ini adalah gambaran secara singkat dari paparan tersebut:

Modul Praktikum Basis Data KA – hal 2 dari 32

Tabel 1

Relasi antar tabel

Tabel 2

Basis data Tabel itu sendiri jika digambarkan merupakan suatu baris serta kolom sebagai berikut:

no_mhs

nama

lahir

tgllahir

alamat

002345 Zaky

Yogyakarta 3­3­2000

Griya Purwa Asri H304

002346 Ahmad

Jakarta

21­12­1978

Kenari II/37 Salemba

002347 Aditya

Jakarta

25­08­1972

Gedongkuning JG IV/16

Tentang PostgreSQL PostgreSQL merupakan suatu software object-relational database management system (ORDBMS) berbasis pada POSTGRES, versi 4.2 yang dikembangkan di University of California at Berkeley Computer Science Department. Software ini merupakan software yang banyak digunakan dikalangan perusahaan, individu, sekolah, maupun organisasi-organisasi lainnya. PostgreSQL mempunyai kompatibilitas dengan SQL92 dan SQL99 serta berbagai fitur berikut ini: ➢ complex queries ➢ foreign keys ➢ triggers

Modul Praktikum Basis Data KA – hal 3 dari 32

views ➢ transactional integrity ➢ multiversion concurrency control PostgreSQL didistribusikan dalam dua kategori software yaitu server dan client. Server diperlukan untuk menyediakan layanan basis data sedangkan client diperlukan untuk mengakses basis data. Untuk mengakses basis data anda harus mengaktifkan terlebih dahulu server yang diperlukan. ➢

Menjalankan server basis data PostgreSQL Ada beberapa cara untuk menjalankan server basis data PostgreQL. Sebelum menjalankan server ini, anda harus memeriksa proses di komputer anda untuk memastikan apakah server telah dijalankan atau belum. Untuk memeriksa, gunakan perintah ps aux dari shell. Jika terdapat baris-baris ini, berarti server PostgreSQL telah siap (angka pada kolom pertama mungkin lain dari angka yang ada pada komputer anda): ...... ......  1698 ?    S   0:00 /usr/bin/postmaster ­p 5432 ­D /var/lib/pgsql/data  1702 ?    S   0:00 postgres: stats buffer process  1703 ?    S   0:00 postgres: stats collector process ...... ......

Untuk menjalankan server PostgreSQL, ada beberapa cara: 1. Sebagai root, jalankan script inisialisasi sistem untuk PostgreSQL. Cara ini biasanya merupakan emulasi perintah postmaster dan pg_ctl untuk memudahkan pengaktifan server. [root@localhost ~]# /etc/init.d/postgresql start Starting postgresql service:                               [  OK  ] [root@localhost ~]#

2. Menggunakan perintah postmaster: nohup postmaster >logfile 2>&1 
3. Menggunakan pg_ctl: pg_ctl start

Variasi untuk masing-masing perintah (postmaster dan pg_ctl) bisa diperoleh pada manual dengan cara memberikan perintah man postmaster atau man pg_ctl pada shell. Modul Praktikum Basis Data KA – hal 4 dari 32

Software Client PostgreSQL telah menyertakan software client untuk mengakses basis data. Software tersebut bisa diakses melalui shell dan bukan merupakan suatu lingkungan grafis (GUI). Software tersebut adalah psql. Software ini yang akan digunakan pada praktikum. Jika ingin menggunakan GUI, bisa digunakan software-software berikut ini: 1. pgAdmin (http://www.pgadmin.org) 2. PgAccess (http://www.pgaccess.org) Konfigurasi Server Konfigurasi server PostgreSQL terletak pada direktori /var/lib/pgsql/data (pada sistem Fedora), jika anda menggunakan distribusi Linux lain atau varian UNIX lain, kemungkinan lokasinya berbeda. Meskipun demikian, isi dari direktori tersebut biasanya adalah sebagai berikut: [root@localhost data]# pwd /var/lib/pgsql/data [root@localhost data]# ls ­la total 52 drwx­­­­­­  6 postgres postgres 4096 Sep  5 16:35 . drwx­­­­­­  4 postgres postgres 4096 Jul 20 16:14 .. drwx­­­­­­  8 postgres postgres 4096 Sep  5 15:11 base drwx­­­­­­  2 postgres postgres 4096 Sep  5 16:35 global drwx­­­­­­  2 postgres postgres 4096 Jul 12 15:51 pg_clog ­rw­­­­­­­  1 postgres postgres 3487 Jul 21 10:21 pg_hba.conf ­rw­­­­­­­  1 postgres postgres 1441 Jul 12 15:51 pg_ident.conf ­rw­­­­­­­  1 postgres postgres    4 Jul 12 15:51 PG_VERSION drwx­­­­­­  2 postgres postgres 4096 Sep  2 10:44 pg_xlog ­rw­­­­­­­  1 postgres postgres 7819 Jul 21 10:19 postgresql.conf ­rw­­­­­­­  1 postgres postgres   59 Sep  5 16:35 postmaster.opts ­rw­­­­­­­  1 postgres postgres   45 Sep  5 16:35 postmaster.pid [root@localhost data]#

Konfigurasi terdapat pada file-file dengan ekstensi .conf. Berikut ini adalah penjelasan fungsi masing-masing: 1. pg_hba.conf: mengatur autentikasi client. Agar client bisa mengakses suatu basis data PostgreSQL, konfigurasi ini yang harus diatur. 2. pg_identd.conf: mengatur autentikasi client dengan metode ident. 3. postgresql.conf: mengatur konfigurasi software PostgreSQL. Pengenalan psql psql merupakan software yang dijalankan dari shell untuk mengakses basis data PostgreSQL. Jika belum memahami cara menggunakannya, anda bisa menggunakan parameter –help berikut ini: Modul Praktikum Basis Data KA – hal 5 dari 32

[bpdp@localhost pics]$ psql ­­help This is psql 7.4.3, the PostgreSQL interactive terminal.   Usage:   psql [OPTIONS]... [DBNAME [USERNAME]]   General options:   ­d DBNAME       specify database name to connect to (default: "bpdp")   ­c COMMAND      run only single command (SQL or internal) and exit   ­f FILENAME     execute commands from file, then exit   ­l              list available databases, then exit   ­v NAME=VALUE   set psql variable NAME to VALUE   ­X              do not read startup file (~/.psqlrc)   ­­help          show this help, then exit   ­­version       output version information, then exit   Input and output options:   ­a              echo all input from script   ­e              echo commands sent to server   ­E              display queries that internal commands generate   ­q              run quietly (no messages, only query output)   ­o FILENAME     send query results to file (or |pipe)   ­n              disable enhanced command line editing (readline)   ­s              single­step mode (confirm each query)   ­S              single­line mode (end of line terminates SQL command)   Output format options:   ­A              unaligned table output mode (­P format=unaligned)   ­H              HTML table output mode (­P format=html)   ­t              print rows only (­P tuples_only)    ­T TEXT                 set HTML table tag attributes (width, border) (­P tableattr=)   ­x              turn on expanded table output (­P expanded)   ­P VAR[=ARG]    set printing option VAR to ARG (see \pset command)   ­F STRING       set field separator (default: "|") (­P fieldsep=)    ­R   STRING               set   record   separator   (default:   newline)   (­P recordsep=)   Connection options:    ­h HOSTNAME         database server host or socket directory (default: "local sock et")   ­p PORT         database server port (default: "5432")   ­U NAME         database user name (default: "bpdp")   ­W              prompt for password (should happen automatically)   For more information, type "\?" (for internal commands) or "\help" (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation.   Report bugs to . [bpdp@localhost pics]$

Modul Praktikum Basis Data KA – hal 6 dari 32

Secara singkat, psql bisa dijalankan untuk mengakses basis data dengan cara berikut ini: [bpdp@localhost ~]$ psql mahasiswa Password: Welcome to psql 7.4.3, the PostgreSQL interactive terminal.   Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help on internal slash commands        \g or terminate with semicolon to execute query        \q to quit   mahasiswa=#

Keterangan: mahasiswa adalah nama basis data yang akan diakses. I.3

Praktik

1. Periksalah apakah proses untuk sever basis data PostgreSQL telah berjalan atau belum. 2. Jika belum, jalankan server PostgreSQL. Jika sudah berjalan, matikan proses tersebut kemudian jalankan lagi. 3. Masuklah sebagai root kemudian dari user root, ganti user menjadi postgres. Setelah menjadi user postgres, masuklah ke basis data template1. Berikut ini adalah cara-caranya: [bpdp@localhost ~]$ su ­ Password: [root@localhost ~]# su ­ postgres ­bash­3.00$ psql template1 Welcome to psql 7.4.3, the PostgreSQL interactive terminal.   Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help on internal slash commands        \g or terminate with semicolon to execute query        \q to quit   template1=#

Dari posisi tersebut, gunakan perintah backslash (“\”) untuk mencari pertolongan tentang bagaimana cara melihat basis data apa saja yang ada pada sistem. 4. Edit file pg_hba.conf pada lokasi yang telah dijelaskan di depan, gantilah baris dengan isi: Modul Praktikum Basis Data KA – hal 7 dari 32

local  all    all             ident   sameuser

Menjadi: local    all         all         password host    all     all     127.0.0.1       255.255.255.255 password

Setelah itu, restart proses server PostgreSQL kemudian cobalah menjalankan perintah pada nomor 3 diatas. Apa yang terjadi? apa kesimpulan anda? 5. Kembalikan isi dari file pg_hba ke kondisi semula.

Modul Praktikum Basis Data KA – hal 8 dari 32

MODUL II Membuat dan Mengelola Basis Data II.1 Maksud dan Tujuan II.1.1 Maksud Modul ini membahas tentang cara membuat dan mengelola suatu basis data PostgreSQL. II.1.2 Tujuan 1. Mahasiswa memahami bagaimana cara membuat suatu basis data. 2. Mahasiswa memahami berbagai perintah yang berkaitan dengan pengelolaan basis data di PostgreSQL. II.2 Dasar Teori Membuat Basis Data Basis data bisa dibuat melalui psql atau melaui shell secara langsung. Untuk membuat suatu basis data, user yang bersangkutan harus mempunyai hak akses untuk membuat basis data. Pembahasan tentang user akan diberikan pada modul tersendiri. Membuat basis data melalui psql bisa dilakukan dengan memberikan perintah berikut ini: CREATE DATABASE name     [ [ WITH ] [ OWNER [=] dbowner ]            [ LOCATION [=] 'dbpath' ]            [ TEMPLATE [=] template ]            [ ENCODING [=] encoding ] ]

Untuk membuat basis data langsung melalui shell diperlukan script yang telah disertakan pada distribusi PostgreSQL. Perintah yang harus diberikan adalah: ­bash­3.00$ createdb ­­help createdb creates a PostgreSQL database.   Usage:   createdb [OPTION]... [DBNAME] [DESCRIPTION]   Options:   ­D, ­­location=PATH       alternative place to store the database   ­E, ­­encoding=ENCODING   encoding for the database   ­O, ­­owner=OWNER         database user to own the new database   ­T, ­­template=TEMPLATE   template database to copy   ­e, ­­echo                show the commands being sent to the server   ­q, ­­quiet               don't write any messages Modul Praktikum Basis Data KA – hal 9 dari 32

  ­­help                    show this help, then exit   ­­version                 output version information, then exit   Connection options:   ­h, ­­host=HOSTNAME       database server host or socket directory   ­p, ­­port=PORT           database server port   ­U, ­­username=USERNAME   user name to connect as   ­W, ­­password            prompt for password   By   default,   a   database   with   the   same   name   as   the   current   user   is created.   Report bugs to . ­bash­3.00$

Menghapus Basis Data Menghapus basis data juga bisa dilakukan melalui psql atau langsung melalui shell. Untuk menghapus dari psql, perintahnya adalah sebagai beriku: DROP DATABASE name

Untuk menghapus basis data dari shell, perintah yang digunakan adalah: ­bash­3.00$ dropdb ­­help dropdb removes a PostgreSQL database.   Usage:   dropdb [OPTION]... DBNAME   Options:   ­e, ­­echo                show the commands being sent to the server   ­i, ­­interactive         prompt before deleting anything   ­q, ­­quiet               don't write any messages   ­h, ­­host=HOSTNAME       database server host or socket directory   ­p, ­­port=PORT           database server port   ­U, ­­username=USERNAME   user name to connect as   ­W, ­­password            prompt for password   ­­help                    show this help, then exit   ­­version                 output version information, then exit   Report bugs to . ­bash­3.00$

Pemeliharaan Basis Data Pengelolaan terhadap suatu basis data meliputi berbagai aktivitas yang digunakan untuk memastikan bahwa suatu basis data berada pada kondisi yang baik. Ada beberapa aktivitas yang bisa dilakukan oleh DBA (Database Administrator) berikut ini. 1. VACUUM. Perintah ini digunakan untuk me-recover tempat di media

Modul Praktikum Basis Data KA – hal 10 dari 32

penyimpan yang ditempati oleh baris tabel yang telah dihapus, memperbaharui data statistik, serta menghindari kemungkinan kehilangan data. 2. REINDEX. Perintah ini digunakan untuk mengindeks ulang basis data atau tabel tertentu agar pengaksesan bisa dilakukan dengan lebih cepat. 3. pg_dump, merupakan perintah dari shell untuk membuat backup berupa file teks yang berisi perintah SQL pada PostgreSQL untuk membuat dan mengisikan basis data. 4. psql bisa digunakan untuk me-restore perintah SQL yang telah dihasilkan dari pg_dump. II.3 Praktik 1. Sebagai user postgres, buatlah basis data bernama akademik dengan perintah berikut ini: ­bash­3.00$ createdb akademik CREATE DATABASE ­bash­3.00$

2. Hapus kembali basis data tersebut dengan perintah: ­bash­3.00$ dropdb akademik DROP DATABASE ­bash­3.00$

3. 4. 5. 6.

Dari psql, buatlah basis data akademik. Dari psql, hapuslah basis data akademik. Buat lagi basis data akademik dengan salah satu cara diatas. Gunakan perintah pg_dump dari shell untuk mengambil isi dari basis data akademik. Perintah yang digunakan adalah: ­bash­3.00$ pg_dump akademik ­­ ­­ PostgreSQL database dump ­­   SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false;   SET SESSION AUTHORIZATION 'postgres';   ­­ ­­ TOC entry 4 (OID 2200) ­­ Name: public; Type: ACL; Schema: ­; Owner: postgres ­­  

Modul Praktikum Basis Data KA – hal 11 dari 32

REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC;     ­­ ­­ TOC entry 3 (OID 2200) ­­ Name: SCHEMA public; Type: COMMENT; Schema: ­; Owner: postgres ­­   COMMENT ON SCHEMA public IS 'Standard public schema';     ­bash­3.00$

7. Simpanlah hasil di atas pada suatu file teks dengan perintah berikut ini: ­bash­3.00$ pg_dump akademik > sql­akademik ­bash­3.00$ ­bash­3.00$ cat sql­akademik ­­ ­­ PostgreSQL database dump ­­   SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false;   SET SESSION AUTHORIZATION 'postgres';   ­­ ­­ TOC entry 4 (OID 2200) ­­ Name: public; Type: ACL; Schema: ­; Owner: postgres ­­   REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC;     ­­ ­­ TOC entry 3 (OID 2200) ­­ Name: SCHEMA public; Type: COMMENT; Schema: ­; Owner: postgres ­­   COMMENT ON SCHEMA public IS 'Standard public schema';     ­bash­3.00$

8. Buatlah basis data baru bernama 'mahasiswa' kemudian isikan hasil dari pg_dump di atas ke basis data mahasiswa dengan perintah berikut ini: ­bash­3.00$ psql mahasiswa < sql­akademik SET Modul Praktikum Basis Data KA – hal 12 dari 32

SET SET REVOKE GRANT COMMENT ­bash­3.00$

Modul Praktikum Basis Data KA – hal 13 dari 32

MODUL III Tabel, Query, View, dan Join III.1 Maksud dan Tujuan III.1.1 Maksud Modul ini membahas tentang cara membuat tabel yang merupakan bagian inti untuk menyimpan data pada suatu basis data. Selain itu, modul ini juga membahas tentang Query untuk mengambil data dari (satu atau lebih) tabel, view untuk mempermudah pengaksesan tabel untuk sesi berikutnya, serta join untuk mengambil data dari berbagai tabel sekaligus. III.1.2 Tujuan 1. Mahasiswa memahami pengertian dari tabel, query, view, serta join. 2. Mahasiswa memahami cara membuat tabel serta tipe-tipe data yang bisa ditampung pada suatu tabel. 3. Mahasiswa memahami cara mengisikan data ke suatu tabel. 4. Mahasiswa memahami cara membuat query untuk mengambil data dari tabel. 5. Mahasiswa memahami cara membuat view. 6. Mahasiswa memahami cara melakukan join untuk lebih dari satu tabel. III.2 Dasar Teori Tabel Tabel merupakan bagian dari basis data dan digunakan untuk menyimpan data dalam bentuk kolom (column / field) dan baris (row/record). Tabel ini bisa dibuat pada basis data langsung, atau bisa juga menempati suatu schema. Schema merupakan suatu pengelompokan logis dari tabel-tabel pada suatu basis data. Perintah SQL untuk membuat tabel: CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (    { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]     | table_constraint     | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE | PRIMARY KEY |   CHECK (expression) | Modul Praktikum Basis Data KA – hal 14 dari 32

   REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE      [ ON DELETE action ] [ ON UPDATE action ] } [   DEFERRABLE   |   NOT   DEFERRABLE   ]   [   INITIALLY   DEFERRED   |   INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) |   PRIMARY KEY ( column_name [, ... ] ) |   CHECK ( expression ) |    FOREIGN   KEY   (   column_name   [,   ...   ]   )   REFERENCES   reftable [ ( refcolumn [, ... ] )      [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE a [   DEFERRABLE   |   NOT   DEFERRABLE   ]   [   INITIALLY   DEFERRED   |   INITIALLY IMMEDIATE ]

Contoh: CREATE TABLE films (     code        char(5) CONSTRAINT firstkey PRIMARY KEY,     title       varchar(40) NOT NULL,     did         integer NOT NULL,     date_prod   date,     kind        varchar(10),     len         interval hour to minute );

Keterangan: 1. CONSTRAINT merupakan bagian dari sintaks yang digunakan untuk mendefinsikan batasan dari kolom yang bersangkutan. 2. PRIMARY KEY merupakan perintah untuk menetapkan kolom yang bersangkutan sebagai kunci primer. 3. NOT NULL merupakan salah satu CONSTRAINT yang digunakan untuk membuat isi dari kolom yang bersangkutan tidak boleh nol/kosong. 4. Tipe data yang bisa digunakan untuk kolom adalah: Tipe data

Alias

Keteterangan

bigint

int8

signed eight-byte integer

bigserial

serial8

autoincrementing eight-byte

bit

fixed-length bit string

bit varying(n)

varbit(n)

variable-length bit string

boolean

bool

logical Boolean (true/false)

Modul Praktikum Basis Data KA – hal 15 dari 32

box

rectangular box in the plane

bytea

binary data

character varying(n)

varchar(n)

variable-length character string

character(n)

char(n)

fixed-length character string

cidr

IPv4 or IPv6 network address

circle

circle in the plane

date

calendar date (year, month, day)

double precision

float8

inet integer

double precision floating-point number IPv4 or IPv6 host address

int, int4

signed four-byte integer

interval(p)

time span

line

infinite line in the plane (not fully implemented)

lseg

line segment in the plane

macaddr

MAC address macaddr

money

currency amount

numeric [ (p, s) ]

decimal [ (p, s) ]

exact numeric with selectable precision

path

open and closed geometric path in the plane

point

geometric point in the plane

polygon

closed geometric path in the plane

real

float4

single precision floating-point number

smallint

int2

signed two-byte integer

serial

serial4

autoincrementing integer

text

Modul Praktikum Basis Data KA – hal 16 dari 32

four-byte

variable-length character string

time [ (p) ] [ without time zone ]

time of day

time [ (p) ] with time timetz zone

time of day, including time zone

timestamp [ (p) ] without timestamp time zone

date and time

timestamp [ (p) ] [ with timestamptz time zone ]

date and time, including time zone

Mengisikan Data (INSERT) Perintah INSERT digunakan untuk mengisikan data ke dalam suatu tabel pada suatu basis data. Perintah yang digunakan adalah sebagai berikut: INSERT INTO table [ ( column [, ...] ) ]     { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

Query Query merupakan kategori perintah yang digunakan untuk meminta layanan dari server basis data. Perintah yang akan kita pelajari sekarang adalah SELECT untuk mengambil data dari basis data. Bentuk perintah SELECT adalah sebagai berikut: SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]     * | expression [ AS output_name ] [, ...]     [ FROM from_item [, ...] ]     [ WHERE condition ]     [ GROUP BY expression [, ...] ]     [ HAVING condition [, ...] ]     [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]     [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]     [ LIMIT { count | ALL } ]     [ OFFSET start ]     [ FOR UPDATE [ OF table_name [, ...] ] ]

View View digunakan menyimpan hasil suatu query ke dalam suatu nama tertentu. Biasanya hal ini diperlukan jika kita ingin menyimpan suatu query yang (cukup) kompleks. Untuk mengakses suatu view, kita cukup memanggil view tersebut dengan perintah SELECT. Perintah untuk membuat view adalah sebagai berikut: CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

Modul Praktikum Basis Data KA – hal 17 dari 32

Untuk menghapus view, gunakan perintah DROP VIEW namaview Join Join adalah suatu bagian dari perintah SELECT yang digunakan untuk menggabungkan lebih dari satu tabel ke dalam suatu hasil query. Ada beberapa jenis join yang bisa digunakan, antara lain: 1. 2. 3. 4. 5.

[ INNER ] JOIN LEFT [ OUTER ] JOIN RIGHT [ OUTER ] JOIN FULL [ OUTER ] JOIN CROSS JOIN

III.3 Praktik 1. Buatlah basis data praktikum dan buatlah dua tabel berikut ini pada basis data tersebut: CREATE TABLE films (     code        char(5) CONSTRAINT firstkey PRIMARY KEY,     title       varchar(40) NOT NULL,     did         integer NOT NULL,     date_prod   date,     kind        varchar(10),     len         interval hour to minute ); CREATE TABLE distributors (      did    integer PRIMARY KEY DEFAULT nextval('serial'),      name   varchar(40) NOT NULL CHECK (name <> '') );

2, Isikan data berikut ini: INSERT INTO films VALUES     ('UA502', 'Bananas', 105, '1971­07­13', 'Comedy', '82 minutes'); INSERT INTO films (code, title, did, date_prod, kind, len)        VALUES   ('T_601',   'Yojimbo',   106,   '1961­06­16',   'Drama',   '100 minutes');

3. Isikan data untuk distributor, sesuai dengan isi data di atas. Nama distributor terserah anda. Isikan sebanyak 5 data distributor. 4. Buatlah query untuk menampilkan data film yang jenisnya hanya 'Comedy' saja!. 5. Buatlah hasil nomor 4 tersebut menjadi suatu view kemudian tampilkan hasilnya. Modul Praktikum Basis Data KA – hal 18 dari 32

6. Buatlah query untuk mengambil data distributor, diurutkan menurut nama distributor. 7. Tampilkan 3 data pertama dari distributor! 8. Kerjakan perintah SQL berikut dan jelaskan artinya: SELECT kind, sum(len) AS total FROM films GROUP BY kind;

9. Kerjakan perintah SQL untuk join berikut ini dan jelaskan artinya: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d INNER JOIN films f ON f.did = d.did 10. Kerjakan perintah SQL berikut ini dan jelaskan artinya: SELECT * FROM films WHERE films.title LIKE 'Y%';

Modul Praktikum Basis Data KA – hal 19 dari 32

MODUL IV Mengelola User IV.1 Maksud dan Tujuan IV.1.1 Maksud Membahas tentang bagaimana PostgreSQL sebagai suatu software server basis data memungkinkan pemakaian oleh lebih dari satu pemakai. Untuk keperluan tersebut, user perlu dikelola dengan baik. Modul ini membahas tentang bagaimana cara mengelola user pada PostgreSQL. IV.1.2 Tujuan 1. Mahasiswa memahami pengertian user pada PostgreSQL dan dapat membedakannya dengan user sistem operasi. 2. Mahasiswa memahami cara membuat user. 3. Mahasiswa memahami cara mengatur hak akses user IV.2

Dasar Teori Setiap obyek basis data (database object) selalu mempunyai user. User pada basis data tidak sama dengan user sistem operasi sehingga meskipun anda mempunyai user sistem operasi, anda tidak otomatis mempunyai hak akses terhadap basis data di PostgreSQL. User ini bisa dibuat oleh user lain yang mempunyai kewenangan (secara software). Pada saat membuat user, bisa sekaligus ditentukan apakah user yang akan dibuat tersebut mempunyai kewenangan untuk membuat user lain serta berbagai kewenangan lain (misalnya membuat basis data). Perintah untuk membuat user bisa dilakukan dari shell maupun dari psql. Membuat user dari shell PostgreSQL menyediakan perintah createuser untuk membuat user langsung dari shell. Berikut ini adalah sintaksis dari perintah tersebut: [bpdp@localhost postgresql]$ createuser ­­help createuser creates a new PostgreSQL user.   Usage:   createuser [OPTION]... [USERNAME]   Options:   ­a, ­­adduser             user can add new users   ­A, ­­no­adduser          user cannot add new users   ­d, ­­createdb            user can create new databases   ­D, ­­no­createdb         user cannot create databases   ­P, ­­pwprompt            assign a password to new user Modul Praktikum Basis Data KA – hal 20 dari 32

  ­E, ­­encrypted           encrypt stored password   ­N, ­­unencrypted         do no encrypt stored password   ­i, ­­sysid=SYSID         select sysid for new user   ­e, ­­echo                show the commands being sent to the server   ­q, ­­quiet               don't write any messages   ­­help                    show this help, then exit   ­­version                 output version information, then exit   Connection options:   ­h, ­­host=HOSTNAME       database server host or socket directory   ­p, ­­port=PORT           database server port    ­U,  ­­username=USERNAME      user  name  to  connect  as  (not  the  one  to create)   ­W, ­­password            prompt for password to connect   If one of ­a, ­A, ­d, ­D, and USERNAME is not specified, you will be prompted interactively.   Report bugs to . [bpdp@localhost postgresql]$

Berikut ini adalah contohnya: [bpdp@localhost postgresql]$ createuser Enter name of user to add: isikannim Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n Password: CREATE USER [bpdp@localhost postgresql]$

Membuat user dari psql Dari psql, user bisa dibuat dengan menggunakan perintah SQL CREATE USER. Berikut ini adalah cara penggunaan perintah tersebut: praktikum=# \help CREATE USER Command:     CREATE USER Description: define a new database user account Syntax: CREATE USER name [ [ WITH ] option [ ... ] ]   where option can be:             SYSID uid     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'     | CREATEDB | NOCREATEDB     | CREATEUSER | NOCREATEUSER     | IN GROUP groupname [, ...]     | VALID UNTIL 'abstime'   praktikum=#

Modul Praktikum Basis Data KA – hal 21 dari 32

Menghapus user Untuk menghapus user, bisa digunakan perintah dropuser (dari shell) atau DROP USER dari psql. Mengatur hak akses Ada dua perintah SQL untuk mengatur hak akses user: 1. GRANT. Digunakan untuk memberikan hak akses ke user. Sintaksis lengkap dari perintah ini adalah: praktikum=# \help GRANT Command:     GRANT Description: define access privileges Syntax: GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }     [,...] | ALL [ PRIVILEGES ] }     ON [ TABLE ] tablename [, ...]     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]   GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }     ON DATABASE dbname [, ...]     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]   GRANT { EXECUTE | ALL [ PRIVILEGES ] }     ON FUNCTION funcname ([type, ...]) [, ...]     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]   GRANT { USAGE | ALL [ PRIVILEGES ] }     ON LANGUAGE langname [, ...]     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]   GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }     ON SCHEMA schemaname [, ...]     TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]   praktikum=#

2. REVOKE. Digunakan untuk mencabut hak akses user. Sintaksis lengkapnya adalah sebagai berikut: praktikum=# \help REVOKE Command:     REVOKE Description: remove access privileges Syntax: Modul Praktikum Basis Data KA – hal 22 dari 32

REVOKE [ GRANT OPTION FOR ]       { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }     [,...] | ALL [ PRIVILEGES ] }     ON [ TABLE ] tablename [, ...]     FROM { username | GROUP groupname | PUBLIC } [, ...]     [ CASCADE | RESTRICT ]   REVOKE [ GRANT OPTION FOR ]     { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }     ON DATABASE dbname [, ...]     FROM { username | GROUP groupname | PUBLIC } [, ...]     [ CASCADE | RESTRICT ]   REVOKE [ GRANT OPTION FOR ]     { EXECUTE | ALL [ PRIVILEGES ] }     ON FUNCTION funcname ([type, ...]) [, ...]     FROM { username | GROUP groupname | PUBLIC } [, ...]     [ CASCADE | RESTRICT ]   REVOKE [ GRANT OPTION FOR ]     { USAGE | ALL [ PRIVILEGES ] }     ON LANGUAGE langname [, ...]     FROM { username | GROUP groupname | PUBLIC } [, ...]     [ CASCADE | RESTRICT ]   REVOKE [ GRANT OPTION FOR ]     { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }     ON SCHEMA schemaname [, ...]     FROM { username | GROUP groupname | PUBLIC } [, ...]     [ CASCADE | RESTRICT ]   praktikum=#

IV.3

Praktik

1. Sebagai user (sistem operasi) postgres, buatlah user bernama 'linuxoke' dengan akses untuk membuat basis data atau membuat user serta sertakan juga opsi untuk menetapkan password untuk user tersebut. 2. Buatlah hak akses penuh terhadap basis data praktikum untuk user 'linuxoke'. 3. Lihat kembali modul I, kerjakan perintah untuk mengubah autentikasi client pada praktik 4. Setelah itu restart PostgreSQL. 4. Buat user 'payah' tanpa hak akses untuk membuat basis data dan membuat user. Buat juga password untuk 'payah'. 5. Bagaimana caranya agar hak akses user 'payah' terhadap basis data praktikum hanya untuk melihat isi tabel distributors? Buktikan!

Modul Praktikum Basis Data KA – hal 23 dari 32

MODUL V Update dan Delete V.1 Maksud dan Tujuan V.1.1 Maksud Modul ini membahas tentang bagaimana cara memperbaharui / mengupdate data serta menghapus data pada suatu tabel. V.1.2 Tujuan 1. Mahasiswa memahami cara memperbaharui data pada suatu tabel. 2. Mahasiswa memahami cara menghapus data pada suatu tabel. V.2

Dasar Teori SQL (dan PostgreSQL) menyediakan perintah untuk memperbaharui data pada suatu tabel serta perintah untuk menghapus data pada suatu tabel (semua atau berdasarkan kondisi tertentu). Perintah-perintah tersebut adalah UPDATE dan DELETE. UPDATE Perintah UPDATE digunakan untuk memperbaharui data pada tabel. Berikut adalah sintaksis lengkapnya: praktikum=# \help UPDATE Command:     UPDATE Description: update rows of a table Syntax: UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]     [ FROM fromlist ]     [ WHERE condition ]   praktikum=#

DELETE Perintah DELETE digunakan untuk menghapus data pada tabel. Berikut ini adalah sintaksis lengkapnya: praktikum=# \help DELETE Command:     DELETE Description: delete rows of a table Syntax: DELETE FROM [ ONLY ] table [ WHERE condition ]   praktikum=#

Modul Praktikum Basis Data KA – hal 24 dari 32

V.3 Praktik 1. Buat cadangan dari database praktikum dengan perintah pg_dump. Simpan ke dalam suatu file. 2. Gantilah isi kolom len pada tabel films untuk film 'Yojimbo' menjadi 1 jam 35 menit. 3. Isikan data film 'Comedy” sebanyak 5 data lagi. 4. Isikan data film 'Drama' sebanyak 5 data lagi. 5. Hapus semua data film 'Drama'. 6. Kosongkan tabel films. 7. Kosongkan tabel distributors. 8. Hapus tabel films 9. Hapus tabel distributors. 10. Keluar dari psql kemudian restore data pada basis data praktikum dengan data hasil pg_dump pada praktik no 1 di atas.

Modul Praktikum Basis Data KA – hal 25 dari 32

MODUL VI Transactions VI.1 Maksud dan Tujuan VI.1.1 Maksud Modul ini membahas tentang transactions yang memungkinkan suatu operasi terhadap basis data dibatalkan atau dilaksanakan, tergantung pada suatu kondisi. VI.1.2 Tujuan 1. Mahasiswa memahami pengertian transactions. 2. Mahasiswa memahami cara menggunakan transactions serta aplikasinya. VI.2

Dasar Teori Transactions merupakan suatu mekanisme yang umum terdapat pada basis data SQL untuk menyimpan kondisi tertentu dari suatu basis data sebelum melakukan operasi-operasi terhadap basis data tersebut. Dengan demikian, pada suatu titik setelah itu, bisa dilakukan penulisan ke basis data atau pembatalan perintah-perintah yang dikerjakan setelah penyimpanan kondisi tersebut. Pada dasarnya, transactions ini digunakan pada saat kondisi masih belum pasti sehingga memungkinkan terjadinya atau tidak terjadinya suatu perubahan terhadap basis data berdasarkan kondisi yang akan datang. Perintah untuk menandai kondisi saat ini adalah BEGIN TRANSACTION, sdangkan perintah untuk membatalkan operasi adalah ROLLBACK dan perintah untuk melaksanakan operasi adalah COMMIT. Berikut ini adalah mekanisme dari transactions: BEGIN TRANSACTION; ....... ....... ....... ....... ....... ....... ....... ....... ....... .......

operasi terhadap basis data = hapus, insert, tampilkan, update

Modul Praktikum Basis Data KA – hal 26 dari 32

....... ....... ....... ....... COMMIT atau ROLLBACK; VI.3 Praktik 1. Kerjakan praktik pada modul V tanpa praktik nomor 1 dan nomor 10. Praktik nomor 1 diganti dengan BEGIN TRANSACTION, sedangkan praktik nomor 10 diganti dengan ROLLBACK. 2. Apa yang terjadi jika keluar dari psql tanpa perintah ROLLBACK atau COMMIT? Jelaskan!

Modul Praktikum Basis Data KA – hal 27 dari 32

MODUL VII Stored Procedure dan Trigger Menggunakan PL/pgSQL (2 sesi praktikum)

VII.1 Maksud dan Tujuan VII.1.1 Maksud Modul ini membahas tentang suatu bahasa prosedural SQL (PL/pgSQL) untuk keperluan pengaksesan data lebih lanjut. Penggunaan bahasa ini memungkinkan untuk melakukan operasi tertentu terhadap basis data sehingga kondisi pengaksesan data yang kompleks bisa ditangani. Selain itu, trigger juga bisa digunakan untuk mengaktifkan suatu function yang dibuat dengan PL/pgSQL sebelum atau sesudah UPDATE, DELETE, dan INSERT. VII.1.2 Tujuan 1. Mahasiswa memahami pengertian Stored Procedure 2. Mahasiswa memahami arti serta fungsi dari PL/pgSQL. 3. Mahasiswa memahami cara membuat fungsi PL/pgSQL dan menjalankan fungsi tersebut. 4. Mahasiswa memahami perintah-perintah dasar PL/pgSQL. 5. Mahasiswa memahami pengertian trigger 6. Mahasiswa memahami bagaimana cara membuat trigger serta menghubungkannya dengan fungsi yang telah dibuat dengan menggunakan PL/pgSQL. VII.2 Dasar Teori Stored Procedure adalah bahasa pemrograman yang bisa disimpan pada suatu basis data sehingga memungkinkan pengolahan terdapat basis data tersebut secara langsung untuk operasi-operasi yang lebih kompleks. Pada PostgreSQL, bahasa tersebut dikenal dengan nama PL/pgSQL. PL/pgSQL merupakan bahasa prosedural pada PostgreSQL yang dibuat untuk tujuan: 1. Untuk membuat fungsi (function) dan trigger procedure. 2. Menambahkan kendali pengendalian ke bahasa SQL. 3. Bisa digunakan untuk perhitungan kompleks. 4. Mewarisi semua type yang didefinisikan user, function dan operator. 5. Dapat dipercaya (trusted) oleh server (beberapa bahasa tertentu, misalnya PL/Python merupakan bahasa yang untrusted). Modul Praktikum Basis Data KA – hal 28 dari 32

6. Mudah untuk digunakankan. Secara default, PostgreSQL dan berbagai software DBMS lainnya menggunakan perintah SQL untuk definisi maupun pengolahan data. Meskipun bersifat lintas platform tetapi masalah muncul karena SQL hanya bisa dilakukan secara individual, akibatnya proses untuk akses dan pengolahan basis data sering dilakukan oleh bahasa lainnya. Hal ini menyebabkan turunnya kinerja karena memerlukan waktu dan sumber daya untuk pemrosesan antara compiler atau interpreter suatu bahasa pemrograman dengan server basis data. Untuk keperluan itulah, biasanya dibuat suatu bahasa. Pada PostgeSQL dikenal dengan PL/pgSQL. DBMS lainnya seperti Oracle juga mempunyai fasilitas seperti itu yaitu Oracle PL/SQL. Definisi dari PL/pgSQL pada PostgreSQL adalah sebagai berikut (bisa disimpan pada suatu file teks, atau langsung dituliskan pada psql): [ <> ] [ DECLARE declarations ] BEGIN      statements END;

Untuk lebih mempermudah, biasanya bahasa ini dituliskan pada suatu file teks kemudian pada psql dipanggil dan dijalankan dengan cara: \i filename.sql

Agar PL/pgSQL ini bisa dijalankan pada suatu basis data PostgreSQL, maka bahasa tersebut harus diinstall ke dalam basis data. Perintah yang digunakan untuk menginstall tersebut adalah createlang dan dijalankan dari shell. Berikut ini adalah sintaksis lengkapnya: [bpdp@localhost postgresql]$ createlang ­­help createlang installs a procedural language into a PostgreSQL database.   Usage:   createlang [OPTION]... LANGNAME [DBNAME]   Options:   ­d, ­­dbname=DBNAME       database to install language in   ­e, ­­echo                show the commands being sent to the server   ­l, ­­list                show a list of currently installed languages   ­L, ­­pglib=DIRECTORY     find language interpreter file in DIRECTORY   ­h, ­­host=HOSTNAME       database server host or socket directory   ­p, ­­port=PORT           database server port   ­U, ­­username=USERNAME   user name to connect as   ­W, ­­password            prompt for password Modul Praktikum Basis Data KA – hal 29 dari 32

  ­­help                    show this help, then exit   ­­version                 output version information, then exit   Report bugs to . [bpdp@localhost postgresql]$ 

Setelah menuliskan perintah PL/pgSQL. kita bisa menjalankan perintah tersebut menggunakan SELECT. Perintah-perintah dasar PL/pgSQL 1. Deklarasi variabel: name   [   CONSTANT   ]   type   [   NOT   NULL   ]   [   {   DEFAULT   |   :=   } expression ];

2. ALIAS, bisa ditetapkan pada DECLARE untuk membuat nama lain dari suatu parameter. Contoh bisa dilihat pada praktik. 3. Seleksi kondisi: IF ... THEN IF ... THEN ... ELSE IF ... THEN ... ELSE IF IF ... THEN ... ELSIF ... THEN ... ELSE

4. Loop: digunakan untuk perulangan. Sintaksis lengkapnya adalah sebagai berikut: [<>] LOOP      statements END LOOP;

Pembahasan tentang PL/pgSQL merupakan pembahasan yang cukup kompleks, terutama berkaitan dengan sintaksis dari bahasa tersebut. Sintaksis lengkap bisa diperoleh pada manual PostgreSQL. Trigger Trigger digunakan untuk menangani suatu kejadian pada basis data, meliputi kejadian sebelum maupun sesudah INSERT, UPDATE, atau DELETE. Fasilitas ini merupakan standar dari SQL yang berfungsi untuk mempermudah pemeriksaan menjelang dilakukan INSERT, UPDATE, atau DELETE serta menentukan tindakan apa yang akan dilakukan berdasarkan hasil pemeriksaan tertentu. Sebagai contoh, kita bisa mendefinisikan trigger untuk memeriksa isi suatu data sebelum diisikan. Perintah untuk membuat trigger ini adalah CREATE TRIGGER. Sintaksis Modul Praktikum Basis Data KA – hal 30 dari 32

lengkapnya adalah sebagai berikut: praktikum=# \help CREATE TRIGGER Command:     CREATE TRIGGER Description: define a new trigger Syntax: CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }     ON table [ FOR [ EACH ] { ROW | STATEMENT } ]     EXECUTE PROCEDURE funcname ( arguments )   praktikum=#

VII.3 Praktik 1. Install PL/pgSQL pada basis data praktikum. Perintah yang digunakan adalah: createlang   ­U   linuxoke   plpgsql   praktikum. Ingat, anda harus menggunakan user 'linuxoke'. 2. Buatlah function untuk menjumlahkan tiga angka berikut ini: [bpdp@localhost postgresql]$ psql praktikum Password: Welcome to psql 7.4.2, the PostgreSQL interactive terminal.   Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help on internal slash commands        \g or terminate with semicolon to execute query        \q to quit   praktikum=# CREATE FUNCTION add_three_values(anyelement, anyelement, anyelement) praktikum­# RETURNS anyelement AS ' praktikum'# DECLARE praktikum'#      result ALIAS FOR $0; praktikum'#      first ALIAS FOR $1; praktikum'#      second ALIAS FOR $2; praktikum'#      third ALIAS FOR $3; praktikum'# BEGIN praktikum'#      result := first + second + third; praktikum'#      RETURN result; praktikum'# END; praktikum'# ' LANGUAGE plpgsql; CREATE FUNCTION praktikum=#

2. Jalankan function tersebut dengan cara: praktikum=# SELECT * from add_three_values(1,2,3); add_three_values 6 Modul Praktikum Basis Data KA – hal 31 dari 32

(1 row) praktikum=#

3.

Buatlah tabel berikut ini pada basis data praktikum: CREATE TABLE emp (     empname text,     salary integer,     last_date timestamp,     last_user text );

4. Buatlah function berikut ini pada basis data praktikum: CREATE FUNCTION emp_stamp() RETURNS trigger AS '     BEGIN         ­­ Check that empname and salary are given         IF NEW.empname IS NULL THEN             RAISE EXCEPTION "empname cannot be null";         END IF;         IF NEW.salary IS NULL THEN             RAISE EXCEPTION "% cannot have null salary", NEW.empname;         END IF;          ­­ Who works for us when she must pay for it?          IF NEW.salary < 0 THEN              RAISE EXCEPTION "% cannot have a negative salary", NEW.empname;          END IF;          ­­ Remember who changed the payroll when          NEW.last_date := "now";          NEW.last_user := current_user;          RETURN NEW;     END; ' LANGUAGE plpgsql;

5. Buatlah trigger berikut ini: CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp     FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

6. Cobalah bereksperimen dengan tabel emp tersebut, isikan sembarang data, data dengan nilai salary negatif, data dengan nilai empname kosong, dan lainlain. Apa kesimpulan anda? 7. Jelaskan maksud dari function emp_stamp diatas!

Modul Praktikum Basis Data KA – hal 32 dari 32