The Result Sheet That Does Five Jobs at Once

Functional dependencies visible in the data

• RollNo → StudentName, Address, Class, ClassTeacher, TeacherPhone - a roll number identifies exactly one student, and the class teacher and phone are facts about that class, repeated on every one of the 96 rows.
• SubjectCode → SubjectName, FullMarks, PassMarks - but the subject name is not stored as data at all; it lives in the column heading Sub2. Physics full marks are the same fact in all 96 rows, yet each row can hold a different value, so there is no dependency protecting it.
• What we actually want is RollNo, Subject → Marks, but the design splits the Subject attribute into five columns so this dependency cannot be declared.

Is it in 1NF?

Every cell is atomic - no cell holds 'Physics, Chemistry'. But 1NF also forbids repeating groups, and Subject1/Sub1Full/Sub1Pass/Sub1Marks repeated five times is exactly a repeating group: five copies of the same attribute set. A table that needs new columns to store a new subject is a spreadsheet pretending to be a relation. So it fails 1NF.

The three classic anomalies are all present: update (teacher phone edited on 96 rows, 3 missed), insertion (a new subject requires a schema change or five empty columns), deletion (removing the leaver destroyed the class-teacher fact, which is a fact about the class and should not have depended on her).

Design rule used: every relation must have a determinant that is a candidate key, and non-key attributes must depend on 'the key, the whole key and nothing but the key'.

1. Class(ClassID PK, ClassName, ClassTeacher, TeacherPhone) - teacher and phone depend only on the class. Removes the update anomaly: one edit now changes one row.
2. Student(RollNo PK, StudentName, Address, ClassID FK → Class) - student facts depend only on RollNo.
3. Subject(SubCode PK, SubName, FullMarks, PassMarks) - full and pass marks depend on the subject, not on the student. Removes the change of Physics full marks being 96 edits; it is now one edit.
4. Result(RollNo, SubCode) PK, Marks with RollNo FK → Student, SubCode FK → Subject.

Deletion anomaly gone: deleting the leaver removes rows from Student and Result only; Mr. Karki stays in Class. Insertion anomaly gone: a sixth subject is one row in Subject, not a new column.

CREATE TABLE Result (
  RollNo  VARCHAR(10) NOT NULL REFERENCES Student(RollNo),
  SubCode VARCHAR(10) NOT NULL REFERENCES Subject(SubCode),
  Marks   INTEGER,
  PRIMARY KEY (RollNo, SubCode)
);

Marks is deliberately nullable: a registered student who was absent has a row with no mark, which is different from that student not choosing the subject. Keeping Marks as INTEGER, not TEXT, is part of the design - it stops '85 ' and '85' being different values.

Access SQL (Jet) version, since the school uses Access:

SELECT s.SubName,
       COUNT(*) AS Sat,
       SUM(IIF(r.Marks >= s.PassMarks, 1, 0)) AS Passed,
       SUM(IIF(r.Marks >= s.PassMarks, 1, 0)) * 100.0 / COUNT(*) AS PassPct
FROM Subject AS s
INNER JOIN Result AS r ON s.SubCode = r.SubCode
GROUP BY s.SubName
ORDER BY SUM(IIF(r.Marks >= s.PassMarks, 1, 0)) * 100.0 / COUNT(*) DESC;

In standard SQL replace IIF(x,1,0) with CASE WHEN x THEN 1 ELSE 0 END.

The trap: COUNT(Marks) does not count rows, it counts non-null marks, so absentees disappear from the denominator and the pass percentage is inflated. COUNT(*) counts every registered candidate, treating an absent student as a fail, which is what NEB result sheets do. If the school instead wants 'percent pass among those who appeared', it must say so and add WHERE r.Marks IS NOT NULL - but then the number of candidates must come from a separate query.

Two smaller points of reasoning: the pass mark is taken from Subject rather than hard-coded as 40, so a subject with a different pass mark is still handled; and the multiplication by 100.0 rather than 100 forces decimal division, otherwise integer division returns 0 or 1 for every subject.