database design issue

Question

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?

Answer 1

Get 4 pieces of paper.

On the first piece, create 2 columns across the page, StudentID, StudentName and write down 1,2,3 for the StudentIDs under the StudentID column and make up some cool names like 'Dr Cheat' under the StudentName column.

On the second piece of paper, create 3 columns called StudentID, ClassID, Score and enter 1,2,3 for the StudentID and 1,1,1 for the ClassID then make up some scores - like 100%, 99%, 0%.

On the 3rd piece of paper create 2 columns - ClassID, ClassName and enter the values 1, 'How To Win by Cheating'

Now, to understand relational databases, all you need to do is on the 4th piece of paper enter the columns StudentID, StudentName, ClassID, ClassName, Score and then populate the values from the other 3 pieces of paper.

You can see you have 3 students, and some scores, and some class names for those scores - the exercise of joining them together into that 4th piece of paper is similar to writing a SQL query.

Whenever you find yourself holding 2 pieces of paper together and looking back and forward between the 2 pieces to find matching values - the column you are matching on is your JOIN column.

That's relational database design.