Normalisasi Pangkalan Data Pesanan Kantin

1. Anomali kemas kini. Nama menu dan harga disimpan berulang dalam setiap baris pesanan. Apabila harga telur rebus dinaikkan kepada RM 1.20, kemas kini hanya kena pada sebahagian baris, jadi laporan jumlah jualan mengira dua harga berbeza untuk item yang sama. Ini juga menjelaskan variasi ejaan 'Nasi Lemak', 'nasi lemak' dan 'NASI LEMAK': setiap kejadian ialah salinan bebas yang boleh diedit secara berasingan, dan tiada satu sumber kebenaran.

2. Anomali sisip. Menu baharu tidak boleh direkodkan sehingga ada pelajar yang memesannya, kerana ID_Menu ialah sebahagian daripada kunci primer PESANAN dan baris baharu memerlukan pesanan. Akibatnya pengurus tidak boleh menyediakan menu minggu hadapan lebih awal, dan harga menu itu tidak dapat disemak sebelum hari jualan.

3. Anomali padam. Jika satu-satunya pesanan untuk satu menu dipadam - contohnya pelajar membatalkan tempahan - maklumat harga dan kewujudan menu itu turut hilang. Data menu bergantung pada pesanan, padahal kedua-duanya entiti berasingan.

Mengapa harga sejarah berubah. Harga_Unit dan Kuantiti berada dalam baris yang sama seperti identiti menu, jadi harga bukan nilai pada masa pesanan tetapi rujukan kepada harga semasa. Apabila harga semasa dikemas kini, baris lama turut berubah dan hasil jualan bulan lepas menjadi tidak tepat. Untuk menyimpan sejarah, harga pada masa pesanan mesti disalin sebagai nilai bebas ke dalam butiran pesanan, bukan dirujuk daripada jadual menu. Ini satu-satunya tujuan yang munasabah untuk menduplikasi data dalam reka bentuk ternormal.

Kesan prestasi. Setiap baris menyimpan rentetan Nama_Menu dan Nama_Guru yang panjang, menyebabkan 4,800 baris mengambil banyak blok cakera. Laporan perlu mengimbas keseluruhan jadual dan mengumpul teks berulang, itulah sebabnya 12 saat diperlukan.

Reka bentuk dicadangkan.

• GURU(ID_Guru PK, Nama_Guru)
• PELAJAR(ID_Pelajar PK, Nama_Pelajar, Kelas)
• MENU(ID_Menu PK, Nama_Menu, Harga_Semasa)
• PESANAN(ID_Pesanan PK, Tarikh, ID_Pelajar FK, ID_Guru FK)
• BUTIRAN_PESANAN(ID_Pesanan FK, ID_Menu FK, Kuantiti, Harga_Masa_Pesan, PK komposit (ID_Pesanan, ID_Menu))

1NF. Semua medan bersifat atomik dan tiada kumpulan berulang. Pesanan berbilang item tidak lagi disimpan sebagai senarai dalam satu medan; setiap item menjadi satu baris dalam BUTIRAN_PESANAN.

2NF. Dalam jadual asal, Nama_Menu dan Harga_Unit hanya bergantung pada ID_Menu, sebahagian daripada kunci primer. Itu kebergantungan separa. Selepas pemisahan, setiap medan bukan kunci dalam BUTIRAN_PESANAN bergantung pada keseluruhan kunci komposit: Kuantiti bergantung pada pasangan pesanan dan menu, bukan pada salah satu sahaja. Harga_Masa_Pesan juga bergantung pada pasangan itu kerana ia nilai transaksi, bukan sifat menu.

3NF. Tiada kebergantungan transitif. Dalam jadual asal, Kelas bergantung pada Nama_Pelajar dan bukan pada ID_Pesanan, jadi ia transitif melalui pelajar. Kini Kelas berada dalam PELAJAR dan dirujuk melalui ID_Pelajar. Begitu juga Nama_Guru dipisahkan ke GURU dan dirujuk melalui ID_Guru, supaya pembetulan ejaan nama guru hanya dilakukan di satu tempat.

Nota reka bentuk penting. Harga_Semasa dalam MENU memenuhi keperluan 'satu harga semasa yang sah'. Harga_Masa_Pesan dalam BUTIRAN_PESANAN sengaja diduplikasi supaya laporan sejarah kekal tepat walaupun harga semasa berubah kemudian. Kedua-duanya bukan percanggahan; ia memisahkan harga rujukan daripada harga transaksi. Tambahan pula, kekangan UNIQUE(Nama_Menu) menghalang variasi ejaan daripada berulang, dan kekangan rujukan asing menghalang pemadaman menu yang masih mempunyai pesanan.

Pertanyaan SQL.

SELECT m.Nama_Menu,
       SUM(b.Kuantiti)                          AS Jumlah_Unit,
       SUM(b.Kuantiti * b.Harga_Masa_Pesan)     AS Jumlah_Jualan
FROM BUTIRAN_PESANAN b
JOIN PESANAN p ON p.ID_Pesanan = b.ID_Pesanan
JOIN MENU    m ON m.ID_Menu    = b.ID_Menu
WHERE p.Tarikh = '2024-07-15'
GROUP BY m.Nama_Menu
ORDER BY Jumlah_Jualan DESC;

Mengapa ia laju berbanding jadual asal. Dalam jadual tunggal, setiap baris membawa rentetan panjang Nama_Menu dan Nama_Guru, jadi pengimbasan penuh membaca banyak blok cakera dan pengumpulan (GROUP BY) membandingkan teks berulang. Dalam reka bentuk ternormal, nombor bulat kecil menggantikan teks, dan GROUP BY berjalan pada kunci yang pendek.

Indeks yang dicadangkan.

CREATE INDEX idx_pesanan_tarikh ON PESANAN(Tarikh);
CREATE INDEX idx_butiran_menu    ON BUTIRAN_PESANAN(ID_Menu);

Indeks pertama menukar carian 'semua pesanan pada tarikh tertentu' daripada pengimbasan penuh kepada carian julat B-tree, yang hanya menyentuh baris hari itu (mungkin 150 daripada 4,800). Indeks kedua membolehkan penggabungan (join) ke MENU dan pengumpulan mengikut menu dijalankan tanpa mengimbas semula keseluruhan jadual butiran.

Langkah tambahan jika masih perlahan. Simpan jenis Tarikh sebagai jenis tarikh, bukan rentetan teks, supaya perbandingan julat berfungsi dan bukan perbandingan abjad. Jika laporan dijalankan beribu kali sehari, simpan hasil agregat harian dalam jadual ringkasan yang dikemas kini selepas waktu kantin tutup; laporan kemudian hanya membaca puluhan baris agregat dan bukannya menjumlahkan ribuan butiran. Akhir sekali, jalankan EXPLAIN untuk mengesahkan pengoptimum benar-benar menggunakan indeks dan bukan pengimbasan penuh.