Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
18  
Smells like homework? There are a few thoughts regarding homework questions on meta, the long and the short of it being that you need to demonstrate some effort to warrant a response. Your question as it stands shows none. –  Mark Storey-Smith Jan 9 '12 at 23:29
2  
No offence intended but if you are truly qualified for this task you should be able to get most of the way to the final answer. If this is a genuine requirement, isn't there some free or commercially available software you can use ("don't build, buy")? –  onedaywhen Jan 10 '12 at 7:13
3  
Google "school management software". If no one else can help, and if you can find me, maybe you can hire me to procure software for you. But how many middlemen does you client want? –  onedaywhen Jan 11 '12 at 15:33
1  
Take a look at Linking or Junction tables. You will probably also want to understand Foreign key relations. The following may give you an overview Defining relationships between database tables. –  user4742 Jan 20 '12 at 21:51
2  
I really don't want to give you an out-and-out answer, as you really need to shome some effort towards it. Do a web search for "many-to-one" table relationships, foreign keys, and hierarchical structures. The wikipedia article on entity-relationship model should get you to the end of this task with just a little effort. –  Matt Spinks Mar 28 '12 at 19:36
show 5 more comments

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.