1 / 17

Perancangan Basis Data

Perancangan Basis Data. Normalisasi Ke Dalam BCNF. Overview. Normalisasi ke BCNF, dan Contoh BCNF. Table Structure Decomposition BCNF. BCNF (Boyce-Code Normal Form). Ketika sebuah relasi memiliki lebih dari 1 candidate keys , anomali dapat terjadi sekalipun telah berada di 3NF.

joanna
Download Presentation

Perancangan Basis Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Perancangan Basis Data NormalisasiKeDalam BCNF

  2. Overview • Normalisasike BCNF, dan • Contoh BCNF

  3. Table Structure Decomposition BCNF

  4. BCNF (Boyce-Code Normal Form) • Ketikasebuahrelasimemilikilebihdari 1 candidate keys, anomalidapatterjadisekalipuntelahberada di 3NF. • 3NF tidakdapatmengakomodasirelasi yang memilikioverlapping candidate keys. • Contohnya: composite candidate keys dimanaterdapat minimal 1 atribut yang sama. • Dasardarikonsep BCNF adalahdeterminant. • Sebuahdeterminant adalahsetiapattribute (simple ataupuncomposite) dimanabeberapaatributlainnyaadalahfully functionally dependent. • Sebuahrelasidalam BCNF, jika, danhanyajikasetiapdeterminant adalahcandidate key.

  5. DasarTeori • Consider the following relation and determinants. R(a, b, c, d) • a, c -> b, d • a, d -> b • Agar dapatberada di BCNF, seluruh determinant harusmenjadisebuahcandidate key. Dalamrelasi R, (a,c->b,d) determinandigunakan, jadideterminanpertamaadalah BCNF. • (a,d->b) hasilnyaadalah (a, d) adalahprimary key, yang menentukan (determine) (b). Akantetapi, tidakmenentukan (c). Jadiinibukanlahsebuahcandidate key, dan R tidakdalam BCNF.

  6. Contoh: • DB(Patno,PatName,appNo,time,doctor) • Determinants: • Patno -> PatName • Patno, appNo -> Time,doctor • Time -> appNo • Duapilihanpadapemilihan primary key 1NF: • DB(Patno,PatName,appNo,time,doctor) (example 1a) • DB(Patno,PatName,appNo,time,doctor) (example 1b)

  7. Example 1A: • DB(Patno,PatName,appNo,time,doctor). • Tidakadapengulangan group, jaditelahberada di 1NF. • 2NF – menghilangkanpartial key dependencies: • DB(Patno, appNo, time, doctor) • R1(Patno, PatName) • 3NF – tidakadatransitif dependency jaditelahberada di 3NF • BCNF??????

  8. BCNF SetiapDeterminant adalahsebuahcandidate key DB(Patno, appNo, time, doctor) R1(Patno, PatName) • Apakah determinant adalah candidate key? • Patno-> PatName PatnoadadalamDB, tetapiPatNametidak, jaditidakrelevan. • Patno,appNo-> Time,doctor Apakahinicandidate key? Patno, appNoadalah KEY-nya, jadiiniadalahcandidate key. • Time -> appNo Time ada, danjugaappNo, jadirelevan. Apakahiniadalahcandidate key? Jikabenar, maka DB harusdirubahkedalam : DB(Patno,appNo,time,doctor)

  9. Merubahke BCNF • DB(Patno,appNo, time,doctor) R1(Patno,PatName) • BCNF: dirubahke DB(Patno, time, doctor) R1(Patno, PatName) R2(time, appNo) • timecukupuntukmenentukan appointment number dariseorangpasien. Sekarang BCNF terpenuhi, danrelasiakhirnyatelahberada di BCNF.

  10. Example 1B: DB(Patno, PatName, appNo, time, doctor) • Tidakadapengulangan group, jaditelahberada di 1NF • 2NF – menghilangkanpartial key dependencies: • DB(Patno, time, doctor) • R1(Patno, PatName) • R2(time, appNo) • 3NF – tidakadatransitif dependency, jaditelahberada di 3NF. • BCNF??

  11. BCNF Setiap Determinant adalahSebuah Candidate Key DB(Patno,time,doctor) R1(Patno, PatName) R2(time, appNo) • Apakah determinant adalahsebuah candidate key? • Patno-> PatName Patnoispresent in DB, but not PatName, irrelevant. • Patno,appNo-> Time,doctor Tidaksemuanyabergantung, jaditidak relevant. • Time -> appNo Time ada, tapitidakappNo, jaditidak relevant. • RelasinyamemenuhiBCNF.

  12. Contoh Lain BCNF

  13. Normalisasi Review (to BCNF)

  14. Normalisasi Review (to BCNF)

  15. CID IDateITimeStaffIDRmNo C01 8-22-99 10:00 S01 B107 C02 8-22-99 11:00 S01 B107 C03 8-22-99 10:00 S05 B108 C01 8-29-99 3:00 S06 B108 Boyce-Codd Normal Form • ContohSelanjutnya: • Anggota team perekrutanmenggelar interview calonkaryawandengancara one-to-one basis. Setiapcalon di-assign keruangtertentupadawaktu yang telahdiberikan. Setiapkandidathanyaakan di interview padatanggaltertentu. • Interview (CID, IDate, ITime, StaffID, RmNo)

  16. Boyce-Codd Normal Form • Relasitersebutmemilikifunctional dependencies berikut: • CID, IDateITime, StaffID, RmNo • StaffID, IDate, ITimeCID, RmNo • RmNo, Idate, ItimeStaffID, CID • StaffID, IDateRmNo • Relasitersebuttidakmemilikipartial atautransitive dependencies padaprimary key (CID, IDate) • Tidakberada di BCNF karena (StaffID, Idate) adalahsebuahdeterminant tetapibukansebuahcandidate key. • Relasibaru yang terbentuk di BCNF adalah: • Interview (CID, IDate, ITime, StaffID) • Room(StaffID, IDate, RmNo)

  17. Dependency Diagram Dependency diagram Fig 1 Fig 2 Fig 3

More Related