Veritabanı tasarımı ve normalizasyon

Ders kayıtlarının tek bir büyük Excel tablosunda tutulduğunu düşün: her satırda öğrencinin adı, şehri, dersin adı, öğretmen ve puan var. Öğretmen soyadını değiştirdiğinde bunu yüzlerce satırda düzeltmek gerekir; birini atlarsan veritabanı kendisiyle çelişir. Yeni bir ders, biri kaydolana kadar eklenemez; son öğrenci dersten ayrıldığında ise dersin kendisi de kaybolur. Bu derste veritabanını her olgu yalnızca bir yerde saklanacak şekilde tasarlamayı öğreneceğiz. Bunun bilimsel adı normalizasyondur ve alıştırma veritabanımız tam da bu kurallarla kurulmuştur.

ER modeli: varlıklar, ilişkiler, kardinalite

Tasarım tablolarla değil, kavramsal bir modelle başlar. Varlık (entity), hakkında veri tuttuğumuz şeydir: öğrenci, ders, ürün. Nitelik (attribute) onun bir özelliğidir: ad, fiyat. İlişki (relationship) varlıkları bağlar: öğrenci bir derse kaydolur, müşteri bir ürünü sipariş eder. Bu modeli 1976'da Peter Chen varlık–ilişki (ER) diyagramları biçiminde önerdi.

Fonksiyonel bağımlılıklar ve anahtarlar

Normalizasyonun temel kavramı fonksiyonel bağımlılıktır (FB). X → Y, “X'in değeri Y'nin değerini tek biçimde belirler” demektir: course_id → teacher, yani dersi bilirsek öğretmeni de biliriz. FB, verinin rastlantısal bir özelliği değil, bir iş kuralıdır: onu alan uzmanlarından öğreniriz; tablodaki mevcut satırlar ise onu yalnızca çürütebilir.

Diğer tüm nitelikleri belirleyen bir nitelikler kümesi süper anahtardır; hiçbir niteliği çıkarılamayan süper anahtar ise aday anahtardır. Aday anahtarlardan biri birincil anahtar olarak seçilir. Herhangi bir aday anahtarın parçası olan niteliğe asal (prime) nitelik denir; bu ayrım 3NF ile BCNF'yi ayırt etmek için gerekecek.

Anomaliler: tekrar neden tehlikelidir

• Güncelleme anomalisi — aynı olgu birkaç satırda bulunur; bir kopya değiştiğinde diğerleri eski kalır.
• Ekleme anomalisi — bir olgu başka bir olgu olmadan kaydedilemez (henüz kaydı olmayan bir dersi eklemek).
• Silme anomalisi — bir olguyu silmek başka bir olguyu da yok eder (son kayıtla birlikte dersin öğretmeni de kaybolur).

-- a denormalised copy: the teacher is repeated in every row
CREATE TABLE report_flat AS
SELECT e.student_id, s.first_name, e.course_id, c.title, c.teacher, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id;

-- the name is corrected in one row only
UPDATE report_flat SET teacher = 'R. Safarov'
WHERE course_id = 1 AND student_id = 1;

SELECT course_id, teacher, COUNT(*) AS rows_with_it
FROM report_flat
WHERE course_id = 1
GROUP BY course_id, teacher
ORDER BY teacher;

course_id | teacher | rows_with_it
1 | R. Safarov | 1
1 | Ramin Səfərov | 3

Normal formlar: 1NF'den BCNF'ye

Ayrıştırma kayıpsız olmalıdır: parçalar JOIN ile birleştirildiğinde fazla ya da eksik satır olmadan özgün tablo geri gelmelidir. İki parçaya bölmede bunu Heath teoremiyle denetleriz: ortak sütunlar parçalardan birinin anahtarı olmalıdır.

Denormalizasyon ne zaman doğrudur

Normalizasyon günlük işlemler (OLTP) için idealdir: çok sayıda küçük yazma, doğruluk, çelişkisizlik. Analitik veri ambarları (OLAP) ise çoğunlukla okunur; bu yüzden bilerek denormalize edilir: “yıldız şemasında” merkezde bir olgu tablosu (satışlar), çevresinde geniş boyut tabloları (ürün, müşteri, tarih) bulunur ve raporlar az sayıda JOIN ile çalışır. Altın kural: önce 3NF'de tasarla, denormalizasyonu yalnızca ölçülmüş bir performans sorunu varsa ve her zaman bir eşitleme mekanizmasıyla (tetikleyici, görünüm, gece yenilemesi) birlikte uygula.

SELECT s.first_name, s.city, c.title, c.teacher, e.score
FROM enrollments AS e
-- join students and courses
WHERE c.title = 'Mechanics'
ORDER BY e.score DESC;

first_name | city | title | teacher | score
Fidan | Naxçıvan | Mechanics | Ülviyyə Axundova | 94
Murad | Gəncə | Mechanics | Ülviyyə Axundova | 81
Rəşad | Bakı | Mechanics | Ülviyyə Axundova | 77
Elvin | Sumqayıt | Mechanics | Ülviyyə Axundova | 68

SELECT teacher
       -- number of different subjects
FROM courses
GROUP BY teacher
ORDER BY teacher;

teacher | subjects
Elnur Qasımov | 1
Nərmin Vəliyeva | 1
Ramin Səfərov | 1
Samir Hacıyev | 1
Sara Mitchell | 1
Ülviyyə Axundova | 1