moduldb
DESCRIPTION
moduldbTRANSCRIPT
BAB IKONTRAK PERKULIAHAN
MATA KULIAH : Sistem Basis DataSKS / SEM : 3 / IV HARI / JAM :
I. MANFAAT
Dapat digunakan untuk mendesain suatu basis data yang terintegrasi baik di bidang pendidikan maupun perusahaan.
II. DESKRIPSI MATA KULIAH
Gambaran tentang Pendahuluan; Arsitektur dan konsep dari DBMS; Data modeling menggunakan model entity relationship; Data modeling menggunakan model relational; Format query language relational lgebra; Orelational Calculus; SQL, QUEL dan QBE; Data model Hierarchical; Data Model Network; Functional dependecies dan normalisasi untuk basis data relational; Algoritma dan Proses design basis data relational; Pemrosesan dan optimasi query; Transaksi; Concurrency control; Recovery, security; Sekilas tentang basis data terdistribusi.
III. TUJUAN INSTRUKSIONAL (TIU)
Diharapkan dapat mendesain suatu basis data yang terintegrasi baik di bidang pendidikan maupun perusahaan.
IV. ORGANISASI MATERI
1
V. STRATEGI PERKULIAHAN- Ceramah- Belajar Mandiri melalui Praktikum- Case Study
VI. MATERI / BACAAN PERKULIAHAN
Kepustakaan wajib :- Elmasri, Rames, Fundamentals of Database System, Benyamin, Tokyo, 1994- Korth, Henry F., Database System Concepts, McGraw-Hill International, USA,
1991
VII. TUGAS
- Membaca- Quis diadakan 2 kali dalam 1 semester, Sebelum UTS dan Sebelum UAS.- UTS dan UAS berdasar kalender akademik
VIII. KRITERIA PENILAIAN
2
TIU
Basis Data Terdistribusi
Quaery
Normalisasi ER-Model
Basis Data Life Cycle
Architecrure & Model Basis Data
Pengertian Basis data
Security
Integrity
RATA-RATA > 60 RATA-RATA < 60NILAI RANGE NILAI RANGEA > 79 A > X + 1.5 SDB 70 - 79 B > X + 0.5 SD s/d X + 1.5 SDC 56 - 69 C > X + 0.5 SD s/d X - 0.5
SDD 45 - 55 D > X - 1.5 SD s/d X - 0.5 SDE < 45 E < X - 1.5 SD Bobot Nilai Akhir :Tugas : 5 %Quis : 5 %Praktikum : 20 %UTS : 30 %Online (UAS) : 40 %
100 %Syarat Lulus : - Kehadiran Kuliah 70 % dan Lulus Praktikum
IX. JADWAL PERKULIAHAN
NO MINGGU/BLN TOPIK BAHASAN1. I - Kontrak Perkuliahan
- General Introduction2. II - Pengertian Basis Data, DBMS
- Traditional VS Database Approach3. II - Architecture Database
- 3 Model Database4. III - Database Life Cycle
- Centralized VS Distributed Database5. IV,V - ER-Model Theory
- Case Study6. V - Tugas & Quis7. VI - Pemetaan ERD ke sejumlah Tabel8. VII - Aljabar Relasi
- Case Study9. VIII - Ujian Tengah Semester10. IX, X - Structure Query Language
- Case Study11. XI,XII - Normalization Theory
- Case Study12. XIII, XIV - Tugas & Quis13. XIV - Operational Database, Security dan Integrity14. XV - Ujian Akhir Semester
BAB II
3
PENGERTIAN DATABASE
Beberapa definisi tentang Database :1. Menurut Gordon C. Everest :Database adalah koleksi atau kumpulan data yang mekanis, terbagi/shared, terdefinisi secara
formal dan dikontrol terpusat pada organisasi.2. Menurut C.J. Date :Database adalah koleksi “data operasional” yang tersimpan dan dipakai oleh sistem aplikasi
dari suatu organisasi.- Data input adalah data yang masuk dari luar sistem- Data output adalah data yang dihasilkan sistem- Data operasional adalah data yang tersimpan pada sistem3. Menurut Toni Fabbri :Database adalah sebuah sistem file-file yang terintegrasi yang mempunyai minimal primary
key untuk pengulangan data.4. Menurut S. Attre :Database adalah koleksi data-data yang saling berhubungan mengenai suatu organisasi /
enterprise dengan macam-macam pemakaiannya.
Jadi SISTEM DATABASE adalah sistem penyimpanan data memakai komputer.
Sifat-sifat database : Internal : Kesatuan (integritas) dari file-file yang terlibat. Terbagi/share : Elemen-elemen database dapat dibagikan pada para user baik secara
sendiri-sendiri maupun secara serentak dan pada waktu yang sama (Concurrent sharing).
Elemen-elemen database :A. Tipe :
1. Enterprise = Suatu jenis organisasi, misalnya Bank, Hotel, Universitas dan lain-lain.
2. Entity = File = Obyek pada enterprise berdasarkan data yang disimpan3. Atribute = Field = Data item = Beberapa hal yang ingin diketahui dari suatu
file4. Record = Satu set field yang merupakan ciri khas dari suatu fileIstilah entity dan atribute biasanya digunakan pada tingkat konsepsual dan logikal,
sedangkan file, record dan field pada tingkat internal/fisikal.Hubungan : Enterprise terdiri dari beberapa entity, entity terdiri dari beberapa record dan record terdiri dari beberapa field.
Enterprise Entity / File Record Atribute/Field
B. Isi / Nilai :1. Data File : Seluruh isi data pada file2. Data Record : Satu set isi data pada suatu susunan field dari suatu file3. Data Value : Isi data masing-masing data elemen.
Sumber-sumber daya suatu organisasi / enterprise :Ada 5 M Sumber Daya, yaitu : Man : Manajemen Personalia (Manusia)
4
Machine : Manajemen peralatan (Mesin) Material : Manajemen industri (Bahan Baku) Money : Manajemen Keuangan (Uang) Message : Manajemen Informasi.
DATA Merupakan sumber daya yang paling penting dalam perusahaan, data perlu disimpan dan dimanajemen (rencana, desain, operasi, kontrol dan evaluasi). Proses manajemen dilaksanakan dalam siklus hidup (Life Cycle).Manajemen data dapat dilakukan secara administrasi manual atau dengan memakai komputer, secara umum Sistem Database adalah sistem penyimpanan data memakai komputer.
4 Komponen DATA PROCESSING yang menggunakan Sistem Database :1. Perangkat Keras (Hardware) : Penyimpanan Sekunder2. Perangkat Lunak (Software) : Program Aplikasi, DBMS3. Data : Database mempunyai sifat internal (integritas dari file-file yang terlibat) dan
terbagi / share4. User : User pembuat program aplikasi, end user (user pemakai data langsung), DBA
(Penanggung jawab).
Penanggung jawab sistem database adalah DBA (Database Administratur) ;1. Syarat menjadi DBA- Berkeahlian Teknik- Berkeahlian tentang enterprise2. DBA yaitu orang/group yang bertanggung jawab pada seluruh pengontrolan database3. Tanggung jawab DBA :- Menetapkan isi database- Menetapkan struktur data pada penyimpanan sekunder dan metode akses- Melayani kebutuhan user- Mendefinisikan pengecekan kewenangan penggunaan database oleh user dan menetapkan prosedur validasi suatu database.- Menetapkan strategi backup dan recovery- Memonitor unjuk kerja dan melayani kebutuhan akan perubahan-perubahan kepentingan.4. Untuk melaksanakan tanggung jawabnya DBA harus mempunyai beberapa program
utility, misalnya :- Create Routine : Untuk membuat database baru- Reorganization Routine : Untuk menyusun kembali database (misal : untuk menghapus tempat-tempat kososng dari record-record yang sudah tidak berlaku)- Journalizing / Logging Routine : Untuk mencatat semua operasi yang telah dikerjakan, siapa usernya.- Recovery Routine : Memperbaiki kerusakan database pada posisi sebelum kerusakan.- Statistical Analisys Routine : Untuk memonitor hasil-hasil database.Salah satu alat penting DBA adalah Data Dictionary yaitu segala sesuatu kekurangan tentang elemen-elemen database misal : Data apa saja yang dipakai suatu program aplikasi, data apa yang dibutuhkan untuk suatu laporan dari suatu departemen.Kedudukan DBA :
Vendor Database DBA Management Hardware +
5
Software Staff Data Processing Aplication User System + operation Development Community Staff Staff
DBMS (Database Managemen System)1. Menutut C.J. Date : DBMS adalah merupakan software yang menghandel seluruh akses
pada database untuk melayani kebutuhan user.2. Menurut S, Attre : DBMS adalah software, hardware, firmware dan procedure-
procedure yang memanage database. Firmware adalah software yang telah menjadi modul yang tertanam pada hardware (ROM).
3. Menurut Gordon C. Everest : DBMS adalah manajemen yang efektif untuk mengorganisasi sumber daya data.
Jadi DBMS : Semua peralatan komputer (Hardware+Software+Firmware). DBMS dilengkapi dengan bahasa yang berorientasi pada data (High level data langauage) yang sering disebut juga sebagai bahasa generasi ke 4 (fourth generation language).
Fungsi DBMS : - Definisi data dan hubungannya - Memanipulasi data - Keamanan dan integritas data - Security dan integritas data - Recovery/perbaikan dan concurency data - Data dictionary - Unjuk kerja / performance
Peralatan untuk menetapkan/menentukan pendekatan database disebut DBMSDBMS merupakan software (dan hardware) yang kusus didesain untuk melindungi dan memanage database.Dengan menggunakan DBMS, maka dapat : Mendefinisikan data dan hubungannya. Mendokumentasikan struktur dan definisi data Menggambarkan, mengorganisasikan dan menyimpan data untuk akses yang
selektif/dipilih dan efisien. Hubungan yang sesuai antara user dengan sumber daya data. Perlindungan terhadap sumber daya data akan terjamin, dapat diandalkan, konsisten
dan benar. Memisahkan masalah Logical dan physical sehingga merubah implementasi database
secara fisik tidak menghendaki user untuk merubah maksud data (Logical). Menentukan pembagian data kepada para user untuk mengakses secara concurent
pada sumber daya data.Contoh DBMS :1. Database Hierarchy : Pengaksesan data harus mengikuti aturan hierarchy yang
sudah didefinisikan terlebih dahulu. Contoh : IMS-2 (Information Management System) oleh IBM, 19682. Data Network : Data membentuk jaringan yang lebih bebas dari model hierarchy. Contoh : IDMS (Integrated Database Management System) oleh Cullinett Software Inc, 19723. Data Relational : Data dikelompokkan secara bebas menurut jenisnya lewat proses
normalisasi
6
Contoh : - INGRES oleh UN of CA & Relational Tech., 1973 - System-R oleh IBM Research, 1975
- ORACLE oleh Relational Software Inc. , 1979 - DBASE II oleh Ashton-Tate, 1981
PENDEKATAN TRADITIONAL VS DATABASE
Mengapa memilih pendekatan database karena pendekatan pemrosesan data sebelum pendekatan database adalah terpusat pada program aplikasi
Pendekatan tradisional, berorientasi pada program aplikasi dan tiap-tiap aplikasi berdiri sendiri-sendiri. contoh : aplikasi produk, aplikasi inventory dsb.Sehingga terdapat masalah-masalah : Data redundancy : Duplikasi data yang sama pada beberapa file.Redundansi dapat direduksi/dikurangi tetapi tidak dapat dihilangkan sama sekalisehingga akan menyebabkan inconsistency (data tidak konsisten), incosistency dapat
dihilangkan dengan cara Propagating Update yaitu melakukan update secara serentak pada field-field yang redundansi.
Contoh : Jika pelanggan pindah alamat dan nomer telponnya maka seharusnya ketiga file yang memuat data tersebut harus diubah/update (contoh File Anggota, File transaksi peminjaman, file Pengembalian), bila salah satu saja dari file yang mengandung data tersebut terlewat diupdate maka terjadilah tidak konsisten tadi. Untuk itu perlu adanya propagating update ketiga file tersebut.
Data Isolation : Datanya terisolasi bagi user dan untuk melihatnya harus membuka program terlebih dahulu.
Ketidakmampuannya dalam menjawab kebutuhan/pertanyaan sewaktu-waktu. Ketidakmampuan dalan sharing data yaitu elemen-elemen database dapat dibagikan
pada para user baik secara sendiri-sendiri maupun serentak dan pada waktu yang sama. Biaya pengembangan yang tinggi sehingga tidak responsif terhadap kebutuhan
perubahan.Gambar :
Aplication 1 Aplication 2 Aplication 3
File File File File File File 1-A 1-B 2-A 2-B 3-A 3-B
Pendekatan Database, berarti data bebas dari program aplikasi (Data independent)- Kemaren : Melakukan Desain- Hari ini : Dioperasikan- Yang akan datang : Dikembangkan
Gambar :
Aplication 1 Aplication 2 Aplication 3
7
Database Management System
Database
Keuntungannya : Mengurangi data redundancy : Data redundansi dapat direduksi/dikurangi, tetapi
tidak dapat dihilangkan sama sekali (untuk kepentingan keyfield) Data integrity : Integritas/kesatuan data dapat dipertahankan, hal ini sebagi akibat
dari penghindaran inconsistency dan pengontrolan security. Data independent : Kebebasan data contoh . Jika terdapat perubahan struktur file
pelanggan maka program tersebut haruslah diubah. Jadi bahwa program yang telah dibuat tidak bebas terhadap database yang ada, apapun yang terjadi pada struktur file, setiapkali hendak melihat data dengan utility List, dll. ini berarti perintah-perintah dalam paket DBMS bebas terhadap database.
Data security : Kontrol sekuriti dapat dilakukan. DBA dapat mengatur kewenganan penggunaan database (update, retrieve, delete)
Data consistency : Inconsistency dapat dihilangkan Mudah dalam penggunaan datanya Membutuhkan sedikit memory untuk penyimpanan data.
Kerugiannya :Mahal (teknologi DBMS, Operation, Conversion, Planning, Risk)
BAB IIIARSITEKTUR DATABASE
ARCHITECTURE DATABASE, tiga tahapan dalam sistem database :
External Level (Individual User Views)
Conceptual Level
8
(Community User Views)
Internal Level (Storage Views)
Keterangan :1. External Level : Setiap user mempunyai bahasa dalam penyelesaiannya
- Bahasa pemrograman : pembuat program aplikasi - Bahasa Query ex. formulir, menu : Pemakai data langsung
2. Conceptual Level : Pandangan secara menyeluruh3. Internal Level : Bagaimana secara fisik data tersimpan pada penyimpan sekunder
Contoh : External (PL/I) DCL 1 EMPP, 2 EMP# CHAR (6), 2 SAL FIXED BIN (31);
External (COBOL) 01 EMPC. 02 EMPNO PIC X(6) 02 DEPTNO PIC X(4)
Conceptual EMPLOYEE EMPLOYEE_NUMBER CHARACTER (6) DEPARTEMENT_NUMBER CHARACTER (4) SALARY NUMERIK (5)
Internal STORED_EMP LENGTH = 18 PREFIX TYPE = BYTE (6) , OFFSET = 0 EMP# TYPE = BYTE (6) , OFFSET = 6 , INDEX = EMPX DEPT# TYPE = BYTE (4) , OFFSET = 12 PAY TYPE = REALWORD , OFFSET = 16
DETAIL SYSTEM ARCHITECTURE DATABASE User A1 User A2 User B1 User B2 User B3
Host +DSL Host +DSL Host + DSL Host + DSL Host + DSL
External Shema External View A External View B
External/conceptual mapping A Mapping B DBA
DBMS Conceptual Conceptual View Schema
9
conceptual/internal mapping
Storage structure Definition Storage Database (Internal Schema) (Internal View)
User InterfaceTIGA MODEL DESAIN DATABASE :1. Conceptual model : Merupakan pengumpulan / integrasi seluruh kebutuhan atribut dari
para user / aplikasi menjadi satu pandangan organisasi2. Logical model : Dari model conceptual yang terbentuk dapat dipilih salah satu model
data dasar logikal : hirarki, network atau relational. Kemudian dibagikan kepada para user yang berwenang
3. Physical model : Bagaimana secara fisik data tersimpan pada penyimpanan sekunder, yang perlu dipertimbangkan mengenai metode akses (menyimpan / stored dan mengambil / retrieval) dan teknik pengindeksan untuk retrieval (pencarian lebih cepat).
Gambar : Model External Model External Model External Aplikasi 1 Aplikasi 2 Aplikasi 3 Kebutuhan konsepsual Aplikasi 1
Kebutuhan konsepsual Aplikasi 2 Model Model
Konsepsual Logikal Model Internal Kebutuhan konsepsual Aplikasi 3
Kebutuhan konsepsual Mapping Mapping Aplikasi 4
Keterangan :Model Konsepsual adalah permodelan data yang merupakan awal dari kegiatan mendesain database.Setiap aplikasi mengharapkan suatu kumpulan atribute. Model konsepsual merupakan pengumpulan / integrasi seluruh kebutuhan atribute dari para user / aplikasi menjadi satu pandangan organisasi. Dengan proses normalisasi seluruh atribut-atribut tersebut dikelompokkan sesuai dengan himpunannya.Model logikal : Dari model konsepsual yang dibentuk dari proses normalisasi diatas dapat dipilih salah satu model data dasar logikal : hierarchy, Network atau Relational.Setelah model data dasar logikalnya ditentukan dapat dipilih salah satu DBMS yang ada dipasaran. Contoh DBMS :- Hirarchy : IMS-2- Network : IDMS- Relational : System-R, Inggres, Oracle, DBase-2Model data logikal yang dibuat berdasarkan suatu DBMS tersebut menentukan bagaimana data diberikan pada user / aplikasi. Masing-masing user / aplikasi menerima sejumlah atribut untuk keperluannya. Kumpulan atribut yang diterima oleh user / aplikasi tersebut dinamakan Sub skema atau sub model.
10
Setelah dipilih salah satu model data logikal, model konsepsual dipertakan (mapping) ke model data logikal.Model Fisikal : Menggambarkan bagaimana secara fisik data tersimpan pada penyimpan sekunder.Pertimbangan-pertimbangan yang perlu dipikirkan antara lain : Metode akses dan teknik pengindeksan.Dengan Low Level Data Language (LLDL : Basic, fortran, cobol pascal dll.) dapat membentuk suatu model data fisikal tertentu untuk melayani kebutuhan model data logikal. Kelemahan LLDL adalah tidak mempunyai Query language.
Ad. 1. Membuat model data Konsepsual dan Proses Normalisasi- Data diasumsikan mempunyai model relational- Langkah-langkah yang dikerjakan :
1. Mengumpulkan dan menganalisa data. Data dikumpulkan dari : - Data yang telah ada (Existing Data) :
contoh : Formulir-formulir, rekening, laporan, file data yang telah ada dan program
- Data yang akan datang (Future Data).2. Proses normalisai : Yaitu proses untuk mengelompokkan atribut pada tabel-tabel
yang telah menggambarkan entity-entity dan hubungan- hubungannya satu sama lain.
3. Gambaran secara grafis : Menggambarkan hubungan entity-entity yang didapat dari proses Normalisasi. Dan melengkapi gambar entity-entity yang ada dengan Atribute-atribute dan kunci-kuncinya.
Ad. 2. Membuat model data Logical
1. Database Hierarchy : Pengaksesan data harus mengikuti aturan hierarchy yang sudah didefinisikan terlebih dahulu.
Contoh : IMS-2 (Information Management System) oleh IBM, 1968
MODEL DATA HIRARKI Database digambarkan sebagai struktur pohon / tree
Course Course# Title Descript
Prereq Offering Course# Title Date Location
Teacher Student Emp# Name Emp# Name Grade
Keuntungan :- Hirarki file-filenya mudah disesuaikan dengan hirarki kebutuhan data manajemen- Dengan menyusun beberapa model hirarki dapat diperoleh beberapa sudut pandang
pada database
11
- Memudahkan sistem security- Unjuk kerja dapat disederhanakan dengan pembatasan model data yang dibentuk
saja. Kerugian :
- File-file yang seharusnya bebas satu sama lain kadang-kadang tidak dapat dipisahkan
- Hubungan many-to-many tidak ada- Redundansi pada tingkat fisikal tidak dapat dihindari.
2. Data Network : Data membentuk jaringan yang lebih bebas dari model hierarchy. Contoh : IDMS (Integrated Database Management System) oleh Cullinett Software Inc, 1972
MODEL DATA NETWORK Komponen utama dari model data network adalah record dengan atribut-atributnya
dan set (link) ---> hubungan antar record atau atribut
Sales Sales S2 Mark Paris S4 Clark London
Sales-PartS2 P1 300 S2 P1 300 S4 P2 200 S4 P4 300 S4 P5 400
P1 A Red 12 P2 B Blue 17 P4 C Green 14 P5 D Red 11
Keuntungan :- Redundansi dapat dihindari- Kebutuhan penyesuaian model data dengan hirarki manajemen perusahaan dapat
dipenu-hi.- Hubungan many-to-many dapat dilayani.
Kerugian- Hubungan antar file kompleks, sehingga lebih membutuhkan perhatian waktu
reorganisasi file
2. Data Relational : Data dikelompokkan secara bebas menurut jenisnya lewat proses normalisasi
Contoh : - INGRES oleh UN of CA & Relational Tech., 1973 - System-R oleh IBM Research, 1975 - ORACLE oleh Relational Software Inc. , 1979 - DBASE II oleh Ashton-Tate, 1981
MODEL DATA RELATIONAL Database digambarkan dalam bentuk beberapa tabel, hubungan antar masing-masing
tabel dibangun oleh kunci-kuncinya.
PASIEN DOKTER
12
No.PasienNamaAlamatPenyakit
No. DokterNamaAlamatSpesialisasi
OPERASITgl. Opr No.Pasien Operasi No.Dokter Obat
Keuntungan :- Sederhana : digambarkan beberapa tabel dengan kunci dan hubungannya- Kebebasan data lebih besar, model internal dan metode akses tidak perlu dipikirkan
pada waktu desain- Operasi pada database langsung ke record pada masing-masing filenya.
Kerugian :- Unjuk kerja kurang efisien terutama untuk database berskala besar.
Ad. 3. Membuat model data Internal / FisikalPada bagian ini akan dibicarakan 3 hal :1. Metoda-metoda akses secara umumMetoda internal menggambarkan bagaimana secara fisik data disimpan pada penyimpan
sekunder. Untuk menyimpan (Stored) dan mengambil (Retrieval) diperlukan suatu METODA AKSES. Unjuk kerja dalam menyimpan dan mengambil ini sangat tergantung pada metoda akses yang digunakan. AKSES adalah proses untuk menyimpan dan mengambil data dari penyimpan sekunder. Metoda akses tergantung pada kemampuan Sistem Operasi yang dipakai oleh DBMS.
- Metoda akses pada sistem operasi ada 2 :- Sequential access method- Direct access method
- Metoda akses pada DBMS dapat dibedakan menjadoi 2 : 1. Metode akses model internal : Yaitu metoda akses yang berdasarkan gambaran
bagaimana data disimpan secara fisikal, menggunakan pengertian-pengertian fisik, misal : alamat, record, key dll.
contoh : - Physical Sequential (akses urut secara fisik) menghasilkan : File sequential - Record-record tersimpan dengan kunci-kunci recordnya yang urut secara logikal. - Metode akses ini dipakai baik untuk simpan dan ambil. - Akses diurut satu persatu dari awal sampai ketemu. - Record yang baru selalu disisipkan pada urutannya. - Hanya baik dipakai untuk file yang jarang berubah dan sering diakses sekuential.
Key Value Memory A A
B B C C
- Indexed Sequential (Akses lewat index untuk file sequential) - Untuk mempercepat pencarian pada file yang sudah terbentuk dengan physical
sequential disamping file data yang ada dibentuk juga index.
13
- Index berisi kunci record yang urut dan alamat recordnya yang juga urut. - Index tersebut dapat setiap kali dibentuk dalam memori prosesor dengan mengambil
dari file index. Setiap ada perubahan jumlah record file data harus disertai dengan update file index.
- Bila jumlah record besar, record-record tersebut dapat dikumpulkan dalam blok-blok.
- Penggunaan index terutama adalah untuk retrieval. Syarat utama dari penggunaan index adalah kunci record harus urut (Sorted).
Key value Blok number Blok 1 A
C 1 BI 2 CO 3 Blok 2
H IBlok3 N O
- Direct (Akses langsung pada alamat record) menghasilkan : File random / acak - Ada hubungan langsung antara kunci record dan alamat record.
Key value Address RecordX101X102 :Y100 :Y131
0102 :100 :131
- Indexed Random (Akses lewat index untuk file random) - Untuk mempercepat pencarian berdasarkan kunci record, dapat dibuatkan index
untuk kunci record tersebut disertai alamat recordnya. Kunci record pada index tersebut harus sequential.
Key value Blok number Blok 1 B
A 2 EB 1 C 3 Blok 2D 2 AE 1 D
Blok 3
C
- Hashing (Akses langsung dengan memproses lebih dulu alamat recordnya)
14
- Untuk mengatasi efisiensi penyimpanan yang boros pada kunci-record yang jarang, kunci record tersebut diproses menurut suatu algoritma hashing yang menghasilkan alamat record. Gunanya agar yang kosong terisi (padat) tetapi jangan samapai rangkap.
Key value Hashing Blok numberB RR 1.1 1.1 BE RR 1.2 1.2 ED RR 2.2 2.1C RR 3.1 3.1 AA RR 2.1 2.2 D
C
- Inverted (Akses lewat macam-macam index) - Metode akses ini hanya untuk retrieval, permintaan data mungkinlewat beberapa
kemungkinan atribute tertentu. Untuk melayani permintaan-permintaan tersebut untuk setiap atribut yang diminta dibuatkan indexnya. Setiap index terdiri dari atributnya dan alamat recordnya.
2. Metoda akses model external : Yaitu metoda akses yang berdasarkan gambaran hubungan logikal menggunakan istilah-istilah HLDL dari DBMS, misal : First record, next record dll.
Relasi antara 2 record secara external dapat dinyatakan sebagai berikut :- Sequence field relationship (Hubungan berurutan) Contoh : Next, Prior- Dependency relationship (Hubungan ketergantungan) contoh : Firts, last, sorted- Parent relationship (Hubungan ke file parent / childnya)- Near relationship (Hubungan khusus) contoh : hubungan record-record pada 1 blok
2. Implementasi metoda akses pada beberapa DBMS :1. DBMS Relational : File-file database relational relatif bebas satu sama lain, karena untuk
aksesnya dapat dipakai metoda akses :- SAM (Sequential Access Method)- DAM (Direct Access Method)- ISAM (Indexed Sequential Access Method)- IDAM (Indexed Direct Access Method)- Hashing- Inverted
- VSAM (Virtual Storage Access Method) : Yaitu membuat file data dengan index pada suatu kunci record / alamt record memakai struktur B-Tree
- OSAM (Overflow Sequential Access Method) : Yaitu membuat file data dengan struktur Linked List.
2. DBMS Hirarki : Akses pada database hirarki tidak dapat langsung kesalah satu file, melainkan harusmengikuti struktur hirarki yang ada. contoh :- HSAM (Hierarchical Sequential Access Method) - Data disimpan urut seperti pada pita sesuai urutan hirarkinya. - Record tidak dapat diinsert - Operasi yang umum adalah membuat file master baru dari file master lama.- HDAM (Hierarchical Direct Access Method)
15
- Akses hashing pada segment akar - Akses direct ke segmen cabang - Akses didalam segmen cabang dapat OSAM atau VSAM- HISAM (Hierarchical Indexed Sequential Access Method) - Akses Index pada segmen akar / root - Akses sequential pada segmen cabang / dependent- HIDAM (Hierarchical Indexed Direct Access Method) - Akses index pada segmen akar, dapat ISAM atau VSAM
- Akses direct ke segmen cabang, akses didalam segmen cabang dapat OSAM atau VSAM
3. DBMS Network : Struktur model database network lebih fleksibel dari pada hirarki, hubungan antar filenya dikerjakan dengan memasang SET antar file tersebut. Untuk menyimpan record pada storage digunakan Location Mode yang sekaligus menggambarkan metode akses internal :
- Calc : Kemampuan kases hashing- Direct- Via-Set : Membuat file yang bersangkutan diakses lewat jalur SET nya.- Index
- System : Sebagian DBMS sudah mempunyai default SYSTEM yang secara otomatis mengatur akses pada database
3. Evaluasi suatu model Internal
BAB IVSIKLUS HIDUP DATABASE
Physical Creation
2
Design Conversion 1 3
For New Aplication
Growth change & Integration maintenace 4
6
16
Operation 5
1. Design Phase : Merencanakan model data yang akan dipakai2. Phisical Creation Phase : Pembentukan phisik yaitu data mulai dibentuk atau disimpan
pada penyimpanan sekunder3. Conversion phase : Konversi dari database yang lama atau yang sudah ada ke database
yang baru4. Integration phase : Integrasi aplikasi yang sudah ada dan yang baru ke database yag
baru5. Operation phase : Pengoperasian database yang baru, contoh : manipulasi, concurency,
backup data6. Growth, change and maintenance : Adaptasi terhadap perubahan lingkungan organisasi.Fase 3 dan 4 diperlukan apabila suatu organisasi sebelumnya telah memiliki database. Apabila database yang didesain merupakan pertama dalam organisasi maka fase 3 dan 4 tidak dilakukan.
The Stages Theory Richard Nolan1. Initiation (Permulaan)- Komputer mulai dipakai - Pengolahan data terpusat pada Data Processing- User menyerahkan masalah pada Dep. DP, user tidak dilibatkan lebih jauh.- Aplikasi-aplikasi minta dikomputerkan dan masing-masing berdiri sendiri.2. Contagion (Penularan)- Manager mulai antusias dan minta macam-macam pemecahan aplikasi- Biaya pemakaian komputer tidak terkontrol dan merupakan biaya over head.- H/W dan staf DP bertambah dengan cepat.- Pengendalian DP lemah, aplikasi-aplikasi tidak terpadu.3. Control - Dipertimbangkan biaya manual dan komputer- Perencanaan dan keterpaduan aplikasi mulai dipikirkan- Pelayanan informasi untuk manajer menengah ditingkatkan dibanding level operasional
yang sudah dikerjakan dulu.4. Integration- Pemakaian teknologi baru, database dan desain sistem yang terstruktur- User dibutuhkan dalam membuat S/I- Komputer dirasa sebagai milik seluruh organisasi.5. Data Administration- Teknologi database dominan- Muncul fungsi baru (administrasi data) yaitu merencanakan kebutuhan dan pembagian data
pada para user.- User merasa sebagai pemilik data, I/S terpadu berfungsi membagi data yang
berkepentingan.6. Maturity- Komputer terpadu dengan proses manajerial- Data merupakan penunjang perencanaan strategi- Aplikasi-aplikasi mencerminkan aliran informasi.
17
Ad. 1 Design phase Merenacanakan model data yang akan dipakai : ada 3 tingkatan yaitu conceptual model, logical model, physical model.
Ad. 2 Struktur fisik databaseMETODA AKSES berarti cara retrieving, uodating, adding, inserting dan deleting.mula-mula melakukan pembentukan fisik, dengan DDL mendefinisikan dan membentuk (create) file-file database.keterangan metode akses :Yaitu untuk menyimpan (stored) dan mengambil (retrieval) dari penyimpanan sekunderMetoda akses pada DBMS :1. Metoda akses model internal yaitu Data disimpan secara fisik- Physical Sequential : Akses urut secara fisik (file sequential)- Indexed Sequential (ISAM): Akses lewat index untuk file sequential- Direct : Akses langsung pada alamat record (file random)- Indexed Random : Akses lewat index untuk file random- Hashing : Akses langsung dengan memproses lebih dahulu alamat
recordnya- Inverted : Akses lewat macam-macam index (bolak-balik).2. Metode akses model external yaitu metode akses yang berdasarkan gambaran hubungan
logical menggunkan istilah-istilah HLDL dari DBMS.Macam-macam : VSAM (Virtual Storage Access Methode)yaitu membuat file data dengan indeks pada suatu kunci record / alamat record memakai
struktur B-Tree OSAM (Overflow Sequential Access Methode)yaitu membuat file data dengan struktur link-list HSAM (Hirarchical Sequential Access Methode)Yaitu data disimpan urut seperti pada pita sesuai dengan urutan hirarkinya HISAM (Hirarchical Indexed Sequential Access Methode)Akses index pada segmen akar/root, akses sequential pada segmen cabang HDAM (Hirarchical Indexed Dirrect Access Methode)Yaitu akses hashing pada segmen akar, akses direct ke segmen cabang HIDAM (Hirarchical Indexed Dirrect Access Methode)Yaitu akses index pada segemen akar, akses direct ke segmen cabang
Ad. 3. KonversiKonversi adalah pengubahan data dari sistem database lama ke sistem database baru.Masalah-masalah yang dihadapi adalah :- Format file yang berbeda- Model database yang berbedaKonversi ini adalah pekerjaan besar yang dilakukan berangsur-angsur dan membutuhkan waktu cukup lama.
Ad. 4. IntegrationIntegrasi adalah penyatuan dari program-program aplikasi untuk database yang baru.
Ad. 5. Operasi Database
Beberapa hal yang harus diperhatikan :
18
1. Entry dan updateLangkah pertama pengoperasian pada database adalah entry dan menyimpan data. Bila ada
kesalahan atau perubahan dari data tersebut, maka data yang telah tersimpan tersebut dapat diperbaiki.
Ada 2 cara entry dan update :a. Entry / update by individual record : Seluruh data item dari formulir data entry / update
disimpan / diambil dan disimpan kembali pada 1 record dalam 1 file.b. Entry / update by transaction : data item dari formulir transaksi dapat diambil / disimpan
dari pada beberapa record dari beberapa file. Data-data item formulir transaski tidak disimpan pada 1 record, selain pada file utamanya (master file), transaski dapat disimpan pada suatu file transaksi.
2. Backup dan recoverySekali database diimplementasikan, salah satu fungsi yang harus dipelihara adalah
tersedianya data setiap saat untuk para user. Backup adalah pekerjaan menduplikasikan record-record database atau menyimpan
perubahan-perubahan pada database.Recovery adalah proses untuk memperbaiki kembali database dari kerusakan yang
dialaminya. kerusakan ini umumnya adalah kerusakan fisik pada penyimpanan sekunder.Ada 3 macam cara backup :a. Dump : Menduplikat seluruh record database yang dioperasikan pada database backupb. Transaction Log : Menyimpan transaksi-transaksi yang merubah database.c. Image Log : Menyimpan record-record database sesudah / sebelum perubahan pada
database.
Ada 6 cara recovery :a. Dual recordingb. Periodic dumpc. Periodic dump dan transaction logd. Periodic dump dan logging after imagee. Periodic dump dan logging before image dan transaction logf. Residual dump.
3. Reorganization Dalam sistem database pada suatu saat dapat diadakan pembersihan database terhadap
record-record yang tidak digunakan secara aktif lagi. Hal ini dimaksudkan untuk mempercepat akses pada database yang terganggu dengan banyaknya record-record yang tidak / jarang digunakan itu, record-record yang tidak aktif tersebut dapat dipindahkan dan disimpan dalam suatu file. Proses pembersihan record-record tidak aktif itu dinamakan reorganization, metode reorganization ini tergantung dari model database yang dipilih.
4. RestructuringPada suatu lingkungan yang dinamis setelah suatu periode berjalan pasti dirasakan kebutuhan
perubahan, contoh :- Penambahan/penghapusan suatu data elemen, perubahan ukuran/size suatu data elemen,
pertukaran data elemen antar file tersebut.- Perubahan metode akses.- Proses perubahan model internal dan sekaligus logikal.
19
5. Monitoring, performance and tuning- Evaluasi secara periodik terhadap unjuk kerja sistem database, dapat pada ketepatan data
atau kelambatan unjuk kerja.- Kekurangan-kekurangan ini hendaknya diperbaiki dan dilaraskan/tuning.
6. SecuritySekurity data sangat penting dalam sistem database, artinya mengontrol pengaksesan data
dalam database terhadap orang-orang yang tidak berwenang, sehingga mencegah :- Penyingkapan rahasia.- Perubahan data.- Perusakan / penghapusan data.Ada 3 kelompok yang berhubungan dengan Database :
a. USER : User adalah pemilik data, user sesuai dengan departemennya hanya berwenang untuk sekumpulan data tertentu, kontrol pengaksesan data oleh user ini dapat dilakukan dengan memakai PASSWORD. Password ini mempunyai 2 fungsi :- Memberi identifikasi seorang user - Memberi kewenangan (authorized) dan hak (privilege)Macam-macam hak pada sistem database adalah :1. Hak retrieval : Mencari dan melaporkan2. Hak Update pada record meliputi :
- Append (Menambah)- Insert (Menyisipi)- Modifikasi (Merubah)- Delete (Menghapus)
3. Hak Definition File meliputi :- Create (Mendefinisikan file baru)- Revision (Merevisi definisi yang ada)- Delete (Menghapus seluruh file)- Memberi indeks
4. Hak GRANT (memberi) hak pada user.Hak definition dan Grant hanya dimiliki oleh DBA.
Pembuatan Password harus mengingat hal-hal :1. Mudah diingat2. Sulit ditebak3. Cukup sering diubah4. Kerahasiaannya terlindungi.
b. Programer : Ada 2 macam programer- Aplication Programer : Programer yang membuat program-program aplikasi
- System programer : Programer yang membuat program-program sistem yang memadukan dan mengontrol program-program aplikasi. misal : membuat menu untuk masing-masing user, membuat log transaction pemakaian program dan database dari para User dan sebagainya, dan harus mentaati prosedur password.
c. Operation staff : Sering kerusakan data disebabkan operator yang kurang terlatih atau petunjuk yang kurang memadai atau sulit dipahami. Operator yang berhubungan dengan database adalah operator program aplikasi.Pengamanan data baginya antara lain berupa :
20
- Validasi yang cukup pada data entry- Pengecekan jumlah counter dengan formulir data- Operator database (staff DBA)- Pada saat backup, data harus mendapat perhatian.
Desain Steps Of database ProjectLangkah-langkah fase desain dengan 3 model database :
Review andEvaluation
V
Identifying the A. Building a conceptualvital entities for Defining modelthe enterprise the entities Building B. Building a logical + and the a data modelIdentifying potensial relationship Dictionary C. Building a physical aplication for the modeldatabase II III
IV I
Keterangan :1. Mengidentifikasikan entity-entity vital dalam enterprise dan identifikasi aplikasi potensial
untuk database2. mengidentifikasi entity dan hubungan (relationship ) pada model data
Hubungan (relationship) dapat terjadi dari :- Hubungan antar himpunan (entity)- Hubungan antar atribut pada 1 himpunan (entity)- Hubungan antar atribut pada beberapa himpunan (entity)Ada 3 macam hubungan :- One - To - One : Contoh Pasien - Rekening- One - To - Many : Contoh Kamar - Pasien- Many - To - Many: Contoh Pasien - Dokter
3. Membuat data dictionary- Membuat model data konsepsual- Membuat model data logikal- Membuat model data fisikal4. Review dan evaluasi
CENTRALIZED VS DISTRIBUTED DATABASE SYSTEM
Centralized Database system adalah database yang dikontrol secara terpusat.Keuntungan : Redundansi dapat dikurangi :Redundansi yaitu duplikasi field yang sama pada beberapa file. Redundansi dapat direduksi
tetapi tidak dapat dihilangkan sama sekali (misal untuk kepentingan field kunci) Inconcistency dapat dihilangkan:Pada field-field yang mempunyai redundancy harus diingat bila diadakan update seluruh
field yang redundadnsi tersebut harus serentak diupdate (propagating update).
21
Memudahkan shared data :Database terpusat adalah milik umum sehingga dapat dibagikan antar aplikasi. Aplikasi baru
dapat dibuat dengan memakai database yang telah ada. Data dapat distandarkan dengan data dictionary : Dengan keadaan telah distandarkan
dimungkinkan pertukaran data antar sistem. Kontrol security dapat dilakukan :DBA dapat mengatur kewengan penggunaan database (update, retrieve, delete dan
sebagainya). integritas dapat dipertahankanHal ini adalah sebagai akibat dari penghindaran non konsistensi dan pengontrolan sekuriti. Pertentangan kebutuhan antar user dapat diatasi :Database dibangun dengan prioritas kepentingan seluruh enterprise.
Kekurangan : Data terpusat berarti data adalah milik umum, hal ini menyebabkan rasa memiliki dan
tanggung jawab pada data dari masing-masing user menjadi berkurang Kemampuan pembagian data menyebabkan terjadinya pelanggaran wewenang dan
sekuriti data Kedua hal diatas dapat diatasi dengan tambahan suatu sistem sekuriti dan hal ini
berarti penambahan biaya pada sistem.
Distributed database system yaitu database yang ada tersebar pada masing-masing terminal yang saling berhubungan lewat jalur komunikasi.Database tersebar pada tingkat konsepsual dan logikal hendaknya dianggap seperti database terpusat, perbedaannya hanya pada tingkat internal / fisikal.Keuntungan : Kontrol local oleh DBA ditambah proteksi terhadap transmisi komunikasi Pengontrolan sedikit ditekankan.Kekurangan : Lebih komplek masalahnya dalam DBMS.
22
BAB VENTITY RELATIONSHIP MODELS
Alasan menagapa ER model diperlukan dalam memodelkan sebuah data : ER Model merupakan model data tingkat tinggi yang popular untuk model data
relasional walau sifat abstrak dan popular, namun aspek notasi, symbol itu sama atau maknanya sama
ER model ini dengan sejumlah variasinya seringkali digunakan dalam desain konseptual dari aplikasi basisdata (banyak alat untuk mendesain sebuah basis data yenga melibatkan ER Model didalamnya)
Penggunaan Model Data Konseptual Tingkat Tinggi untuk Desain Basis DataTahapan dalam Mendesain sebuah Basis Data dapat digambarkan pada gambar dibawah ini Tahap :
Miniword
Functional Requirements Database Requirements
23
Functional Analysisis Conceptual Design
Requirements Collection and analysis
High Level Transaction Conceptual Schema Specification (In a HighLevel Data Model)
DBMS-independent
DBMS Specific
Logical (conceptual) Schema (In the data model of a specific DBMS)
Internal Schema
Application Programs
Contoh Aplikasi Basis Data ER-ModelUntuk keperluan penjelasan konsep dan penggunaan ER Model, digunakan satu contoh basis data “Company” yang didalamnya terdapat :
- Data Pegawai (Employees)- Data Departemen (Departement)- Dataya Proyek (Project)
Keterangan sebagian darai data Company yang diperoleh setelah fase pengumpulan dan analisis kebutuhan sbb:1. Company terdiri dari sejumlah departement dimana setiap departement mempunyai
satu nama yang unik, nomor yang unik. Dan seorang pegawai (employee) yang bertindak sebagai manager dari departement, tanggal mulai bekerja dan manager juga dicatat dalam database (tanggal Pengangkatan sebagai manager).Satu departement dapat tersebar di sejumlah lokasi.
1 N Memiliki
Nama
Nomor
1 N Manages Lokasi
Tgl Pengangkatan
24
Application Program Design
Logical Design(Data Model Mapping)
Physical Design
Transaction Implementation
Company
Departement
Employee
Project
2. Suatu departement dapat mengendalikan sejumlah project. Setiap project mempunyai satu nama yang unik, nomor yang unik dan satu lokasi tertentu
1
Control Nama
M Nomor
Lokasi
3. Data pegawai yang perlu dicatat berupa : nama, social security, number, address, salary, sex dan birthdate.Seorang pegawai ditempatkan disatu departement, tetapi dapat bekerja di sejumlah project yang tidak harus dikendalikan oleh departement dimana pegawai tersebut ditempatkan.Jumlah jam per minggunya dari pegawai yang bekerja pada suatu project perlu
dicatat, juga perlu dicatat supervisor langsung dari setiap pegawai
Sex Salary MidFirst
Last SSN
BD Nama Address
N Workfor 1
1 N N 1Tgl kerja
Supervision M 1 Manajer
Jam Kerja Control N
Work-on N
NamaNomor Lokasi
4. Untuk tujuan / keperluan asuransi pegawai, maka perlu dicatat anggota keluarga dari setiap pegawaiData yang dicatat berupa : nama anggota keluarga, sex, birthdate dan hubungan keluarga
1
DepentOf
25
Departement
Pegawai Departemen
Project
Pegawai
N
Nama Hubungan birthdate Sex
Dari keempat contoh diatas dapat digabungkan gambarnya menjadi satu gambar ER-Model
3.1. Entity Types, Entity Sets, attributes, And Keys1. Entity dan attribute
Entity adalah Segala sesuatu yang berhubungan dengan dunia nyata, bisa berupa objek yang berdasarkan file yang disimpanAttribute adalah segala sesuatu yang ingin diketahui dari suatu entitya. Simple (atomic) attribut adalah attribute yang bernilai atomic (tidak dapat
dipecah lagi)Ex. Umur, Jenis Kelamin dst
b. Composite attribute adalah attribute yang masih bisah dipecah lagi atau mempunyai sub attributeEx. Nama (Nama Depan, Nama Tengah, Nama belakang) dst
c. Singgle Value Attribute adalah nilai dari suatu attribute yang hanya mempunyai nilai satu (single) nilai dari attribute yang bersangkutanEx. Setiap Pegawai hanya mempunyai nilai satu (single) attribute “umur (15 tahun)”
d. Multivalued Attribute adalah nilai dari suatu attribute yang mempunyai lebih dari satu (multivalue) nilai dari atrribute yang bersangkutanEx. Setiap Mobil bisa mempunyai banyak (multivalued) atrribute “warna (putih, merah, dst)”
e. Store Attribute adalah attribute yang harus disimpan dalam database Ex. NIP, Nama dst
f. Derived Attributte adalah attribute yang tidak harus disimpan dalam databaseEx. Total
g. Null Value adalah atrribute yang tidak mempunyai nilai (value)Ex. Pegawai yang tidak mempunyai No. Telp Rumah
h. Complex Attribute adalah attribute lengkap yang mempunyai multivalued dan composite attributeEx. (AlamatTelp {Telp (Kodearea, NoTelp) }, Alamat (AlamatJalan (No,Jalan,NoBlok),Kota,Negara,Kodepos))}Dimana ( ) = Composite Attribute { } = Multivalued Attribute
2. Entity Types, Entity Sets, Keys dan Value Setsa. Tipe Entity adalah atribut-atribut yang seusai dengan entity b. Set Entity adalah kumpulan attribute-atribute dari suatu entityc. Key Atribute adalah kunci attribute yang unik yang membedakan antara record
yang satu dengan record yang laind. Set value (Domain) adalah isi/nilai dari entity
Ex. Employee
26
Dependent
Key Tipe Entity
Nip Name Age Salary
N01 Bella 18 500000 Domain
N02 Ruben 20 800000 (age(18,20))
3. Inisialisasi Desain Konsepsual pada database COMPANYKebutuhan dari database company
a. Entity Departemen Nama, Nomor, {Lokasi}, Manager, Managerstartdate
Key : Nama dan Nomor Multivalued : Lokasi
b. Entity ProjectNama, Nomor, Lokasi, KontrolDepartemenKey : Nama dan Nomor
c. Entity EmployeeNama(Namadepan, Namatengah, Namabelakang), SSN, Sex, Address, Salaray, Birthdate, Departemen, Supervisor,{WorksOn(Project, Hours)}Composite Attribute : Name
Complex Attribute : WorksOnd. Entity Dependent
Employee, Dependentname, Sex, Birthdate, Relationship
3.2. Relasi, Tipe relasi, Tugas dan Pembatas Struktur1. Tipe Relasi, Set dan Kejadian
A. One To One : Terjadi bila tiap anggota entity A hanya boleh berpasangan dengan satu anggota
1 1
B. One To Many : Terjadi bila tiap anggota entity A boleh berpasangan dengan lebih dari satu anggota B tetapi tidak berlaku sebaliknya
1 N
C. Many To Many : Terjadi bila tiap anggota A boleh berpasangan dengan lebih dari satu anggota B dan berlaku untuk sebaliknya
M N
27
A B
**
###
A B
**
###
A B
**
###
2. Derajat Relasi, Penamaan Rules dan Relasi RekursifDerajat : Menyatakan jumlah anggota entity yang terlibat didalam ikatan yang terjadiDerajat Relasi pada Type Relasi adalah jumlah partisipasi type entityAda 2 tipe derajat relasi yaitu derajat dua (Binary) dan derajat tiga (ternary)Binary relationship adalah relasi yang melibatkan dua entity, ex. Hubungan work_for antara entity Employee dan DepartementTernary relationship adalah relasi yang melibatkan tiga entity, Ex Hubungan Suply antara entity supplier, part dan projectPenamaan Rules adalah Penamaan dari sebuah hubungan misalnya Hubungan Work-For terdiri dari entity Employee yang bisa menjadi Employee atau Worker dan entity Departement bisa menjadi Departemen atau employerRelasi Rekursif adalah hubungan yang terjadi pada dirinya (entity) sendiri, Ex. Hubungan Supervision pada entity Employee dimana bisa sebagai supervisi atau sebagai supervisor
3. Pembatas pada tipe Relasi
Relasi
Habis Habis TOTAL Habis Sisa PARSIAL
Partisipasi : Menyatakan sifat keterlibatan tiap anggota entity dalam ikatan terjadinya hubungan.A. Wajib
Hubungan dosen dengan mata kuliah
1 1
Tiap dosen harus mengajar satu mata kuliahTiap mata kuliah harus diajar oleh seorang dosen
B. Tidak Wajib Hubungan dosen dengan mata kuliah
Tiap dosen harus mengajar satu mata kuliahTiap mata kuliah mungkin diajarkan oleh seorang dosen dan mungkin belum ada yang mengajar.
: Total Partisipasi E2 dalam R R
28
Dosen Mata Kuliah
Dosen Mata Kuliah
E1 E2
1 R N : Cardinality Ratio 1:N untuk E1=E2
dalam R
(Min,Max) : Structural Constraint (Min,Max) R Dalam partisipasi E didalam R
Min 1 : Total Relasi
Min 0 : Parsial Relasi
4. Attribute pada tipe RelasiINTERN : Suatu entity terdapat hubungan intern antar anggota entity
A. Derajat 1:1 :
Kawin
Sensus Penduduk : Person (entity) terjadi hubungan kawin
Identitas person : Id_Person
Identitas table kawin Id_Person_Suami
Id_Person_Istri
B. Derajat 1:M : 1 Supervisi M
Suatu perusahaan terdapat daftar karyawan dalam melaksanakan
pekerjaannya tentunya ada karyawan yang menduduki jabatan
pimpinan dari sejumlah karyawan
(Misal kepala departemen)
Salah satu tugas seorang pimpinan : melakukan supervisi para
karyawan yang menjadi anak buahnya (hubungan 1:M)
C. Derajat M:N : N Team OR (No_Mhs,…..) (No_Mhs_Ketua, M No_Mhs_Angg.)
Mahasiswa disuatu fakultas akan membentuk tim Olah Raga (Team
OR). Ditentukan bahwa setiap mahasiswa boleh menjadi anggota lebih
29
Person
Karyawan
Mahasiswa
E1 E2
E2
dari satu jenis team.
Dalam satu team OR harus ada ketua team yang dipegang oleh
mahasiswa.
Seorang mahasiswa yang menduduki ketua team boleh menjadi anggota team OR lain
3.3. Tipe Entity WeakAdalah suatu entity yang tergantung pada entity yang lain, tidak akan ada kalau tidak ada entity master (identik dengan entity Transaksi)Ex. Entity Penjualan : Entity ini tidak akan ada kalau tidak ada entity barang, Sales dan Customer
3.4. Contoh Hubungan Desain Entity Relationship pada Database COMPANYa. Manages : terjadi hubungan 1:1 antara Employee dan Departemen
Maksudnya adalah satu Employee hanya akan menjadi manager pada satu departemen begitu sebaliknya satu departemen hanya dimanager oleh 1 employee
b. Work_For : terjadi hubungan 1:N antara Departemen dengan EmployeeMaksudnya adalah satu Departemen mempekerjakan banyak employee tetapi sebaliknya satu employee hanya bekerja pada satu departemen
c. Controls : terjadi hubungan 1:N antara Departemen dan ProjectMaksudnya adalah satu Departemen mengendalikan banyak Project tetapi sebaliknya satu Project hanya dikendalikan oleh satu Departemen
d. Supervision : terjadi hubungan 1:N antara Employee Supervisor dengan Employee supervisi
e. Work_On : terjadi hubungan M:N antara Employee dengan ProjectMaksudnya adalah satu Employee bisa mnegerjakan banyak project begitu sebaliknya satu project bisa dikerjakan dengan banyak employee
f. Dependents_Of : terjadi hubungan 1:N antara Employee dan Dependence
3.5. ER Diagram, Penamaan dan Masalah DesainNotasi dan Penamaan Untuk Konstruksi Skema Diagram ER
No Simbol Keterangan1. : Entity Type
Suatu yang ada (secara eksplisit ada) namun keberadaannya dapat nyata
dapat virtual, serta perbedaan antar entity harus jelas
Ex. Pegawai, Departemen
30
2. : Weak entity Type
Suatu entity yang tidak punya key atribut, keberadaannya tidak perlu
berdiri sendiri / diluar system
Didalam weak entity dimungkinkan 1 weak mempunyai banyak entity
Setidak-tidaknya memiliki 1 relasi
Ex.
3. : Attribute
Keterangan yang dimiliki entity / sifat-sifat yang melekat pada entity yang
perlu dicatat
Ex. Pegawai : Nopeg, Nama, Alamat, Jenis Kel, tgl. Masuk
4. : Key Attribute
Bila didalam attribute terdapat nilai sama, maka kita perlu membuat “Key
attribute” sehingga dipastikan tidak akan terjadi nilai/record sama
Ex. Pegawai : sebagai key adalah NoPeg
NoPeg Nama Alamat
P01 Bella Malang
P02 Bella Batu
5. : Multivalued Attribute
Satu entity yang memiliki 2 attribute sama
Ex. Departemen yang memiliki 2 lokasi pabrik
Lokasi
Hal ini bukan berarti bias untuk orang yang mempunyai 2 nama atau 2
alamat
6. : Composite Attribute
Attribute yang mempunyai nilai attribute lebih dari Satu
Ex. Nama : Nama Depan Alamat : Jalan Nama Tengah Nomer Nama Belakang Kota
31
Salary
Karyawan Departemen
Departemen
7. : Derived Attribute
Merupakan kombinasi dari attribute-attribute dimana keberadaannya tidak
perlu disimpan
Ex.
Nilai
8. : Relationship Type
Menyatakan hubungan antar attribute sehingga terjadi pemetaan
Ex. M Bisa N ambil
Range Domain Kodomain
Hasil Dari Relasi :
- One To One (1:1)
- One To Many (1:N)
- Many To Many (1:M)
9. : Identifying Relationship Type
Bila entity mempunyai hubungan lebih dari satu entity lain
Mengapa ER Model :- Model data tingkat tinggi yang populer untuk model data relasional- Banyak Database Desain Tools yang melibatkan ER ModelPembahasan :- Konsep Dasar Penyusunan dan Constraint dari ER Model- Penggunaan ER Model dalam desain skema Konseptual
Jenis Data Independence :1. Logical Data Independence :
- Kemampuan untuk merubah skema konseptual tanpa harus merubah skema eksternal- Hanya definisi dari view dan mapping yang perlu diubah dalam DBMS- Constraint dari basis data juga dapat diubah dalam skema konseptual tanpa
mempengaruhi skema eksternal2. Physical Data Independence :
32
MHS Mata Kuliah
Mahasiswa Mat. Kul
*
*
####
E1
E2
- Kemampuan untuk merubah skema internal tanpa haarus merubah skema konseptual (atau eksternal)
- Lebih mudah untuk dilakukan daripada logical data independence karena adanya isolasi aplikasi dari susunan penyimpanan fisik.
CONTOH Untuk keperluan penjelasan konsep dan penggunaan ER Model,digunakan satu contoh database “COMPANY” yang didalamnya mencatat :
- Data Pegawai (Employee)- Data Departemen (Department)- Data Proyek (Projects)Penjelasan dari sebagian data Company yang diperoleh setelah fase “Requirement collection and analysis” sbb :1. COMPANY terdiri dari sejumlah Departements. Setiap department mempunyai satu
nama yang unik, nomor yang unik, dan seorang pegawai (Employee) yang bertindak sebagai manager dari department. Tanggal mulai bekerja dari manager juga dicatat dalam database (Tanggal pengangkatan sebagai manager).Satu department dapat tersebar di sejumlah lokasi.
2. Suatu department dapat mengendalikan sejumlah project. Setiap project mempunyai satu nama yang unik, nomor yang unik, dan satu lokasi tertentu.
3. Data pegawai yang perlu dicatat berupa : nama, social security number, address, salary, sex dan birthdate.Seorang pegawai ditempatkan di satu department, tetapi dapat bekerja di sejumlah project yang tidak harus dikendalikan oleh department dimana pegawai tersebut ditempatkan.Jumlah jam perminggunya dari pegawai yang bekerja pada suatu project perlu dicatat. Juga perlu dicatat supervisor langsung dari setiap pegawai.
4. Untuk tujuan/keperluan asuransi pegawai, maka perlu dicatat anggota keluarga dari setiap pegawai. Data yang dicatat berupa : nama anggota keluarga, sex, birthdate dan hubungan keluarga (relationship)
Alternative Notasi untuk ER Model ex (Min,Max)
Fname Mname Lname Address Number Name
(4,N)SSN Name Sex Salary Works-For Location(1,1) Department
Bdate Employee NumbOfEmp Startdate
(0,1) (1,1) Department (0,N) Manager Managed Controlling
Manages Department Worker
(0,N) (0,1) (1,N) ControlsSupervisor Supervisee Hours Controlled Supervison Project (1,N) (1,1)
Works-On
33
Employee
Department
Project (0,N)
Employee
Name Number Location Dependents_Of
dependent(1,1)
Relationship
Name Sex BirtDate
BAB VIMODEL DATA RELASIONAL
ALGORITMA PEMETAAN
KONSEPModel Relasional menyajikan database sebagai satu kumpulan relasi atau dapat
juga disebut TabelTerminology :
Sebuah baris disebut TupleSuatu header dalam kolom di sebut AttributeTabel disebut RelasiEx. R(A1,A2,A3,……,An)Satu nama Relasi R dan satu List Attribute A1,A2,A3,….AnTipe data yang menjelaskan jenis-jenis nilai yang dapat muncul dalam setiap kolom disebut DomainEx. Dom(A1)
Domain “NRP-Mahasiswa” Satu set dari 10 digit
CONSTRAINTSDomain Constrain : Nilai dari setiap attribut suatu relasi harus atomic dari
domain attribut tersebutKey Contraints : Suatu relasi adalah satu set tuple, dan semua elemen dalam satu
set harus berbedaEntity Integrity Contraint : Dalam suatu relasi, tidak boleh terdapat “Primary
Key” yang bernilai kosong (Null).
REFERENTIAL INTEGRITY CONSTRAINTBerkaitan dengan konsep Foreign Key (kunci tamu) antara dua buah relasi yang mempunyai “Relatioship”Konsep Foreign Key :Untuk dua relasi R1 dan R2, satu set attribute FK dalam skema R1 disebut sebagai kunci tamu dari R2, jika memenuhi 2 aturan sbb :
34
Project
Dependent
1. Attribute-attribute dalam FK mempunyai domain yang sama dengan attribute-attribute yang menjadi primary key dalam skema realsi R2 ---- Attribut-attribut FK dikatakan mengacu pada relasi R2
2. Suatu nilai FK dalam suatu tuple t1 dalam R1 mungkin :a. Berupa suatu nilai PK dalam sembarang tuple t2 dalam R2b. Atau bernilai kosong (Null)
ALGORITMA PEMETAAN SKEMA ER KE RELASIONAL
1. Untuk setiap entity type E (pemilik/owner) yang regular (non-weak) dalam skema ER, buat satu relasi R yang attributnya meliputi semua simple attribut dari E.Untuk Composite Attribute : hanya komponen simple attributenya yang dipetakan.Kemudian pilih satu key attribute dari E sebagai Primary Key dari R. Jika key terpilih adalah composite, maka primary key dar R dibentuk dari satu set simple attribute dari compositeCatatan : Dalam langkah pertama ini, keterlibatan Foreign key dan relationship attribute jangan dipikirkan dulu.
2. Untuk setiap Weak Entity type W dalam skema ER dengan owner entity type E, buat satu relasi R yang attributtenya meliputi semua simple attribute (atau simple component dari composite Attribute)Masukkan sebagai Foreign key dalam R, Primary key attribute dari Relasi-relasi yang merupakan owner/pemilik dari W (dapat diperoleh dengan memperhatikan identifying relationship type dari W)Primary Key dari R dibentuk dari kombinasi antara primary key dari relasi-relasi pemilik dan partial key dari weak entity type W (jika ada)
3. Untuk Setiap binary 1:1 relationship type R dalam skema ER, perhatikan relasi-relasi S dan T yang berkorespondensi dengan entity type yang berpartisipasi dalam R. Kemudian ambil salah satu relasi (misalkan S) dan cantumkan primary keydari T sebagai foreign key. Untuk kasus ini, S sebaiknya dipilih dari entity type yang berpartisipasi total
dalam R (untuk mengurangi null values dari foreign key yang diambil dari T).
Catatan : Sebagai Alternatif untuk pemetaan dari 1:1 relationship type ini adalah dengan menggabungkan kedua entity type menjadi satu relasi tunggal. Cara ini terutama sangat sesuai bilamana kedua entity type berpartisipasi total dan tidak berpartisipasi dalam relationship type lain.
4. Untuk setiap regular (non-weak) binary 1:N relationship type R. Perhatikan relasi S yang mewakili entuty type yang berpartisipasi pada sisi-N dari relatinship type.Cantumkan primary key dari relasy yang berperan pada sisi-1 (Primary) sebagai foreign key dalam S (Sisi-N); karena setiap entity instance pada sisi-N terkait dengan paling banyak satu entity instance pada sisi-1 dari relationship type.
35
Kemudian cantumkan semua simple attribute (atau simple component dari composite attribute) dari 1:N relationship type sebagai attribute dalam S.
5. Untuk setiap binary M:N relationship type R, buat satu relasi baru S untuk mewakili R.Kemudian cantumkan sebagai foreign key dalam S, Primary key dari relasi-relasi yang mewakili entity type yang berpartisipasi dalam R; dimana kombinasi dari primary key ini akan membentuk primary key dan juga cantumkan semua simple attribute dari M:N relationshp type sebagai attribute dari S.Catatan : Binary relationship 1:1 atau 1:N selalu dapat dipetakan seperti pada M:N relationship. Alternatif ini terutama sangat berguna bilamana relationship instance yang ada sangat sedikit (menghindari null values dalam foreign key)Untuk kasus ini, primary key dari relasi “Relationship” hanya akan menjadi foreignkey dari satu “Entity” relasi yang berpartisipasi. Jadi untuk 1:N relationship, hanya pada sisi_N, sedang untuk 1:1 adalah entity relasy yang berpartisipasi total (jika ada) yang dipilih.
6. Untuk setiap multivalued attribute A, buat satu relasi baru R yang mencantum-kan satu attribute yang mewakili A ditambah dengan Primary key (sebagai foreign key dalam R) dari relasi yang mewakili entity type atau relationship type yang mempunyai A sebagai attributenya.
Primary key dari R adalah kombinasi A dan R. Jika multivalued attribute adalah composite, maka hnya simple componentnya yang dicantumkan.
7. Untuk setiap n-ary relationship type R (n>2), buat satu relasi baru S yang mewakili R. Cantumkan sebagai foreign key attribute dalam S, Primary key dari relasi-relasi yang mewakili entity type yang berpartisipasi. Juga cantumkan sebagai attribute dalam S, semua simple attribute dalam n-ary arelationship type.Primary key dari S biasanya berupa kombinasi dari semua foreign key yang mengacu pada relasi-relasi yang mewakili entity type yang berpartisipasi. Namun jika “participation constraint’ (min,max) dari satu entity type E yang berpartisipasi dalam R mempunyai max = 1, maka primary key dari S berupa set satu foreign key attribute yang mengacu pada relasi E. ini dilakukan karena setiap entity instance e dalam E hanya akan berpartisipasi paling banyak satu relationship instance dalam R, sehingga dapat secara unik mengidentifikasi relationship instance dari R.
36
BAB VIIALJABAR RELASI
SSN FName Minit LName BDate Address Sex Salary SuperSSN DNo
EMPLOYEE
DNumber
DName MgrSSN MgrStartDate
DEPARTEMENT
DNumber DLocation
DEPT_LOC
PNumber PName PLocation DNum
PROJECT
ESSN PNo Hours
WORKS_ON
ESSN DependentName
Sex BDate Relationship
DEPENDENT
37
DATABASE COMPANY
EMPLOYEEFNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO
John B Smith 123456789 1/9/65 731 Fondren Houston, TX M 30000 333445555 5Franklin T Wong 333445555 12/8/55 638 Voos Houston, TX M 40000 888665555 5
Alice J Zelaya 999887777 7/19/68 3321 Castle Spring, TX F 25000 987654321 4Jennifer S Walace 987654321 6/20/41 291 Berry Bellaire, TX F 43000 888665555 4
Rames K Narrayan 666884444 9/15/62975 Fire Oak, Humble, TX M 38000 333445555 5
Joyce A English 453453453 7/31/72 5631 Rice Houston, TX F 25000 333445555 5Ahmad V Jabbar 987987987 3/29/69 980 Dalas, Houston, TX M 25000 987654321 4James E Borg 888665555 11/10/37 450 Stone, Houston, TX M 56000 null 1Ruben B Ella 999999999 1/1/00 123 Gottham City, TX M 20000 999887777 5
DEPENDENTESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP
333445555 Alice F 4/5/86 Doughter333445555 Theodore M 10/25/83 Son333445555 Joy F 5/3/58 Spouse987654321 Jennifer F 2/28/42 Spouse123456789 Michael M 1/4/88 Son123456789 Alice F 12/30/88 Doughter123456789 Elizabeth F 5/5/67 Spouse
DEPARTEMENTDNAME DNUMBER MGRSSN MGRSTARTDATEResearch 5 333445555 5/22/88
Administration 4 987654321 1/1/95Headquarters 1 888665555 6/19/81
DEPT_LOCATIONS
38
DNUMBER DLOCATION1 Houson4 Stafford5 Bellaire5 Sugarland5 Houston
PROJECTPNAME PNUMBER PLOCATION DNUMProductX 1 Bellaire 5ProductY 2 SugarLand 5ProductZ 3 Houston 5
Computerization 10 Stafford 4Reorganization 20 Houston 1NewBenefits 30 Stafford 4
WORKS_ONESSN PNO HOUR
123456789 1 32.5123456789 2 7.5666884444 3 40453453453 1 20453453453 2 20333445555 2 10333445555 3 10333445555 10 10333445555 20 10999887777 30 30999887777 10 10987987987 10 35987987987 30 5987654321 30 20987654321 20 15888665555 20 0333445555 1 15333445555 30 20
ALJABAR RELASI
Sekumpulan operasi untuk memanipulasi relasi, dibagi 2 kelompok yaitu :1. Operasi yang melibatkan teori set : Union, Intersection, Difference da Cartesian
product2. Operasi yang khusus untuk basis data relasional seperti Select, Project dan Join
OPERASI SELECT
39
Digunakan untuk memilih satu subset tuple dalam suatu relasi yang memenuhi suatu kondisi pemilihanBentuk Umum : <Kondisi Pemilihan> (<Nama Relasi>)Ket : : Select Operator
<Kondisi Pemilihan> : Ekspresi Boolean (Where)Terdiri sejumlah clausa yang mempunyai bentuk :- <nama atrribute><operator perbandingan><nama konstanta>- <nama attribute><operator perbandingan><nama attribute>Operator Perbandingan : =, <, , > ,, untuk domain yang nilainya terurut
Suatu clause dengan clause yang lian dapat dihubungkan dengan Operator Boolean yaitu AND, OR, NOT <cond1> AND <cond2> AND ….AND <condn> ( R ))Operasi Select mempunyai sifat Komutatif : <cond1> ( <cond2> ( R )) = <cond2> ( <cond3> ( R ))
Contoh :Dapatkan subset dari tuple EMPLOYEE yang bekerja pada departemen 4 dan mempunyai gaji lebih besar dari 50000 :
(DNo = 4) AND (Salary > 50000) (EMPLOYEE)
OPERASI PROJECT
Operasi ini digunakan untuk memilih sejumlah kolom tertentu dari tabelBentuk umum :
<Attribute List> (<Nama Relasi>)Ket : : Operasi Project
<Attribute List> : Satu List dari nama attribute dari relasi yang dinyatakan dalam (<nama relasi>)
Urutan attribute harus sama dengan yang ditulis dalam listOperasi project tidak bersifat komutatif
Contoh:Dapat suatu list dengan menampilkan atrribute Sex dan Salary
Sex, Salary (EMPLOYEE)Menghasilkan relasi baru yang nilai tuplenya unik satu dengan yang lain
PENAMAAN KEMBALI DARI ATTRIBUTE
Relasi yang dihasilkan oleh suatu relasi tidak mempunyai nama untuk itu perlu penamaan yang eksplisit.Penamaan relasi hasil suatu operasi adalah “nama sementara” dari sebagian hasil operasi, yang dapat digunakan dalam operasi yang lain untuk mendapatkan hasil relasi yang lain (dalam satu kesatuan operasi).
Contoh :Dapatkan list dari nama (lastname) dan salary dari semua employee yang bekerja pada departemen 5
Result Lname,Salary ( DNo = 5 (EMPLOYEE))Atau
Dept_Emp5 Dno = 5 (EMPLOYEE)Result Lname,Salary (Dept_Emp5)
40
Nama-nama attribute dari relasi-relasi hasil sementara atau hasil akhir diberikan penamaan kembali (renaming of attribute)
TEMP Dno = 5 (EMPLOYEE)HASIL(nama, gaji) Lname,Salary (TEMP)
Renaming of attribute sangat berguna dalam operasi yang kompleks yang melibatkan operasi UNION dan JOIN
OPERASI-OPERASI YANG MELIBATKAN TEORI SET
Operasi-operasi teori set dalam matematika relasi, seperti UNION (), INTERSECTION () dan DIFFERENCE (-) dapt digunakan karena reasi adalah set dari tuple
- Ketiga operasi diatas bersifat biner, yaitu melibatkan dua buah set dimana kedua relasi yang dilibatkan harus mempunyai set dari tuple dengan tipe yang sam disebut dengan kondisi UNION COMPABILITY
- Dua relasi R(A1, A2, A3….An) dan relasi S(B1, B2, B3,…..Bn) dikatakan Union Compatible apabila : keduanya mempunyai degree n yang sama dan dom(Ai) = dom(Bi) untuk 1 I n
- Sifat dari ketiga operasi diatas sama dengan yang berlaku dalam matematika relasi biasa :R S = S R, R S = S R, R – S S - R
Contoh :Dapatkan social security number dari semua employee baik yang bekerja dalam department 5 atau yang secara langsung menjadi supervisor dari employee yang bekerja di departemen 5
DEP5_EMP Dno = 5 (EMPLOYEE)RESULT1 SSN (Dept_Emp5)RESULT2 SuperSSN (RESULT1)RESULT RESULT1 RESULT 2
OPERASI CARTESIAN PRODUCT ( X )Bersifat biner, tetapi relasi-relasi yang dilibatkan tidak harus union compatibleDisebut juga CROSS PRODUCT atu CROSS JOIN, dan digunakan untuk mengkombinasikan tuple dari 2 relasi :R(A1, A2, A3, ….. An) X S (B1, B2, B3,……Bm)Hasilnya adalah relasi Q :
Q (A1, A2, A3, ….., An, B1, B2, B3,……..Bm)Jumlah atrribute = n + mJumlah Tuple = NR x NS ( NR = Jumlah tuple R, Ns = Jumlah tuple S )
Contoh :Dapatkan untuk setiap Female Employee, satu list nama dari dependent (keluarga) employee tersebut
FEMALE_EMP Sex = ‘F’ (EMPLOYEE)EMPNAMES Lname, SSN (FEMALE_EMP)EMP_DEPENDENT EMPNAMES X DEPENDENTACTUAL_DEPENDENT Ssn = ESSN (EMP_DEPENDENT)RESULT Lname, Dependent_name (ACTUAL_DEPENDENT)
OPERASI JOIN ( )
41
Digunakan untuk mengkombinasikan “Related Tuple” dari dua relasi menjadi sekumpulan tuple tunggal. Untuk “relationship” dari sejumlah relasi.Bentuk Umum :
R <Join Condition> S , Dimana R= (A1,A2…,An) S = (B1,B2….Bm)
Hasilnya adalah relasi Q (A1, A2,….An, B1, B2,…. Bm) dengan jumlah attribute (n+m) yang setiap tuplenya adalah kombinasi tuple dari R dan S yang memenuhi kondisi JOIN.
Contoh :Dapatkan Nama-nama dari manager setiap department:Tanpa operasi join (menggunakan Cartesian product)
TEMP DEPARTEMENT X EMPLOYEEDEPT_MGR MGRSSN = SSN (TEMP)RESULT Dname, Lname (DEPT_MGR)
Dengan menggunakan operasi join :DEPT_MGR DEPARTEMENT MGRSSN = SSN (EMPLOYEE)RESULT Dname, Lname (DEPT_MGR)
THETA JOIN ( )Hanya tuple yang join attributenya tidak null yang ditampilkan dalam hasilEQUIJOIN ( = )Jika operator perbandingan hanya melibatkan Equality Operator (=), dalam hasil operasi setiap tuple pasangan-pasangan attribute yang identik dapat lebih dari satu.NATURAL JOIN (*)Karena setiap pasangan attribute yang nilainya identik tidak berguna (superfluous), untuk mengabaikan attribute yang kedua dalam EQUIJOIN.
Natural Join memerlukan dua join attribute yang mempunya nama attribute yang sama.Bentuk umum Natural Join :
Q R * (<List1>, <List2>) S, dimana <list1> adalah list dari I attribute dari relasi R
<list2> adalah list dari I attribute dari relasi SDefinisi standar dari Natural Join :
- Bilamana pasangan join attribute adalah sama, maka operasi natural join dapat dilakukan langsung :DEPT_LOCS DEPARTEMENT * DEPT_LOCATIONNatural join pada attribute DNumber
- Tetapi jika join attributenya tidak sama, maka perlu melakukan renaming pada salah satu relasi yang terlibatDEPT(Dname, Dnum, MGRSSN, MGRStartDate) DEPARTEMENTPROJ_DEPT PROJECT * DEPT
Attribute Dnum sebagai join attribute
OPERASI DIVISION ( )Konsep dasarnya sama dengan aturan pembagian aljabar biasa dengan mengabaikan sisa
pembagian.F = ad + bd + cd + ae + be + ce + af + bfG = (d+e)
42
F G = (a + b + c) (d + e)+ af + bf = a + b + c
(d + e)Jika operasi division dikenakan terhadap suatu relasi R(Z) S(X) dimana X Z, maka jika Y = Z – X (yaitu Y = set atrribute dari R yang tidak ada dalam S),Sehingga Division bisa dinyatakan dalam urutan operasi , X dan - :
T1 Y ( R )T2 Y ( (S X T1) - R )T T1 – T2
Contoh :Dapatkan nama-nama employee yang bekerja pada semua project dimana smith juga bekerja
didalamnya :SMITH Lname = ’Smith’ ( EMPLOYEE )SMITH_PNO PNO ( WORKS_ON * ESSN=SSN SMITH )SSN_PNO PNO, ESSN ( WORKS_ON )TEMP_RES SSN_PNO SMITH_PNORESULT LName ( TEMP_RES * EMPLOYEE )
OPERASI TAMBAHAN
Operasi tambahan meliputi operasi yang tidak dapat dilakukan dengan menggunakan operasi relasional standarFUNGSI AGREGATE
- SUM : Menjumlahkan nilai-nilai dari suatu domain- AVERAGE : Menghitung nilai rata-rata dari domain- MAXIMUM : Menghitung nilai maksimum dari domain- MINIMUM : Menghitung nilai minimum dari domain- COUNT : Menghitung jumlah tuple suatu relasi
Operasi tambahan lain juga sangat berguna dalam operasi (request) yang melibatkan pengelompokan (grouping) sejumlah tuple dalam suatu relasi berdasarkan nilai beberpa attributenya, kemudian diikuti dengan fungsi agregasi untuk setiap kelompok secara terpisah.Bentuk umum operasi function :
ƒ <script F> :<grouping attribute> ƒ <function list> (<nama relasi>)
Contoh :Dapatkan setiap nomor departement, jumlah employee dalam departement dan gaji rata-rata
mereka :R(DNo, Jumlah, Gaji_Rata2) DNo ƒ Count SSN, Average Salary (EMPLOYEE)Jika tidak ada pengelompokkan attribute maka fungsi-fungsi hanya menghasilkan satu tuple saja.
ƒ Count SSN, Average Salary (EMPLOYEE)SSN Average Salary8 35125
OPERASI OUTER JOIN dan OUTER UNIONOuter Join dapat digunakan untuk menggabungkan tuple yang tidak harus memenuhi kondisi JOIN.
Jenis Outer JOIN:- LEFT OUTER JOIN
Setiap tuple dari relasi yang pertama (relasi kiri R dan R S tetap dipertahankan dalam hasil jika tidak ada matching dalam S; untuk ini attribute-attribute S diisi dengan null value dalam hasil
43
- RIGTH OUTER JOINKebalikan Left Outer Join, yaitu tuple-tuple relasi kanan yang dipertahankan
- FULL OUTER JOINMempertahankan tuple-tuple relasi kiri atau relasi kanan jika tidak ditemukan matching
OUTER UNION (tanpa notasi) disediakan apabila diinginkan melakukan operasi union pada dua relasi yang tidak union compatible. Nilai-nilai dari tuple untuk attribute yang tidak mempunyai nilai diisi dengan null valueContoh :
R(A,B,C,D) S(A, B, C, E)
TUGAS
a. Dapatkan nama-nama pegawai di departemen 5 yang bekerja lebih dari 10 jam per minggu pada project “Product X”OutputFname LnameJohn SmithJoyce English
b. Tampilkan nama-nama employee yang mempunyai keluarga dengan nama depan sama dengan nama employee tersebutOutputFname LnameNull Null
c. Dapatkan nama-nama employee yang secara langsung menjadi supervisi(anak buah) dari ‘FranklinWong’OutputSupervisionFirstName SupervisionLastNameJohn SmithRamesh NarayaJoyce English
d. Untuk setiap project, dapatkan nama project dan total jam/minggu (untuk semua employee) yang bekerja pada project tersebutOutput
Pname Sum(Hours)Product X 52.5Product Y 37.5Product Z 50.0Computerization 55.0Reorganization 25.0Newbenefit 55.0
e. Dapatkan nama-nama employee yang bekerja pada setiap projectOutputFname LnameNull Null
f. Dapatkan nama-nama employee yang tidak bekerja pada beberapa projectOutputFname Lname
44
Null Nullg. Untuk setiap department, dapatkan nama department dan rata-rata salary setiap
employee yang bekerja didalam depertment tersebutOutputDname Average(Salary)Research 33,250Administration 31,000Hadquarters 55,000
h. Dapatkan rata-rata salary untuk employee ‘Female’Output
Average(Salary)31,000
i. Dapatkan nama dan alamt employee yang bekerja sedikitnya satu project yang berlokasi di ‘Houston tetapi departement dari employee tersebut tidak berlokasi di’Houston’Output
Fname Lname AddressJennifer Wallace 291 berry,Bellaire,TX
j. Dapatkan nama belakang manager suatu departement yang tidak mempunyai keluargaOutputLnameBorg
45
BAB VIIISTRUCTURE QUERY LANGUAGE
I. DATA DEFINITION LANGUAGE
A. PERINTAH CREATEDigunakan untuk mendefinisikan satu relasi baru yang berisikan :
- Nama Relasi- Nama atribut-atributnya disertai dengan tipe-tipe datanya dan domain constrain /
value- Key, entity integrity dan referential integrity constraint
Syntax Umum :CREATE TABLE <Table Name>( <Column name> <Column Type> [ <attribute constraint>] {,<Column name> <Column Type> [<Attribute constraint>]} [ < Table Constraint> {, <Table Constraint>}]);Dari Syntax diatas ada definisi yang optional :
- < Attribute Constraint> : Not null yang dapat diikuti dengan Default value dari attribute
- <Table Constraint> : Untuk menspesifikan Primary Key dan Key Constraint dan Foreign Key (Referential Integerity Constraint)
ContohCREATE TABLE EMPLOYEE
Fname VarChar(9) Not null,Minit Char,Lname VarChar(15) Not null,SSN Char(9) Not null,BDate Date,Address VarChar(30)Sex Char,Salary Decimal(10,2)SuperSSN Char(9),DNo Int Not null Default 1,
Constraint EmpPKPrimary Key (SSN),Constraint EmpSuperFKForeign Key (SuperSSN) References Employee (SSN)
On Delete set nullOn Update Cascade,
46
Constraint EmpDeptFKForeign Key (DNo) References Department (Dnumber)
On Delete Set DefaultOn Update Cascade
B. PERINTAH DROP SCHEMA DAN DROP TABLEDigunaka untuk menghapus schema atau sejumlah table dari basis data, keduanya boleh diikuti dengan option : Cascade atau Restrict
Syntax :DROP SCHEMA <Schema name>DROP TABLE <Table name>
Contoh :DROP SCHEMA Company CascadeKet : Menghapus schema basis data company dan semua tabel, domain dan elemen-elemen lain didalamnya,Option Restrict digunakan apabila diinginkan untuk menghapus jika dan hanya jika schema tidak mempunyai elemen didalamnya
DROP TABLE Dependent Cascade
C. PERINTAH ALTERDigunakan untuk mengganti atau merubah definisi dari base table yang telah dibuatTindakan atau action yang diakibatkan oleh perintah ini dapat meliputi :
- Adding atau dropping suatu kolom atau attribute- Mengganti definisi suatu attribute- Adding atau dropping table constraint
Contoh :ALTER Table Company.Employee DROP Address Cascade;Menghapus satu kolom dari suatu table, harus diikuti oleh salah satu pilihan : Cascade atau Restrict.Dengan Cascade, maka semua constraint dan views yang mengacu pada kolom akan dihapus secara otomatis dari schemaJika Restrict dipilih, maka perinta akan sukses bilamana tidak ada constraint atau view yang mengacu pada kolom yang akan dihapus.
ALTER Table Company.Department Alter MgrSSNDROP Default;
ALTER Table Company.Department ALTER MgrSSNSet Default “333445555”;
Merubah definisi satu kolom dengan cara menghapus default Clause yang ada, atau mengganti nilai dari default clause
ALTER Table Company.EmployeeDROP Constraint EmpSuperFK Cascade;
Mengganti constraint yang telah didefinisikan dalam suatu table dengan menambah (adding) atau menghapus (dropping) constraint.
47
Untuk tindakan dropping suatu constraint, maka constraint tersebut harus diberi nama pada saat didefinisikan. Contoh constraint Foreign Key “EmpSuperFK” dalam table Employee.Constraint dapat didefinisikan kembali dengan menambahkan (adding) satu constraint baru kedalam relasiDapat digunakan untuk menyingkat penulisan qualifier suatu attribute dalam kasus untuk menghindari ambiquity,contoh berikut merupakan penulisan yang lain untuk query yang sama dengan Q2A
SELECT E.Fname, E.Name, E.AddressFROM employee E, Department DWhere D.Name = ‘Research’ And
D.Dnumber = E.Dnumber
II. UPDATE STATEMENT
Digunakan untuk melakukan modifikasi database yang meliputi :- Insert- Delete berlaku 1 tabel- Update
A. INSERT COMMANDDigunakan untuk menyisipkan/menambahkan satu (single) tuple kedalam suatu relasi
INSERT Into employeeValues (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ’30-dec-52’, ’98 oak forest, katy’, ‘M’, 37000, ‘987654321’, 4)
INSERT Into Employee (Fname, Lname, SSN)Values (‘Richard’, ‘Marini’, ‘653298653’)
VARIATIONMenyisipkan multiple tuples kedalam suatu relasi sehubungan dengan Creating Relation dan Loading relasi yang dibuat dengan hasil suatu query dari relasi yang telah ada.
CREATE Temporary table dengan heading berupa nama, jumlah employee, dan total salaries dari setiap department :CREATE Table Depts-Info ( Dept-Name Varchar (15),
No-Of-Emps Integer,Total-Sal Integer
);
INSERT Into Depts-Info (Dept-name, No-Of-Emps, Total-Sal)SELECT Dname, Count(*), Sum (Salary)FROM Department, employeeWHERE Dnumber = DNoGROUP BY Dname;
Catatan : untuk variasi ini tabel sementara yang dihasilkan tidak berubah akibat adanya perubahan-perubahan pada tabel Department atau employee
48
B. DELETE COMMANDDigunakan untuk menghapus sejumlah tuple dari suatu relasi.Tergantung dari kondisi penghapusan yang dinyatakan dalam Where Clause, maka jumlah tuple yang dihapus akan bervariasiTanpa Where clause, berarti menghapus semua tuple yang ada dalam tabel.
DELETE From EmployeeWHERE Lname = ‘Brown’
DELETE From EmployeeWHERE SSN = ‘123456789’
DELETE From EmployeeWHERE Dno In ( Select Dnumber
From Department Where Dname = ‘Research’ )
DELETE From employee
C. UPDATE COMMANDDigunakan untuk memodifikasi nilai-nilai attribute dari satu atau lebih tuple yang dipilih
UPDATE ProjectSET Plocation = ‘Bellaire’, Dnum = 5WHERE Pnumber = 10
UPDATE employeeSET Salary = Salary * 1.15WHERE DNo In ( Select Dnumber
From Department Where Dname = ‘Research’ )
Setiap perintah update secara eksplisit hanya boleh menspesifikasikan satu relasi saja
III. QUERY TANPA ‘WHERE’ CLAUSE DAN PENGGUNAAN *
Query SQL yang dispesifikasikan tanpa ‘where’ clause mengindikasikan tidak adanya kondisi pemilihan tuple, sehingga semua tuple dari relasi yang dinyatakan dalam FROM clause dipilih sebagai hasil query
Dapatkan semua SSN dari EmployeeSELECT SSNFROM EMPLOYEE
49
Ada relasi yang dinyatakan dalam FROm clause lebih dari satu,maka Cross Product dari semua kombinasi yang mungkin dihasilkan :Dapatkan semua kombinasi dari SSN employee dan Dname DepartmentSELECT SSN, DnameFROM Employee, Department
Tanda asterik ?(*) digunakan dalam Select clause bilamana diinginkan untuk melakukan retrieve dari semua attribute dari tuple-tuple terpilih
Retrieve semua nilai-nilai attribute dari employee tuples yang bekerja pada departemen 5 SELECT *FROM employeeWhere Dno = 5
Retrieve semua nilai-nilai attribute dari employee dan attribute Departemen dimana employee tersebut bekerja bagi setiap employee yang bekerja pada departemen ‘research’:SELECT *FROM employee, DepartementWHERE Dname = ‘Research’ and Dno = Dnumber
Dapatkan Cross Product dari relasi employee dan DepartmentSELECT *FROM Employee, Department
A. TABLE SEBAGAI SET
SQL tidak memperlakukan suatu relasi/tabel sebagai suatu set,sehingga adanya tuple yang muncul lebih dari sekali tidak dieliminasi secara otomatis dalam hasil query, alasanya:- Proses eliminasi ‘duplicate tuples’ merupakan operasi yang mahal, dimana satu cara
untuk mengimplementasikan ini adalah dengan mensorting tuples dan kemudian mengeliminasi ‘duplicate tuples’ yang ada.
- User mungkin menginginkan untuk melihat duplicate tuples dalam hail query- Bilamana suatu fungsi agregrate digunakan terhadap sejumlah tuple dalam SQL, hampir
dalam semua kasus tidak diinginkan untuk mengeliminasi duplikasi tuple yang ada.
Untuk mengeliminasi duplikasi tuple dari hasil suatu query dalam SQL dilakukan dengan menggunakan keyword Distinct dalam Select Clause.Contoh :Retrieve gaji yang diterima oleh setiap employee :Selectalary Select Distinct SalaryFrom Employee From employee
B. MENDEFINISIKAN QUERY DALAM SQL
Syntax Umum :SELECT [Distinct] <Attribute List>FROM ( <Table name>{ <alias>} | <joined table>)
{, <table name> {<alias>} | <jpined table>)}[ WHERE <condition>]
50
[ GROUP BY <grouping attribute>[HAVING <group selection condition>]]
[ OREDER BY <column name> [<order>]{, <column name> [<order>] } ]
<attribute list> ::= (* | (<column name> | <function>(( [Distinct] <column name> | * ))){, <column name> | <function>(( [Distinct] <column name> | * )) } ))
<grouping attribute> := <column name> { , <column name> }<Order> := (ASC | Desc)
C. BASIC SQL QUERY
Bentuk dasar dari SELECT statement disebut dengan ‘SELECT FROM WHERE Blok’ yang mempunyai bentuk umum :Contoh :Retrieve birthdate dan address dari employee yang bernama ‘John B. Smith’SELECT Bdate, AddressFROM EmployeeWHERE Fname = ‘John’ And Minit = ‘B’ And LnAme = ‘Smith’
Retrieve name dan address dari semua employee yang bekerja pada departemen ‘Research’ :SELECT Fname, Lname, AddressFROM Employee, DepartmentWHERE Dname = ‘Research’ and Dnumber = DNo
Untuk setiap project yang berlokasi di ‘Stafford’, dapatkan daftar nomor project, nomor department pengendalinya, dan nama, alamt serta tanggal lahir dari manajer department pengendali.SELECT Pnumber, Dnum, Lname, Address, BdateFROM Project, Department, EmployeeWHERE Dnum = Dnumber and MgrSSN = SSN and Plocation = ‘Stafford’
D. PENANGANAN NAMA ATTRIBUTE YANG AMBIGUOUS DAN PENGGUNAAN ALIAS
Oleh karena sejumlah relasi yang berbeda mungkin mempunyai nama-nama attribute yang sama; maka untuk menghindariambiquity (kerancuan), suatu query yang mengacu ke dua atau lebih nama attribute yang sama harus menggunakan qualifier nama attribute dan nama relasi yang diacu (dipisahkan oleh tanda ‘. ‘)Sebagai contoh, misalnya attribute Lname dan DNo pada relasi Employee diganti dengan Name dan Dnumber. Pada attribute Dname dalam relasi Department diganti dengan Name, maka query Q2 menjadi :SELECT Fname, Employee. Name, AddressFROM Employee, DepartmentWHERE Department.Name = ‘Research’ and
Department.Dnumber = Employee.DnumberAmbiquity juga terjadi pada kasus dimana suatu query mengacu pada relasi yang sama dua kali (recursive relationship), contoh :
51
Untuk setiap employee, retrieve first dan last name dari employee, dan first dan last name dari supervisor langsung dari employee
SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM Employee E, Employee SWhere E.SuperSSN = S.SSN
Nama alias dapat dituliskan langsung setelah nama relasi, atau dituliskan setelah keyword ‘As’ :
FROM Employee As EAtau dapat juga digunakan untuk menamakan kembali (renaming) attribute-attribute suatu relasi dalam FROM Clause :EMPLOYEE As E(FN, MI, LN, SSN, BD, ADDR, SEX, SAL, SSSN, DNO)
Dapat digunakan untuk menyingkat penulisan qualifier suatu attribute dalam kasus untuk menghindari ambiquity,contoh berikut merupakan penulisan yang lain untuk query yang sama.
SELECT E.Fname, E.Name, e.AddressFROM employee E, Department DWhere D.Name = ‘Research’ And
D.Dnumber = E.Dnumber
E. QUERY TANPA ‘WHERE’ CLAUSE DAN PENGGUNAAN
Query SQL yang dispesifikasikan tanpa ‘where’ clause mengindikasikan tidak adanya kondisi pemilihan tuple, sehingga semua tuple dari relasi yang dinyatakan dalam FROM clause dipilih sebagai hasil query
Dapatkan semua SSN dari Employee
SELECT SSNFROM EMPLOYEE
Ada relasi yang dinyatakan dalam FROm clause lebih dari satu,maka Cross Product dari semua kombinasi yang mungkin dihasilkan :Dapatkan semua kombinasi dari SSN employee dan Dname Department
SELECT SSN, DnameFROM Employee, Department
Tanda asterik ?(*) digunakan dalam Select clause bilamana diinginkan untuk melakukan retrieve dari semua attribute dari tuple-tuple terpilih
Retrieve semua nilai-nilai attribute dari employee tuples yang bekerja pada departemen 5
SELECT *FROM employeeWhere Dno = 5
52
Retrieve semua nilai-nilai attribute dari employee dan attribute Departemen dimana employee tersebut bekerja bagi setiap employee yang bekerja pada departemen ‘research’:
SELECT *FROM employee, DepartementWHERE Dname = ‘Research’ and Dno = Dnumber
Dapatkan Cross Product dari relasi employee dan Department
SELECT *FROM Employee, Department
F. TABLE SEBAGAI SET
SQL tidak memperlakukan suatu relasi/tabel sebagai suatu set,sehingga adanya tuple yang muncul lebih dari sekali tidak dieliminasi secara otomatis dalam hasil query, alasanya:- Proses eliminasi ‘duplicate tuples’ merupakan operasi yang mahal, dimana satu cara
untuk mengimplementasikan ini adalah dengan mensorting tuples dan kemudian mengeliminasi ‘duplicate tuples’ yang ada.
- User mungkin menginginkan untuk melihat duplicate tuples dalam hail query- Bilamana suatu fungsi agregrate digunakan terhadap sejumlah tuple dalam SQL, hampir
dalam semua kasus tidak diinginkan untuk mengeliminasi duplikasi tuple yang ada.
Untuk mengeliminasi duplikasi tuple dari hasil suatu query dalam SQL dilakukan dengan menggunakan keyword Distinct dalam Select Clause.Contoh :Retrieve gaji yang diterima oleh setiap employee :Select Salary Select Distinct SalaryFrom Employee From employee
G. OPERASI-OPERASI SET DALAM SQL :
- UNION (Operasi Union)- INTERSECT (Operasi Intersection)- EXCEPT (Operasi Set Difference)
Relasi yang dihasilkan oleh ketiga operasi ini berupa set of tupleOleh karena ketiga operasi set diatas hanya boleh dikenakan terhadap relasi-relasi yang union_compatible, maka kedua relasi yang dikenakan operasi harus :- Mempunyai attribute yang sama- Mempunyai urutan attribute yang sama
** Dapatkan satu list dari semua nomor project yang melibatkan seorang employee yang bernama ‘Smith’, baik sebagai seorang pekerja biasa atau sebagai seorang manajer dari department yang mengendalikan project :
SELECT PnumberFROM Project, Department, EmployeeWHERE Dnum = Dnumber and MgrSSN = SSN and Lname = ‘Smith”UNION
53
SELECT PnumberFROM Project, WorksOn, EmployeeWHERE Pnumber = Pno and ESSN = SSN and Lname = ‘Smith’
H. NESTED QUERY AND SET COMPARISONS
Nested query sangat cocok digunakan dalam suatu query dimana nilai-nilai yang ada diambil dan digunakan dalam suatu kondisi perbandingan
Dalam nested query, suatu query SELECT yang lengkap digunakan dalam Where clause dari query yang lain (Outer query)Spesifikasi ** dengan menggunakan nested query
SELECT Distinct PnumberFROM ProjectWHERE Pnumber In ( Select Pnumber
From Project, Department, employee Where Dnum = Dnumber and MgrSSN = SSN and Lname = ‘Smith’)
ORPnumber In ( Select PNo
From Works-On, employeeWhere ESSN = SSN and Lname = ‘Smith’
)
Dalam contoh diatas, operator perbandingan ‘In’ digunakan untuk membandingkan satu nilai v dengan satu set (atau multiset) nilai V dan menghasilkan evaluasi True jika v V.
Operator In juga dapat membandingkan suatu tuple dari nilai-nilai yang dituliskan dalam tanda kurung dengan satu set tuple yang union compatible
Dapatkan social security number dari semua employee yang bekerja pada project dan jumlah jam yang sama dengan project dan jumlah jam diamana ‘Smith’ terlibat :
SELECT Distinct ESSNFROM Works-OnWHERE (Pno, Hours) In ( Select Pno, Hours
From Works-OnWhere ESSN = ‘123456789’
)
Selain operator In, terdapat sejumlah operator perbandingan yang dapat digunakan untuk membandingkan satu nilai v (biasanya satu attribute) terhadap satu set V (biasnya nested query) :Operator = Any (atau = Some) ekivalen dengan operator In, yang memberikan nilai true jika nilai v sama dengan beberapa nilai dalam set V.
54
Operatro-operaotr lain yang dapat dikombinasikan dengan Any (atau Some) adalah : >, >=, <, <= dan <>. Keyword ALL juga dapat dikombinasikan dengan salah satu operator-operator ini, misalnya :Kondisi perbandingan (v > all V) akan memberikan nilai True jika nilai v lebih besar dari semua nilai dalam set V.
Retrieve nama-nama employee yang gajinya lebih besar dari pada gaji semua employee dalam departemen 5
SELECT Lname, FnameFROM EmployeeWHERE Salary > All ( SELECT Salary
FROM Employee WHERE DNo = 5)
Dalam nested query yang melibatkan relasi dengan nama yang sama dengan outer query, maka acuan terhadap Unqualified Attribute hanya diperbolehkan dilakukan terhadap nested query yang terdalam. Sedang acuan terhadap outer query dilakukan dengan menggunakan nama alias dari relasi yang diacu.
Retrieve nama dari setiap employee yang mem[unyai dependent dengan first name dan sex yang sama dengan employee tersebut :
SELECT E. Fname, E.LnameFROM Employee EWHERE E.SSN In (SELECT ESSN
FROM Dependent WHERE ESSN = E.SSN And
E.Fname = Dependent.Name AndSex = E.Sex
)
Secara umum, suatu query dengan structure nested Select… From,,, Where…. Dan menggunakan operator perbandingan = atau In selalu dapat dinyatakan sebagai query dengan struktur satu blok saja
SELECT E.Fname, E.LnameFROM Employee E, Dependent DWHERE E.SSN = D.ESSN and
E.Sex = D.Sex And E. Fname = D.Dependent-name
Catatan :Bilamana suatu kondisi dalam Where clause dari suatu nested query mengacu beberapa attribute dari suatu relasi yang dideklarasi dalam outer query, maka kedua query dikatakan saling berkorelasi (correlated).
I. FUNGSI EXISTS DAN NOT EXISTS
55
Digunakan untuk melakukan pengecekan apakah hasil dari suatu ‘correlated nested query’ berisi tuple atau tidak.
EXISTS (Q) : Memberikan nilai return True, jika dalam hasil query Q minimal terdiri dari satu tuple
NOT EXISTS (Q) : Memberikan nilaai return TRUE, jika tak satupun tuple yang dihasilkan dalam hasil query Q
Retrieve nama-nama employee yang tidak mempunyai dependent
SELECT Fname, LnameFROM EmployeeWHERE Not Exists ( Select *
From DependentWhere SSN = ESSN
)
Dapatkan list nama-nama manager yang paling tidak mempunyai satu dependent
SELECT Fname, LnameFROM EmployeeWHERE Exists ( SELECT *
FROM Dependent WHERE SSN = ESSN )
And Exists ( SELECT *
FROM Department Where SSN = MgrSSN )
J. DIVISION
Retrieve nama-nama employee yang bekerja pada semua proyek yang dikontrol oleh departement nomor 5
SELECT Lname, FnameFROM EmployeeWHERE Not Exists ( SELECT *
FROM Works-On B WHERE (B. Pno IN ( SELECT Pnumber
FROM Project WHERE Dnum = 5)
) And Not Exists ( SELECT * FROM Works-On C WHERE C.ESSN = SSN And
C.Pno = B. Pno
56
))
Query diatas dapat dinyatakan kembali :Cari setiap employee sedemikian rupa sehingga (tidak terdapat suatu project yang dikendalikan oleh departement 5 dimana employee tersebut (tidak bekerja didalamnya))
K. EKSPLISIT SET DAN NULL
Digunakan untuk menyatakan nilai-nilai set yang eksplisit dalam suatu Where clause
Retrieve SSN dari semua employee yang bekerja pada project nomor 1, 2 dan 3SELECT Distinct ESSNFROM Works-OnWHERE Pno In (1,2,3)
Retrieve nama-nama semua employee yang tidak mempunyai supervisorSELECT Fname, LnameFROM EmployeeWHERE SuperSSN is Null
= NULL Is Null NULL Is Not Null
L. PENAMAAN KEMBALI ATTRIBUTE-ATTRIBUTE HASIL QUERY DAN TABEL-TABEL-TABEL HASIL OPERASI JOIN
Penamaan kembali dari attribute :
Untuk setiap Employee, retrieve last name dari employee dan lastname dari supervisor langsung dari employee tersebut
SELECT E.Lname As Employee-Name S.Lname As Supervisor-Name
FROM Employee As E, Employee As SWHERE E.SuperSSN = S.SSN KONSEP ‘JOINED TABLE’ dalam SQL dimaksudkan untuk memungkinkan menyajikan spesifikasi yang dihasilkan dari operasi Join dalam From Clause
Dalam SQL terdapat 5 pilihan untuk menyajikan spesifikasi Joined Table :- Inner Join (Join)- Left Outer Join (Left Join)- Rigth Outer Join (Rigth Join)- Full Outer Join (Full Join)- Natural Join
Retrieve nama dan address dari semua employee yang bekerja pada Research Departemen
SELECT Fname, Lname, Address
57
FROM (Employee JOIN Department ON DNo = Dnumber)WHERE Dname = ‘Research’
SELECT Fname, Lname, AddressFROM (Employee NATURAL JOIN ( Department AS
Dept(Dname, DNo,MSSN, MSDate)))WHERE Dname = ‘Research’
SELECT Pnumber, Dnum, Lname, Address, BDateFROM (( Project JOIN Department ON DNum = Dnumber)
JOIN Employee ON MgrSSN = SSN)WHERE PLocation = ‘Stafford’
M. AGGREGATE FUNCTIONS AND GROUPING
Fungsi Aggregate :- Count- Sum Digunakan pada Select Clause atau Having clause- Max- Min- Avg
Dapatkan jumlah salary dari semua employee, maksimum salary, minimum salary dan average salary dari semua employee
SELECT Sum(Salary), Max(Salary), Min(Salary), Avg (Salary)FROM employee
Dapatkan jumlah salary dari semua employee, maksimum salary, minimum salary dan average salary dari semua employee dari Research Departement
SELECT Sum(Salary), Max(Salary), Min(Salary), Avg (Salary)FROM employee, DepartmentWhere DNo = Dnumber and Dname = ‘Research’
Retrieve jumlah employee dalam company, dan jumlah employee dalam Research DepartmentSELECT Count (*)FROM Employee
SELECT Count(*)FROM Employee, DepartmentWHERE DNo = Dnumber and DName = ‘Research’
Hitung jumlah nilai-nilai distinct salary dalam databse :
SELECT Count (Distinct Salary)FROM Employee
58
Retrieve nama-nama Employee yang mempunyai dependent sebanyak 2 atau lebih
SELECT Lname, FnameFROM EmployeeWHERE ( SELECT Count (*)
FROM Dependent WHERE SSN = ESSN ) 2
Penggunaan pengelompokan hasil berdasarkan attribute tertentu dapat menggunakan Group By Claouse. Untuk ini attribute yang dijadikan ‘Grouping Attribute’ harus juga muncul dalam ‘SELECT Caluse’
Untuk setiap department, retrieve departement number, jumlah employee dalam department dan juga average salary dalam department tersebut :
SELECT Dno, Count (*), Avg (Salary)FROM EmployeeGroup By DNo
Untuk setiap project, retrieve nomor dan nama project, serta jumlah employee yang bekerja pada proyek tersebut :
SELECT Pnumber, Pname, Count (*)FROM Project, Works-OnWHERE Pnumber = PnoGroup by Pnumber, Pname
Pasangan clause “Group by” dan ‘Having’ digunakan untuk melakukan retrieving pengelompokkan yang memenuhi kondisi tertentu
Untuk setiap project yang melibatkan employee sebanya 2 atau lebih, retrieve project number, project name, dan jumlah employee yang bekerja didalamnya :
SELECT Pnumber, Pname, count (*)FROM Project, Works-OnWHERE Pnumber = PnoGROUP BY Pnumber, PnameHAVING Count(*) > 2
Hitung jumlah pegawai yang mempunyai salary > $40.000, dalam setiap departement, tetapi hanya untuk departement yang mempunyai jumlah pegawai > 5
SELECT Dname, Count(*)FROM Department, employeeWHERE Dnumber = DNo And Salary > 40000 And
DNo In ( SELCET DNo FROM Employee
59
GROUP BY Dno HAVING Count (*) > 5 )
GROUP BY Dname
IV. SUBSTRING COMPARISON, ARITHMETIC OPERATOR AND ORDERING
Substring Comparison : Meggunakan LIKE comparison operator :% Substring% : Menggantikan sembarang jumlah karakter- (underscore) : Menggantikan satu karakter
Retrieve semua employee yang mempunyai alamat di Houston
SELECT Fname, LnameFROM EmployeeWHERE Address Like %Houston%
Retrieve semua employee yang dilahirkan dalam tahun 50-an (1950 – 1959)
SELECT Fname, LnameFROM EmployeeWHERE BDAte Like ‘—S-------‘
Artithmatic Operator : +, -. * dan /
Tunjukkan hasil perubahan gaji dari setiap pegawai yang bekerja pada project ‘Product X’ jika diberikan kenaikan sebesar 10 % :
SELECT Fname, Lname, 1.1 * SalaryFROM employee, Works-On, ProjectWHERE SSN = ESSN And Pno = Pnumber and Pname = ‘Product X’
Untuk tipe data string :Operator || digunakan untuk menggabungkan (concatenate) 2 string Untuk tipe data DATE, TIME, TIMESTAMP dan INTERVAL Data Types :Operator yang dapat digunakan meliputi penambahan (+) dan pengurangan (-) oleh suatu interval dengan tipe yang kompatibel dari masing-masing tipe data diatas
Untuk mendapatkan hasil yang terurut berdasarkan satu atau lebih nilai-nilai attribute dapat digunakan : ORDER BY Clause
Dapatkan satu list dari employee dan project dimana ia bekerja; terurut berdasarkan nama department, dan dalam setiap department diurut secara alphabetical order berdasarkan first name:
SELECT Dname, Fname, PnameFROM Department, employee, Works-On, ProjectWHERE Dnumber = Dno and SSN = ESSN and Pno = PnumberORDER BY Dname, Fname
60
Default Order : Ascending OrderPilihan Eksplisit : ASC (Ascending) | DESC (Descending)
V. VIEWS DALAM SQL
A. KONSEP VIEW
- Dalam terminology SQL, View adalah sebuah tabel yang diturunkan dari tabel-tabel yang lain. Tabel-tabel yang lain dapat berupa : Base Table atau View Lain yang telah didefinisikan sebelumnya.
- View dapat dipandang sebagai virtual Table, sehingga tidak perlu ada secara fisik (tidak disimpan dalam database)
- Query terhadap view dapat dilakukan sepert halnya query pada tabel biasa (base table). Tetapi untuk operasi-operasi update terdapat beberapa pembatasan
- View dapat digunakan sebagai suatu cara untuk menspesifikasikan suatu tabel yang perlu diacu dalam frekuensi yang sering, tetapi ia tidak perlu dibuat secara permanen.
B. SPESIFIKASI VIEW
Dilakukan dengan perintah CREATE VIEWSatu view diberikan nama tabel (virtual) dan satu list nama-nama attributnya; yang kemudian dilakukan Query untuk menspesifikasikan isi dari view
ContohCREATE View Works-On-1As Select Fname, Lname, Pname, Hours
From Employee, Project, Works-OnWhere SSN = ESSN And Pno = Pnumber;
CREATE View Dept-Info (Dept-Name, No-Of-Emps, Total-Sal)AS Select Dname, count(*), Sum(Salary)
From Department, employeeWhere Dnumber = DNoGroupBy Dname
Retrieve last name dan first name dari semua employee yang bekerja pada ‘Project X’:
SELECT Pname, Fname, LnameFROM Works-On-1Where Pname = ‘Project X’
- View mempunyai sifat selalu up-to-date. Jadi jika dilakukan modifikasi terhadap nilai-nilai tupe dari base tabel yang digunakan dalam mendefinisikan view maka secara otomatis nilai-bilai view juga akan berubah pada saat dilakukan query
61
- View dapat dihapus dengan menggunakan perintah DROP VIEWDROP View Works-On-1
C. UPDATING VIEW dan IMPLEMENTASINYA
- Implementasi dari updating view masih merupakan active research area, terutama untuk updating yang melibatkan modifikasi dari sejumlah base table yang digunakan untuk mendefinisikan view.
- Implementasi dari updating view dilakukan dengan cara memetakan perintah update terhadap base table yang digunakan.
VI. MENSPESIFIKASIKAN CONSTRAINTS TAMBAHAN DG MENGGUNAKAN ASSERTIONS
Dalam SQL , Assertion digunakan untuk menyatakan constraints yang tidak termasuk dalam kategori costriants yang ada (Selain key, entity dan referential integrity constraint)
Statement :CREATE ASSERTIONSetiap assertion diberikan nama constraints dan dispesifikasikan melalui suatu kondisi yang serupa dengan where clause
ContohSpesifikasi constraint :
Salary dari employee harus tidak lebih besar dari pada salary manager dari department dimana employee bekerja :
CREATE Assertion Salaray_ConstraintCHECK ( Not Exists
( Select * From Employee E, Employee M
Department D Where E.Salary > M.Salary And E.Dno = D.Dnumber
And D.MgrSSN = M.SSN)
)
VII MENSPESIFIKASIKAN INDEXES
Hanya ada dalam versi sebelum SQL2, meliputi :- Clustering & Unique Index (Primary Indexing)
CREATE Unique Index SSN-Index On Employee(SSN)
- Clustering, tetapi non-unique (Clustering Index)CREATE Index DNo-Index
On Employee(DNo) Cluster
- Non-Clustering (Secondary Index)
62
CREATE Index Lname-Index On Employee(Lname)CREATE Index Names-Index On Employee ( Lname ASC, Fname DESC, Minit )
Suatu base tabel hanya boleh mempunyai paling banyak satu clustering index, tetapi dapat mempunyai banyak non-clustering index.
63