A teacher stores student and subject data in a single flat-file table as shown below. StudentID StudentName Subject1 Subject2 Subject3 TeacherName TeacherEm...

(a) Two problems with the flat-file table design: [2]

Problem 1: Data redundancy. Teacher details (name and email) are repeated for every student they teach. If Mr. Jones teaches 30 students, his name and email are stored 30 times. This wastes storage space and creates the risk of inconsistency: if his email changes, every row must be updated, and missing even one creates contradictory data (an update anomaly). [1]

Problem 2: The structure limits each student to exactly three subjects. A student taking fewer subjects leaves empty fields (wasted space), and a student taking more than three subjects cannot be accommodated without adding more columns to the table. This inflexible design requires changing the table structure whenever the maximum number of subjects changes. [1]

(b) Why separate Subject columns are poor design and how to fix it: [2]

Having separate Subject1, Subject2, Subject3 columns is called a repeating group. [1] It is a violation of first normal form (1NF) in database design. This structure limits the number of subjects to three and creates empty cells when a student takes fewer subjects. It also makes queries difficult: searching for all students taking "Maths" requires checking three separate columns.

The subjects should be moved to a separate table where each student-subject combination is a separate record. [1] For example:

STUDENT_SUBJECT table:

This design allows any number of subjects per student without altering the table structure, and querying becomes simple: WHERE Subject = 'Maths' finds all students taking Maths in one column.