In your students table, slno is already AUTO_INCREMENT, so the intention is that slno should uniquely identify a student. So why is jntunno also part of the primary key? I think it should be
CREATE TABLE students
( slno int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY
, jntunno char(15) NOT NULL UNIQUE
, name char(50)
, UNIQUE KEY (jntunno)
);
By the way, slno and jntunno are programmer-unfriendly names. Please pick something else.
ALTERing the dailyatt table every time you add or remove a student is going to be a nightmare. Database schema changes should be extremely rare events, because they are risky, hard to manage, and hurt performance while they happen. Basically, it's wrong.
CREATE TABLE class_sessions
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, date date
, subject char(10)
, classesconducted int(2)
, UNIQUE KEY (date, subject) -- Maybe?
);
CREATE TABLE attendance
( class_session_id INT NOT NULL
, student_id int(5)
, PRIMARY KEY (class_session_id, student_id)
, FOREIGN KEY (class_session_id) REFERENCES class_sessions(id)
, FOREIGN KEY (student_id) REFERENCES students(slno)
);
If you want the data presented in one big table, with one column per student, you should leave it to your application-layer code or a stored procedurestored procedure to compose the query.
