I am trying to create attendance entry system, where the system will perform the following tasks:
1. Individual or whole class %age attendance for all subjects of a particular semesters, particular departments some specified class section or individual subjects of a particular semesters, particular departments some specified class section.
2. %age analysis of whole class or particular student for all subjects of a particular semesters, particular departments some specified class section or individual subjects of a particular semesters, particular departments some specified class section for a supplied range of attendance %age. For Example: I want to know a what is %age of class attended by a student A of Computer Science department, 3rd Semester, Sections A who are falling in the range of 70-80%
3. I also want to perform the above two analysis against a particular faculty or all the faculties who are taking class to which student A belong to.
4. Number of classes taken by individual faculty of each departments particular semesters some section.
Now I am listing all tables with attribute names.
a. Departments(Department_ID, Department_Name) sample values are 1, 'CSE'; 2, 'ECE' etc...
b. Subjects(Subject_ID, Subject_Title, Subject_Code, Subject_Credit) sample values are 1, 'Java Programming', 'CS 506', 4; 2,'Database Management System', 'CS 503', 4
c. Periods(Period_ID, Period_Name), sample values are 1, 'First Period'; 2, 'Second Period'..... till 'Tenth Period'
d. Sections(Section_ID, Section_Name), sample values are 1, 'Section A'; 2. 'Section B'; 3. 'Section C'
e. Semesters(Semester_ID, Semester_Name, Semester_Type), sample values are 1, 8, 'even'; 2,7,'odd', 3, 6, 'even'.....so on till 8,1,'odd'
f. Students(Student_ID, Student_Name, Semester_ID, Department_ID, Class_Roll, Section_ID, Group_ID)
g. Lab_Group(Group_ID, Group_Name), sample values are 1,'Group A'; 2, 'Group B'
h. Attendance(Attendance_ID, Attendance_Status), sample values are 1, 'Present'; 2,'Absent'; 3,'Leave'
Now the problem I am facing is what will be table structure and how to store the attendance of several students in a separate table to perform the above mentioned tasks.
Please Advice this as soon as possible.
How are you doing in CS506? Are you getting good grades other than this assignment? Perhaps you need to create a sample database, create tables and indexes that you already listed above, figure out the parent child relationships between each of the above tables and create foreign keys for those tables, decide if you need any indexes remembering to go for general indexes at first and get more specific if there is a need and then and only then should "you" write some queries to get out the data that you are being asked to get. In fact turn those queries into views.