Baza layihələndirmə və normallaşdırma

Təsəvvür et ki, kurs qeydiyyatı bir böyük Excel cədvəlində aparılır: hər sətirdə şagirdin adı, şəhəri, kursun adı, müəllim və bal. Müəllim soyadını dəyişəndə onu yüzlərlə sətirdə düzəltmək lazımdır, birini unutsan, baza özü ilə ziddiyyətə düşür. Yeni kursa hələ heç kim yazılmayıbsa, onu əlavə etmək mümkün deyil, sonuncu şagird kursdan çıxanda isə kursun özü də yoxa çıxır. Bu dərsdə bazanı elə layihələndirməyi öyrənəcəyik ki, hər fakt yalnız bir yerdə saxlansın. Bunun elmi adı normallaşdırmadır, məşq bazamız da məhz bu qaydalarla qurulub.

ER modeli: obyektlər, əlaqələr, kardinallıq

Layihələndirmə cədvəllərdən yox, konseptual modeldən başlayır. Obyekt (entity) — haqqında məlumat saxladığımız şeydir: şagird, kurs, məhsul. Atribut onun xüsusiyyətidir: ad, qiymət. Əlaqə (relationship) obyektləri bağlayır: şagird kursa yazılır, müştəri məhsulu sifariş edir. Bu modeli 1976-cı ildə Piter Çen «obyekt–əlaqə» (ER) diaqramları şəklində təklif edib.

Funksional asılılıqlar və açarlar

Normallaşdırmanın əsas anlayışı funksional asılılıqdır (FA). X → Y yazılışı «X-in qiyməti Y-in qiymətini birmənalı müəyyən edir» deməkdir: course_id → teacher — kursu bilsək, müəllimi də bilirik. FA məlumatın təsadüfi xüsusiyyəti yox, biznes qaydasıdır: onu sahə mütəxəssisindən öyrənirik, cədvəldəki cari sətirlər isə yalnız onu təkzib edə bilər.

Bütün digər atributları müəyyən edən atributlar çoxluğu superaçardır; heç bir atributu atıla bilməyən superaçar isə namizəd açardır. Namizəd açarlardan biri əsas açar seçilir. Hər hansı namizəd açara daxil olan atribut açar (prime) atribut adlanır — bu fərq 3NF və BCNF-ni ayırmaq üçün lazım olacaq.

Anomaliyalar: təkrarçılıq niyə təhlükəlidir

• Yeniləmə anomaliyası — eyni fakt bir neçə sətirdədir, bir nüsxə dəyişəndə digərləri köhnə qalır.
• Əlavə anomaliyası — bir faktı başqa fakt olmadan yazmaq olmur (yazılışı olmayan kursu əlavə etmək).
• Silmə anomaliyası — bir faktı silmək başqa faktı da məhv edir (sonuncu yazılışla birlikdə kursun müəllimi də itir).

-- 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 formalar: 1NF-dən BCNF-yə

Parçalama itkisiz olmalıdır: hissələri JOIN edəndə ilkin cədvəl artıq və ya əskik sətir olmadan geri qayıtmalıdır. İki hissəyə bölmə üçün bunu Hit teoremi ilə yoxlayırıq: ortaq sütunlar hissələrdən birinin açarı olmalıdır.

Denormallaşdırma nə vaxt doğrudur

Normallaşdırma gündəlik əməliyyatlar (OLTP) üçün idealdır: çoxlu kiçik yazma, dəqiqlik, ziddiyyətsizlik. Analitik anbarlarda (OLAP) isə məlumat əsasən oxunur, ona görə bilərəkdən denormallaşdırırlar: «ulduz sxemi»ndə mərkəzdə faktlar cədvəli (satışlar), ətrafında isə geniş ölçü cədvəlləri (məhsul, müştəri, tarix) olur və hesabatlar az JOIN ilə işləyir. Qızıl qayda: əvvəlcə 3NF-də layihələndir, denormallaşdırmanı isə yalnız ölçülmüş performans problemi olanda və sinxronizasiya mexanizmi (trigger, görünüş, gecə yenilənməsi) ilə birlikdə tətbiq et.

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