Ikkinchi normal shakl (2NF)

CREATE TABLE yozilishlar_yomon (
    talaba_id     INT NOT NULL,
    fan_id        INT NOT NULL,
    talaba_ismi   VARCHAR(100) NOT NULL,
    talaba_sinfi  VARCHAR(10)  NOT NULL,
    fan_nomi      VARCHAR(50)  NOT NULL,
    fan_krediti   TINYINT      NOT NULL,
    baho          TINYINT,
    PRIMARY KEY (talaba_id, fan_id)
);

INSERT INTO yozilishlar_yomon VALUES
    (1, 1, 'Husanboy', '9-A', 'Matematika',  6, 5),
    (1, 2, 'Husanboy', '9-A', 'Fizika',      4, 4),
    (1, 3, 'Husanboy', '9-A', 'Ingliz tili', 3, 5),
    (2, 1, 'Malika',   '9-A', 'Matematika',  6, 4),
    (2, 2, 'Malika',   '9-A', 'Fizika',      4, 5),
    (3, 1, 'Kamola',   '9-B', 'Matematika',  6, 3);

SELECT talaba_id, fan_id, talaba_ismi, fan_nomi, baho
FROM yozilishlar_yomon
ORDER BY talaba_id, fan_id;

-- Isbot: bir xil talaba_id da ism DOIM bir xil
SELECT talaba_id, COUNT(DISTINCT talaba_ismi) AS turli_ismlar
FROM yozilishlar_yomon
GROUP BY talaba_id
ORDER BY talaba_id;

-- Baho esa faqat juftlikka bog'liq - bir talabada turli baholar bor
SELECT talaba_id, COUNT(DISTINCT baho) AS turli_baholar
FROM yozilishlar_yomon
GROUP BY talaba_id
ORDER BY talaba_id;

-- 1. Yangilash anomaliyasi: ismni bir joyda o'zgartirdik
UPDATE yozilishlar_yomon
SET talaba_ismi = 'Husanboy Qodirov'
WHERE talaba_id = 1 AND fan_id = 1;

SELECT talaba_id, COUNT(DISTINCT talaba_ismi) AS turli_ismlar
FROM yozilishlar_yomon
WHERE talaba_id = 1
GROUP BY talaba_id;

-- 2. O'chirish anomaliyasi: Kamolaning yagona bahosini o'chirsak,
--    uning ismi va sinfi ham yo'qoladi
DELETE FROM yozilishlar_yomon WHERE talaba_id = 3;

SELECT COUNT(*) AS kamola_haqida_malumot
FROM yozilishlar_yomon
WHERE talaba_ismi = 'Kamola';

-- 3. Qo'shish anomaliyasi: hali hech kim yozilmagan fanni
--    qo'shib bo'lmaydi - talaba_id kerak
INSERT INTO yozilishlar_yomon (fan_id, fan_nomi, fan_krediti)
VALUES (4, 'Tarix', 2);

ERROR 1364 (HY000): Field 'talaba_id' doesn't have a default value

CREATE TABLE talabalar (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    ism  VARCHAR(100) NOT NULL,
    sinf VARCHAR(10)  NOT NULL
);

CREATE TABLE fanlar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    nom    VARCHAR(50) NOT NULL UNIQUE,
    kredit TINYINT NOT NULL
);

CREATE TABLE yozilishlar (
    talaba_id INT NOT NULL,
    fan_id    INT NOT NULL,
    baho      TINYINT,
    PRIMARY KEY (talaba_id, fan_id),
    FOREIGN KEY (talaba_id) REFERENCES talabalar(id),
    FOREIGN KEY (fan_id)    REFERENCES fanlar(id)
);

INSERT INTO talabalar (ism, sinf) VALUES
    ('Husanboy', '9-A'), ('Malika', '9-A'), ('Kamola', '9-B');

INSERT INTO fanlar (nom, kredit) VALUES
    ('Matematika', 6), ('Fizika', 4), ('Ingliz tili', 3);

INSERT INTO yozilishlar VALUES
    (1, 1, 5), (1, 2, 4), (1, 3, 5),
    (2, 1, 4), (2, 2, 5),
    (3, 1, 3);

SELECT t.ism, f.nom AS fan, y.baho
FROM yozilishlar y
JOIN talabalar t ON t.id = y.talaba_id
JOIN fanlar f    ON f.id = y.fan_id
ORDER BY y.talaba_id, y.fan_id;

-- 1. Ismni o'zgartirish - bitta qator, ziddiyat mumkin emas
UPDATE talabalar SET ism = 'Husanboy Qodirov' WHERE id = 1;

SELECT ism FROM talabalar WHERE id = 1;

-- 2. Barcha bahoni o'chirsak ham talaba qoladi
DELETE FROM yozilishlar WHERE talaba_id = 3;

SELECT ism, sinf FROM talabalar WHERE id = 3;

-- 3. Hali hech kim yozilmagan fanni qo'shish mumkin
INSERT INTO fanlar (nom, kredit) VALUES ('Tarix', 2);

SELECT f.nom, COUNT(y.talaba_id) AS yozilganlar
FROM fanlar f
LEFT JOIN yozilishlar y ON y.fan_id = f.id
GROUP BY f.id, f.nom
ORDER BY f.id;

-- Takrorlanish darajasini o'lchash
SELECT
    COUNT(*) AS qatorlar,
    COUNT(DISTINCT talaba_id) AS noyob_talaba,
    COUNT(DISTINCT fan_id) AS noyob_fan
FROM yozilishlar;