ENTITY RELATIONSHIP DIAGRAM ERD atau Entity Relationship Diagram adalah diagram yang menggambarkan entitas data (entity) dan hubungannya. Entity sangat mirip dengan class pada OOP, akan tetapi entity lebih terkait dengan data yang disimpan secara fisik pada database. Perancangan ERD dilakukan pada tahap awal fase pengembangan software dan harus dilakukan dengan cermat dan detail, karena sedikit saja perubahan pada ERD dapat berdampak besar pada proses implementasi coding. 3
3/11/2015
IKG2I4 – Software Project I
ENTITY RELATIONSHIP DIAGRAM Ketika pengembangan software sudah mulai masuk tahap implementasi (coding), sedikit perubahan pada ERD dapat berakibat cukup besar pada arsitektur software, dan programmer mungkin harus mengubah sebagian besar source codenya. Oleh karena itu, tanggung jawab seorang System Analyst sangat besar dalam merancang ERD yang tepat untuk software yang sedang dikembangkan.
4
3/11/2015
IKG2I4 – Software Project I
ENTITY RELATIONSHIP DIAGRAM Pada kuliah ini kita akan menggunakan skema HRM (Human Resource Management) pada Oracle untuk studi kasus. Sebagai bahan perbandingan, lihat juga “BANKING SYSTEM”, pada textbook – Silberschatz−Korth−Sudarshan. Database System Concepts, 4th ed, McGrawHill. Chapter 2.
5
3/11/2015
IKG2I4 – Software Project I
ENTITY DAN ATRIBUT Pada studi kasus BANKING SYSTEM, ada beberapa entitas yang teridentifikasi (mirip seperti class pada materi object oriented): – Employee: pegawai bank – Customer: semua nasabah yang tercatat di bank – Depositor: nasabah yang menyimpan uang di bank – Borrower: nasabah yang meminjam uang dari bank – Account: rekening penyimpanan – Loan: rekening peminjaman Catatan: sistem di atas hanya asumsi saja, dan mungkin tidak sesuai dengan sistem banking sebenarnya. 6
3/11/2015
IKG2I4 – Software Project I
ENTITY DAN ATRIBUT Tiap entity memiliki atribut (mirip seperti attribute class): – Employee: employee-id, name, telephone number, dsb – Customer: customer-id, name, address (street & city) – Depositor: daftar nasabah yang menyimpan uang di bank – Borrower: daftar nasabah yang meminjam uang dari bank – Account: rekening penyimpanan – Loan: rekening peminjaman Atribut pada entity bersifat atomic yaitu hanya mengandung sebuah nilai. Bila yang dibutuhkan adalah beberapa nilai, maka atribut tsb sebaiknya dijadikan entity baru. 7
3/11/2015
IKG2I4 – Software Project I
PRIMARY KEY Tiap entity utama memiliki primary key. Primary key yaitu atribut yang bersifat unique. Contoh: – Untuk tabel mahasiswa, primary key-nya adalah NIM – Untuk tabel employee: employee-id, name, telephone number, dsb – Customer: customer-id, name, address (street & city) – Depositor: daftar nasabah yang menyimpan uang di bank – Borrower: daftar nasabah yang meminjam uang dari bank – Account: rekening penyimpanan – Loan: rekening peminjaman
8
3/11/2015
IKG2I4 – Software Project I
ERD ENTITY RELATIONSHIP DIAGRAM
9
3/11/2015
IKG2I4 – Software Project I
HUBUNGAN ANTAR ENTITY Hubungan antar entity dapat berupa: – One-to-one: relasi 11 Contoh: tiap mahasiswa hanya boleh punya satu NIM
– One-to-many atau many-to-one: relasi 1N – Many-to-many: relasi MN
Coba ingat kembali tentang multilist pada matakuliah Pemrograman Terstruktur 2: – Relasi 1N: hubungan list pegawai dan list anak Tiap pegawai boleh punya banyak anak, tiap anak hanya boleh punya 1 bapak
– Relasi NM: hubungan list dosen dan list matakuliah 10
3/11/2015
IKG2I4 – Software Project I
ERD & TABLE DIAGRAM Setelah ERD selesai dirancang, ERD tersebut harus dimapping atau dipetakan ke dalam table diagram, untuk kemudian disetting di software DBMS (Database Management System) misalnya Microsoft SQL SERVER atau ORACLE. Saat ini para system analyst yang sudah berpengalaman dan punya skill biasanya langsung merancang table diagram tanpa terlebih dahulu merepresentasikan diagram ERD secara formal. Contoh table diagram ada di halaman berikut. 11
3/11/2015
IKG2I4 – Software Project I
TABLE DIAGRAM HRM (Oracle)
12
3/11/2015
IKG2I4 – Software Project I
MAPPING ERD KE TABLE DIAGRAM Thumb rule (aturan tak resmi) untuk melakukan mapping ERD ke table diagram: – Untuk 2 entitas yang memiliki relasi 1-1 maka kedua entitas itu digabungkan dalam 1 tabel – Untuk 2 entitas yang memiliki relasi 1-N maka masingmasing entitas dibuat menjadi 1 tabel, sehingga ada 2 tabel untuk 2 entitas. Contoh: relasi pegawai-anak adalah relasi 1N, maka dibuat tabel pegawai dan ada tabel anak, di mana tabel anak memiliki foreign key (asosiasikan sebagai pointer pada relasi 1N) yang mengacu pada orangtuanya di tabel pegawai
13
3/11/2015
IKG2I4 – Software Project I
MAPPING ERD KE TABLE DIAGRAM Thumb rule (aturan tak resmi) untuk melakukan mapping ERD ke table diagram: – Untuk 2 entitas yang memiliki relasi N-M maka masingmasing entitas dibuat menjadi 1 tabel, ditambah tabel baru yaitu tabel relasi, sehingga ada 3 tabel untuk 2 entitas. Contoh: relasi dosen-matakuliah adalah relasi N-M, maka dibuat tabel dosen dan tabel matakuliah, serta tabel MKDOS di mana tabel MKDOS ini memiliki dua foreign key yang masingmasing mengacu ke tabel dosen dan ke tabel matakuliah.
Mekanisme pointer (pada kuliah PT2) sekarang digantikan dengan mekanisme foreign key. 14
3/11/2015
IKG2I4 – Software Project I
JADI APA ITU FOREIGN KEY? Foreign key adalah atribut suatu entity yang mengacu pada atribut (primary key) dari entity lain. Contoh, tabel dosen primary key-nya NIP, tabel matakuliah primary key-nya KodeMK, maka tabel MKDOS punya dua atribut foreign key yang masing-masing mengacu ke NIP (dosen) dan ke KodeMK (matakuliah). Semua nilai pada foreign key di tabel MKDOS harus sudah ada datanya di tabel dosen dan tabel matakuliah. Tabel dosen dan tabel matakuliah adalah tabel master atau reference, karena tabel ini menyimpan data yang dirujuk oleh tabel lain. 15
3/11/2015
IKG2I4 – Software Project I
NORMALISASI Dalam proses pengembangan software, database yang dirancang haruslah memenuhi kriteria : – Integrity: keutuhan sistem, bila ada data berubah maka perubahan itu harus diketahui oleh keseluruhan sistem – No redundancy: tidak ada data yang sama tersimpan di 2 tabel (repetition of information atau duplikasi data)
Ada beberapa tingkat ‘kenormalan’ database: – 1st Normal Form: masih banyak redundancy (very loose) – 2nd Normal Form – 3rd Normal Form – Boyce Codd Normal Form – 4th Normal Form: paling sedikit redundancy (very strict) 16
3/11/2015
IKG2I4 – Software Project I
NORMALISASI Proses untuk menormalkan atau mengurangi redundancy pada sebuah rancangan database disebut normalisasi. 4th Normal Form menetapkan kriteria yang sangat ketat (strict) dalam menerapkan prinsip integrity dan no-redundancy, namun akibatnya membutuhkan waktu yang lebiih lama untuk query ke banyak tabel. Oleh karena itu untuk menangani database yang sangat kompleks terkadang 3NF atau BCNF saja sudah cukup. 17
3/11/2015
IKG2I4 – Software Project I
QUERY SELECT KE SATU TABLE Query SELECT * FROM
– Digunakan untuk meretrieve atau mengambil data dari sebuah tabel database.
– Untuk memilih kolom tertentu, tuliskan nama kolomnya.
18
3/11/2015
IKG2I4 – Software Project I
CONTOH SELECT customer_id, customer_name FROM customer; SELECT * FROM customer;
19
3/11/2015
IKG2I4 – Software Project I
QUERY SELECT + WHERE Query SELECT * FROM
WHERE – WHERE digunakan untuk menyeleksi atau menyaring (filter) data yang ingin ditampilkan sesuai kebutuhan.
– Kondisi yang dituliskan di dalam WHERE adalah pernyataan/kalimat logika. Setiap baris data pada DBMS akan diseleksi berdasarkan kondisi tersebut. Jika memenuhi syarat kondisi (TRUE), maka baris tersebut ditampilkan. Jika sebaliknya (FALSE), maka tidak ditampilkan. 20
3/11/2015
IKG2I4 – Software Project I
CONTOH
21
3/11/2015
IKG2I4 – Software Project I
QUERY KE DUA TABLE (JOIN) Query SELECT * FROM JOIN – JOIN digunakan untuk mendapatkan data dari dua tabel. Setiap tabel dianggap sebagai himpunan, dan proses JOIN pada dasarnya adalah perkalian (cross product) antara dua buah himpunan, dengan syarat tertentu misalnya adanya kesamaan pada salah satu atribut (foreign key). – Contoh:
22
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI Menginstal ORACLE Mengimport skema HUMAN RESOURCE MANAGEMENT (file hr_master.sql) ke ORACLE – Pada tampilan utama Web Oracle di atas (http://127.0.0.1:8080/apex/), klik SQL Workshop -> klik SQL Scripts -> klik Upload -> Klik Choose File, arahkan ke file hr_master.sql. Isi nama script name = HR. – Klik Upload.
23
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI
24
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Selanjutnya akan muncul tampilan berikut:
– Klik di gambar icon di bawah Run.
25
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Selanjutnya, muncul tampilan di samping ini: – Klik Run Now dan tunggu beberapa detik sampai seluruh query dieksekusi. – Bila berhasil maka akan muncul status COMPLETE (lihat gambar di halaman berikutnya): 26
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Selanjutnya akan muncul tampilan berikut:
– Untuk menguji skema yang sudah Anda import, klik menu Home -> SQL Workshop -> Object Browser. Bila di situ ada table COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY, LOCATIONS, dan REGIONS, maka Anda telah berhasil meng-import skema HR.
27
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Eksekusi query-query berikut, amati hasil yang muncul, dan pahami mengenai arti query tersebut: SELECT * FROM departments; SELECT department_id, location_id FROM departments; SELECT last_name, hire_date, salary FROM employees; SELECT last_name, salary, (salary + 300) FROM employees; SELECT last_name, salary, (12*salary+100) FROM employees; SELECT last_name, job_id, salary, commission_pct FROM employees; SELECT last_name AS name, commission_pct comm FROM employees;
28
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Eksekusi query-query berikut, amati hasil yang muncul, dan pahami mengenai arti query tersebut: SELECT first_name || last_name || ' adalah ' || job_id AS "Detail Pegawai" FROM employees; SELECT * FROM locations; SELECT * FROM countries; SELECT * FROM jobs; SELECT * FROM job_history;
29
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Apa perbedaan hasil antara dua query berikut? SELECT department_id FROM employees; SELECT DISTINCT department_id FROM employees;
– Apa perbedaan hasil antara dua query berikut? SELECT job_id FROM employees; SELECT DISTINCT job_id FROM employees;
– Apa perbedaan hasil antara dua query berikut? SELECT department_id, job_id FROM employees; SELECT DISTINCT department_id, job_id FROM employees;
– Apa arti perintah DESCRIBE berikut ini? DESCRIBE employees; DESCRIBE departments; 30
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Apa perbedaan hasil antara dua query berikut? SELECT department_id FROM employees; SELECT DISTINCT department_id FROM employees;
– Apa perbedaan hasil antara dua query berikut? SELECT job_id FROM employees; SELECT DISTINCT job_id FROM employees;
– Apa perbedaan hasil antara dua query berikut? SELECT department_id, job_id FROM employees; SELECT DISTINCT department_id, job_id FROM employees;
– Apa arti perintah DESCRIBE berikut ini? DESCRIBE employees; DESCRIBE departments; 31
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Eksekusi query-query berikut, amati hasil yang muncul, dan pahami mengenai arti query tersebut: SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen'; SELECT first_name, last_name, hire_date FROM employees WHERE hire_date = '02/17/1996';
32
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Eksekusi query-query berikut, amati hasil yang muncul, dan pahami mengenai arti query tersebut: SELECT first_name, last_name, salary FROM employees WHERE salary > 6000; SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; SELECT first_name, last_name FROM employees WHERE last_name LIKE 'Mar%';
33
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Eksekusi query-query berikut, amati hasil yang muncul, dan pahami mengenai arti query tersebut: SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%MAN%'; SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%'; SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date; 34
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Eksekusi query-query berikut, amati hasil yang muncul, dan pahami mengenai arti query tersebut: SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC; SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;
35
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Buatlah query SQL untuk menampilkan data berikut ini: Create a query that displays the last name and salary of employees who earn more than 12000. Create a query that displays the last name and department number for employee number 176. Buatlah query untuk menampilkan data employees yang memiliki salary antara 5000-12000 (>= 5000 dan <= 12000). Buat juga query untuk menampilkan data di luar range itu. Create a query to display the last name, job ID, and start date for the employees with the last names of Matos and Taylor. Order the query in ascending order by the start date. Create a query to display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name. 36
3/11/2015
IKG2I4 – Software Project I
TUGAS PEKAN INI – Buatlah query SQL untuk menampilkan data berikut ini: Create a query that displays the last name and hire date for all employees who were hired in 1994. Create a query to display the last name and job title of all employees who do not have a manager. Create a query to display the last name, salary, and commission of all employees who earn commissions. Sort data in descending order of salary and commissions. Create a query to display the last name, job, and salary for all employees whose jobs are either those of a sales representative or of a stock clerk, and whose salaries are not equal to 2500, 3500, or 7000. Create a query to display the last name, salary, and commission for all employees whose commission_pct is 20%. 37
