I've been wondering how to come up with a relational database design for a student grading and result collation system. I am not particularly good at database design and I would need some expert help.

What I want to know in essence is how to design a relational database to handle student results and GPA calculation for a typical university (100L - 400L/500L). The data would be stored in such a way that a particular student would have his records stored from 100L - 400L during his stay in the university.

Please, my explanation may not be exhaustive enough but what I am essentially trying to know is how a database is designed to handle student results and GPA in a university.

Database design happens to be my bane in computer programming and I need serious help please!!! Thanks a bunch.

i would have put you tru but my time is up.
keep your head up. Till some other time

I would really appreciate any help from you, kolinz.

You have little information but I will attempt to help:

Tables and Data Points will include:

Student Table:
StudentID
FirstName
LastName
MidName
DateAdmitted
CourseLevelAtAdmission

Course Table
CourseId
StudentID
CourseLevelID
CourseName
CourseDescription
Score1 (e.g. Exam1 score)
Score2 (e.g. MidTerm score)
Score3 (e.g. Final Exam score)
Cummulative Score
FinalGrade


GPA Table
UID
StudentID
CourseLevelID
GPA


CourseLevel Table
CourseLevelID
CourseLevelName
CourseLevelDescription

This can be an approach to your DB design. Let me know if you need further help.

Samm, thanks a lot. I will go through your idea.

More design ideas are welcome by any other person willing to help,

Thanks a lot guys.

@sbucareer
Thanks. Hope he doesn't get overwhelmed. By the way, I have something I want to discuss with you based on one of your previous post. I will send you an email so as not to hijack this thread.

@davidt
This is really what you will be needing. Feel free to post any questions you may have.

@sbucareer
Thanks for your contribution. It is highly appreciated.

@samm
Thanks again.

I understand basic database design concepts (primary keys, foreign keys, etc.) and I'm sure that has helped me in understanding your ideas and putting them into my skull and they seem to be a really good way out of my problem. Thanks a lot. I'll work on this and see how it goes.

I respect,

I understand that in samm's solution, there are three columns in the course table that are redundant, score1, score2, score3 and this is probably the reason sbucareer normalised the the course table and used a foreign key instead. The problem is that I do not understand how the normalisation and consequent usage of a foreign key would handle the three required scores e.g. midsemester, assignments and final exam as indicated in samm's solution. I understand it is good practice to avoid repitition of columns but how would the normalised version actually work in handling the three scores.

Could anyone please explain this to me? Thanks a bunch.

I read somewhere that the rules of normalisation are not to be followed in absolutely all cases. Is this one of those cases? I really don't know.

davidt:

I read somewhere that the rules of normalisation are not to be followed in absolutely all cases. Is this one of those cases? I really don't know.

Generally, you should always normalize.
Then you can denormalize later for efficiency. i.e Not normalizing is different from denormalization ,

Look at the score table for instance ,
Depending on your requirements you can further break it down into say, Continuous assessments (if thats required in your project)
As it is the single score column potentially contains all that info.

(p.s @sbucareer and @samm did some justice to the exercise)

So in summary go ahead and normalize(always). When your application has been deployed and maybe the users need a dataware house for say, reporting purposes, then they can demormalize for perfromance.

Thanks guys.