This is my first time on this beautiful content rich site. I have a sql server 2008 database, but am not too experienced with good relational database design.
My project is this: I have a school that has Students. The students offer subjects which are graded on a termly basis in a session. There is a session which means 1 year and in this session, there are 3 terms namely 1st,2nd and 3rd terms. Each student has a results/continuous assessment that are reviewed on a termly basis in one session (3 terms) in a session. How do I design m database to encompass all these details, so that I can check results or performance per term, in the 3 terms and in a session or more than one session.
STUDENT TABLE STUID|STUNAME|STUADDRESS CONTINOUS ASSESSMENT TABLE CAID| FIRST_CA(20) |SECOND_CA(20)| EXAM SCORE(60)|TOTAL SCORE|SUBJECTID|RANK|YEARID SUBJECT TABLE SUBJECTID |SUBJECT_NAME |SUBJECT DESCRIPTION YEAR/SESSION/TERM TABLE ID|YEAR|TERM|STATUS|FROM|TO CLASS TABLE CLASSID | CLASS_NAME | CLASS_TEACHER DEMO QUERY SELECT RESULT FROM CONTINUOUS ASSESSMENT WHERE STUDENT ID = ID AND CLASS = CLASS5 AND TERM = 1ST TERM AND YEAR = 2012 OR SELECT RESULT FROM CONTINUOUS ASSESSMENT WHERE STUDENT ID = ID AND CLASS = CLASS5 AND TERM = 1ST TERM, 2ND TERM ,3RD TERM AND YEAR = 2012
How do I link all these tables in order to execute this query successfully?