Amaliy loyiha - maktab tizimi sxemasi

-- Ma'lumotnoma jadvallari
CREATE TABLE oqituvchilar (
    id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ism       VARCHAR(50)  NOT NULL,
    familiya  VARCHAR(50)  NOT NULL,
    email     VARCHAR(100) NOT NULL,
    ishga_kirgan DATE      NOT NULL,
    faolmi    BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_oqituvchi_email UNIQUE (email),
    CONSTRAINT chk_oqituvchi_email CHECK (email LIKE '%@%.%')
) ENGINE = InnoDB;

CREATE TABLE fanlar (
    id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    nom       VARCHAR(50) NOT NULL,
    haftalik_soat TINYINT UNSIGNED NOT NULL,
    CONSTRAINT uq_fan_nom UNIQUE (nom),
    CONSTRAINT chk_fan_soat CHECK (haftalik_soat BETWEEN 1 AND 12)
) ENGINE = InnoDB;

CREATE TABLE sinflar (
    id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    nom        VARCHAR(10) NOT NULL,
    oquv_yili  SMALLINT UNSIGNED NOT NULL,
    rahbar_id  INT UNSIGNED NULL,
    CONSTRAINT uq_sinf UNIQUE (nom, oquv_yili),
    CONSTRAINT fk_sinf_rahbar FOREIGN KEY (rahbar_id)
        REFERENCES oqituvchilar(id) ON DELETE SET NULL
) ENGINE = InnoDB;

-- Asosiy mavjudliklar
CREATE TABLE oquvchilar (
    id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ism           VARCHAR(50) NOT NULL,
    familiya      VARCHAR(50) NOT NULL,
    tugilgan_sana DATE        NOT NULL,
    sinf_id       INT UNSIGNED NOT NULL,
    ochirilgan    DATETIME    NULL DEFAULT NULL,
    CONSTRAINT fk_oquvchi_sinf FOREIGN KEY (sinf_id)
        REFERENCES sinflar(id) ON DELETE RESTRICT,
    KEY idx_oquvchi_sinf (sinf_id),
    KEY idx_oquvchi_familiya (familiya, ism)
) ENGINE = InnoDB;

CREATE TABLE ota_onalar (
    id       INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ism      VARCHAR(50)  NOT NULL,
    familiya VARCHAR(50)  NOT NULL,
    telefon  VARCHAR(20)  NOT NULL,
    CONSTRAINT uq_ota_ona_telefon UNIQUE (telefon)
) ENGINE = InnoDB;

-- Bog'lovchi jadvallar
CREATE TABLE oqituvchi_fanlari (
    oqituvchi_id INT UNSIGNED NOT NULL,
    fan_id       INT UNSIGNED NOT NULL,
    PRIMARY KEY (oqituvchi_id, fan_id),
    CONSTRAINT fk_of_oqituvchi FOREIGN KEY (oqituvchi_id)
        REFERENCES oqituvchilar(id) ON DELETE CASCADE,
    CONSTRAINT fk_of_fan FOREIGN KEY (fan_id)
        REFERENCES fanlar(id) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE ota_ona_oquvchi (
    ota_ona_id INT UNSIGNED NOT NULL,
    oquvchi_id INT UNSIGNED NOT NULL,
    qarindosh  ENUM('ota', 'ona', 'vasiy') NOT NULL,
    PRIMARY KEY (ota_ona_id, oquvchi_id),
    CONSTRAINT fk_oo_ota_ona FOREIGN KEY (ota_ona_id)
        REFERENCES ota_onalar(id) ON DELETE CASCADE,
    CONSTRAINT fk_oo_oquvchi FOREIGN KEY (oquvchi_id)
        REFERENCES oquvchilar(id) ON DELETE CASCADE,
    KEY idx_oo_oquvchi (oquvchi_id)
) ENGINE = InnoDB;

CREATE TABLE darslar (
    id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    sinf_id      INT UNSIGNED NOT NULL,
    fan_id       INT UNSIGNED NOT NULL,
    oqituvchi_id INT UNSIGNED NOT NULL,
    hafta_kuni   TINYINT UNSIGNED NOT NULL,
    juftlik      TINYINT UNSIGNED NOT NULL,
    CONSTRAINT uq_dars_vaqt UNIQUE (sinf_id, hafta_kuni, juftlik),
    CONSTRAINT chk_dars_kun     CHECK (hafta_kuni BETWEEN 1 AND 6),
    CONSTRAINT chk_dars_juftlik CHECK (juftlik BETWEEN 1 AND 8),
    CONSTRAINT fk_dars_sinf FOREIGN KEY (sinf_id) REFERENCES sinflar(id),
    CONSTRAINT fk_dars_fan  FOREIGN KEY (fan_id)  REFERENCES fanlar(id),
    CONSTRAINT fk_dars_oqituvchi FOREIGN KEY (oqituvchi_id)
        REFERENCES oqituvchilar(id)
) ENGINE = InnoDB;

-- Assotsiativ mavjudliklar
CREATE TABLE baholar (
    id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    oquvchi_id INT UNSIGNED NOT NULL,
    fan_id     INT UNSIGNED NOT NULL,
    ball       TINYINT UNSIGNED NOT NULL,
    sana       DATE NOT NULL,
    izoh       VARCHAR(200) NULL,
    CONSTRAINT chk_ball CHECK (ball BETWEEN 1 AND 5),
    CONSTRAINT fk_baho_oquvchi FOREIGN KEY (oquvchi_id)
        REFERENCES oquvchilar(id) ON DELETE CASCADE,
    CONSTRAINT fk_baho_fan FOREIGN KEY (fan_id) REFERENCES fanlar(id),
    KEY idx_baho_oquvchi_sana (oquvchi_id, sana),
    KEY idx_baho_fan (fan_id)
) ENGINE = InnoDB;

CREATE TABLE davomat (
    oquvchi_id INT UNSIGNED NOT NULL,
    dars_id    INT UNSIGNED NOT NULL,
    sana       DATE NOT NULL,
    holat      ENUM('keldi', 'kelmadi', 'kechikdi', 'sababli') NOT NULL,
    PRIMARY KEY (oquvchi_id, dars_id, sana),
    CONSTRAINT fk_davomat_oquvchi FOREIGN KEY (oquvchi_id)
        REFERENCES oquvchilar(id) ON DELETE CASCADE,
    CONSTRAINT fk_davomat_dars FOREIGN KEY (dars_id)
        REFERENCES darslar(id) ON DELETE CASCADE,
    KEY idx_davomat_sana (sana)
) ENGINE = InnoDB;

SELECT TABLE_NAME AS jadval
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY BINARY TABLE_NAME;

INSERT INTO oqituvchilar (ism, familiya, email, ishga_kirgan) VALUES
    ('Nodira', 'Karimova', '[email protected]', '2020-09-01'),
    ('Aziza',  'Tosheva',  '[email protected]',  '2022-09-01'),
    ('Kamola', 'Rasulova', '[email protected]', '2024-09-01');

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

INSERT INTO sinflar (nom, oquv_yili, rahbar_id) VALUES
    ('9-A', 2026, 1), ('9-B', 2026, 2);

INSERT INTO oquvchilar (ism, familiya, tugilgan_sana, sinf_id) VALUES
    ('Husanboy', 'Qodirov',  '2011-03-15', 1),
    ('Malika',   'Yusupova', '2011-07-22', 1),
    ('Dilnoza',  'Karimova', '2011-01-09', 2);

INSERT INTO ota_onalar (ism, familiya, telefon) VALUES
    ('Baxtiyor', 'Qodirov', '901234567'),
    ('Zulfiya',  'Yusupova', '911112233');

INSERT INTO ota_ona_oquvchi (ota_ona_id, oquvchi_id, qarindosh) VALUES
    (1, 1, 'ota'), (2, 2, 'ona');

INSERT INTO oqituvchi_fanlari (oqituvchi_id, fan_id) VALUES
    (1, 1), (1, 2), (2, 3), (3, 1);

INSERT INTO darslar (sinf_id, fan_id, oqituvchi_id, hafta_kuni, juftlik) VALUES
    (1, 1, 1, 1, 1),
    (1, 2, 1, 1, 2),
    (1, 3, 2, 2, 1),
    (2, 1, 3, 1, 1);

INSERT INTO baholar (oquvchi_id, fan_id, ball, sana) VALUES
    (1, 1, 5, '2026-09-07'),
    (1, 2, 4, '2026-09-07'),
    (2, 1, 5, '2026-09-07'),
    (2, 1, 4, '2026-09-14'),
    (3, 1, 3, '2026-09-07');

INSERT INTO davomat (oquvchi_id, dars_id, sana, holat) VALUES
    (1, 1, '2026-09-07', 'keldi'),
    (2, 1, '2026-09-07', 'kechikdi'),
    (1, 2, '2026-09-07', 'keldi'),
    (2, 2, '2026-09-07', 'kelmadi');

SELECT
    (SELECT COUNT(*) FROM oquvchilar)   AS oquvchilar,
    (SELECT COUNT(*) FROM oqituvchilar) AS oqituvchilar,
    (SELECT COUNT(*) FROM darslar)      AS darslar,
    (SELECT COUNT(*) FROM baholar)      AS baholar;

-- 1. Sinf ro'yxati va rahbari
SELECT s.nom AS sinf,
       CONCAT(o.ism, ' ', o.familiya) AS rahbar,
       COUNT(oq.id) AS oquvchilar
FROM sinflar s
LEFT JOIN oqituvchilar o ON o.id = s.rahbar_id
LEFT JOIN oquvchilar oq  ON oq.sinf_id = s.id AND oq.ochirilgan IS NULL
GROUP BY s.id, s.nom, rahbar
ORDER BY s.nom;

-- 2. O'quvchining o'rtacha bahosi fanlar bo'yicha
SELECT CONCAT(o.ism, ' ', o.familiya) AS oquvchi,
       f.nom AS fan,
       ROUND(AVG(b.ball), 2) AS ortacha,
       COUNT(b.id) AS baholar_soni
FROM baholar b
JOIN oquvchilar o ON o.id = b.oquvchi_id
JOIN fanlar f     ON f.id = b.fan_id
GROUP BY o.id, oquvchi, f.id, f.nom
ORDER BY o.id, f.id;

-- 3. Dushanba kungi dars jadvali
SELECT s.nom AS sinf, d.juftlik, f.nom AS fan,
       CONCAT(o.ism, ' ', o.familiya) AS oqituvchi
FROM darslar d
JOIN sinflar s      ON s.id = d.sinf_id
JOIN fanlar f       ON f.id = d.fan_id
JOIN oqituvchilar o ON o.id = d.oqituvchi_id
WHERE d.hafta_kuni = 1
ORDER BY s.nom, d.juftlik;

-- 4. Davomat statistikasi
SELECT CONCAT(o.ism, ' ', o.familiya) AS oquvchi,
       SUM(d.holat = 'keldi')    AS keldi,
       SUM(d.holat = 'kechikdi') AS kechikdi,
       SUM(d.holat = 'kelmadi')  AS kelmadi
FROM davomat d
JOIN oquvchilar o ON o.id = d.oquvchi_id
GROUP BY o.id, oquvchi
ORDER BY o.id;

-- 5. Ota-ona bolasining natijalarini ko'radi
SELECT CONCAT(oo.ism, ' ', oo.familiya) AS ota_ona,
       ooo.qarindosh,
       CONCAT(o.ism, ' ', o.familiya)   AS farzand,
       ROUND(AVG(b.ball), 2) AS ortacha_baho
FROM ota_onalar oo
JOIN ota_ona_oquvchi ooo ON ooo.ota_ona_id = oo.id
JOIN oquvchilar o        ON o.id = ooo.oquvchi_id
LEFT JOIN baholar b      ON b.oquvchi_id = o.id
GROUP BY oo.id, ota_ona, ooo.qarindosh, o.id, farzand
ORDER BY oo.id;

-- Bir sinfda bir vaqtda ikki dars bo'la olmaydi
INSERT INTO darslar (sinf_id, fan_id, oqituvchi_id, hafta_kuni, juftlik)
VALUES (1, 3, 2, 1, 1);

ERROR 1062 (23000): Duplicate entry '1-1-1' for key 'uq_dars_vaqt'

-- Baho 1 dan 5 gacha bo'lishi kerak
INSERT INTO baholar (oquvchi_id, fan_id, ball, sana)
VALUES (1, 1, 9, '2026-09-20');

ERROR 4025 (23000): CONSTRAINT `chk_ball` failed for `maktab`.`baholar`

-- Mavjud bo'lmagan sinfga o'quvchi qo'shib bo'lmaydi
INSERT INTO oquvchilar (ism, familiya, tugilgan_sana, sinf_id)
VALUES ('Test', 'Test', '2011-01-01', 999);

ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`maktab`.`oquvchilar`,
CONSTRAINT `fk_oquvchi_sinf` FOREIGN KEY (`sinf_id`)
REFERENCES `sinflar` (`id`))

-- Barcha cheklovlarni ko'rish
SELECT CONSTRAINT_TYPE AS turi, COUNT(*) AS soni
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
GROUP BY CONSTRAINT_TYPE
ORDER BY BINARY CONSTRAINT_TYPE;