School Database Case Study

Project

School database classic DBMS mini project है। यह सब कुछ एक साथ लाता है: entities, primary और foreign keys, one-to-many और many-to-many relationships, और report cards व fee summaries जैसी असली queries। हम इसे design, create, और query करेंगे शुरू से अंत तक।

Tables design करना

• classes — id, class_name।
• students — id, name, class_id (FK → classes)।
• subjects — id, subject_name।
• marks — id, student_id (FK), subject_id (FK), marks।
• fees — id, student_id (FK), amount, status।

एक class के कई students होते हैं (one-to-many)। Marks students और subjects जोड़ते हैं (marks table से many-to-many)।

Schema बनाना

CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(20)
);
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);
CREATE TABLE subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(30)
);
CREATE TABLE marks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT, subject_id INT, marks INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

Sample data

INSERT INTO classes (class_name) VALUES ('Class 10');
INSERT INTO students (name, class_id) VALUES ('Aman', 1), ('Diya', 1);
INSERT INTO subjects (subject_name) VALUES ('Math'), ('Science');
INSERT INTO marks (student_id, subject_id, marks) VALUES
    (1, 1, 88), (1, 2, 76), (2, 1, 95), (2, 2, 90);

अब हमारे पास query करने को छोटा पर पूरा dataset है।

उपयोगी queries

-- एक class के students
SELECT s.name, c.class_name
FROM students s JOIN classes c ON s.class_id = c.id;

-- प्रति class students की संख्या
SELECT c.class_name, COUNT(s.id) AS total
FROM classes c LEFT JOIN students s ON s.class_id = c.id
GROUP BY c.id;

-- प्रति subject औसत marks
SELECT sub.subject_name, AVG(m.marks) AS avg_marks
FROM marks m JOIN subjects sub ON m.subject_id = sub.id
GROUP BY sub.id;

Report card query

SELECT s.name,
    SUM(m.marks) AS total,
    ROUND(AVG(m.marks), 2) AS average,
    CASE WHEN AVG(m.marks) >= 90 THEN 'A'
         WHEN AVG(m.marks) >= 75 THEN 'B'
         ELSE 'C' END AS grade
FROM students s
JOIN marks m ON m.student_id = s.id
GROUP BY s.id, s.name;

आम गलतियाँ

• सारे subjects students table में store करना। अलग marks table इस्तेमाल कीजिए।
• कोई foreign keys नहीं। Tables जोड़िए ताकि data संगत रहे।
• प्रति student class details दोहराना। उन्हें classes table में रखिए।
• खाली classes को LEFT JOIN भूलना। बिना students वाली classes गिनने को इसे इस्तेमाल कीजिए।

सारांश

• School database students, classes, subjects, marks और fees इस्तेमाल करता है।
• उन्हें foreign keys से जोड़िए (student → class, marks → student और subject)।
• Marks प्रति student प्रति subject एक row store कीजिए।
• असली reports को JOIN, GROUP BY और CASE से query कीजिए।
• यह एक project में सारे मुख्य DBMS concepts दिखाता है।

आगे, report card query गहराई से बनाइए, या library database project आज़माइए।