ER model va mavjudliklar

CREATE TABLE sinflar (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    nom  VARCHAR(10) NOT NULL UNIQUE
);

CREATE TABLE oqituvchilar (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    ism      VARCHAR(100) NOT NULL,
    familiya VARCHAR(100) NOT NULL
);

CREATE TABLE fanlar (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    nom          VARCHAR(50) NOT NULL UNIQUE,
    oqituvchi_id INT,
    FOREIGN KEY (oqituvchi_id) REFERENCES oqituvchilar(id)
);

CREATE TABLE oquvchilar (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    ism      VARCHAR(100) NOT NULL,
    familiya VARCHAR(100) NOT NULL,
    sinf_id  INT NOT NULL,
    FOREIGN KEY (sinf_id) REFERENCES sinflar(id)
);

CREATE TABLE baholar (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    oquvchi_id INT NOT NULL,
    fan_id     INT NOT NULL,
    ball       TINYINT NOT NULL,
    sana       DATE NOT NULL,
    FOREIGN KEY (oquvchi_id) REFERENCES oquvchilar(id),
    FOREIGN KEY (fan_id) REFERENCES fanlar(id)
);

INSERT INTO sinflar (nom) VALUES ('9-A'), ('9-B');

INSERT INTO oqituvchilar (ism, familiya) VALUES
    ('Nodira', 'Karimova'), ('Aziza', 'Tosheva');

INSERT INTO fanlar (nom, oqituvchi_id) VALUES
    ('Matematika', 1), ('Fizika', 1), ('Ingliz tili', 2);

INSERT INTO oquvchilar (ism, familiya, sinf_id) VALUES
    ('Husanboy', 'Qodirov', 1),
    ('Malika', 'Yusupova', 1),
    ('Kamola', 'Rasulova', 2);

INSERT INTO baholar (oquvchi_id, fan_id, ball, sana) VALUES
    (1, 1, 5, '2026-03-01'),
    (1, 2, 4, '2026-03-02'),
    (2, 1, 5, '2026-03-01'),
    (3, 3, 3, '2026-03-03');

SELECT o.ism, o.familiya, s.nom AS sinf
FROM oquvchilar o
JOIN sinflar s ON s.id = o.sinf_id
ORDER BY o.id;

SELECT o.ism AS oquvchi, f.nom AS fan, b.ball,
       CONCAT(oq.ism, ' ', oq.familiya) AS oqituvchi
FROM baholar b
JOIN oquvchilar o   ON o.id = b.oquvchi_id
JOIN fanlar f       ON f.id = b.fan_id
JOIN oqituvchilar oq ON oq.id = f.oqituvchi_id
ORDER BY b.id;

-- oqituvchi_id NULL bo'lishi mumkin - fan o'qituvchisiz bo'lishi mumkin
INSERT INTO fanlar (nom, oqituvchi_id) VALUES ('Tarix', NULL);

-- sinf_id NOT NULL - o'quvchi albatta sinfga tegishli
SELECT nom, IFNULL(CAST(oqituvchi_id AS CHAR), 'tayinlanmagan') AS oqituvchi
FROM fanlar
ORDER BY id;

-- Baho o'quvchisiz mavjud bo'la olmaydi - u ZAIF mavjudlik
-- Uning o'ziga xos ma'nosi faqat o'quvchi va fan kontekstida bor

SELECT COUNT(*) AS baholar_soni FROM baholar;

DELETE FROM oquvchilar WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (`maktab`.`baholar`, CONSTRAINT
`baholar_ibfk_1` FOREIGN KEY (`oquvchi_id`) REFERENCES `oquvchilar` (`id`))

SELECT TABLE_NAME AS jadval, COLUMN_NAME AS ustun,
       REFERENCED_TABLE_NAME AS kimga
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY BINARY TABLE_NAME, ustun;