Tarkibga o'tish

07 β€” Normalizatsiya I: 1NF, 2NF, 3NF va anomaliyalar

⬅️ Oldingi: 06 β€” Kalit dizayni: natural, surrogate, UUID, kompozit Β· 🏠 README Β· Keyingi: 08 β€” Normalizatsiya II: BCNF, 4NF, 5NF va denormalizatsiya ➑️

Bu bobda: normalizatsiyaning asl mexanizmi β€” funksional bog'liqlik (FD) nimaligini, "hammasi bitta jadvalda" dizayni keltiradigan uchta anomaliyani (insert/update/delete) bitta yomon jadvalda ko'rib chiqamiz, so'ng aynan shu jadvalni 1NF -> 2NF -> 3NF bo'ylab qadam-baqadam parchalaymiz. SQL kitobi normalizatsiyaga sodda "3 qoida" sifatida qaragan edi; bu bobda nazariy asos (determinant, candidate key, qisman va tranzitiv bog'liqlik) bilan nega har qadam shunday qilinishini tushunamiz.

Kirish: normalizatsiya β€” bu ratsional jarayon, sehr emas

SQL kitobining 20-bobida normalizatsiyani uchta sodda qoida sifatida o'rgangansiz: "bitta katak β€” bitta qiymat", "har fakt β€” o'z jadvalida", "jadvallar id orqali bog'lanadi". Bu qoidalar amaliyot uchun ajoyib, lekin ular nega ishlashini tushuntirmaydi. Nima uchun aynan uchta jadval, ikkita emas? Qaysi ustun qaysi jadvalga tegishli β€” buni qanday qat'iy aniqlaymiz?

Javob bitta nazariy tushunchada: funksional bog'liqlik. Normal formalar (1NF, 2NF, 3NF) β€” bu funksional bog'liqliklar haqidagi qoidalar to'plami. Funksional bog'liqlikni tushunsangiz, normalizatsiya mexanik, deyarli matematik jarayonga aylanadi: jadvalga qarab "bu yerda qanday bog'liqliklar bor?" deb so'raysiz, javobga ko'ra jadvalni qayerdan kesishni aniq bilasiz.

Bu bob aynan shu nazariy asosni beradi. Avval kasallikni (anomaliyalar) ko'ramiz, keyin tashxis vositasini (FD), so'ng davoni (1NF -> 2NF -> 3NF qadamlari) o'rganamiz.

Yomon jadval va uchta anomaliya

Bitta amaliy misol bilan boshlaymiz. Onlayn kurs platformasi har bir yozilishni (talaba qaysi kursga yozilgan) bitta keng jadvalga yozyapti:

talabalar_kurslar (talaba_id, ism, kurs, oqituvchi, oqituvchi_telefoni)
talaba_id ism kurs oqituvchi oqituvchi_telefoni
1 Aziz SQL asoslari Olimov +99890111
1 Aziz Python Karimov +99890222
2 Bobur SQL asoslari Olimov +99890111
3 Dilnoza Python Karimov +99890222

Birinchi qarashda hammasi joyida ko'rinadi. Lekin diqqat qiling: Olimovning telefoni ikki qatorda takrorlangan, Karimovniki ham. Bu takror bejiz emas β€” u uchta jiddiy muammoning manbai. Bularning rasmiy nomi anomaliya: jadval tuzilishi noto'g'ri bo'lgani uchun normal amallar (qo'shish, yangilash, o'chirish) "g'alati" oqibatlarga olib keladi.

Insert, update va delete anomaliyalari yagona yomon jadvalda: bir nechta mustaqil fakt bitta jadvalga tiqishtirilgani sabab

1. UPDATE (yangilash) anomaliyasi

Olimov telefon raqamini o'zgartirdi. Endi siz uni har bir qatorda yangilashingiz kerak. Bu jadvalda 2 qator, lekin real bazada Olimov 500 talabaga dars bersa β€” 500 ta UPDATE. Bittasi qolib ketsa, bazada ikki xil "haqiqat" paydo bo'ladi: qaysi raqam to'g'ri? Bazada bir faktning bir nechta nusxasi borligi har doim bu xavfni keltiradi.

Tekshirib ko'ramiz β€” yomon jadvalda har o'qituvchining raqami necha qatorda takrorlanganini sanaymiz:

SELECT oqituvchi,
       count(*)                          AS qatorlar,
       count(DISTINCT oqituvchi_telefoni) AS xil_raqamlar
FROM talabalar_kurslar
GROUP BY oqituvchi
ORDER BY oqituvchi;

 oqituvchi | qatorlar | xil_raqamlar
-----------+----------+--------------
 Karimov   |        2 |            1
 Olimov    |        2 |            1

Hozir xil_raqamlar = 1 β€” yaxshi. Lekin bu shunchaki omad: bironta UPDATE chala bajarilsa, bu qiymat 2 ga aylanadi va bazaga ishonib bo'lmay qoladi. To'g'ri dizaynda esa raqam jismonan bir joyda saqlangani uchun bu holat mumkin emas.

2. INSERT (kiritish) anomaliyasi

Platformaga yangi o'qituvchi β€” Saidov β€” qo'shilmoqchi, lekin hali unga talaba biriktirilmagan. Uni qanday qo'shasiz?

-- ❌ Bunday qatorni qo'sha olmaymiz: talaba_id PRIMARY KEY, NULL bo'la olmaydi
INSERT INTO talabalar_kurslar VALUES (NULL, NULL, NULL, 'Saidov', '+99890333');

O'qituvchi fakti talaba fakti bilan garovga bog'langan: talabasiz o'qituvchini yoza olmaysiz. Mustaqil mavjudotni (o'qituvchi) boshqa mavjudot (talaba) bo'lmaguncha kiritolmaslik β€” bu insert anomaliyasi.

3. DELETE (o'chirish) anomaliyasi

Dilnoza o'qishni tashladi, qatorini o'chiramiz. Lekin u "Python" + "Karimov" bo'yicha oxirgi qator edi (3-talaba). Qatorni o'chirsak β€” Karimov va uning telefoni haqidagi ma'lumot ham birga yo'qoladi! Talabani o'chirmoqchi edik, ammo o'qituvchi haqidagi mutlaqo boshqa faktni nobud qildik. Bu delete anomaliyasi.

Umumiy sabab. Uchala anomaliya bitta ildizga borib taqaladi: bir nechta mustaqil fakt bitta jadvalga tiqishtirilgan. "Talaba kimligi", "o'qituvchi telefoni", "talaba qaysi kursga yozilgani" β€” bular alohida faktlar, lekin ular bitta qatorda yopishib qolgan. Normalizatsiya β€” aynan shu faktlarni o'z jadvallariga ajratish. Endi buni qat'iy qilish uchun vositamiz bilan tanishamiz.

Funksional bog'liqlik (FD) β€” normalizatsiyaning ildizi

Funksional bog'liqlik (functional dependency, FD) β€” relyatsion nazariyaning markaziy tushunchasi. Ta'rifi sodda:

X -> Y ("X Y ni aniqlaydi") degani: agar ikki qatorda X qiymatlari bir xil bo'lsa, ularda Y qiymatlari ham albatta bir xil bo'ladi. X β€” determinant (aniqlovchi), Y β€” bog'liq tomon.

Hayotiy o'xshatish: pasport raqamini bilsangiz, egasining ismi bir qiymatli aniqlanadi. pasport_raqami -> ism. Bitta pasport raqamiga ikki xil ism bo'lishi mumkin emas. Bu β€” funksional bog'liqlik.

Funksional bog'liqlik X -> Y va undan candidate key aniqlash; tranzitiv bog'liqlik zanjiri

Misol jadvalga qaraylik:

xodim_id email bolim_id bolim
101 a@x.uz D1 Sotuv
102 b@x.uz D1 Sotuv
103 c@x.uz D2 Ombor

Bu yerdagi funksional bog'liqliklar (biznes qoidalaridan kelib chiqib):

  • xodim_id -> email, bolim_id, bolim β€” xodim raqami uning hamma narsasini aniqlaydi.
  • email -> xodim_id β€” email noyob, demak u ham xodimni aniqlaydi.
  • bolim_id -> bolim β€” bo'lim raqami bo'lim nomini aniqlaydi (D1 doim "Sotuv").

E'tibor bering: FD β€” bu ma'lumotdagi tasodif emas, biznes qoidasi. Yuqoridagi jadvalda 101 va 102 bir xil bo'limda β€” bu tasodifiy emas, "bir bo'lim raqami = bir bo'lim nomi" qoidasi shunday talab qiladi. FD ni jadvaldagi joriy ma'lumotga qarab "taxmin qilmaysiz" β€” domenni tushunib aniqlaysiz. Aynan shu sababdan 02-bobda o'rgangan talab tahlili muhim: u FD larni yuzaga chiqaradi.

FD dan kalitni aniqlash: candidate key va superkey

Funksional bog'liqlik nima uchun muhim? Chunki u kalit nima ekanini matematik aniqlaydi (kalit turlarini 05-bobda ko'rgansiz):

  • Superkey β€” jadvaldagi barcha atributlarni funksional aniqlaydigan atribut(lar) to'plami. Yuqorida {xodim_id} superkey, chunki xodim_id -> hamma narsa.
  • Candidate key β€” ortiqcha atributi yo'q minimal superkey. Bu yerda {xodim_id} va {email} β€” ikkita candidate key (ikkalasi ham hamma narsani aniqlaydi va minimal).
  • Primary key β€” candidate key lardan biri tanlangani (qaysi birini tanlash dizayn qarori β€” 06-bobda ko'rgansiz).
  • Alternativ kalit β€” tanlanmagan candidate key (bu yerda email).

Atribut yopilishi (attribute closure, {X}+ deb belgilanadi) β€” X dan funksional yetib boriladigan barcha atributlar to'plami. {xodim_id}+ = {xodim_id, email, bolim_id, bolim} = barcha atributlar, demak {xodim_id} superkey. Bu β€” kalit topishning aniq algoritmi: atribut to'plamining yopilishi barcha atributlarni qamrasa, u superkey.

Atama: nokalit atribut. Hech qaysi candidate key tarkibiga kirmaydigan atribut β€” nokalit (non-prime) atribut. Yuqorida bolim, bolim_id nokalit (email va xodim_id esa candidate key larga kiradi). Bu atama 2NF va 3NF ta'riflarida kerak bo'ladi β€” yodda tuting.

Endi bizda vosita bor. Normal formalar β€” bu funksional bog'liqliklar qanaqa ko'rinishda bo'lishi kerakligi haqidagi qoidalar. Har bir normal formani o'sha yomon jadvalimizni parchalab ko'rsatamiz.

Bitta jadvalning 0NF -> 1NF -> 2NF -> 3NF bo'ylab qadam-baqadam parchalanishi: atomiklik, qisman va tranzitiv bog'liqliklarni ketma-ket yo'qotish

1NF β€” birinchi normal forma: atomiklik

Qoida: jadval 1NF da bo'lishi uchun har bir katakda atomik (bo'linmas) bitta qiymat bo'lishi, takrorlanuvchi guruhlar bo'lmasligi va har qatorni noyob aniqlaydigan kalit bo'lishi kerak.

Yangi misol β€” buyurtma jadvali, hozircha normallashmagan ("0NF") holatda:

buyurtma (buyurtma_id, mijoz_id, mijoz_ism, mahsulotlar, mahsulot_narx, miqdor)
buyurtma_id mijoz_id mijoz_ism mahsulotlar ...
500 7 Davron "Telefon; Chexol" ...

mahsulotlar katagida ro'yxat ("Telefon; Chexol") β€” bu 1NF ni buzadi. Bu eng keng tarqalgan anti-naqsh: vergulli/nuqta-vergulli ro'yxatni bitta ustunga tiqish (SQL Antipatterns kitobida "Jaywalking" deyiladi β€” uni 13-bobda batafsil ko'ramiz). Muammo: "Chexol nechta sotilgan?" so'rovini yoza olmaysiz, FK qo'ya olmaysiz, indeks samarasiz.

1NF ga keltirish: ro'yxatdagi har elementni alohida qatorga yoyamiz. Endi bitta buyurtma β€” bir nechta qator, har qatorda bitta mahsulot. Bu kompozit kalit talab qiladi:

qayd_1nf (buyurtma_id, mahsulot_id, mijoz_id, mijoz_ism,
          mahsulot_nomi, mahsulot_narx, miqdor)
PRIMARY KEY (buyurtma_id, mahsulot_id)

CREATE TABLE qayd_1nf (
    buyurtma_id   int,
    mahsulot_id   int,
    mijoz_id      int,
    mijoz_ism     text,
    mahsulot_nomi text,
    mahsulot_narx numeric(12,2),
    miqdor        int,
    PRIMARY KEY (buyurtma_id, mahsulot_id)   -- kompozit kalit
);

Endi har katak atomik. Lekin bu hali yaxshi dizayn emas β€” mijoz_ism har mahsulot qatorida takrorlanadi (bir buyurtmada 5 mahsulot bo'lsa, mijoz ismi 5 marta). Bu bizni 2NF ga olib boradi.

Atomiklik nisbiy tushuncha. "To'liq ism" bitta ustunda atomikmi yoki "ism" + "familiya" ga bo'linadimi β€” bu domenga bog'liq. Agar siz hech qachon faqat familiya bo'yicha qidirmasangiz, "to'liq ism" atomik hisoblanadi. PostgreSQL'da JSONB va array turlari ataylab "bir katakda murakkab qiymat" saqlaydi β€” bu 1NF ni "buzadi"mi? Amaliyotda: agar siz qiymat ichidan relyatsion so'rov qilmasangiz va u yaxlit birlik bo'lsa, bu joiz dizayn qaroridir. Bularni 10-bobda (turlar) va 13-bobda (anti-naqshlar) ko'rib chiqamiz.

2NF β€” ikkinchi normal forma: qisman bog'liqlikni yo'qotish

2NF faqat kompozit (ko'p ustunli) kalitli jadvallar uchun ma'noli. Agar kalit bitta ustun bo'lsa, jadval 1NF da bo'lsa avtomatik 2NF da ham bo'ladi.

Qoida: jadval 2NF da bo'lishi uchun u 1NF da bo'lishi va har bir nokalit atribut to'liq kalitga bog'liq bo'lishi kerak β€” kalitning faqat bir qismiga bog'liq bo'lmasligi kerak. Kalitning qismiga bog'liqlik qisman bog'liqlik (partial dependency) deyiladi va aynan u 2NF ni buzadi.

qayd_1nf jadvalimizdagi funksional bog'liqliklarni yozamiz (PK = {buyurtma_id, mahsulot_id}):

buyurtma_id                -> mijoz_id, mijoz_ism      (kalitning FAQAT bir qismi!)
mahsulot_id                -> mahsulot_nomi, mahsulot_narx  (kalitning FAQAT bir qismi!)
buyurtma_id, mahsulot_id   -> miqdor                   (to'liq kalit β€” yaxshi)

Birinchi ikki FD β€” qisman bog'liqlik: - mijoz_ism faqat buyurtma_id ga bog'liq, mahsulot_id ga emas. Shuning uchun u har mahsulot qatorida takrorlanadi. - mahsulot_nomi/mahsulot_narx faqat mahsulot_id ga bog'liq.

Buni amalda ko'rsatamiz β€” qisman bog'liqlik takrorga olib kelishini sanaymiz:

-- mahsulot_id -> mahsulot_nomi qisman bog'liqligi sabab nomi takrorlanadi
CREATE TABLE buyurtma_2nf_buzuq (
    buyurtma_id   int,
    mahsulot_id   int,
    mahsulot_nomi text,   -- qisman bog'liq: faqat mahsulot_id ga
    miqdor        int,
    PRIMARY KEY (buyurtma_id, mahsulot_id)
);
INSERT INTO buyurtma_2nf_buzuq VALUES
 (100,1,'Telefon',1),(100,2,'Chexol',2),(101,1,'Telefon',1);

SELECT mahsulot_id, count(*) AS marta
FROM buyurtma_2nf_buzuq
GROUP BY mahsulot_id ORDER BY mahsulot_id;

 mahsulot_id | marta
-------------+-------
           1 |     2      <- "Telefon" nomi 2 marta yozilgan
           2 |     1

2NF ga keltirish: har qisman bog'liqlikni o'z jadvaliga ajratamiz. Determinant (buyurtma_id, mahsulot_id) yangi jadvalning kaliti bo'ladi:

buyurtmalar         (buyurtma_id PK, mijoz_id, mijoz_ism, ...)
mahsulotlar         (mahsulot_id PK, mahsulot_nomi, mahsulot_narx)
buyurtma_qatorlari  (buyurtma_id, mahsulot_id) PK, miqdor, narx_snapshot

Endi mahsulot_nomi bitta joyda β€” mahsulotlar jadvalida. buyurtma_qatorlari da faqat to'liq kalitga bog'liq atribut (miqdor) qoldi.

Diqqat: narx_snapshot. buyurtma_qatorlari ga narx_snapshot qo'shdik. Mahsulot narxi vaqt o'tib o'zgaradi, ammo buyurtma berilgan ondagi narx tarixiy fakt bo'lib qolishi kerak. Bu ataylab takror β€” denormalizatsiya emas, balki narx "qachon" ma'nosiga ko'ra mahsulot joriy narxidan boshqa fakt. Buni 08-bobda (denormalizatsiya) chuqurroq ko'ramiz.

3NF β€” uchinchi normal forma: tranzitiv bog'liqlikni yo'qotish

2NF dagi buyurtmalar jadvalida hali muammo bor. Uning FD lariga qaraylik (PK = buyurtma_id):

buyurtma_id -> mijoz_id        (kalitga to'g'ridan-to'g'ri bog'liq β€” yaxshi)
mijoz_id    -> mijoz_ism       (nokalit -> nokalit β€” MUAMMO!)

mijoz_ism kalitga (buyurtma_id) faqat mijoz_id orqali bog'langan: buyurtma_id -> mijoz_id -> mijoz_ism. Bu tranzitiv bog'liqlik (transitive dependency) β€” nokalit atribut boshqa nokalit atribut orqali kalitga bog'lanishi.

Qoida: jadval 3NF da bo'lishi uchun u 2NF da bo'lishi va har bir nokalit atribut faqat candidate key(lar)ga to'g'ridan-to'g'ri bog'liq bo'lishi kerak β€” boshqa nokalit atribut orqali emas (tranzitiv emas).

Klassik xotirlash formulasi (Bill Kent): har bir nokalit atribut

"kalitga, butun kalitga va faqat kalitga" bog'liq bo'lishi kerak β€” "so help me Codd".

  • "kalitga" = 1NF (kalit bor, atomik),
  • "butun kalitga" = 2NF (qisman bog'liqlik yo'q),
  • "faqat kalitga" = 3NF (tranzitiv bog'liqlik yo'q).

3NF ga keltirish: tranzitiv bog'liqlikni o'z jadvaliga ajratamiz. Determinant (mijoz_id) yangi jadvalning kaliti, eski jadvalda u FK bo'lib qoladi:

mijozlar     (mijoz_id PK, ism, telefon)
buyurtmalar  (buyurtma_id PK, mijoz_id FK, sana, holat)

Endi to'liq parchalangan sxemani PostgreSQL 18 da yaratamiz (ilgaridagi talabalar_kurslar yomon jadvalini 3NF gacha olib boramiz β€” uchala anomaliya yo'qoladi):

CREATE SCHEMA IF NOT EXISTS ch07;
SET search_path = ch07;

CREATE TABLE talabalar (
    talaba_id int PRIMARY KEY,
    ism       text NOT NULL
);
CREATE TABLE oqituvchilar (
    oqituvchi_id int PRIMARY KEY,
    ism          text NOT NULL,
    telefon      text NOT NULL
);
CREATE TABLE kurslar (
    kurs_id      int PRIMARY KEY,
    nomi         text NOT NULL,
    oqituvchi_id int NOT NULL REFERENCES oqituvchilar(oqituvchi_id)
);
CREATE TABLE qaydlar (
    talaba_id int REFERENCES talabalar(talaba_id),
    kurs_id   int REFERENCES kurslar(kurs_id),
    PRIMARY KEY (talaba_id, kurs_id)   -- N:M yozilish
);

Ma'lumotni joylaymiz:

INSERT INTO talabalar    VALUES (1,'Aziz'),(2,'Bobur'),(3,'Dilnoza');
INSERT INTO oqituvchilar VALUES (1,'Olimov','+99890111'),(2,'Karimov','+99890222');
INSERT INTO kurslar      VALUES (10,'SQL asoslari',1),(20,'Python',2);
INSERT INTO qaydlar      VALUES (1,10),(1,20),(2,10),(3,20);

Endi uchala anomaliya yo'qolganini tekshiramiz:

-- INSERT anomaliyasi YO'Q: talabasiz o'qituvchini qo'shamiz
INSERT INTO oqituvchilar VALUES (3,'Saidov','+99890333');

-- UPDATE anomaliyasi YO'Q: Olimov raqamini BITTA joyda yangilaymiz
UPDATE oqituvchilar SET telefon='+99899999' WHERE ism='Olimov';

-- JOIN bilan asl ko'rinishni tiklaymiz
SELECT t.ism, k.nomi AS kurs, o.ism AS oqituvchi, o.telefon
FROM qaydlar q
JOIN talabalar    t ON t.talaba_id    = q.talaba_id
JOIN kurslar      k ON k.kurs_id      = q.kurs_id
JOIN oqituvchilar o ON o.oqituvchi_id = k.oqituvchi_id
ORDER BY t.ism, k.nomi;

   ism   |     kurs     | oqituvchi |  telefon
---------+--------------+-----------+-----------
 Aziz    | Python       | Karimov   | +99890222
 Aziz    | SQL asoslari | Olimov    | +99899999
 Bobur   | SQL asoslari | Olimov    | +99899999
 Dilnoza | Python       | Karimov   | +99890222

E'tibor bering: bitta UPDATE Olimovning raqamini har joyda yangiladi (+99899999) β€” chunki raqam endi jismonan bitta qatorda. UPDATE anomaliyasi tuzilish darajasida yo'q qilindi. DELETE anomaliyasi ham yo'q: Dilnozaning qaydlar qatorini o'chirsangiz, kurslar va oqituvchilar daxlsiz qoladi.

Bu DDL va natijalar PostgreSQL 18.4 da (port 5434) haqiqatan ishga tushirilib tekshirilgan β€” yuqoridagi chiqishlar psql'ning aynan o'zi.

3NF β€” odatda yetarli daraja

Amaliyotda 3NF β€” ko'pchilik OLTP loyihasi uchun maqsadli daraja. 3NF ga yetgan sxema:

  • takrorni yo'qotadi (har fakt bir joyda),
  • uchala anomaliyani bartaraf qiladi,
  • yangilashni bir nuqtaga jamlaydi (bitta UPDATE β€” bitta haqiqat).

3NF dan keyin yana BCNF, 4NF, 5NF bor β€” ular nozikroq holatlarni qoplaydi (masalan, bir nechta candidate key bir-birining ustiga tushib qolganda). Aksariyat real jadvallarda 3NF allaqachon BCNF ham bo'ladi. Bu ilg'or formalarni va "qanchalik normalizatsiya kerak", "qachon ataylab denormalizatsiya qilinadi" degan trade-off'ni keyingi β€” 08-bobda ko'rib chiqamiz.

Ekspert maslahati. Normalizatsiyani "qancha ko'p bo'lsa shuncha yaxshi" deb tushunmang. 3NF β€” bu standart, undan pastga tushish (denormalizatsiya) β€” bu o'lchangan, asoslangan qaror (performans uchun, hisobot uchun, snapshot uchun). "Avval normalize qiling, keyin o'lchang, faqat dalil bilan denormalize qiling" β€” bu kitobning 08- va 15-boblarida qaytariladigan asosiy tamoyil.

Mashqlar

Quyidagi mashqlarda asosan: jadval qaysi normal formada ekanini aniqlang, anomaliyani toping, va jadvalni 3NF gacha normalizatsiya qiling (DDL yozing). Funksional bog'liqliklarni yozib chiqishni odat qiling β€” ular sizning ish quroli.

Oson

  1. talabalar_kurslar (talaba_id, ism, kurs, oqituvchi, oqituvchi_telefoni) jadvalida uchta anomaliyaning har birini o'z so'zingiz bilan tushuntiring va aynan qaysi qatorlar/qiymatlar muammo tug'dirishini ko'rsating.

  2. Quyidagi jadval qaysi normal formani buzadi? kitoblar (kitob_id, sarlavha, janr_kodi, janr_nomi), bunda janr_kodi -> janr_nomi. Buzilish turini nomlang (qisman/tranzitiv).

  3. xaridlar (xarid_id, mahsulotlar) jadvalida mahsulotlar ustunida "Olma, Non, Sut" kabi vergulli ro'yxat saqlanadi. Bu qaysi normal formani buzadi va nega? 1NF ga keltiring.

  4. Funksional bog'liqlik ta'rifini ishlatib, quyidagi qaysi bog'liqliklar to'g'ri ekanini ayting: telefon raqami bo'yicha telefon -> mijoz_ism har doim to'g'rimi? tugilgan_yil -> yosh chi?

O'rta

  1. buyurtma_qatorlari (buyurtma_id, mahsulot_id, mahsulot_nomi, mahsulot_narx, miqdor), PK = (buyurtma_id, mahsulot_id). FD larni yozing, qaysi normal formada ekanini aniqlang va 3NF gacha normalizatsiya qiling (DDL bilan).

  2. xodimlar (xodim_id, ism, email, bolim_id, bolim_nomi, bolim_binosi), bunda bolim_id -> bolim_nomi, bolim_binosi. Tranzitiv bog'liqlikni toping va jadvalni 3NF ga keltiring.

  3. Bir kutubxonada bitta kitobni bir nechta muallif yozishi mumkin. kitoblar (kitob_id, sarlavha, isbn, muallif_ismlari) da muallif_ismlari ustunida vergulli ro'yxat bor. Bu N:M bog'lanishni 3NF da to'g'ri modellashtiring (DDL bilan).

  4. talaba_id -> guruh_id va guruh_id -> kurator_ism berilgan. talaba_id -> kurator_ism bog'liqligi mavjudmi? Agar ha bo'lsa, uning turi qanday (tranzitivmi)? Tushuntiring.

Qiyin

  1. Quyidagi jadvalda ikkita candidate key bor: xodimlar (xodim_id, email, bolim_id, bolim), bunda xodim_id -> email, bolim_id, bolim; email -> xodim_id; bolim_id -> bolim. Ikkala candidate key'ni aniqlang, primary key tanlang, va jadvalni 3NF ga keltiring. Qaysi atribut nokalit?

  2. loyihalar (loyiha_id, xodim_id, xodim_roli, soat, xodim_ism, loyiha_nomi), PK = (loyiha_id, xodim_id). Bu jadvalda ham qisman, ham tranzitiv bog'liqlik bor. Ikkalasini ham toping va jadvalni bosqichma-bosqich (1NF -> 2NF -> 3NF) parchalang, har bosqichdagi jadvallarni yozing.

  3. Faktura tizimi: faktura_qatorlari (faktura_id, mahsulot_id, miqdor) va mahsulotlar (mahsulot_id, nomi, joriy_narx). Mijoz "faktura ustidagi narx keyin mahsulot narxi o'zgarsa ham o'zgarmasligi kerak" deydi. 3NF ni buzmasdan (yoki asoslangan istisno bilan) bu talabni qondiradigan sxemani loyihalang va nega narx_snapshot ataylab "takror" emasligini tushuntiring.

  4. Quyidagi jadval 2NF da, lekin 3NF da emas: arizalar (ariza_id, talaba_id, talaba_email, holat_kodi, holat_matni), bunda holat_kodi -> holat_matni va talaba_id -> talaba_email. Ikkita tranzitiv bog'liqlikni toping va to'liq 3NF sxemani DDL bilan yozing (FK va CHECK constraint'lar bilan).

Yechimlar

Yechim β€” 1
  • UPDATE: Olimov telefonini o'zgartirsa, 1- va 3-qatorni (har ikkala "Olimov" qatorini) yangilash kerak. Bittasi qolib ketsa, Olimovning ikki xil raqami paydo bo'ladi.
  • INSERT: yangi o'qituvchi (masalan Saidov) talaba biriktirilmaguncha qo'shilolmaydi, chunki talaba_id/ism NULL bo'la olmaydi (PK).
  • DELETE: Dilnoza qatorini (3-talaba) o'chirsangiz, u "Python"+"Karimov" bo'yicha oxirgi qator bo'lsa, Karimov va uning telefoni haqidagi yagona yozuv ham yo'qoladi.

Umumiy sabab: talaba, o'qituvchi va yozilish β€” uch mustaqil fakt bitta jadvalda.

Yechim β€” 2

3NF buziladi. FD: kitob_id -> sarlavha, janr_kodi (kalitga to'g'ridan-to'g'ri) va janr_kodi -> janr_nomi (nokalit -> nokalit). Demak kitob_id -> janr_kodi -> janr_nomi β€” tranzitiv bog'liqlik. Qisman emas, chunki kalit bitta ustun (kitob_id), shuning uchun qisman bog'liqlik bo'lishi mumkin emas β€” jadval 2NF da, lekin 3NF da emas.

Tuzatish: janrlar (janr_kodi PK, janr_nomi) va kitoblar (kitob_id PK, sarlavha, janr_kodi FK).

Yechim β€” 3

1NF buziladi (atomiklik): bitta katakda ro'yxat. "Sut nechta xaridda bor?" so'rovi yozib bo'lmaydi, FK qo'yib bo'lmaydi.

1NF ga keltirish β€” har element alohida qatorga:

CREATE SCHEMA IF NOT EXISTS s3; SET search_path = s3;
CREATE TABLE mahsulotlar (mahsulot_id int PRIMARY KEY, nomi text NOT NULL);
CREATE TABLE xaridlar    (xarid_id int PRIMARY KEY, sana date NOT NULL DEFAULT current_date);
CREATE TABLE xarid_qatorlari (
    xarid_id    int REFERENCES xaridlar(xarid_id),
    mahsulot_id int REFERENCES mahsulotlar(mahsulot_id),
    PRIMARY KEY (xarid_id, mahsulot_id)
);
DROP SCHEMA s3 CASCADE;
Yechim β€” 4
  • telefon -> mijoz_ism: faqat agar bitta telefon bitta mijozga tegishli bo'lsa to'g'ri. Agar oilada bitta raqamni ikki kishi ishlatsa β€” buziladi. Bu biznes qoidasiga bog'liq, universal emas.
  • tugilgan_yil -> yosh: bir yilda tug'ilganlarning yoshi bir xil bo'lgani uchun "shu yilda" to'g'ri ko'rinadi, lekin yosh vaqt o'tib o'zgaradi β€” yosh hosil (derived) atribut. Uni saqlamaslik, current_date - tugilgan_sana bilan hisoblash to'g'ri (hosil atribut β€” 10-bobda generated column).
Yechim β€” 5

FD lar (PK = {buyurtma_id, mahsulot_id}): - mahsulot_id -> mahsulot_nomi, mahsulot_narx β€” qisman bog'liqlik (kalitning bir qismi). - (buyurtma_id, mahsulot_id) -> miqdor β€” to'liq kalit.

Jadval 1NF da, lekin 2NF da emas (qisman bog'liqlik bor). 3NF ga keltirish:

CREATE SCHEMA IF NOT EXISTS s5; SET search_path = s5;
CREATE TABLE mahsulotlar (
    mahsulot_id int PRIMARY KEY,
    nomi        text NOT NULL,
    joriy_narx  numeric(12,2) NOT NULL
);
CREATE TABLE buyurtma_qatorlari (
    buyurtma_id  int,
    mahsulot_id  int REFERENCES mahsulotlar(mahsulot_id),
    miqdor       int NOT NULL CHECK (miqdor > 0),
    narx_snapshot numeric(12,2) NOT NULL,  -- tarixiy narx (10-bobga qarang)
    PRIMARY KEY (buyurtma_id, mahsulot_id)
);
DROP SCHEMA s5 CASCADE;
Yechim β€” 6

Tranzitiv bog'liqlik: xodim_id -> bolim_id -> bolim_nomi, bolim_binosi. bolim_nomi va bolim_binosi kalitga (xodim_id) faqat bolim_id orqali bog'langan. 3NF ga keltirish (PG18 da tekshirilgan):

CREATE SCHEMA IF NOT EXISTS s6; SET search_path = s6;
CREATE TABLE bolimlar (
    bolim_id int PRIMARY KEY,
    nomi     text NOT NULL,
    bina     text NOT NULL
);
CREATE TABLE xodimlar (
    xodim_id int PRIMARY KEY,
    ism      text NOT NULL,
    email    text UNIQUE NOT NULL,
    bolim_id int NOT NULL REFERENCES bolimlar(bolim_id)
);
-- Endi bo'lim binosini o'zgartirish β€” BITTA UPDATE, hamma xodimga ta'sir qiladi:
INSERT INTO bolimlar VALUES (1,'Sotuv','A-bino'),(2,'Ombor','B-bino');
INSERT INTO xodimlar VALUES (101,'A','a@x.uz',1),(102,'B','b@x.uz',1),(103,'C','c@x.uz',2);
UPDATE bolimlar SET bina='C-bino' WHERE bolim_id=1;
DROP SCHEMA s6 CASCADE;
Yechim β€” 7

muallif_ismlari ro'yxati 1NF ni buzadi, kitob-muallif esa N:M bog'lanish (04-bobga qarang) β€” bog'lovchi (junction) jadval kerak:

CREATE SCHEMA IF NOT EXISTS s7; SET search_path = s7;
CREATE TABLE mualliflar (
    muallif_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism        text NOT NULL
);
CREATE TABLE kitoblar (
    kitob_id   int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sarlavha   text NOT NULL,
    isbn       text UNIQUE NOT NULL
);
CREATE TABLE kitob_muallif (        -- N:M junction
    kitob_id   int REFERENCES kitoblar(kitob_id),
    muallif_id int REFERENCES mualliflar(muallif_id),
    PRIMARY KEY (kitob_id, muallif_id)
);
DROP SCHEMA s7 CASCADE;

PG18 da tekshirilgan: INSERT + 3 jadvalli JOIN kitob-muallif juftliklarini to'g'ri qaytaradi.

Yechim β€” 8

Ha, talaba_id -> kurator_ism mavjud β€” bu funksional bog'liqlikning tranzitivlik xususiyati (Armstrong aksiomalaridan biri): agar X -> Y va Y -> Z bo'lsa, X -> Z ham kelib chiqadi. Bu yerda talaba_id -> guruh_id -> kurator_ism, demak talaba_id -> kurator_ism tranzitiv bog'liqlik. Aynan shu tranzitiv FD 3NF ni buzadi (agar hammasi bitta jadvalda bo'lsa), shuning uchun guruhlar (guruh_id PK, kurator_ism) alohida jadval bo'lishi kerak.

Yechim β€” 9

Candidate key lar: {xodim_id} (chunki xodim_id -> hamma narsa) va {email} (chunki email -> xodim_id -> hamma narsa, tranzitivlik bo'yicha). Primary key sifatida xodim_id ni tanlaymiz (barqaror surrogate-ga yaqin, 06-bobga qarang), email β€” alternativ kalit (UNIQUE).

Nokalit atribut: bolim (u hech qaysi candidate key tarkibiga kirmaydi). bolim_id ham nokalit. Tranzitiv bog'liqlik bolim_id -> bolim 3NF ni buzadi. Tuzatish:

CREATE SCHEMA IF NOT EXISTS s9; SET search_path = s9;
CREATE TABLE bolimlar (bolim_id text PRIMARY KEY, bolim text NOT NULL);
CREATE TABLE xodimlar (
    xodim_id int PRIMARY KEY,
    ism      text NOT NULL,
    email    text UNIQUE NOT NULL,        -- alternativ (candidate) kalit
    bolim_id text NOT NULL REFERENCES bolimlar(bolim_id)
);
DROP SCHEMA s9 CASCADE;
Yechim β€” 10

FD lar (PK = {loyiha_id, xodim_id}): - xodim_id -> xodim_ism β€” qisman (kalit qismiga). - loyiha_id -> loyiha_nomi β€” qisman (kalit qismiga). - (loyiha_id, xodim_id) -> xodim_roli, soat β€” to'liq kalit.

1NF: atomik, kompozit PK bor (boshlang'ich holat).

2NF (qisman bog'liqliklarni ajratamiz): 

xodimlar             (xodim_id PK, xodim_ism)
loyihalar            (loyiha_id PK, loyiha_nomi)
ishtirok             (loyiha_id, xodim_id) PK, xodim_roli, soat

3NF: bu misolda 2NF dan keyin tranzitiv bog'liqlik qolmadi (har jadvalda nokalit atribut to'g'ridan-to'g'ri kalitga bog'liq), demak sxema allaqachon 3NF da.

CREATE SCHEMA IF NOT EXISTS s10; SET search_path = s10;
CREATE TABLE xodimlar  (xodim_id int PRIMARY KEY, xodim_ism text NOT NULL);
CREATE TABLE loyihalar (loyiha_id int PRIMARY KEY, loyiha_nomi text NOT NULL);
CREATE TABLE ishtirok (
    loyiha_id  int REFERENCES loyihalar(loyiha_id),
    xodim_id   int REFERENCES xodimlar(xodim_id),
    xodim_roli text NOT NULL,
    soat       numeric(6,2) NOT NULL CHECK (soat >= 0),
    PRIMARY KEY (loyiha_id, xodim_id)
);
DROP SCHEMA s10 CASCADE;
Yechim β€” 11

Talab: faktura narxi tarixiy, mahsulot narxi joriy. narx_snapshot β€” bu takror EMAS, chunki u boshqa faktni ifodalaydi: "buyurtma berilgan ondagi narx" mahsulotlar.joriy_narx ("hozirgi narx") dan ma'no jihatdan farq qiladi. Funksional bog'liqlik nuqtai nazaridan ham bu to'g'ri: (faktura_id, mahsulot_id) -> narx_snapshot (to'liq kalitga bog'liq, qisman emas).

CREATE SCHEMA IF NOT EXISTS s11; SET search_path = s11;
CREATE TABLE mahsulotlar (
    mahsulot_id int PRIMARY KEY,
    nomi        text NOT NULL,
    joriy_narx  numeric(12,2) NOT NULL
);
CREATE TABLE faktura_qatorlari (
    faktura_id    int,
    mahsulot_id   int REFERENCES mahsulotlar(mahsulot_id),
    miqdor        int NOT NULL CHECK (miqdor > 0),
    narx_snapshot numeric(12,2) NOT NULL,   -- tarixiy fakt
    PRIMARY KEY (faktura_id, mahsulot_id)
);
INSERT INTO mahsulotlar VALUES (1,'Telefon',1000),(2,'Chexol',50);
INSERT INTO faktura_qatorlari VALUES (500,1,1,1000),(500,2,2,50);
UPDATE mahsulotlar SET joriy_narx=1200 WHERE mahsulot_id=1;
-- Faktura hali 1000.00, joriy narx 1200.00 β€” tarixiy fakt saqlandi:
SELECT f.faktura_id, m.nomi, f.narx_snapshot, m.joriy_narx
FROM faktura_qatorlari f JOIN mahsulotlar m USING(mahsulot_id) ORDER BY 1,2;
DROP SCHEMA s11 CASCADE;

PG18 da tekshirilgan natija: Telefon narx_snapshot=1000.00, joriy_narx=1200.00 β€” narx o'zgarsa ham faktura o'zgarmadi.

Yechim β€” 12

Ikki tranzitiv bog'liqlik: ariza_id -> talaba_id -> talaba_email va ariza_id -> holat_kodi -> holat_matni. Ikkalasini ham o'z jadvaliga ajratamiz:

CREATE SCHEMA IF NOT EXISTS s12; SET search_path = s12;
CREATE TABLE talabalar (
    talaba_id int PRIMARY KEY,
    email     text UNIQUE NOT NULL
);
CREATE TABLE holatlar (
    holat_kodi text PRIMARY KEY,
    matn       text NOT NULL
);
CREATE TABLE arizalar (
    ariza_id   int PRIMARY KEY,
    talaba_id  int  NOT NULL REFERENCES talabalar(talaba_id),
    holat_kodi text NOT NULL REFERENCES holatlar(holat_kodi)
);
-- holat lug'at-jadvali (lookup) β€” qadriyatlarni cheklaydi (11-, 12-bobga qarang)
INSERT INTO holatlar VALUES ('YANGI','Yangi'),('TASDIQ','Tasdiqlangan'),('RAD','Rad etilgan');
INSERT INTO talabalar VALUES (1,'a@x.uz'),(2,'b@x.uz');
INSERT INTO arizalar  VALUES (100,1,'YANGI'),(101,2,'TASDIQ');
DROP SCHEMA s12 CASCADE;

Endi talaba emaili va holat matni bitta joyda saqlanadi; FK ular bilan yaxlitlikni qo'riqlaydi.

⬅️ Oldingi: 06 β€” Kalit dizayni: natural, surrogate, UUID, kompozit Β· 🏠 README Β· Keyingi: 08 β€” Normalizatsiya II: BCNF, 4NF, 5NF va denormalizatsiya ➑️