Pada artikel ini kami akan mencoba mempertimbangkan segala sesuatu yang berhubungan dengan kunci SQL: untuk apa pembuatan dan pembatasan kunci? Secara umum: akan membosankan 😉
Rencana hari ini adalah:
Dalam teori basis data relasional - kunci Ini adalah entitas tertentu yang dibuat untuk menetapkan batasan tertentu yang menjaga integritas dan ketersediaan data dalam tabel database.
Dengan kata sederhana, kuncinya ada di dalam sql dibuat untuk menunjukkan fungsionalitas tambahan untuk kolom. Baik itu keunikan atau fakta bahwa kolom tersebut mereferensikan tabel lain (kunci asing).
Kunci utama
Kolom yang harus unik dalam database ditandai dengan kunci utama. Kunci utama atau primary key artinya nilai kolom kunci utama tidak dapat diulangi dalam tabel. Dengan demikian, kunci ini memungkinkan Anda mengidentifikasi catatan dalam tabel secara unik tanpa takut nilai kolom akan terulang. Sebagai contoh saja: katakanlah Anda memiliki tabel pengguna. Tabel ini memiliki kolom berikut: nama lengkap, tahun lahir, telepon. Bagaimana cara mengidentifikasi pengguna? Parameter seperti nama lengkap dan nomor telepon tidak dapat dipercaya. Bagaimanapun, kita dapat memiliki beberapa pengguna tidak hanya dengan nama belakang yang sama, tetapi juga dengan nama depan yang sama. Nomor telepon dapat berubah seiring waktu dan pengguna yang memiliki nomor telepon tersebut mungkin bukan pengguna yang ada di database kami.
Inilah sebabnya mengapa kunci utama diciptakan. Setelah diberi pengidentifikasi unik dan hanya itu. DI DALAM mySql pada contoh yang kami lakukan semua contoh dari lapangan AUTO_INCREMENT tidak dapat disetel kecuali Anda menunjukkan bahwa ini adalah kunci utama.
Saya rasa tidak ada gunanya menyebutkan bahwa bidang yang ditandai sebagai kunci utama tidak boleh kosong saat membuat catatan.
Kunci eksternal ( kunci asing)
Apakah masih ada lagi kunci eksternal (kunci asing). Ini juga disebut referensi. Diperlukan untuk menghubungkan tabel bersama-sama.
Jika dilihat pada gambar di atas, Foreign Key akan menjadi field Supplier pada tabel Shoes. Biasanya, saat membuat tabel, Anda menentukan kolom nilai integer unik. Bagaimana kami melakukannya saat kami membuat tabel pemasok
Kolom pemasok_id akan unik untuk setiap entri. Nilainya akan muncul di kolom pemberi di meja sepatu. Saya sarankan segera melihat contoh bagaimana kunci asing dibuat.
Membuat kunci asing
buat sepatu tabel (shoes_id int auto_increment kunci utama, teks judul, ukuran int, harga mengambang, hitungan int, ketik varchar(30), pemasok int, kunci asing (pemasok) referensi pemasok (pemasok_id));
Seperti yang Anda lihat pada contoh di atas, sintaks untuk membuat kunci asing cukup sederhana. Anda perlu menambahkan bidang ke tabel, lalu mendeklarasikan bidang ini sebagai kunci asing dan menunjukkan ke mana bidang tersebut akan dirujuk. Dalam hal ini lapangan pemasok akan merujuk ke lapangan pemasok_id di meja pemasok
Kunci komposit (kunci komposit)
Sedangkan untuk kunci komposit, ini adalah beberapa kunci utama dalam sebuah tabel. Jadi, setelah menciptakan kunci komposit, keunikan record akan diperiksa oleh field yang digabungkan ke dalam kunci ini.
Ada situasi ketika, saat memasukkan ke dalam tabel, Anda perlu memeriksa keunikan catatan menggunakan beberapa bidang sekaligus. Inilah sebabnya mengapa kunci komposit diciptakan. Misalnya, saya akan membuat tabel sederhana dengan kunci komposit untuk menampilkan sintaks:
Buat tes tabel (field_1 int, teks field_2, field_3 bigint, kunci utama (field_1, field_3));
Dalam contoh di atas, dua bidang digabungkan menjadi kunci komposit dan tidak akan ada catatan dalam tabel dengan bidang yang identik tersebut.
Itu semua tentang kuncinya SQL. Tutorial kecil ini merupakan persiapan artikel dimana kita akan melihat secara detail bagaimana menggabungkan tabel sehingga membentuk satu database.
INI BERLAKU UNTUK: SQL Server (sejak 2016)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse
Kunci primer dan kunci asing adalah dua jenis batasan yang dapat digunakan untuk memastikan integritas data dalam tabel SQL Server. Ini adalah objek database yang penting.
Topik ini dibahas di bagian berikut.
Batasan Kunci Utama
Batasan Kunci Asing
Tugas terkait
Biasanya sebuah tabel memiliki kolom atau kombinasi kolom yang berisi nilai-nilai yang secara unik mengidentifikasi setiap baris dalam tabel. Kolom atau kolom ini disebut kunci utama (PK) tabel dan memberikan integritas pada entitas tabel. Batasan kunci utama sering kali ditentukan pada kolom identitas karena batasan tersebut memastikan bahwa datanya unik.
Saat Anda menetapkan batasan kunci utama pada tabel di komponen, Mesin Database memastikan bahwa data tersebut unik dengan secara otomatis membuat indeks unik pada kolom kunci utama. Indeks ini juga menyediakan akses cepat ke data saat menggunakan kunci utama dalam kueri. Jika batasan kunci utama ditentukan pada lebih dari satu kolom, nilainya dapat diduplikasi dalam kolom yang sama, namun setiap kombinasi nilai semua kolom dalam definisi batasan kunci utama harus unik.
Seperti yang ditunjukkan pada gambar berikut, kolom ID Produk Dan ID Vendor di meja Pembelian.ProdukVendor membentuk batasan kunci utama gabungan untuk tabel tertentu. Hal ini memastikan bahwa setiap baris dalam tabel Penjual Produk mempunyai kombinasi makna yang unik ID Produk Dan ID Vendor. Ini mencegah baris duplikat disisipkan.
Sebuah tabel hanya dapat memiliki satu batasan kunci utama.
Kunci utama tidak boleh lebih dari 16 kolom, dan total panjang kunci tidak boleh melebihi 900 byte.
Indeks yang dibentuk oleh batasan kunci utama tidak boleh menyebabkan jumlah indeks dalam tabel melebihi batas 999 indeks noncluster dan 1 indeks clustered.
Jika batasan kunci utama tidak menentukan apakah indeks tersebut terklaster atau tidak terklaster, indeks terklaster akan dibuat jika tidak ada pada tabel.
Semua kolom dengan batasan kunci utama harus didefinisikan sebagai non-nullable. Jika nullability tidak ditentukan, maka semua kolom dengan batasan kunci utama disetel ke non-nullable.
Jika kunci utama ditentukan pada kolom tipe data yang ditentukan pengguna CLR, implementasi tipe tersebut harus mendukung pengurutan biner.
Kunci asing (FK) adalah kolom atau kombinasi kolom yang digunakan untuk memaksa hubungan antara data dalam dua tabel untuk mengontrol data yang dapat disimpan dalam tabel kunci asing. Jika satu atau lebih kolom yang berisi kunci utama untuk satu tabel direferensikan dalam satu atau lebih kolom tabel lain, tautan kunci asing akan membuat hubungan antara dua tabel. Kolom ini menjadi kunci asing pada tabel kedua.
Misalnya, tabel Penjualan.SalesOrderHeader ditautkan ke tabel Penjualan.SalesPerson menggunakan kunci asing karena ada hubungan logis antara pesanan penjualan dan manajer penjualan. Kolom ID Tenaga Penjualan di meja Penjualan.SalesOrderHeader cocok dengan kolom kunci utama dalam tabel Pramuniaga. Kolom ID Tenaga Penjualan di meja Penjualan.SalesOrderHeader adalah kunci asing ke tabel Pramuniaga. Dengan membangun hubungan ini menggunakan kunci asing, nilai untuk ID Tenaga Penjualan tidak dapat dimasukkan ke dalam tabel SalesOrderHeader, jika saat ini tidak terdapat dalam tabel Pramuniaga.
Jumlah maksimum tabel dan kolom yang bisa direferensikan tabel sebagai kunci asing (referensi keluar) adalah 253. SQL Server 2016 meningkatkan batas jumlah tabel dan kolom lain yang bisa mereferensikan kolom di tabel yang sama (referensi masuk), dari 253 hingga 10.000. (Membutuhkan tingkat kompatibilitas minimal 130.) Pembesaran tunduk pada batasan berikut:
Melebihi 253 referensi kunci asing hanya didukung untuk operasi DELETE DML. Operasi UPDATE dan MERGE tidak didukung.
Referensi kunci asing yang melebihi 253 saat ini tidak tersedia untuk indeks penyimpan kolom, tabel dengan memori yang dioptimalkan, database Stretch, atau tabel yang dipartisi kunci asing.
Indeks dalam Batasan Kunci Asing
Tidak seperti batasan kunci utama, saat Anda membuat batasan kunci asing, indeks terkait tidak dibuat secara otomatis. Namun, sering kali perlu membuat indeks pada kunci asing secara manual karena alasan berikut:
Kolom kunci asing sering digunakan dalam kriteria gabungan ketika digunakan bersama untuk mengkueri data dari tabel terkait. Hal ini diterapkan dengan memetakan satu atau beberapa kolom dalam batasan kunci asing di satu tabel ke satu atau lebih kolom kunci utama atau unik di tabel lain. Indeks memungkinkan Mesin Database dengan cepat menemukan data terkait dalam tabel kunci asing. Namun, membuat indeks bukanlah suatu keharusan. Data dari dua tabel terkait dapat digabungkan meskipun tidak ada batasan kunci utama atau kunci asing yang ditentukan di antara tabel, namun hubungan kunci asing antara dua tabel menunjukkan bahwa kedua tabel dioptimalkan untuk digunakan bersama dalam kueri yang menggunakan kunci sebagai kriteria.
Batasan kunci asing memeriksa perubahan pada batasan kunci utama pada tabel terkait.
Integritas referensial
Tujuan utama dari batasan kunci asing adalah untuk mengontrol data yang dapat disimpan dalam tabel kunci asing, namun batasan tersebut juga mengontrol perubahan pada data dalam tabel kunci utama. Misalnya, jika Anda menghapus baris manajer penjualan dari tabel Penjualan.SalesPerson, yang ID-nya digunakan dalam pesanan penjualan di tabel Penjualan.SalesOrderHeader, integritas referensial kedua tabel akan dilanggar. Pesanan penjualan manajer jarak jauh dalam tabel SalesOrderHeader akan menjadi tidak valid tanpa koneksi ke data di tabel Pramuniaga.
Kendala kunci asing mencegah terjadinya situasi ini. Batasan menerapkan integritas referensial dengan cara berikut: Batasan ini mencegah perubahan pada data dalam tabel kunci utama jika perubahan tersebut akan membatalkan referensi dalam tabel kunci asing. Jika Anda mencoba menghapus baris dalam tabel kunci utama atau mengubah nilai kunci tersebut, jika Anda menemukan bahwa nilai kunci utama yang dihapus atau diubah memiliki nilai yang sesuai dalam batasan kunci asing di tabel lain, tindakan tersebut akan gagal. Agar berhasil mengubah atau menghapus baris dengan batasan kunci asing, Anda harus terlebih dahulu menghapus data kunci asing di tabel kunci asing atau mengubah data di tabel kunci asing yang menghubungkan kunci asing dengan data di kunci utama lainnya.
Integritas referensial bertingkat
Dengan menggunakan batasan integritas referensial berjenjang, Anda dapat menentukan tindakan yang akan diambil oleh Mesin Database ketika pengguna mencoba menghapus atau memperbarui kunci yang ditunjuk oleh kunci asing yang masih ada. Tindakan berjenjang berikut dapat ditentukan.
TIDAK ADA TINDAKAN
Mesin Basis Data menghasilkan kesalahan dan kemudian mengembalikan operasi penghapusan atau pembaruan pada baris dalam tabel induk.
RIAM
Baris terkait diperbarui atau dihapus dari tabel referensi jika baris tersebut diperbarui atau dihapus dari tabel induk. Nilai CASCADE tidak dapat ditentukan jika kolom bertipe stempel waktu adalah bagian dari kunci asing atau referensi. Tindakan ON DELETE CASCADE tidak dapat ditentukan pada tabel yang memiliki pemicu INSTEAD OF DELETE yang ditentukan. Klausa ON UPDATE CASCADE tidak dapat ditentukan pada tabel yang memiliki pemicu INSTEAD OF UPDATE yang ditentukan.
TETAPKAN BATAL
Semua nilai yang membentuk kunci asing disetel ke NULL ketika baris terkait di tabel induk diperbarui atau dihapus. Untuk memenuhi batasan ini, kolom kunci asing harus dapat dibatalkan. Tidak dapat disetel pada tabel yang memiliki pemicu BUKAN UPDATE yang ditentukan.
SET STANDAR
Semua nilai yang membentuk kunci asing disetel ke nilai defaultnya ketika baris terkait dalam tabel induk dihapus atau diperbarui. Untuk memenuhi batasan ini, semua kolom kunci asing harus memiliki definisi default. Jika kolom dapat dibatalkan dan nilai default tidak ditentukan secara eksplisit, nilai default kolom menjadi NULL. Tidak dapat disetel pada tabel yang memiliki pemicu BUKAN UPDATE yang ditentukan.
Kata kunci CASCADE, SET NULL, SET DEFAULT, dan NO ACTION dapat digabungkan dalam tabel yang mempunyai hubungan saling referensi. Jika Mesin Basis Data menemukan kata kunci NO ACTION, ia akan menghentikan dan mengembalikan operasi CASCADE, SET NULL, dan SET DEFAULT terkait. Jika pernyataan DELETE berisi kombinasi kata kunci CASCADE, SET NULL, SET DEFAULT, dan NO ACTION, semua operasi CASCADE, SET NULL, dan SET DEFAULT dilakukan sebelum Mesin Database mencari operasi NO ACTION.
Pemicu dan tindakan referensi berjenjang
Tindakan referensi bertingkat memicu pemicu AFTER UPDATE atau AFTER DELETE sebagai berikut:
Semua tindakan referensi berjenjang yang secara langsung disebabkan oleh pernyataan DELETE atau UPDATE asli akan dieksekusi terlebih dahulu.
Jika ada pemicu SETELAH yang ditentukan pada tabel yang telah berubah, pemicu tersebut akan diaktifkan setelah semua tindakan berjenjang selesai. Ini memicu kebakaran dalam urutan kebalikan dari tindakan berjenjang. Jika beberapa pemicu ditentukan untuk satu tabel, pemicu tersebut akan diaktifkan secara acak kecuali pemicu pertama dan terakhir tabel tersebut dipilih. Urutan ini ditentukan oleh prosedur.
Jika rangkaian tindakan berjenjang berasal dari tabel yang merupakan target langsung dari tindakan DELETE atau UPDATE, urutan pemicu kebakaran rangkaian tindakan tidak ditentukan. Namun, satu rangkaian tindakan selalu memicu semua pemicunya sebelum rangkaian tindakan berikutnya.
Pemicu SETELAH pada tabel yang merupakan target langsung dari tindakan DELETE atau UPDATE akan diaktifkan terlepas dari apakah ada baris yang berubah. Dalam hal ini, tidak ada tabel lain yang terpengaruh oleh cascading.
Jika salah satu pemicu sebelumnya melakukan operasi DELETE atau UPDATE pada tabel lain, operasi tersebut dapat memicu rangkaian tindakan berjenjangnya sendiri. Urutan tindakan sekunder ini diproses untuk setiap operasi DELETE atau UPDATE setelah semua pemicu urutan tindakan utama selesai. Proses ini dapat diulangi secara rekursif untuk operasi DELETE atau UPDATE berikutnya.
Melakukan operasi CREATE, ALTER, DELETE, atau operasi DDL lainnya di dalam pemicu dapat menyebabkan pemicu DDL terpicu. Hal ini dapat menyebabkan operasi DELETE atau UPDATE lebih lanjut, yang akan memulai rangkaian tindakan berjenjang tambahan dan memicu pemicunya.
Jika kesalahan terjadi pada urutan tertentu dari tindakan referensi berjenjang, tidak ada pemicu SETELAH yang akan diaktifkan pada urutan tersebut, dan operasi DELETE atau UPDATE yang dihasilkan oleh urutan tersebut akan dibatalkan.
Tabel yang memiliki pemicu INSTEAD OF yang ditentukan juga dapat memiliki klausa REFERENCES yang menentukan tindakan berjenjang tertentu. Namun, pemicu SETELAH pada tabel target tindakan berjenjang dapat mengeluarkan pernyataan INSERT, UPDATE, atau DELETE pada tabel atau tampilan lain, yang akan memicu pemicu INSTEAD OF pada objek tersebut.
Tabel berikut mencantumkan tugas umum yang terkait dengan batasan kunci utama dan kunci asing.
Gambar tersebut menunjukkan tabel (rasio derajat 5) yang berisi beberapa informasi tentang karyawan suatu perusahaan hipotetis. Baris tabel berhubungan dengan tupel. Setiap baris sebenarnya merupakan deskripsi dari satu objek dunia nyata (dalam hal ini, pekerja), yang karakteristiknya terdapat dalam kolom. Hubungan relasional berhubungan dengan kumpulan entitas, dan tupel berhubungan dengan entitas. Kolom dalam tabel yang mewakili hubungan relasional disebut atribut.
Setiap atribut didefinisikan pada suatu domain, sehingga domain dapat dianggap sebagai kumpulan nilai yang valid untuk atribut tertentu. Beberapa atribut dari hubungan yang sama, dan bahkan atribut dari hubungan yang berbeda, dapat didefinisikan pada domain yang sama.
Atribut yang nilainya secara unik mengidentifikasi tupel disebut kunci (atau sederhananya kunci). Kuncinya adalah atribut "Nomor personel", karena nilainya unik untuk setiap karyawan perusahaan. Jika tupel diidentifikasi hanya dengan menggabungkan nilai beberapa atribut, maka relasi tersebut dikatakan mempunyai kunci komposit.
Kunci utama- dalam model data relasional, salah satu kunci potensial suatu hubungan, dipilih sebagai kunci utama (atau kunci default).
Suatu relasi dapat berisi banyak kunci. Salah satu kuncinya selalu dideklarasikan utama, nilainya tidak dapat diperbarui. Semua kunci relasi lainnya dipanggil kunci yang mungkin.
Dari sudut pandang teoritis, semua kunci relasi potensial (yang mungkin) adalah ekuivalen, yaitu memiliki sifat keunikan dan minimalis yang sama. Namun, kunci utama biasanya dipilih dari kunci potensial yang paling sesuai untuk tujuan praktis tertentu, misalnya untuk pembuatan luar kunci dalam hal lain atau untuk membuat indeks berkerumun. Oleh karena itu, sebagai aturan, salah satu yang memiliki ukuran terkecil (penyimpanan fisik) dan/atau berisi jumlah atribut terkecil dipilih sebagai kunci utama.
Jika kunci utama terdiri dari satu atribut, yang disebut dengan kunci sederhana.
Jika kunci utama terdiri dari dua atau lebih atribut, disebut kunci majemuk. Jadi, nama depan, nama belakang, patronimik, nomor paspor, seri paspor tidak bisa menjadi kunci utama satu per satu, karena bisa sama untuk dua orang atau lebih. Namun tidak ada dua dokumen pribadi yang sejenis, seri dan nomor yang sama. Oleh karena itu, dalam suatu relasi yang berisi data tentang orang, kunci utama dapat berupa subset atribut yang terdiri dari jenis dokumen pribadi, seri, dan nomornya.
Berbeda dengan model data hierarki dan jaringan, model relasional tidak memiliki konsep hubungan kelompok. Untuk mencerminkan asosiasi antara tupel dari relasi yang berbeda, duplikasi kuncinya digunakan.
Atribut yang merupakan salinan kunci dari hubungan lain disebut kunci asing.
Misalnya, hubungan antara hubungan DEPARTMENT dan EMPLOYEE dibuat dengan menyalin kunci utama "Nomor_Departemen" dari hubungan pertama ke hubungan kedua. Jadi, untuk mendapatkan daftar karyawan suatu departemen tertentu, perlu: 1) Dari tabel DEPARTEMEN, tetapkan nilai atribut "Nomor_Departemen" , sesuai dengan “Nama_Departemen” ini. 2) pilih semua catatan dari tabel EMPLOYEE, nilai atribut "Nomor_Departemen" yang sama dengan yang diperoleh pada langkah sebelumnya. Untuk mengetahui di departemen mana seorang karyawan bekerja, Anda perlu melakukan operasi sebaliknya: 1) Tentukan "Nomor_Departemen" dari tabel KARYAWAN. 2) Dengan menggunakan nilai yang diperoleh, kami menemukan entri di tabel DEPARTMENT.
18. Normalisasi dalam database relasional, konsep bentuk normal dalam desain database.
Bentuk biasa - properti hubungan dalam model data relasional, yang mengkarakterisasinya dari sudut pandang redundansi, yang berpotensi menyebabkan hasil pengambilan sampel atau perubahan data yang salah secara logis. Bentuk normal didefinisikan sebagai sekumpulan persyaratan yang harus dipenuhi oleh suatu relasi.
Proses mengubah database ke bentuk normal disebut normalisasi . Normalisasi dimaksudkan untuk membawa struktur database ke bentuk yang memberikan redundansi minimal, artinya normalisasi tidak dimaksudkan untuk mengurangi atau meningkatkan produktivitas kerja atau memperkecil atau menambah volume database. Tujuan akhir dari normalisasi adalah untuk mengurangi potensi ketidakkonsistenan informasi yang disimpan dalam database.
Penghapusan redundansi biasanya dilakukan dengan menguraikan relasi sedemikian rupa sehingga hanya fakta primer yang disimpan dalam setiap relasi (yaitu fakta yang tidak disimpulkan dari fakta tersimpan lainnya).
Ketergantungan fungsional.
Basis data relasional berisi informasi struktural dan semantik. Struktur database ditentukan oleh jumlah dan tipe relasi yang dikandungnya, dan relasi satu-ke-banyak yang ada di antara tupel relasi tersebut. Bagian semantik menggambarkan kumpulan ketergantungan fungsional yang ada antara atribut-atribut hubungan ini. Mari kita definisikan ketergantungan fungsional.
19. 1NF: Definisi dasar dan aturan transformasi.
Untuk membahas bentuk normal pertama, diperlukan dua definisi:
Atribut sederhana - atribut yang nilainya bersifat atomik (tidak dapat dibagi).
Atribut kompleks - diperoleh dengan menghubungkan beberapa atribut atom yang dapat didefinisikan pada domain yang sama atau berbeda (disebut juga vektor atau agregat data).
Definisi bentuk normal pertama:
suatu relasi berada dalam 1NF jika nilai semua atributnya bersifat atomik. . Jika tidak, ini bukan tabel sama sekali dan atribut tersebut harus didekomposisi.
Mari kita lihat sebuah contoh:
Dalam database departemen SDM perusahaan, perlu untuk menyimpan informasi tentang karyawan yang dapat dicoba untuk disajikan sehubungan dengan
KARYAWAN(EMPLOYEE_NUMBER, NAMA, TANGGAL LAHIR, WORK_HISTORY, ANAK).
Dari pertimbangan yang cermat terhadap hubungan ini, maka atribut-atributnya "sejarah_pekerjaan" Dan "anak-anak" kompleks, terlebih lagi, atributnya "sejarah_pekerjaan" menyertakan atribut kompleks lainnya "gaji_sejarah".
Unit-unit ini terlihat seperti ini:
JOB_HISTORY (TANGGAL_TERIMA, NAMA, GAJI_HISTORY),
GAJI_HISTORY (TANGGAL_APPOINTMENT, GAJI),
ANAK-ANAK (NAMA_ANAK, TAHUN_LAHIR).
Koneksi mereka ditunjukkan pada Gambar. 3.3.
Gambar.3.3. Sikap awal.
Untuk membawa relasi asli SERVANT ke bentuk normal pertama, maka perlu diuraikan menjadi empat relasi, seperti terlihat pada gambar berikut: 
Gambar.3.4. Kumpulan relasi yang dinormalisasi.
Di sini, kunci utama setiap hubungan disorot dengan bingkai biru, nama kunci asing menggunakan font biru. Ingatlah bahwa kunci asing digunakan untuk mewakili ketergantungan fungsional yang ada dalam relasi sumber. Ketergantungan fungsional ini ditunjukkan oleh garis dengan panah.
Algoritma normalisasi dijelaskan oleh E.F. Codd sebagai berikut:
- Dimulai dengan relasi di bagian atas pohon (Gambar 3.3.), kunci primernya diambil, dan setiap relasi subordinat langsung diperluas dengan memasukkan domain atau kombinasi domain dari kunci primer tersebut.
- Kunci Utama setiap relasi yang diperluas dengan cara ini terdiri dari Kunci Utama yang dimiliki relasi sebelum perluasan dan Kunci Utama tambahan dari relasi induk.
- Setelah ini, semua domain non-sederhana dihapus dari relasi induk, simpul teratas pohon dihapus, dan prosedur yang sama diulangi untuk setiap subpohon yang tersisa.
20. 2NF: Definisi dasar dan aturan transformasi.
Seringkali kunci utama suatu hubungan mencakup beberapa atribut (dalam hal ini disebut gabungan) - lihat, misalnya, relasi ANAK-ANAK yang ditunjukkan pada Gambar. 3.4 pertanyaan 19. Pada saat yang sama, konsep diperkenalkan ketergantungan fungsional penuh.
Definisi:
atribut bukan kunci secara fungsional bergantung penuh pada kunci komposit jika secara fungsional bergantung pada seluruh kunci secara keseluruhan, tetapi tidak bergantung secara fungsional pada salah satu atribut penyusunnya.
Contoh:
Misalkan ada relasi SUPPLY (N_SUPPLIER, product, PRICE).
Pemasok mungkin memasok produk yang berbeda, dan produk yang sama mungkin dipasok oleh pemasok berbeda. Maka kunci relasinya adalah "N_pemasok + produk". Biarkan semua pemasok memasok barang dengan harga yang sama. Kemudian kita memiliki dependensi fungsional berikut:
- N_pemasok, produk -> harga
- produk -> harga
Ketergantungan fungsional yang tidak lengkap dari atribut harga pada kunci menyebabkan anomali berikut: ketika harga suatu barang berubah, pandangan penuh tentang hubungan tersebut diperlukan untuk mengubah semua catatan tentang pemasoknya. Anomali ini merupakan konsekuensi dari fakta bahwa dua fakta semantik digabungkan dalam satu struktur data. Perluasan berikut memberikan relasi dalam 2NF:
- PENGIRIMAN (N_SUPPLIER, PRODUK)
- PRODUK_HARGA (PRODUK, HARGA)
Jadi kamu bisa memberi
Pengertian bentuk normal kedua: Suatu relasi berada dalam 2NF jika berada dalam 1NF dan setiap atribut bukan kunci bergantung sepenuhnya pada kunci tersebut.
21. 3NF: Definisi dasar dan aturan transformasi.
Sebelum membahas bentuk normal ketiga, perlu diperkenalkan konsep: ketergantungan fungsional transitif.
Definisi:
Misalkan X, Y, Z adalah tiga atribut dari suatu relasi. Dalam hal ini, X --> Y dan Y --> Z, tetapi tidak ada korespondensi terbalik, yaitu. Z -/-> Y dan Y -/-> X. Maka Z bergantung secara transitif pada X.
Biarkan ada hubungan PENYIMPANAN ( TEGAS, GUDANG, VOLUME), yang berisi informasi tentang perusahaan yang menerima barang dari gudang dan volume gudang tersebut. Atribut kunci - "tegas". Jika setiap perusahaan hanya dapat menerima barang dari satu gudang, maka dalam hal ini terdapat ketergantungan fungsional sebagai berikut:
- tegas -> saham
- saham -> volume
Dalam hal ini, timbul anomali:
- jika saat ini tidak ada perusahaan yang menerima barang dari gudang, maka data volumenya tidak dapat dimasukkan ke dalam database (karena atribut kunci tidak ditentukan)
- jika volume gudang berubah, perlu untuk melihat seluruh hubungan dan mengubah kartu untuk semua perusahaan yang terkait dengan gudang ini.
Untuk menghilangkan anomali ini, relasi asli perlu didekomposisi menjadi dua:
- PENYIMPANAN ( TEGAS, SAHAM)
- PENYIMPANAN_VOLUME ( SAHAM, VOLUME)
Pengertian Bentuk Normal Ketiga:
Suatu relasi berada dalam 3NF jika berada dalam 2NF dan setiap atribut bukan kunci tidak bergantung secara transitif pada kunci utama.
InterBase dapat menggunakan jenis pembatasan berikut:- PRIMARY KEY - kunci utama tabel.
- UNIK - kunci tabel unik.
- KUNCI ASING- kunci asing, menyediakan tautan ke tabel lain dan menjamin integritas referensial antara induk dan tabel anak.
Catatan tentang terminologi
Jika Anda seperti penulis kursus ini karena Anda suka mencari jawaban atas pertanyaan yang Anda minati secara komprehensif, dalam karya berbeda dari penulis berbeda, maka Anda pasti akan melihat beberapa kebingungan dalam definisinya. utama (master) -> bawahan (detail) tabel. Ingatlah bahwa tabel utama sering disebut tabel induk, dan tabel bawahan sering disebut tabel anak.
Hal ini mungkin disebabkan oleh bagaimana definisi ini diinterpretasikan dalam DBMS server lokal dan SQL.
Dalam DBMS lokal, tabel utama adalah tabel yang berisi data utama, dan tabel bawahan berisi data tambahan. Mari kita ambil contoh, tiga tabel terkait. Yang pertama berisi data penjualan, yang kedua - tentang produk dan yang ketiga - tentang pelanggan:
Beras. 18.1.
Di sini informasi utama disimpan dalam tabel penjualan, oleh karena itu merupakan tabel utama (induk). Informasi tambahan disimpan di tabel produk dan pelanggan, yang berarti mereka adalah anak-anak. Hal ini dapat dimaklumi: satu anak perempuan tidak dapat memiliki dua ibu kandung, tetapi satu ibu cukup mampu melahirkan dua anak perempuan.
Namun di server database SQL terdapat definisi hubungan yang berbeda: ketika satu bidang dalam tabel merujuk ke bidang di tabel lain, maka bidang tersebut disebut kunci asing. Dan bidang yang dirujuknya disebut orang tua atau kunci utama. Tabel yang mempunyai kunci asing (link ke record di tabel lain) sering disebut anak, dan tabel dengan kunci orang tua- orang tua. Juga dalam definisi hubungan mereka mengatakan bahwa orang tua hanya dapat memiliki satu catatan unik, yang dapat direferensikan oleh beberapa catatan meja anak.
Jadi pada contoh di atas, tabel penjualan memiliki dua kunci asing: ID produk, dan ID pelanggan. Dan kedua tabel di sisi kanan gambar memilikinya kunci orang tua"Pengidentifikasi". Karena pelanggan atau produk yang sama dapat muncul berulang kali di tabel penjualan, ternyata kedua tabel di sebelah kanan gambar adalah orang tua, dan tabel di sebelah kiri adalah anak. Karena kami sekarang sedang belajar AntarBase - SQL server database, kita akan dipandu oleh definisi ini pada kuliah selanjutnya. Agar tidak semakin pusing memikirkan kebingungan ini, mari kita sepakati saja: meja anak memiliki kunci asing (FOREIGN KEY) ke tabel lain.
KUNCI UTAMA
KUNCI UTAMA- kunci utama adalah salah satu jenis batasan utama dalam database. Kunci utama dirancang untuk mengidentifikasi secara unik catatan dalam tabel dan harus unik. Kunci primer PRIMARY KEY terletak pada tabel, yang biasa disebut induk (Parent). Kunci utama tidak sama dengan indeks utama database lokal; kunci utama bukanlah indeks, namun sebuah batasan. Saat membuat kunci utama Antar Pangkalan secara otomatis menciptakan untuknya indeks unik. Namun, jika kita menciptakan indeks unik, ini tidak akan terjadi batasan kunci utama. Sebuah tabel hanya dapat memiliki satu kunci utama, yaitu PRIMARY KEY.
Katakanlah Anda memiliki tabel dengan daftar karyawan. Bidang Nama Belakang dapat berisi nilai duplikat (senama), sehingga tidak dapat digunakan sebagai kunci utama. Jarang terjadi, tetapi ada senama yang juga memiliki nama yang sama. Yang lebih jarang lagi, ada nama lengkap, sehingga ketiga kolom “Nama Belakang” + “Nama Depan” + “Patronimik” tidak dapat menjamin keunikan rekaman, dan tidak dapat menjadi kunci utama. Dalam hal ini, solusinya, seperti sebelumnya, adalah menambahkan kolom pengenal yang berisi nomor seri orang tersebut. Bidang seperti itu biasanya dibuat bertambah secara otomatis (kita akan membahas tentang pengorganisasian bidang yang bertambah secara otomatis di kuliah berikutnya). Jadi,
Kunci utama adalah satu atau lebih bidang dalam tabel, yang kombinasinya unik untuk setiap catatan.
Jika kunci utama berisi satu kolom (seperti yang paling sering terjadi), penentu PRIMARY KEY digunakan ketika definisi kolom:
BUAT TABEL Prim_1(Stolbec1 INT BUKAN NULL PRIMARY KEY, Stolbec2 VARCHAR(50))
Jika kunci utama dibuat pada beberapa kolom, maka penentu ditempatkan setelah mendefinisikan semua bidang:
BUAT TABEL Prim_2(Stolbec1 INT BUKAN NULL, Stolbec2 VARCHAR(50) BUKAN NULL, KUNCI UTAMA (Stolbec1, Stolbec2))
Seperti dapat dilihat dari contoh, kunci utama harus memiliki batasan kolom NOT NULL.
UNIK
UNIK- kunci unik. Penentu UNIK menunjukkan bahwa semua nilai bidang ini harus unik, sehingga bidang tersebut juga tidak boleh berisi nilai BATAL. Bisa dibilang UNIQUE key merupakan alternatif dari primary key, namun terdapat perbedaan. Perbedaan utamanya adalah hanya boleh ada satu kunci utama, sedangkan kunci unik bisa ada beberapa. Selain itu, batasan UNIK tidak dapat dibuat pada kumpulan kolom yang sama dengan yang digunakan untuk KUNCI UTAMA atau batasan UNIK lainnya. Kunci unik, seperti kunci primer, ditemukan dalam tabel yang merupakan induk dari tabel lainnya.
Kolom yang dideklarasikan dengan batasan UNIK, seperti kunci utama, dapat digunakan untuk menerapkan integritas referensial antara induknya dan tabel anak. Dalam hal ini, kunci asing meja anak akan mengacu pada bidang ini. Seperti halnya kunci utama, ketika kunci unik dibuat, a indeks unik. Namun tidak sebaliknya. Contoh pembuatan tabel dengan satu kunci utama dan dua kunci unik:
BUAT TABEL Prim_3(Stolbec1 INT BUKAN NULL PRIMARY KEY, Stolbec2 VARCHAR(50) BUKAN NULL UNIK, Stolbec3 FLOAT BUKAN NULL UNIK)
KUNCI ASING
KUNCI ASING- kunci eksternal. Ini adalah alat yang sangat ampuh untuk memastikan integritas referensial antar tabel, yang memungkinkan Anda tidak hanya memantau keberadaan tautan yang benar, tetapi juga mengelolanya secara otomatis. Kunci asing terdapat dalam tabel yang merupakan anak (Child) dari tabel lainnya. Integritas referensial disediakan secara tepat oleh kunci asing yang mengacu pada kunci utama atau
KUNCI ASING digunakan untuk pembatasan tautan.
Ketika semua nilai dalam satu bidang tabel direpresentasikan dalam bidang di tabel lain, bidang pertama dikatakan merujuk ke bidang kedua. Hal ini menunjukkan adanya hubungan langsung antara nilai kedua bidang tersebut.
Ketika satu jenis kelamin dalam sebuah tabel mengacu pada jenis kelamin lainnya, maka disebut kunci asing; dan bidang yang dirujuknya disebut kunci orang tua. Nama kunci asing dan kunci induk tidak harus sama. Kunci asing dapat memiliki sejumlah bidang, yang semuanya diproses sebagai satu unit. Kunci asing dan kunci induk yang dirujuknya harus memiliki nomor bidang dan jenis bidang yang sama, serta berada dalam urutan yang sama. Jika suatu bidang adalah kunci asing, maka bidang tersebut terkait dengan tabel yang dirujuknya. Setiap nilai (setiap baris) dari kunci asing harus secara jelas mengacu pada satu dan hanya nilai (baris) dari kunci induk tersebut. Jika kondisi ini terpenuhi, maka database berada dalam status integritas referensial.
SQL mempertahankan integritas referensial dengan batasan KUNCI ASING. Fungsi ini harus membatasi nilai yang dapat dimasukkan ke dalam database untuk memaksa kunci asing dan kunci induk mematuhi integritas referensial. Salah satu tindakan pembatasan KUNCI ASING adalah penghapusan nilai untuk bidang yang dibatasi sebagai kunci asing yang belum terwakili dalam kunci induk. Pembatasan ini juga mempengaruhi kemampuan untuk mengubah atau menghapus nilai kunci induk
Keterbatasan KUNCI ASING digunakan dalam perintah CREATE TABLE (atau ALTER TABLE (dimaksudkan untuk mengubah struktur tabel)) yang berisi bidang yang dinyatakan sebagai kunci asing. Kunci induk diberi nama yang direferensikan dalam batasan KUNCI ASING.
Seperti kebanyakan batasan, batasan ini dapat berupa batasan tabel atau kolom, dalam bentuk tabel yang memungkinkan beberapa bidang digunakan sebagai kunci asing tunggal.
Sintaks Batasan Tabel KUNCI ASING:
KUNCI ASING
Daftar kolom pertama adalah daftar satu atau lebih kolom tabel yang dipisahkan koma yang akan dibuat atau diubah dengan perintah ini.
tabel tabel- ini adalah tabel yang berisi kunci induk. Ini bisa berupa tabel yang dibuat atau dimodifikasi oleh perintah saat ini.
Daftar kolom kedua adalah daftar kolom yang akan membentuk kunci induk. Daftar dua kolom harus kompatibel, yaitu:
- mempunyai jumlah kolom yang sama
- dalam urutan tertentu, kolom pertama, kedua, ketiga, dst. dari daftar kolom kunci asing harus memiliki tipe dan ukuran data yang sama dengan kolom pertama, kedua, ketiga, dst. dari daftar kolom kunci induk.
- kolom dalam daftar kedua kolom tidak boleh memiliki nama yang sama.
Contoh KUNCI ASING 1
BUAT TABEL Siswa
(Kod_stud integer BUKAN NULL UTAMA KUNCI,
Kod_spec bilangan bulat BUKAN NULL,
Alamat char(50),
desimal bola),
KUNCI ASING(Kod_spesifikasi) REFERENSI Spesifikasi (Kod_spec)
);
Saat menggunakan ALTER TABLE alih-alih CREATE TABLE untuk menerapkan batasan KUNCI ASING, nilai yang ditentukan dalam kunci asing dan kunci induk harus dalam keadaan integritas referensial. Jika tidak, perintah tersebut akan ditolak.
Menggunakan batasan KUNCI ASING tabel atau kolom, Anda dapat menghilangkan daftar kolom kunci induk jika kunci induk memiliki batasan PRIMARY KUNCI. Secara alami, dalam kasus kunci dengan banyak bidang, urutan kolom pada kunci asing dan kunci utama harus cocok, dan, bagaimanapun juga, prinsip kompatibilitas antara kedua kunci tersebut tetap berlaku.
Contoh KUNCI ASING 2
BUAT TABEL Siswa (
Kod_stud bilangan bulat BUKAN UTAMA NULL KUNCI,
Fam char(30) BUKAN NULL UNIK,
Alamat char(50),
desimal bola),
Kod_spec bilangan bulat REFERENSI Spesifikasi
);
Mempertahankan integritas referensial memerlukan beberapa batasan pada nilai yang dapat direpresentasikan dalam bidang yang dinyatakan sebagai kunci asing dan kunci induk. Kunci induk harus disusun untuk memastikan bahwa setiap nilai kunci asing sesuai dengan satu baris tertentu. Artinya (kuncinya) harus unik dan tidak mengandung nilai kosong (NULL).
Hal ini tidak cukup agar kunci induk memenuhi persyaratan yang sama seperti saat mendeklarasikan kunci asing. SQL harus yakin bahwa nilai ganda atau nilai nol belum dimasukkan ke dalam kunci induk. Oleh karena itu, Anda harus memastikan bahwa semua bidang yang digunakan sebagai kunci induk memiliki batasan UTAMA KUNCI atau batasan UNIK, seperti batasan NOT NULL.
Merujuk kunci asing hanya ke kunci utama adalah strategi yang baik. Ketika kunci asing digunakan, kunci tersebut tidak hanya dikaitkan dengan kunci induk yang dirujuknya; mereka dikaitkan dengan baris tabel tertentu tempat kunci induk tersebut akan ditemukan. Kunci induk itu sendiri tidak memberikan informasi apa pun yang belum ada dalam kunci asing.
Karena tujuan dari kunci utama adalah untuk mengidentifikasi keunikan suatu baris, ini adalah pilihan yang lebih logis dan tidak terlalu ambigu untuk kunci asing. Untuk kunci asing apa pun yang menggunakan kunci unik sebagai kunci induknya, Anda harus membuat kunci asing yang menggunakan kunci utama tabel yang sama untuk efek yang sama. Kunci asing, yang tidak memiliki tujuan selain menghubungkan baris, mirip dengan kunci utama, hanya digunakan untuk mengidentifikasi baris, dan merupakan cara yang baik untuk menjaga struktur database tetap jelas dan sederhana. Kunci asing hanya dapat berisi nilai yang benar-benar ada di kunci induk atau nilai yang kosong (NULL). Segala upaya untuk memasukkan nilai lain ke dalam kunci ini akan ditolak.
Contoh KUNCI ASING 3
BUAT TABEL pembayaran (
sh_bilangan bulat pembayaran,
sh_eml bilangan bulat,
tanggal_tanggal pembayaran,
jumlah_pembayaran nyata,
KUNCI ASING(sh_eml) REFERENSI k_sotr2 (idul fitri)
);
Dalam contoh ini KUNCI ASING kolom sh_eml dikaitkan dengan kolom idul fitri dari tabel k_sotr2.
