UNIVERSITAS BINA NUSANTARA Jurusan Teknik Informatika Skripsi Sarjana Komputer Semester ganjil tahun 2005/2006 ANALISIS HUBUNGAN GENERALISASI/SPESIALISASI PADA MODEL ENTITY RELATIONSHIP DAN PEMETAANNYA KE MODEL RELASIONAL
Etina Chia Chia Richard
0600628541 0600632910 0600634052
Abstrak Tujuan penelitian ini adalah melakukan analisis terhadap proses mapping generalisasi/spesialisasi dari model ER (Entity Relationship) ke EER (Enhanced Entiy Relationship) yang kemudian dilanjutkan ke model relasional, dan membuat modifikasi statement dalam bahasa SQL agar dapat mengenali multi referential, serta membuat alat bantu (tool) yang dapat diimplementasikan ke dalam RDBMS (Relational Database Management Systems) untuk menjaga integritas referensial bagian superclass ke bagian subclass. Sedangkan manfaatnya adalah terciptanya solusi untuk mengatasi masalah integritas referensial yang berkaitan dengan model generalisasi/spesialisasi. Metodologi penelitian ini adalah studi literatur, dan analisis masalah serta opsi-opsi pemecahan masalahnya dan merancang alat bantu. Hasil dari penelitian ini adalah sebuah alat bantu yang dapat menyelesaikan masalah generalisasi/spesialisasi yang menggunakan opsi 3 (Mandatory disjoint) dan 4 (Optional Disjoint) sebagai solusi. Kesimpulannya alat bantu ini hanya dapat di implementasikan di komputer klien yang terhubung pada server dengan RDBMS SQL Server.
Kata Kunci: Generalisasi/spesialisasi, entity relationship, model relasional, referensial key, superkelas, subkelas
vii
KATA PENGANTAR
Segala puji dan syukur kepada Tuhan Yang Maha Esa, atas rahmat dan berkatnya, sehingga penulis bisa menyelesaikan skripsi dengan judul : “Analisis Hubungan Generalisasi/Spesialisasi Pada Model Entity Relationship dan Pemetaannya ke Model Relasional “ Tujuan dari penyusunan skripsi ini adalah untuk memenuhi salah satu syarat dalam menyelesaikan perkuliahan Jenjang Pendidikan Strata-1, Jurusan Teknik Informatika di Universitas Bina Nusantara, Jakarta. Dalam penyusunan skripsi ini, penulis mendapat bimbingan dan bantuan dari berbagai pihak, baik secara langsung maupun tidak langsung. Pada kesempatan ini perkenankanlah penulis untuk menyampaikan rasa terima kasih kepada pihak-pihak terkait tersebut : 1. Almarhumah Ibu DR. Ir. Theresia Widia Soeryaningsih, MM, selaku pendiri dan mantan rektor Universitas Bina Nusantara. 2. Bapak Prof. Dr. Gerardus Polla, M.APP., Sc, selaku rektor Universitas Bina Nusantara. 3. Bapak H.M. Subekti, BE, M.Sc, selaku ketua Jurusan Teknik Informatika Universitas Bina Nusantara. 4. Bapak Fredy Purnomo, S.Kom., M.Kom, selaku sekretaris Jurusan Teknik Informatika Universitas Bina Nusantara.
viii
5. Bapak Ashari S.Kom., M.Kom, selaku dosen pembimbing yang telah meluangkan waktu serta memberikan bimbingan, nasehat, petunjuk dalam penyusunan dan menyelesaikan skripsi ini 6. Seluruh staf pengajar dan karyawan Universitas Bina Nusantara yang telah memberikan bantuan selama penulis menuntut ilmu di jurusan Teknik Informatika Universitas Bina Nusantara. 7. Orang tua, saudara-saudara, dan teman-teman yang telah memberikan bantuan berupa dorongan, bimbingan, dan fasilitas selama penyusunan skripsi ini. 8. Semua pihak yang tidak dapat disebutkan satu persatu yang telah memberi semangat dan saran-saran yang membantu dalam penyusunan skripsi ini.
Akhir kata, penulis menyadari bahwa dalam penulisan skripsi ini, masih banyak kekurangan dan kelemahan, oleh karena itu penulis mengharapkan adanya saran dan kritik yang bersifat membangun dari pembaca sangat diharapkan. Meskipun demikian, penulis tetap berharap agar skripsi ini dapat bermanfaat bagi para pembaca.
Jakarta, Januari 2006
Penulis
ix
DAFTAR ISI
Halaman Judul Luar ...........................................................................................................i Halaman Judul Dalam ....................................................................................................... ii Halaman Persetujuan Hardcover...................................................................................... iii Halaman Pernyataan Dewan penguji ................................................................................iv Abstrak ............................................................................................................................ vii Kata Pengantar ............................................................................................................... viii Daftar Isi ............................................................................................................................x Daftar Tabel ....................................................................................................................xiv Daftar Gambar..................................................................................................................xv Daftar Lampiran ..............................................................................................................xix
BAB 1 PENDAHULUAN .................................................................................................1 1.1
Latar Belakang Masalah.....................................................................................1
1.2
Ruang Lingkup...................................................................................................3
1.3
Tujuan dan Manfaat ...........................................................................................3
1.4
Metodologi Penelitian ........................................................................................4
1.5
Sistematika Penulisan ........................................................................................5
BAB 2 LANDASAN TEORI.............................................................................................7 2.1
Database .............................................................................................................7
2.2
Model Relational................................................................................................8
2.2.1
Sejarah Model Data Relational ..................................................................8
2.2.2
Pengertian Model Relasional ...................................................................11 x
2.2.2.1
Struktur Data Relasional ......................................................................12
2.2.2.2
Relasi Matematika................................................................................15
2.2.2.3
Relasi Database ....................................................................................16
2.2.2.4
Properti dari relasi ................................................................................18
2.2.2.5
Kunci-kunci relasional (Relational Keys)............................................20
2.2.3
Integritas Relasional.................................................................................21
2.2.3.1
Nulls .....................................................................................................22
2.2.3.2
Integritas Entitas...................................................................................22
2.2.3.3
Integritas Referensial ...........................................................................23
2.2.3.4
Batasan Kegiatan (Enterprise Constraint)............................................24
2.2.4
Views .......................................................................................................24
2.2.4.1
Terminology.........................................................................................25
2.2.4.2
Tujuan dari views.................................................................................26
2.2.4.3
Pembaharuan View (updating view).....................................................27
2.3
Model Entity Relationship (ER Model) ...........................................................28
2.3.1
Entity types (Tipe Entitas) .......................................................................28
2.3.2
Relationship types (Tipe hubungan) ........................................................29
Derajat dari tipe relasi ..........................................................................................29 2.3.3
Atribut dan atribut domains .....................................................................32
2.3.4
Entitas Kuat dan Entitas Lemah (Strong dan Weak Entity).....................34
2.3.5
Atribut dalam Relasi-Relasi .....................................................................35
2.3.6
Batasan struktural.....................................................................................36
2.3.7
Masalah dalam ER model .......................................................................39
2.4
Model Enhanced Entity Relationship...............................................................42 xi
2.4.1
Generalisasi/Spesialisasi (Generalization/ Specialization) ......................42
2.4.1.1
Superkelas dan subkelas.......................................................................43
2.4.1.2
Relasi superkelas/subkelas ...................................................................43
2.4.1.3
Attribute Inheritance ............................................................................45
2.4.1.4
Proses Spesialisasi (Specialization Process) ........................................46
2.4.1.5
Proses Generalisasi (Generalization Process) ......................................47
2.4.1.6
Diagram representasi dari generalisasi/spesialisasi .............................48
2.4.1.7
Batasan dalam Generalisasi/Spesialisasi (Constraints on
Generalization/Specialization) .............................................................................52 2.4.2
Aggregation (Agregasi)............................................................................53
2.4.3
Composition (Komposisi) ........................................................................54
2.4.4
Generalisasi dan spesialisasi ....................................................................55
2.5
Perancangan Database......................................................................................55
2.6
Trigger..............................................................................................................62
BAB 3 ANALISIS DAN PERANCANGAN ..................................................................64 3.1
Analisis Generalisasi/Spesialisasi ....................................................................64
3.1.1
Option 1 (Mandatory Nondisjoint) ..........................................................65
3.1.2
Option 2 (Optional Nondisjoint) ..............................................................67
3.1.3
Option 3 (Mandatory Disjoint) ................................................................70
3.1.4
Option 4 (Optional Disjoint) ....................................................................72
3.1.5
Rekomendasi Pengunaan Opsi-Opsi ........................................................74
3.1.6
Analisis Pemilihan Opsi Berdasarkan Kasus ...........................................75
3.1.7
Analisis Opsi 3 dan Opsi 4 dalam RDBMS (Relational Data Base
Management System)...............................................................................................77 xii
3.2
Perancangan .....................................................................................................80
3.2.1
Perancangan Foreign Key ........................................................................80
3.2.2
Perancangan Aplikasi...............................................................................80
Perancangan Interface ..........................................................................................92 BAB 4 IMPLEMENTASI DAN EVALUASI .................................................................99 4.1
Implementasi ....................................................................................................99
4.1.1
Spesifikasi Hardware ...............................................................................99
4.1.2
Spesifikasi Software...............................................................................100
4.2
Tampilan Layar dan Cara Operasi Tool/alat bantu ........................................100
4.2.1
Tampilan Setting ....................................................................................100
4.2.2
Tampilan Layar Utama ..........................................................................107
4.2.3
Tampilan Layar Input Subclass..............................................................108
4.2.4
Tampilan Menu Mandatory Disjoint .....................................................108
4.2.5
Tampilan Menu Optional Disjoint .........................................................109
4.2.6
Tampilan Layar Hasil.............................................................................110
4.2.7
Tampilan Message Box Kesalahan.........................................................111
4.3
Evaluasi ..........................................................................................................115 Evaluasi Performa Alat Bantu............................................................................115
BAB 5 KESIMPULAN DAN SARAN .........................................................................119 5.1
Kesimpulan ....................................................................................................119
5.2
Saran...............................................................................................................120
DAFTAR PUSTAKA ....................................................................................................121 RIWAYAT HIDUP........................................................................................................122
xiii
DAFTAR TABEL
Tabel 2. 1 : Tabel Mahasiswa ..........................................................................................11 Tabel 2. 2 : Tabel Kuliah .................................................................................................11 Tabel 2. 3 : Relasi viewing (Connolly,p80) .....................................................................23 Tabel 2.4 : Panduan untuk relasi superkelas/subkelas berdasarkan participation dan disjoint constraint (Connolly,p451) .........................................................................59 Tabel 2. 5 : Syntax Trigger dalam SQL Server, Oracle, dan MySQL .............................62 Tabel 2. 6 : Keterangan inventaris dalam suatu sintaks pembuatan trigger.....................63 Tabel 3. 1 : Option 1 Mandatory Nondisjoint ..................................................................65 Tabel 3. 2 : Option 2 – Optional, nondisjoint ..................................................................67 Tabel 3. 3 : Mandatory Disjoint .......................................................................................70 Tabel 3. 4 : Optional Disjoint...........................................................................................72 Tabel 3. 5 : Tabel rekomendasi penggunaan opsi-opsi. ..................................................74 Tabel 3. 6 : Analisis Opsi 3 dan 4 ...................................................................................78
xiv
DAFTAR GAMBAR
Gambar 2. 1 : Contoh Database dari Perusahaan Telekomunikasi ....................................7 Gambar 2. 2: Instansi dari relasi Branch dan Staff (Connolly,p73).................................14 Gambar 2. 3 : Bentuk entitas (Connolly,p333). ...............................................................29 Gambar 2. 4 : Tentang tipe Has relasi (Connolly,p334). .................................................29 Gambar 2. 5 : Relasi binary yang disebut Powns (Connolly,p336) .................................30 Gambar 2. 6 : Gambar relasi ternary disebut Registers (Connolly,p336)........................30 Gambar 2. 7 : Gambar relasi quarternary disebut Arranges (Connolly,p337).................31 Gambar 2. 8 : Relasi rekursif disebut Supervises (Connolly,p337).................................31 Gambar 2. 9 : Contoh dari entitas yang diasosiasikan pada dua perbedaan relasi (Connolly,p338) .......................................................................................................32 Gambar 2. 10 : Gambar sebuah relasi disebut Advertises dengan atribut-atribut date Advert dan cost (Connolly,p344).............................................................................35 Gambar 2. 11 : Gambar tentang tipe relasi Staff Manages Branch (Connolly,p345)......36 Gambar 2. 12 : Gambar tentang relasi keserbaragaman dari Staff Manages Branch oneto-one (1:1) (Connolly,p346) ...................................................................................37 Gambar 2. 13 : Gambar tipe relasi pada Staff Oversees PropertyForRent (Connolly,p346) .......................................................................................................37 Gambar 2. 14 : Gambar tipe relasi keserbaragaman dari Staff Oversees PeopertyForRent one-to-many (1:*) (Connolly,p347) .........................................................................37 Gambar 2. 15 : Gambar tipe relasi Newspaper Advertises PropertyForRent (Connolly,p348) .......................................................................................................38
xv
Gambar 2. 16 : Gambar relasi keserbaragaman pada Newspaper Advertises PropertyForRent many-to-many (*:*) (Connolly,p348) ..........................................38 Gambar 2. 17 : Gambar contoh fan trap (Connolly,p352) ...............................................39 Gambar 2. 18 : Gambar hubungan semantik pada ER model (Connolly,p352) ..............40 Gambar 2. 19 : Gambar model ER direstruktur untuk menghilangkan fan trap (Connolly,p353) .......................................................................................................40 Gambar 2. 20 : Gambar rangkaian semantik dari ER model (Connolly,p353)................40 Gambar 2. 21 : Gambar contoh chasm trap (Connolly,p353) .........................................41 Gambar 2. 22 : Rangkaian semantik dari ER model (Connolly,p354) ............................41 Gambar 2. 23 : Model ER di restruktur untuk menghilangkan chasm trap (Connolly,p354) .......................................................................................................42 Gambar 2. 24 : Gambar rangkaian semantik dari model ER (Connolly,p355)................42 Gambar 2. 25 : Gambar relasi AllStaff memegang detail-detail dari semua staff (Connolly,p361) .......................................................................................................45 Gambar 2. 26 : Gambar spesialisasi/generalisasi dari entitas staff (Connolly,p364)......50 Gambar 2. 27 : Gambar spesialisasi/generalisasi pada entitas staff (Connolly, p365) ....51 Gambar 2. 28 : Gambar Spesialisasi/generalisasi pada entitas staff (Connolly,p365).....51 Gambar 2. 29 : Contoh Agregation : Branch Has Staff dan Branch Offers PropertyForRent (Connolly,p372)...........................................................................54 Gambar 2. 30 : Contoh dari komposisi NewsPaper Displays Advert (Connolly,p373) .54 Gambar 2. 31 : Beragam representasi dari Owner superkelas/subkelas berdasarkan participant dan disjoint constraints (Connolly,p451) ...............................................60 Gambar 2. 32: Relasi superkelas/subkelas supervisor dan staff (Connolly,p433)...........62
xvi
Gambar 3. 1: Contoh kasus (Connolly, p443) .................................................................64 Gambar 3. 2: Contoh mapping Option 2..........................................................................68 Gambar 3. 3 : Contoh mapping option 3..........................................................................71 Gambar 3. 4 : Contoh mapping option 4..........................................................................73 Gambar 3. 5: Proses Log In .............................................................................................85 Gambar 3. 6 : Proses Input Subclass................................................................................86 Gambar 3. 7 : Mandatory Disjoint ...................................................................................87 Gambar 3. 8 : Flowchart Optional Disjoint .....................................................................88 Gambar 3. 9 : Flowchart Create Trigger Opsi 3...............................................................90 Gambar 3. 10 : Flowchart Create Trigger Opsi 4.............................................................91 Gambar 3. 11 : Perancangan Struktur Menu....................................................................92 Gambar 3. 12 : Gambar STD ...........................................................................................93 Gambar 3. 13 : Perancangan Layar Menu Utama ............................................................94 Gambar 3. 14 : Perancangan Layar Input Subclass..........................................................94 Gambar 3. 15 : Perancangan Menu Mandatory Disjoint..................................................96 Gambar 3. 16 : Perancangan Menu Optional Disjoint .....................................................97 Gambar 3. 17 : Perancangan Menu Output......................................................................98
Gambar 4. 1 : Layar Windows ......................................................................................101 Gambar 4. 2 : Control Panel...........................................................................................101 Gambar 4. 3 : Administration Tools ..............................................................................102 Gambar 4. 4 : ODBC Data Source Administrator..........................................................102 Gambar 4. 5 : Microsoft SQL Server DSN Configuration.............................................103 Gambar 4. 6 : Authentifikasi..........................................................................................104 xvii
Gambar 4. 7 :ODBC Microsoft SQL Server Setup........................................................105 Gambar 4. 8 : Layar tes koneksi.....................................................................................105 Gambar 4. 9 : Create New Data Source .........................................................................106 Gambar 4. 10 : Connector/ODBC pada My SQL ..........................................................107 Gambar 4. 11 : Hasil Tes Koneksi My SQL ..................................................................107 Gambar 4. 12 : Layar Log In..........................................................................................108 Gambar 4. 13 : Layar Input Subclass .............................................................................108 Gambar 4. 14 : Layar Pilihan Mandatory Disjoint........................................................109 Gambar 4. 15 : Layar Optional Disjoint.........................................................................110 Gambar 4. 16 : Layar Hasil ............................................................................................111 Gambar 4. 17 : Message Kesalahan karena tidak diisi...................................................111 Gambar 4. 18 : Message Kesalahan Login untuk SQL Server ......................................112 Gambar 4. 19 : Message Kesalahan Input Subclass.......................................................112 Gambar 4. 20 : Message Penggunaan Tools/alat bantu Telah Selesai...........................113 Gambar 4. 21: Message Kesalahan Tidak Ada Record..................................................113 Gambar 4. 22 : Message Kesalahan Referensial............................................................113 Gambar 4. 23 : Message Cancel dari Input Subclass .....................................................114 Gambar 4. 24 : Message Kesalahan Inputan Kosong ....................................................114 Gambar 4. 25 : Evaluasi pada Insert Opsi 3...................................................................116 Gambar 4. 26 : Evaluasi Insert pada Opsi 4...................................................................116 Gambar 4. 27 : Evaluasi Delete pada Opsi 3 ................................................................117 Gambar 4. 28 Evaluasi Delete pada Opsi 4...................................................................117 Gambar 4. 29 : Evaluasi Update pada Opsi 3 ................................................................118 Gambar 4. 30 : Evaluasi Update pada Opsi 4 ................................................................118 xviii
DAFTAR LAMPIRAN
Listing Program Java ......................................................................................................L1 Listing statement SQL Server........................................................................................L32
xix