Download - Basis Data _postgresql
-
Modul PraktikumBasis Data
Oleh:
Bambang Purnomosidi D. P., S.E., Ak., S.Kom., MMSI
SEKOLAH TINGGI MANAJEMEN INFORMATIKA DAN KOMPUTERAKAKOM
YOGYAKARTA
-
MODUL IPengenalan dan Lingkungan Kerja PostgreSQL
I.1 Maksud dan TujuanI.1.1 Maksud
Membahas tentang pengertian basis data secara umum, pemahamanumum tentang PostgreSQL, serta dasar-dasar pengoperasianPostgreSQL.
I.1.2 Tujuan1. 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 berbagaiperintah SQL maupun non-SQL.
I.2 Dasar TeoriSuatu software basis data merupakan software yang digunakan untuk
menyimpan dan memungkinkan data yang telah disimpan untuk diakses denganperintah-perintah tertentu. Software basis data sering dikenal dengan sebutanDBMS (Database Management System). Perintah-perintah yang digunakanuntuk mengelola basis data mempunyai standar yang disebut dengan SQL(Structured Query Language). Standar ini dibuat oleh suatu badan yangberwenang (ANSI) sehingga sering disebut juga dengan istilah ANSI SQL. Saatini standar SQL yang diacu kebanyakan software adalah SQL92 dan SQL99.
Pada umumnya, data yang tersimpan merupakan data relasional (datayang saling terhubung). Proses untuk merancang suatu basis data disebutdengan istilah normalisasi. Pada dasarnya, suatu basis data terdiri atas berbagaitabel yang saling terhubung. Tabel itu sendiri terdiri atas data rinci untuk suatuentitas dan terdiri atas row dan column (pada software non-SQL dan softwarebasis data tertentu sering disebut record dan field). Berikut ini adalah gambaransecara singkat dari paparan tersebut:
Modul Praktikum Basis Data KA hal 2 dari 32
-
Tabel itu sendiri jika digambarkan merupakan suatu baris serta kolomsebagai berikut:
no_mhs nama lahir tgllahir alamat
002345 Zaky Yogyakarta 332000 GriyaPurwaAsriH304
002346 Ahmad Jakarta 21121978 KenariII/37Salemba
002347 Aditya Jakarta 25081972 GedongkuningJGIV/16
Tentang PostgreSQLPostgreSQL merupakan suatu software object-relational database
management system (ORDBMS) berbasis pada POSTGRES, versi 4.2 yangdikembangkan di University of California at Berkeley Computer ScienceDepartment. Software ini merupakan software yang banyak digunakandikalangan perusahaan, individu, sekolah, maupun organisasi-organisasilainnya. PostgreSQL mempunyai kompatibilitas dengan SQL92 dan SQL99 sertaberbagai fitur berikut ini: complex queries foreign keys triggers
Modul Praktikum Basis Data KA hal 3 dari 32
RelasiantartabelTabel1Tabel2
Basisdata
-
views transactional integrity multiversion concurrency control
PostgreSQL didistribusikan dalam dua kategori software yaitu server danclient. Server diperlukan untuk menyediakan layanan basis data sedangkanclient diperlukan untuk mengakses basis data. Untuk mengakses basis dataanda harus mengaktifkan terlebih dahulu server yang diperlukan.
Menjalankan server basis data PostgreSQLAda beberapa cara untuk menjalankan server basis data PostgreQL.
Sebelum menjalankan server ini, anda harus memeriksa proses di komputeranda untuk memastikan apakah server telah dijalankan atau belum. Untukmemeriksa, gunakan perintah ps aux dari shell. Jika terdapat baris-baris ini,berarti server PostgreSQL telah siap (angka pada kolom pertama mungkin laindari angka yang ada pada komputer anda):......
......
1698?S0:00/usr/bin/postmasterp5432D/var/lib/pgsql/data1702?S0:00postgres:statsbufferprocess1703?S0:00postgres:statscollectorprocess......
......
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 untukmemudahkan pengaktifan server.
[root@localhost~]#/etc/init.d/postgresqlstartStartingpostgresqlservice:[OK][root@localhost~]#
2. Menggunakan perintah postmaster:
nohuppostmaster>logfile2>&1
-
Software Client PostgreSQL telah menyertakan software client untuk mengakses basis
data. Software tersebut bisa diakses melalui shell dan bukan merupakan suatulingkungan grafis (GUI). Software tersebut adalah psql. Software ini yang akandigunakan pada praktikum.
Jika ingin menggunakan GUI, bisa digunakan software-software berikutini: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 varianUNIX lain, kemungkinan lokasinya berbeda. Meskipun demikian, isi dari direktoritersebut biasanya adalah sebagai berikut:
[root@localhostdata]#pwd/var/lib/pgsql/data[root@localhostdata]#lslatotal52drwx6postgrespostgres4096Sep516:35.drwx4postgrespostgres4096Jul2016:14..drwx8postgrespostgres4096Sep515:11basedrwx2postgrespostgres4096Sep516:35globaldrwx2postgrespostgres4096Jul1215:51pg_clogrw1postgrespostgres3487Jul2110:21pg_hba.confrw1postgrespostgres1441Jul1215:51pg_ident.confrw1postgrespostgres4Jul1215:51PG_VERSIONdrwx2postgrespostgres4096Sep210:44pg_xlogrw1postgrespostgres7819Jul2110:19postgresql.confrw1postgrespostgres59Sep516:35postmaster.optsrw1postgrespostgres45Sep516:35postmaster.pid[root@localhostdata]#
Konfigurasi terdapat pada file-file dengan ekstensi .conf. Berikut ini adalahpenjelasan 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 psqlpsql merupakan software yang dijalankan dari shell untuk mengakses
basis data PostgreSQL. Jika belum memahami cara menggunakannya, andabisa menggunakan parameter help berikut ini:
Modul Praktikum Basis Data KA hal 5 dari 32
-
[bpdp@localhostpics]$psqlhelpThisispsql7.4.3,thePostgreSQLinteractiveterminal.
Usage:psql[OPTIONS]...[DBNAME[USERNAME]]
Generaloptions:dDBNAMEspecifydatabasenametoconnectto(default:"bpdp")cCOMMANDrunonlysinglecommand(SQLorinternal)andexitfFILENAMEexecutecommandsfromfile,thenexitllistavailabledatabases,thenexitvNAME=VALUEsetpsqlvariableNAMEtoVALUEXdonotreadstartupfile(~/.psqlrc)helpshowthishelp,thenexitversionoutputversioninformation,thenexit
Inputandoutputoptions:aechoallinputfromscripteechocommandssenttoserverEdisplayqueriesthatinternalcommandsgenerateqrunquietly(nomessages,onlyqueryoutput)oFILENAMEsendqueryresultstofile(or|pipe)ndisableenhancedcommandlineediting(readline)ssinglestepmode(confirmeachquery)Ssinglelinemode(endoflineterminatesSQLcommand)
Outputformatoptions:Aunalignedtableoutputmode(Pformat=unaligned)HHTMLtableoutputmode(Pformat=html)tprintrowsonly(Ptuples_only) TTEXTsetHTMLtabletagattributes(width,border)(Ptableattr=)xturnonexpandedtableoutput(Pexpanded)PVAR[=ARG]setprintingoptionVARtoARG(see\psetcommand)FSTRINGsetfieldseparator(default:"|")(Pfieldsep=) R STRING set record separator (default: newline) (Precordsep=)
Connectionoptions: hHOSTNAMEdatabaseserverhostorsocketdirectory(default:"localsocket")pPORTdatabaseserverport(default:"5432")UNAMEdatabaseusername(default:"bpdp")Wpromptforpassword(shouldhappenautomatically)
Formoreinformation,type"\?"(forinternalcommands)or"\help"(forSQLcommands)fromwithinpsql,orconsultthepsqlsectioninthePostgreSQLdocumentation.
Reportbugsto.[bpdp@localhostpics]$
Modul Praktikum Basis Data KA hal 6 dari 32
-
Secara singkat, psql bisa dijalankan untuk mengakses basis data dengancara berikut ini:
[bpdp@localhost~]$psqlmahasiswaPassword:Welcometopsql7.4.3,thePostgreSQLinteractiveterminal.
Type:\copyrightfordistributionterms\hforhelpwithSQLcommands\?forhelponinternalslashcommands\gorterminatewithsemicolontoexecutequery\qtoquit
mahasiswa=#
Keterangan:mahasiswa adalah nama basis data yang akan diakses.
I.3 Praktik
1. Periksalah apakah proses untuk sever basis data PostgreSQL telah berjalanatau belum.
2. Jika belum, jalankan server PostgreSQL. Jika sudah berjalan, matikan prosestersebut 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 iniadalah cara-caranya:
[bpdp@localhost~]$suPassword:[root@localhost~]#supostgresbash3.00$psqltemplate1Welcometopsql7.4.3,thePostgreSQLinteractiveterminal.
Type:\copyrightfordistributionterms\hforhelpwithSQLcommands\?forhelponinternalslashcommands\gorterminatewithsemicolontoexecutequery\qtoquit
template1=#
Dari posisi tersebut, gunakan perintah backslash (\) untuk mencaripertolongan tentang bagaimana cara melihat basis data apa saja yang adapada sistem.
4. Edit file pg_hba.conf pada lokasi yang telah dijelaskan di depan, gantilah barisdengan isi:
Modul Praktikum Basis Data KA hal 7 dari 32
-
localallallidentsameuser
Menjadi:localallallpasswordhostallall127.0.0.1255.255.255.255password
Setelah itu, restart proses server PostgreSQL kemudian cobalah menjalankanperintah 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 IIMembuat dan Mengelola Basis Data
II.1 Maksud dan TujuanII.1.1 Maksud
Modul ini membahas tentang cara membuat dan mengelola suatu basisdata PostgreSQL.
II.1.2 Tujuan1. Mahasiswa memahami bagaimana cara membuat suatu basis data.2. Mahasiswa memahami berbagai perintah yang berkaitan dengan
pengelolaan basis data di PostgreSQL.
II.2 Dasar TeoriMembuat Basis Data
Basis data bisa dibuat melalui psql atau melaui shell secara langsung.Untuk membuat suatu basis data, user yang bersangkutan harus mempunyaihak akses untuk membuat basis data. Pembahasan tentang user akan diberikanpada modul tersendiri.
Membuat basis data melalui psql bisa dilakukan dengan memberikanperintah berikut ini:
CREATEDATABASEname[[WITH][OWNER[=]dbowner][LOCATION[=]'dbpath'][TEMPLATE[=]template][ENCODING[=]encoding]]
Untuk membuat basis data langsung melalui shell diperlukan script yangtelah disertakan pada distribusi PostgreSQL. Perintah yang harus diberikanadalah:
bash3.00$createdbhelpcreatedbcreatesaPostgreSQLdatabase.
Usage:createdb[OPTION]...[DBNAME][DESCRIPTION]
Options:D,location=PATHalternativeplacetostorethedatabaseE,encoding=ENCODINGencodingforthedatabaseO,owner=OWNERdatabaseusertoownthenewdatabaseT,template=TEMPLATEtemplatedatabasetocopye,echoshowthecommandsbeingsenttotheserverq,quietdon'twriteanymessages
Modul Praktikum Basis Data KA hal 9 dari 32
-
helpshowthishelp,thenexitversionoutputversioninformation,thenexit
Connectionoptions:h,host=HOSTNAMEdatabaseserverhostorsocketdirectoryp,port=PORTdatabaseserverportU,username=USERNAMEusernametoconnectasW,passwordpromptforpassword
By default, a database with the same name as the current user iscreated.
Reportbugsto.bash3.00$
Menghapus Basis DataMenghapus basis data juga bisa dilakukan melalui psql atau langsung
melalui shell. Untuk menghapus dari psql, perintahnya adalah sebagai beriku:
DROPDATABASEname
Untuk menghapus basis data dari shell, perintah yang digunakan adalah:
bash3.00$dropdbhelpdropdbremovesaPostgreSQLdatabase.
Usage:dropdb[OPTION]...DBNAME
Options:e,echoshowthecommandsbeingsenttotheserveri,interactivepromptbeforedeletinganythingq,quietdon'twriteanymessagesh,host=HOSTNAMEdatabaseserverhostorsocketdirectoryp,port=PORTdatabaseserverportU,username=USERNAMEusernametoconnectasW,passwordpromptforpasswordhelpshowthishelp,thenexitversionoutputversioninformation,thenexit
Reportbugsto.bash3.00$
Pemeliharaan Basis DataPengelolaan terhadap suatu basis data meliputi berbagai aktivitas yang
digunakan untuk memastikan bahwa suatu basis data berada pada kondisi yangbaik. Ada beberapa aktivitas yang bisa dilakukan oleh DBA (DatabaseAdministrator) 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 kehilangandata.
2. REINDEX. Perintah ini digunakan untuk mengindeks ulang basis data atautabel tertentu agar pengaksesan bisa dilakukan dengan lebih cepat.
3. pg_dump, merupakan perintah dari shell untuk membuat backup berupa fileteks yang berisi perintah SQL pada PostgreSQL untuk membuat danmengisikan basis data.
4. psql bisa digunakan untuk me-restore perintah SQL yang telah dihasilkan daripg_dump.
II.3 Praktik1. Sebagai user postgres, buatlah basis data bernama akademik dengan
perintah berikut ini:
bash3.00$createdbakademikCREATEDATABASEbash3.00$
2. Hapus kembali basis data tersebut dengan perintah:
bash3.00$dropdbakademikDROPDATABASEbash3.00$
3. Dari psql, buatlah basis data akademik.4. Dari psql, hapuslah basis data akademik.5. Buat lagi basis data akademik dengan salah satu cara diatas.6. Gunakan perintah pg_dump dari shell untuk mengambil isi dari basis data
akademik. Perintah yang digunakan adalah:
bash3.00$pg_dumpakademik
PostgreSQLdatabasedump
SETclient_encoding='SQL_ASCII';SETcheck_function_bodies=false;
SETSESSIONAUTHORIZATION'postgres';
TOCentry4(OID2200)Name:public;Type:ACL;Schema:;Owner:postgres
Modul Praktikum Basis Data KA hal 11 dari 32
-
REVOKEALLONSCHEMApublicFROMPUBLIC;GRANTALLONSCHEMApublicTOPUBLIC;
TOCentry3(OID2200)Name:SCHEMApublic;Type:COMMENT;Schema:;Owner:postgres
COMMENTONSCHEMApublicIS'Standardpublicschema';
bash3.00$
7. Simpanlah hasil di atas pada suatu file teks dengan perintah berikut ini:
bash3.00$pg_dumpakademik>sqlakademikbash3.00$bash3.00$catsqlakademik
PostgreSQLdatabasedump
SETclient_encoding='SQL_ASCII';SETcheck_function_bodies=false;
SETSESSIONAUTHORIZATION'postgres';
TOCentry4(OID2200)Name:public;Type:ACL;Schema:;Owner:postgres
REVOKEALLONSCHEMApublicFROMPUBLIC;GRANTALLONSCHEMApublicTOPUBLIC;
TOCentry3(OID2200)Name:SCHEMApublic;Type:COMMENT;Schema:;Owner:postgres
COMMENTONSCHEMApublicIS'Standardpublicschema';
bash3.00$
8. Buatlah basis data baru bernama 'mahasiswa' kemudian isikan hasil daripg_dump di atas ke basis data mahasiswa dengan perintah berikut ini:
bash3.00$psqlmahasiswa
-
SETSETREVOKEGRANTCOMMENTbash3.00$
Modul Praktikum Basis Data KA hal 13 dari 32
-
MODUL IIITabel, Query, View, dan Join
III.1 Maksud dan TujuanIII.1.1 Maksud
Modul ini membahas tentang cara membuat tabel yang merupakanbagian inti untuk menyimpan data pada suatu basis data. Selain itu,modul ini juga membahas tentang Query untuk mengambil data dari (satuatau lebih) tabel, view untuk mempermudah pengaksesan tabel untuksesi berikutnya, serta join untuk mengambil data dari berbagai tabelsekaligus.
III.1.2 Tujuan1. 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 TeoriTabel
Tabel merupakan bagian dari basis data dan digunakan untuk menyimpandata dalam bentuk kolom (column / field) dan baris (row/record). Tabel ini bisadibuat pada basis data langsung, atau bisa juga menempati suatu schema.Schema merupakan suatu pengelompokan logis dari tabel-tabel pada suatubasis data.
Perintah SQL untuk membuat tabel:
CREATE[[GLOBAL|LOCAL]{TEMPORARY|TEMP}]TABLEtable_name( {column_namedata_type[DEFAULTdefault_expr][column_constraint[...]]|table_constraint|LIKEparent_table[{INCLUDING|EXCLUDING}DEFAULTS]}[,...])[INHERITS(parent_table[,...])][WITHOIDS|WITHOUTOIDS][ONCOMMIT{PRESERVEROWS|DELETEROWS|DROP}]wherecolumn_constraintis:[CONSTRAINTconstraint_name]{NOTNULL|NULL|UNIQUE|PRIMARYKEY|CHECK(expression)|
Modul Praktikum Basis Data KA hal 14 dari 32
-
REFERENCESreftable[(refcolumn)][MATCHFULL|MATCHPARTIAL|MATCHSIMPLE[ONDELETEaction][ONUPDATEaction]}[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLYIMMEDIATE]andtable_constraintis:[CONSTRAINTconstraint_name]{UNIQUE(column_name[,...])|PRIMARYKEY(column_name[,...])|CHECK(expression)| FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable[(refcolumn[,...])[MATCHFULL|MATCHPARTIAL|MATCHSIMPLE][ONDELETEaction][ONUPDATEa[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLYIMMEDIATE]
Contoh:
CREATETABLEfilms(codechar(5)CONSTRAINTfirstkeyPRIMARYKEY,titlevarchar(40)NOTNULL,didintegerNOTNULL,date_proddate,kindvarchar(10),lenintervalhourtominute);
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 bolehnol/kosong.
4. Tipe data yang bisa digunakan untuk kolom adalah:
Tipe data Alias Keteteranganbigint int8 signed eight-byte integerbigserial serial8 autoincrementing eight-bytebit fixed-length bit stringbit varying(n) varbit(n) variable-length bit stringboolean bool logical Boolean (true/false)
Modul Praktikum Basis Data KA hal 15 dari 32
-
box rectangular box in the planebytea binary datacharacter varying(n) varchar(n) variable-length character stringcharacter(n) char(n) fixed-length character stringcidr IPv4 or IPv6 network addresscircle circle in the planedate calendar date (year, month,
day)double precision float8 double precision floating-point
numberinet IPv4 or IPv6 host addressinteger int, int4 signed four-byte integerinterval(p) time spanline infinite line in the plane (not fully
implemented)lseg line segment in the planemacaddr MAC address
macaddrmoney currency amountnumeric [ (p, s) ] decimal [ (p, s) ] exact numeric with selectable
precisionpath open and closed geometric path
in the planepoint geometric point in the planepolygon closed geometric path in the
planereal float4 single precision floating-point
numbersmallint int2 signed two-byte integerserial serial4 autoincrementing four-byte
integertext variable-length character string
Modul Praktikum Basis Data KA hal 16 dari 32
-
time [ (p) ] [ withouttime zone ]
time of day
time [ (p) ] with timezone
timetz time of day, including time zone
timestamp [ (p) ] withouttime zone
timestamp date and time
timestamp [ (p) ] [ withtime zone ]
timestamptz date and time, including timezone
Mengisikan Data (INSERT)Perintah INSERT digunakan untuk mengisikan data ke dalam suatu tabel
pada suatu basis data. Perintah yang digunakan adalah sebagai berikut:
INSERTINTOtable[(column[,...])]{DEFAULTVALUES|VALUES({expression|DEFAULT}[,...])|query}
QueryQuery merupakan kategori perintah yang digunakan untuk meminta
layanan dari server basis data. Perintah yang akan kita pelajari sekarang adalahSELECT untuk mengambil data dari basis data. Bentuk perintah SELECT adalahsebagai berikut:
SELECT[ALL|DISTINCT[ON(expression[,...])]]*|expression[ASoutput_name][,...][FROMfrom_item[,...]][WHEREcondition][GROUPBYexpression[,...]][HAVINGcondition[,...]][{UNION|INTERSECT|EXCEPT}[ALL]select][ORDERBYexpression[ASC|DESC|USINGoperator][,...]][LIMIT{count|ALL}][OFFSETstart][FORUPDATE[OFtable_name[,...]]]
ViewView 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 viewtersebut dengan perintah SELECT. Perintah untuk membuat view adalahsebagai berikut:
CREATE[ORREPLACE]VIEWname[(column_name[,...])]ASquery
Modul Praktikum Basis Data KA hal 17 dari 32
-
Untuk menghapus view, gunakan perintah DROP VIEW namaview
JoinJoin adalah suatu bagian dari perintah SELECT yang digunakan untuk
menggabungkan lebih dari satu tabel ke dalam suatu hasil query. Ada beberapajenis join yang bisa digunakan, antara lain:
1. [ INNER ] JOIN2. LEFT [ OUTER ] JOIN3. RIGHT [ OUTER ] JOIN4. FULL [ OUTER ] JOIN5. CROSS JOIN
III.3 Praktik1. Buatlah basis data praktikum dan buatlah dua tabel berikut ini pada basis
data tersebut:
CREATETABLEfilms(codechar(5)CONSTRAINTfirstkeyPRIMARYKEY,titlevarchar(40)NOTNULL,didintegerNOTNULL,date_proddate,kindvarchar(10),lenintervalhourtominute);CREATETABLEdistributors(didintegerPRIMARYKEYDEFAULTnextval('serial'),namevarchar(40)NOTNULLCHECK(name''));
2, Isikan data berikut ini:
INSERTINTOfilmsVALUES('UA502','Bananas',105,'19710713','Comedy','82minutes');INSERTINTOfilms(code,title,did,date_prod,kind,len) VALUES('T_601','Yojimbo',106,'19610616','Drama','100minutes');
3. Isikan data untuk distributor, sesuai dengan isi data di atas. Nama distributorterserah 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 tampilkanhasilnya.
Modul Praktikum Basis Data KA hal 18 dari 32
-
6. Buatlah query untuk mengambil data distributor, diurutkan menurut namadistributor.
7. Tampilkan 3 data pertama dari distributor!8. Kerjakan perintah SQL berikut dan jelaskan artinya: SELECTkind,sum(len)
AStotalFROMfilmsGROUPBYkind;9. Kerjakan perintah SQL untuk join berikut ini dan jelaskan artinya: SELECT
f.title,f.did,d.name,f.date_prod,f.kindFROMdistributorsdINNERJOINfilmsfONf.did=d.did
10. Kerjakan perintah SQL berikut ini dan jelaskan artinya: SELECT*FROMfilmsWHEREfilms.titleLIKE'Y%';
Modul Praktikum Basis Data KA hal 19 dari 32
-
MODUL IVMengelola User
IV.1 Maksud dan TujuanIV.1.1 Maksud
Membahas tentang bagaimana PostgreSQL sebagai suatu softwareserver basis data memungkinkan pemakaian oleh lebih dari satupemakai. Untuk keperluan tersebut, user perlu dikelola dengan baik.Modul ini membahas tentang bagaimana cara mengelola user padaPostgreSQL.
IV.1.2 Tujuan1. 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 TeoriSetiap obyek basis data (database object) selalu mempunyai user. User
pada basis data tidak sama dengan user sistem operasi sehingga meskipunanda mempunyai user sistem operasi, anda tidak otomatis mempunyai hakakses terhadap basis data di PostgreSQL. User ini bisa dibuat oleh user lainyang mempunyai kewenangan (secara software). Pada saat membuat user, bisasekaligus ditentukan apakah user yang akan dibuat tersebut mempunyaikewenangan untuk membuat user lain serta berbagai kewenangan lain(misalnya membuat basis data). Perintah untuk membuat user bisa dilakukandari 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@localhostpostgresql]$createuserhelpcreateusercreatesanewPostgreSQLuser.
Usage:createuser[OPTION]...[USERNAME]
Options:a,adduserusercanaddnewusersA,noadduserusercannotaddnewusersd,createdbusercancreatenewdatabasesD,nocreatedbusercannotcreatedatabasesP,pwpromptassignapasswordtonewuser
Modul Praktikum Basis Data KA hal 20 dari 32
-
E,encryptedencryptstoredpasswordN,unencrypteddonoencryptstoredpasswordi,sysid=SYSIDselectsysidfornewusere,echoshowthecommandsbeingsenttotheserverq,quietdon'twriteanymessageshelpshowthishelp,thenexitversionoutputversioninformation,thenexit
Connectionoptions:h,host=HOSTNAMEdatabaseserverhostorsocketdirectoryp,port=PORTdatabaseserverport U,username=USERNAME usernametoconnectas(nottheonetocreate)W,passwordpromptforpasswordtoconnect
Ifoneofa,A,d,D,andUSERNAMEisnotspecified,youwillbepromptedinteractively.
Reportbugsto.[bpdp@localhostpostgresql]$
Berikut ini adalah contohnya:
[bpdp@localhostpostgresql]$createuserEnternameofusertoadd:isikannimShallthenewuserbeallowedtocreatedatabases?(y/n)nShallthenewuserbeallowedtocreatemorenewusers?(y/n)nPassword:CREATEUSER[bpdp@localhostpostgresql]$
Membuat user dari psql Dari psql, user bisa dibuat dengan menggunakan perintah SQL CREATE
USER. Berikut ini adalah cara penggunaan perintah tersebut:
praktikum=#\helpCREATEUSERCommand:CREATEUSERDescription:defineanewdatabaseuseraccountSyntax:CREATEUSERname[[WITH]option[...]]
whereoptioncanbe:
SYSIDuid|[ENCRYPTED|UNENCRYPTED]PASSWORD'password'|CREATEDB|NOCREATEDB|CREATEUSER|NOCREATEUSER|INGROUPgroupname[,...]|VALIDUNTIL'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 aksesAda dua perintah SQL untuk mengatur hak akses user:
1. GRANT. Digunakan untuk memberikan hak akses ke user. Sintaksis lengkapdari perintah ini adalah:
praktikum=#\helpGRANTCommand:GRANTDescription:defineaccessprivilegesSyntax:GRANT{{SELECT|INSERT|UPDATE|DELETE|RULE|REFERENCES|TRIGGER}[,...]|ALL[PRIVILEGES]}ON[TABLE]tablename[,...]TO{username|GROUPgroupname|PUBLIC}[,...][WITHGRANTOPTION]
GRANT{{CREATE|TEMPORARY|TEMP}[,...]|ALL[PRIVILEGES]}ONDATABASEdbname[,...]TO{username|GROUPgroupname|PUBLIC}[,...][WITHGRANTOPTION]
GRANT{EXECUTE|ALL[PRIVILEGES]}ONFUNCTIONfuncname([type,...])[,...]TO{username|GROUPgroupname|PUBLIC}[,...][WITHGRANTOPTION]
GRANT{USAGE|ALL[PRIVILEGES]}ONLANGUAGElangname[,...]TO{username|GROUPgroupname|PUBLIC}[,...][WITHGRANTOPTION]
GRANT{{CREATE|USAGE}[,...]|ALL[PRIVILEGES]}ONSCHEMAschemaname[,...]TO{username|GROUPgroupname|PUBLIC}[,...][WITHGRANTOPTION]
praktikum=#
2. REVOKE. Digunakan untuk mencabut hak akses user. Sintaksis lengkapnyaadalah sebagai berikut:
praktikum=#\helpREVOKECommand:REVOKEDescription:removeaccessprivilegesSyntax:
Modul Praktikum Basis Data KA hal 22 dari 32
-
REVOKE[GRANTOPTIONFOR]{{SELECT|INSERT|UPDATE|DELETE|RULE|REFERENCES|TRIGGER}[,...]|ALL[PRIVILEGES]}ON[TABLE]tablename[,...]FROM{username|GROUPgroupname|PUBLIC}[,...][CASCADE|RESTRICT]
REVOKE[GRANTOPTIONFOR]{{CREATE|TEMPORARY|TEMP}[,...]|ALL[PRIVILEGES]}ONDATABASEdbname[,...]FROM{username|GROUPgroupname|PUBLIC}[,...][CASCADE|RESTRICT]
REVOKE[GRANTOPTIONFOR]{EXECUTE|ALL[PRIVILEGES]}ONFUNCTIONfuncname([type,...])[,...]FROM{username|GROUPgroupname|PUBLIC}[,...][CASCADE|RESTRICT]
REVOKE[GRANTOPTIONFOR]{USAGE|ALL[PRIVILEGES]}ONLANGUAGElangname[,...]FROM{username|GROUPgroupname|PUBLIC}[,...][CASCADE|RESTRICT]
REVOKE[GRANTOPTIONFOR]{{CREATE|USAGE}[,...]|ALL[PRIVILEGES]}ONSCHEMAschemaname[,...]FROM{username|GROUPgroupname|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 sertakanjuga 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 VUpdate dan Delete
V.1 Maksud dan TujuanV.1.1 Maksud
Modul ini membahas tentang bagaimana cara memperbaharui /mengupdate data serta menghapus data pada suatu tabel.
V.1.2 Tujuan1. Mahasiswa memahami cara memperbaharui data pada suatu tabel.2. Mahasiswa memahami cara menghapus data pada suatu tabel.
V.2 Dasar TeoriSQL (dan PostgreSQL) menyediakan perintah untuk memperbaharui data
pada suatu tabel serta perintah untuk menghapus data pada suatu tabel (semuaatau berdasarkan kondisi tertentu). Perintah-perintah tersebut adalah UPDATEdan DELETE.
UPDATEPerintah UPDATE digunakan untuk memperbaharui data pada tabel.
Berikut adalah sintaksis lengkapnya:
praktikum=#\helpUPDATECommand:UPDATEDescription:updaterowsofatableSyntax:UPDATE[ONLY]tableSETcolumn={expression|DEFAULT}[,...][FROMfromlist][WHEREcondition]
praktikum=#
DELETEPerintah DELETE digunakan untuk menghapus data pada tabel. Berikut
ini adalah sintaksis lengkapnya:
praktikum=#\helpDELETECommand:DELETEDescription:deleterowsofatableSyntax:DELETEFROM[ONLY]table[WHEREcondition]
praktikum=#
Modul Praktikum Basis Data KA hal 24 dari 32
-
V.3 Praktik1. 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 VITransactions
VI.1 Maksud dan TujuanVI.1.1 Maksud
Modul ini membahas tentang transactions yang memungkinkan suatuoperasi terhadap basis data dibatalkan atau dilaksanakan, tergantungpada suatu kondisi.
VI.1.2 Tujuan1. Mahasiswa memahami pengertian transactions.2. Mahasiswa memahami cara menggunakan transactions serta
aplikasinya.
VI.2 Dasar TeoriTransactions merupakan suatu mekanisme yang umum terdapat pada
basis data SQL untuk menyimpan kondisi tertentu dari suatu basis data sebelummelakukan operasi-operasi terhadap basis data tersebut. Dengan demikian,pada suatu titik setelah itu, bisa dilakukan penulisan ke basis data ataupembatalan perintah-perintah yang dikerjakan setelah penyimpanan kondisitersebut.
Pada dasarnya, transactions ini digunakan pada saat kondisi masih belumpasti sehingga memungkinkan terjadinya atau tidak terjadinya suatu perubahanterhadap basis data berdasarkan kondisi yang akan datang. Perintah untukmenandai kondisi saat ini adalah BEGIN TRANSACTION, sdangkan perintahuntuk membatalkan operasi adalah ROLLBACK dan perintah untukmelaksanakan 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 Praktik1. Kerjakan praktik pada modul V tanpa praktik nomor 1 dan nomor 10. Praktik
nomor 1 diganti dengan BEGIN TRANSACTION, sedangkan praktik nomor 10diganti dengan ROLLBACK.
2. Apa yang terjadi jika keluar dari psql tanpa perintah ROLLBACK atauCOMMIT? Jelaskan!
Modul Praktikum Basis Data KA hal 27 dari 32
-
MODUL VIIStored Procedure dan Trigger
Menggunakan PL/pgSQL
(2 sesi praktikum)
VII.1 Maksud dan TujuanVII.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 operasitertentu terhadap basis data sehingga kondisi pengaksesan datayang kompleks bisa ditangani. Selain itu, trigger juga bisadigunakan untuk mengaktifkan suatu function yang dibuat denganPL/pgSQL sebelum atau sesudah UPDATE, DELETE, danINSERT.
VII.1.2 Tujuan1. Mahasiswa memahami pengertian Stored Procedure2. 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 trigger6. Mahasiswa memahami bagaimana cara membuat trigger serta
menghubungkannya dengan fungsi yang telah dibuat denganmenggunakan PL/pgSQL.
VII.2 Dasar TeoriStored Procedure adalah bahasa pemrograman yang bisa disimpan pada
suatu basis data sehingga memungkinkan pengolahan terdapat basis datatersebut secara langsung untuk operasi-operasi yang lebih kompleks. PadaPostgreSQL, bahasa tersebut dikenal dengan nama PL/pgSQL. PL/pgSQLmerupakan 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. Meskipunbersifat lintas platform tetapi masalah muncul karena SQL hanya bisa dilakukansecara individual, akibatnya proses untuk akses dan pengolahan basis datasering dilakukan oleh bahasa lainnya. Hal ini menyebabkan turunnya kinerjakarena memerlukan waktu dan sumber daya untuk pemrosesan antara compileratau interpreter suatu bahasa pemrograman dengan server basis data. Untukkeperluan itulah, biasanya dibuat suatu bahasa. Pada PostgeSQL dikenaldengan PL/pgSQL. DBMS lainnya seperti Oracle juga mempunyai fasilitasseperti itu yaitu Oracle PL/SQL.
Definisi dari PL/pgSQL pada PostgreSQL adalah sebagai berikut (bisadisimpan pada suatu file teks, atau langsung dituliskan pada psql):
[][DECLAREdeclarations]BEGINstatementsEND;
Untuk lebih mempermudah, biasanya bahasa ini dituliskan pada suatu file tekskemudian pada psql dipanggil dan dijalankan dengan cara:
\ifilename.sql
Agar PL/pgSQL ini bisa dijalankan pada suatu basis data PostgreSQL, makabahasa tersebut harus diinstall ke dalam basis data. Perintah yang digunakanuntuk menginstall tersebut adalah createlang dan dijalankan dari shell. Berikutini adalah sintaksis lengkapnya:
[bpdp@localhostpostgresql]$createlanghelpcreatelanginstallsaprocedurallanguageintoaPostgreSQLdatabase.
Usage:createlang[OPTION]...LANGNAME[DBNAME]
Options:d,dbname=DBNAMEdatabasetoinstalllanguageine,echoshowthecommandsbeingsenttotheserverl,listshowalistofcurrentlyinstalledlanguagesL,pglib=DIRECTORYfindlanguageinterpreterfileinDIRECTORYh,host=HOSTNAMEdatabaseserverhostorsocketdirectoryp,port=PORTdatabaseserverportU,username=USERNAMEusernametoconnectasW,passwordpromptforpassword
Modul Praktikum Basis Data KA hal 29 dari 32
-
helpshowthishelp,thenexitversionoutputversioninformation,thenexit
Reportbugsto.[bpdp@localhostpostgresql]$
Setelah menuliskan perintah PL/pgSQL. kita bisa menjalankan perintahtersebut menggunakan SELECT.
Perintah-perintah dasar PL/pgSQL1. Deklarasi variabel:
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := }expression];
2. ALIAS, bisa ditetapkan pada DECLARE untuk membuat nama lain dari suatuparameter. Contoh bisa dilihat pada praktik.
3. Seleksi kondisi:
IF...THENIF...THEN...ELSEIF...THEN...ELSEIFIF...THEN...ELSIF...THEN...ELSE
4. Loop: digunakan untuk perulangan. Sintaksis lengkapnya adalah sebagaiberikut:
[]LOOPstatementsENDLOOP;
Pembahasan tentang PL/pgSQL merupakan pembahasan yang cukupkompleks, terutama berkaitan dengan sintaksis dari bahasa tersebut. Sintaksislengkap bisa diperoleh pada manual PostgreSQL.
TriggerTrigger 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 mempermudahpemeriksaan menjelang dilakukan INSERT, UPDATE, atau DELETE sertamenentukan tindakan apa yang akan dilakukan berdasarkan hasil pemeriksaantertentu. Sebagai contoh, kita bisa mendefinisikan trigger untuk memeriksa isisuatu 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=#\helpCREATETRIGGERCommand:CREATETRIGGERDescription:defineanewtriggerSyntax:CREATETRIGGERname{BEFORE|AFTER}{event[OR...]}ONtable[FOR[EACH]{ROW|STATEMENT}]EXECUTEPROCEDUREfuncname(arguments)
praktikum=#
VII.3 Praktik
1. Install PL/pgSQL pada basis data praktikum. Perintah yang digunakan adalah:createlang U linuxoke plpgsql praktikum. Ingat, anda harusmenggunakan user 'linuxoke'.
2. Buatlah function untuk menjumlahkan tiga angka berikut ini:[bpdp@localhostpostgresql]$psqlpraktikumPassword:Welcometopsql7.4.2,thePostgreSQLinteractiveterminal.
Type:\copyrightfordistributionterms\hforhelpwithSQLcommands\?forhelponinternalslashcommands\gorterminatewithsemicolontoexecutequery\qtoquit
praktikum=#CREATEFUNCTIONadd_three_values(anyelement,anyelement,anyelement)praktikum#RETURNSanyelementAS'praktikum'#DECLAREpraktikum'#resultALIASFOR$0;praktikum'#firstALIASFOR$1;praktikum'#secondALIASFOR$2;praktikum'#thirdALIASFOR$3;praktikum'#BEGINpraktikum'#result:=first+second+third;praktikum'#RETURNresult;praktikum'#END;praktikum'#'LANGUAGEplpgsql;CREATEFUNCTIONpraktikum=#
2. Jalankan function tersebut dengan cara:
praktikum=#SELECT*fromadd_three_values(1,2,3);add_three_values6
Modul Praktikum Basis Data KA hal 31 dari 32
-
(1row)praktikum=#
3. Buatlah tabel berikut ini pada basis data praktikum:
CREATETABLEemp(empnametext,salaryinteger,last_datetimestamp,last_usertext);
4. Buatlah function berikut ini pada basis data praktikum:
CREATEFUNCTIONemp_stamp()RETURNStriggerAS'BEGINCheckthatempnameandsalaryaregivenIFNEW.empnameISNULLTHENRAISEEXCEPTION"empnamecannotbenull";ENDIF;IFNEW.salaryISNULLTHENRAISEEXCEPTION"%cannothavenullsalary",NEW.empname;ENDIF;
Whoworksforuswhenshemustpayforit?IFNEW.salary