Perancangan Basis Data

1. Perancangan Basis Data Normalization Feri Sulianta Re-Arrange by : Education Fair Use material

2. Learning Objectives • Referential Integrity • Functional Dependency • Transitive Dependency • 1NF, 2NF, 3NF

3. NORMALISASI • Normalisasi adalah suatu teknik untuk mengorganisasi data ke dalam table – table untuk memenuhi kebutuhan pemakai

4. Tujuan Dari Normalisasi • Menghilangkan Kerangkapan Data • Mengurangi Kompleksitas • Mempermudah Pemodifikasian Data

5. Proses Normalisasi • Data diuraikan dalam bentuk tabel, selanjutnya di analisis berdasarkan persyaratan tertentu ke beberapa tingkat • Apabila tabel yang diuji belum memenuhi persyaratan, maka tabel tersebut perlu dipecah menjadi beberapa tabel yg lebih sederhana sampai memenuhi kriteria optimal

6. Tahapan Normalisasi BENTUK TIDAK NORMAL Menghilangkan perulangan group BENTUK NORMAL PERTAMA (1NF) Menghilangkan ketergantungan sebagian BENTUK NORMAL KEDUA (2NF) Menghilangkan ketergantungan transitif BENTUK NORMAL KETIGA (3NF)

7. Ketergantungan Fungsional • Atribut Y pada relasi R dikatakan tergantung fungsional pada atribut X, jika dan hanya jika stp nilai X pada relasi R mempunyai tepat satu nilai Y pada R • Misal,terdapat skema db pemasok-barang: pemasok(no-pem,na-pem)

8. Tabel Pemasok-barang • CTH KET FUNGSIONAL : NO-PEMNA-PEM

9. Ketergantungan Fungsional Penuh • Atribut y pada relasi r dikatakan tergantung fungsional penuh pada atribut x pd relasi r, jika y tidak tergantung pd subset dr x (bila x adalah key gabungan) • Kirim-barang(no-pem,na-pem,nobar,jumlah)

10. Tabel Kirim-barang • CTH KET FUNGSIONAL : NO-PEMNA-PEM • NO-BAR,NO-PEMJUMLAH (TERGANTUNG PENUH THD KEYNYA)

11. Ketergantungan Sebagian

12. Ketergantungan Transitif • Atribut z pada relasi r dikatakan tergantung transitif pada atribut x, jika atribut y tergantung pada atribut x pada relasi r dan atribut z tergantung pada atribut y pada relasi r. ( X  y, y  z, maka x  z)

13. Ketergantungan Transitif • KET FUNGSIONAL : • NO-PEM  KODE-KOTA • KODE-KOTA  KOTA, MAKA • NO-PEM  KOTA

14. CONTOH NORMALISASIBENTUK UN NORMAL s/d BENTUK NORMAL KE-TIGA (3NF)

15. Tabel Kirim-1 (Unnormal)

16. Bentuk Normal Kesatu (1nf) • Suatu relasi dikatakan sdh memenuhi bentuk normal kesatu bila setiap data bersifat atomik, yaitu setiap irisan baris dan kolom hanya mempunyai satu nilai data

17. Tabel Kirim-2 (1nf)

18. KODE-KOTA NO-PEM JUMLAH KOTA NO-BAR

19. Bentuk Normal Kedua (2nf) • Suatu relasi dikatakan sdh memenuhi bentuk normal kedua bila relasi tersebut sudah memenuhi bentuk normal pertama dan atribut yang bukan key sudah tergantung penuh terhadap key nya

20. Tabel Pemasok-1 (2nf)

21. Bentuk Normal Kedua (2nf) TABEL KIRIM-3 (2NF)

22. Bentuk Normal Ketiga (3nf) • Suatu relasi dikatakan sdh memenuhi bentuk normal ketiga bila relasi tersebut sudah memenuhi bentuk normal kedua dan atribut yang bukan key sudah tidak tergantung transitif terhadap key nya

23. Bentuk Normal Ketiga (3nf) TABEL KIRIM-3 (3NF) TABEL PEMASOK-3 (3NF) TABEL PEMASOK-2 (3NF)

24. The Question & Quiz Ubah bentuk tabel tidak normal berikut sampai memenuhi bentuk normal 3 (3NF)

25. Normalisasi Database Perkuliahan • ASUMSI : • SEORANG MHS DAPAT MENGAMBIL BEBERAPA MATAKULIAH • SATU MATAKULIAH DAPAT DIAMBIL OLEH LBH DR 1 MHSW • SATU MATAKULIAH HANYA DIAJARKAN SATU DOSEN • SATU DOSEN DAPAT MENGAJAR BEBERAPA MATAKULIAH • SEORANG MHSW PD MATAKULIAH TERTENTU HANYA MEMPUNYAI SATU NILAI

26. Diagram Ketergantungan Fungsional NAMA-MHS NO-MHS JURUSAN NILAI NAMA-MK KODE-MK KODE-DOSEN NAMA-DOSEN

27. Normalisasi – Contoh II

28. Un Normal Form The data we would want to store could be expressed as:

29. Normalization 1 We could place the data into a table called: tblProjects_Employees

30. Normalization 2 tblProjects We now have 3 tables: tblProjects_Employees tblEmployees

31. Normalization Looking at the project note the reduction in: • Redundant data The text “Madagascar travel site” is stored once only, not for each occurrence of an employee working on the project. • Inconsistent data Because we only store the project name once we are less likely to enter “Madagascat” The link is made through the key, Project No. Obviously there is no way to remove this duplication without losing the relation altogether, but it is far more efficient storing a short number repeatedly, than a large chunk of text.

32. Normalization The solution, as before, is to remove this excess data to another table. We do this by: • Looking for Transitive Relationships Relationships where a non-key attribute is dependent on another non-key attribute. Hourly rate should depend on rate category BUT rate category is not a key • Removing Transitive Relationships As before we remove the redundant data and place it in a separate table. In this case we create a new table tblRates and add the fields rate category and hourly rate. We then delete hourly rate from the employees table.

33. Normalization 3 tblProjects We now have 4 tables: tblProjects_Employees tblEmployees tblRates

34. Normalization Again, we have cut down on redundancy and it is now impossible to assume Rate category A is associated with anything but £90. Our model is now in its most efficient format with: • Minimum REDUNDANCY • Minimum INCONSISTENCY

35. Normalisasi – Contoh III

36. First Normal Form (1NF) • “Flattening” the table • All columns (fields) must have no repeating items in columns Solution: make a separate table for each set of attributes with a primary key (parser, append query)

37. Second Normal Form (2NF) • In 2NF and every non-key column is fully dependent on the (entire) primary key • Means : Does the key field imply the rest of the fields? • Do we need to know both OrderID and Item to know the Customer and Date? Solution: Remove to a separate table (Make Table)

38. Third Normal Form (3NF) • In 3NF, every non-key column is mutually independent • means : no transitive dependency like calculations • Solution: Put calculations in queries and forms

39. Transitive Dependency • Transitive Dependency is a condition where • A, B and C are attributes of a relation such that if A  B and B  C, • then C is transitively dependent on A through B. (Provided that A is not functionally dependent on B or C).

40. DreamHome Example

41. Example - Normalization UNF to 1NF Relation

42. Example - Normalization UNF to 1NF Relation

43. Example - Normalization UNF to 1NF Relation

44. Second Normal Form (2NF) • A relation that is in 1NF, and • Every non-primary-key attribute is functionaly dependent only on the primary key, but not any subset of the primary key.

45. 1NF to 2NF • Identify the primary key for the 1NF relation. • Identify the functional dependencies in the relation. • If partial dependencies exist on the primary key remove them by placing them in a new relation.

46. FDs forCustomer_Rental Relation Rental (Customer_No, Property_No, RentStart, RentFinish) Customer (Customer_No, Cname) Property_Owner (Property_No, Paddress, Rent, Owner_No, Oname)

47. FDs forCustomer_Rental Relation Rental (Customer_No, Property_No, RentStart, RentFinish) Customer (Customer_No, Cname) Property_Owner (Property_No, Paddress, Rent, Owner_No, Oname)

48. FDs forCustomer_Rental Relation Rental (Customer_No, Property_No, RentStart, RentFinish) Customer (Customer_No, Cname) Property_Owner (Property_No, Paddress, Rent, Owner_No, Oname)

49. FDs forCustomer_Rental Relation Rental (Customer_No, Property_No, RentStart, RentFinish) Customer (Customer_No, Cname) Property_Owner (Property_No, Paddress, Rent, Owner_No, Oname)

50. FDs forCustomer_Rental Relation Rental (Customer_No, Property_No, RentStart, RentFinish) Customer (Customer_No, Cname) Property_Owner (Property_No, Paddress, Rent, Owner_No, Oname)