Uchinchi normal shakl va BCNF

CREATE TABLE xodimlar_yomon (
    id              INT PRIMARY KEY,
    ism             VARCHAR(100) NOT NULL,
    bolim_id        INT NOT NULL,
    bolim_nomi      VARCHAR(50)  NOT NULL,
    bolim_boshligi  VARCHAR(100) NOT NULL,
    bolim_qavati    TINYINT      NOT NULL
);

INSERT INTO xodimlar_yomon VALUES
    (1, 'Husanboy', 10, 'IT',      'Nodira Karimova', 3),
    (2, 'Malika',   20, 'Hisobot', 'Aziza Tosheva',   2),
    (3, 'Kamola',   10, 'IT',      'Nodira Karimova', 3),
    (4, 'Dilnoza',  10, 'IT',      'Nodira Karimova', 3);

SELECT id, ism, bolim_nomi, bolim_boshligi
FROM xodimlar_yomon
ORDER BY id;

-- bolim_id -> bolim_nomi bog'liqligi bormi?
SELECT bolim_id,
       COUNT(DISTINCT bolim_nomi)     AS turli_nom,
       COUNT(DISTINCT bolim_boshligi) AS turli_boshliq,
       COUNT(*)                       AS qatorlar
FROM xodimlar_yomon
GROUP BY bolim_id
ORDER BY bolim_id;

id -> bolim_id -> bolim_nomi, bolim_boshligi, bolim_qavati

-- 1. Bo'lim boshlig'i almashdi - uch qatorni yangilash kerak
UPDATE xodimlar_yomon
SET bolim_boshligi = 'Malika Yusupova'
WHERE id = 1;

SELECT bolim_id, COUNT(DISTINCT bolim_boshligi) AS turli_boshliq
FROM xodimlar_yomon
WHERE bolim_id = 10
GROUP BY bolim_id;

-- 2. Xodimsiz bo'lim saqlanmaydi
DELETE FROM xodimlar_yomon WHERE bolim_id = 20;

SELECT COUNT(*) AS hisobot_bolimi_haqida
FROM xodimlar_yomon
WHERE bolim_nomi = 'Hisobot';

CREATE TABLE bolimlar (
    id       INT PRIMARY KEY,
    nom      VARCHAR(50)  NOT NULL UNIQUE,
    boshliq  VARCHAR(100) NOT NULL,
    qavat    TINYINT      NOT NULL
);

CREATE TABLE xodimlar (
    id       INT PRIMARY KEY,
    ism      VARCHAR(100) NOT NULL,
    bolim_id INT NOT NULL,
    FOREIGN KEY (bolim_id) REFERENCES bolimlar(id)
);

INSERT INTO bolimlar VALUES
    (10, 'IT',      'Nodira Karimova', 3),
    (20, 'Hisobot', 'Aziza Tosheva',   2),
    (30, 'Marketing', 'Dilnoza Yusupova', 1);

INSERT INTO xodimlar VALUES
    (1, 'Husanboy', 10),
    (2, 'Malika',   20),
    (3, 'Kamola',   10),
    (4, 'Dilnoza',  10);

SELECT x.ism, b.nom AS bolim, b.boshliq
FROM xodimlar x
JOIN bolimlar b ON b.id = x.bolim_id
ORDER BY x.id;

-- 1. Boshliq almashdi - BITTA qator
UPDATE bolimlar SET boshliq = 'Malika Yusupova' WHERE id = 10;

SELECT x.ism, b.boshliq
FROM xodimlar x
JOIN bolimlar b ON b.id = x.bolim_id
WHERE b.id = 10
ORDER BY x.id;

-- 2. Xodimsiz bo'lim ham saqlanadi
SELECT b.nom, COUNT(x.id) AS xodimlar_soni
FROM bolimlar b
LEFT JOIN xodimlar x ON x.bolim_id = b.id
GROUP BY b.id, b.nom
ORDER BY b.id;

CREATE TABLE dars_biriktirish (
    talaba_id  INT NOT NULL,
    fan        VARCHAR(50) NOT NULL,
    oqituvchi  VARCHAR(100) NOT NULL,
    PRIMARY KEY (talaba_id, fan)
);

INSERT INTO dars_biriktirish VALUES
    (1, 'Matematika',  'Nodira Karimova'),
    (1, 'Fizika',      'Aziza Tosheva'),
    (2, 'Matematika',  'Nodira Karimova'),
    (3, 'Matematika',  'Nodira Karimova');

SELECT talaba_id, fan, oqituvchi
FROM dars_biriktirish
ORDER BY talaba_id, fan;

-- fan -> oqituvchi bog'liqligi bor
SELECT fan, COUNT(DISTINCT oqituvchi) AS turli_oqituvchi
FROM dars_biriktirish
GROUP BY fan
ORDER BY fan;

CREATE TABLE fan_oqituvchi (
    fan       VARCHAR(50)  PRIMARY KEY,
    oqituvchi VARCHAR(100) NOT NULL
);

CREATE TABLE talaba_fan (
    talaba_id INT NOT NULL,
    fan       VARCHAR(50) NOT NULL,
    PRIMARY KEY (talaba_id, fan),
    FOREIGN KEY (fan) REFERENCES fan_oqituvchi(fan)
);

INSERT INTO fan_oqituvchi VALUES
    ('Matematika', 'Nodira Karimova'),
    ('Fizika',     'Aziza Tosheva');

INSERT INTO talaba_fan VALUES
    (1, 'Matematika'), (1, 'Fizika'),
    (2, 'Matematika'), (3, 'Matematika');

SELECT tf.talaba_id, tf.fan, fo.oqituvchi
FROM talaba_fan tf
JOIN fan_oqituvchi fo ON fo.fan = tf.fan
ORDER BY tf.talaba_id, tf.fan;

-- Endi o'qituvchini almashtirish - bitta qator
UPDATE fan_oqituvchi SET oqituvchi = 'Kamola Rasulova' WHERE fan = 'Matematika';

SELECT fan, oqituvchi FROM fan_oqituvchi ORDER BY fan;