From spreadsheet to MySQL: normalization and login security at a coaching institute
A coaching institute stores student records in one spreadsheet. It has 1,200 students, 18 batches, and 40 tutors. A student can join multiple batches. The spreadsheet repeats the student's name, phone, and address in every batch row. Staff report that changing a phone number requires editing many rows, and some rows show different numbers for the same student. A developer proposes MySQL tables: Students(student_id, name, phone, address), Batches(batch_id, subject, tutor_id), and Enrolments(student_id, batch_id, fee_paid). The director also wants a student login page to view attendance. The developer suggests storing passwords as unsalted MD5 hashes and building SQL queries by concatenating the entered username and password. The institute's server is on a shared hostel Wi-Fi connection, and the MySQL port is open to the local network.
1. Identify the normalization problems in the spreadsheet and evaluate whether the proposed three-table design is in 3NF. State any assumptions.
Read the worked answer
The spreadsheet is not in first normal form because each student's batch information is repeated across rows; a student with three batches has three rows, duplicating name, phone, and address. This causes redundancy, update anomalies, and inconsistent phone numbers. The proposed Students and Batches entities with an Enrolments bridge correctly resolve the many-to-many relationship. However, the design is not automatically 3NF. First, tutor details are missing: if tutor name or subject is stored in Batches alongside tutor_id, those attributes depend on tutor_id, not on batch_id, creating a transitive dependency; split off a Tutors table. Second, fee_paid: if the fee is the same for every student in a batch, it depends only on batch_id, so storing it in Enrolments violates 2NF; move it to Batches. If the fee varies by student because of scholarships, it depends on the full key (student_id, batch_id), so keep it in Enrolments. Third, if a student can have multiple phone numbers, the single phone column violates 1NF; use a separate Phone table. With those changes, the schema can be 3NF.
2. Review the proposed login security. What specific risks arise from MD5, SQL concatenation, and the shared Wi-Fi, and what should replace them?
Read the worked answer
Unsalted MD5 is a fast hash; attackers can use rainbow tables and brute force, and identical passwords produce identical hashes. Replace it with Argon2id, bcrypt, or scrypt using a unique salt and an appropriate work factor. SQL concatenation creates injection risk: an input such as ' OR '1'='1 can bypass login or expose data. Use parameterised queries or prepared statements, never string concatenation; validate input and give the application a least-privilege database account. The shared hostel Wi-Fi means traffic can be sniffed; enforce HTTPS/TLS, do not expose the MySQL port to the local network, use a firewall and network segmentation, and consider a VPN for remote access. Add rate limiting, account lockout, audit logs, and generic error messages that do not reveal whether the username or password was wrong. Backups should be encrypted.
3. The director wants students to see their own attendance. What access-control and privacy issues must be handled?
Read the worked answer
Authentication alone is not enough; authorization must check every query. Use role-based access: a student may read only rows where student_id matches the logged-in user; a tutor may read only assigned batches; an administrator may read all. Hiding links in the interface is not a control. Add server-side checks, audit logs of who viewed or edited attendance, and protection against insecure direct object references, such as changing a student_id in the URL. Under India's Digital Personal Data Protection Act, 2023, collect only necessary personal data, state the purpose, and secure it. Attendance data should not be exposed to other students, and exports should be restricted. These controls sit on top of the normalized database design; they are not replaced by it.