Hi folks,

I'm doing a final year project for university which involves building a database to store student results, from which student transcripts can be automatically generated. Most of the marks for this project will be awarded for an accompanying written report, rather than the database itself. I'm on the System Design chapter now, so I'm talking about how I went about designing the database from an ER diagram, and considered important things like normalization.

At the moment, the academic administrator inputs the results into Excel spreadsheets, and copies the marks from various Excel files to produce a transcript. An annoying aspect of this project is that Excel must be retained for inputting data, which is then exported to my database.

I've attached a diagram showing the tables in my database. Basically, I want to write that I considered normalisation and referential integrity and all that lark. But I realise my design is not optimal. I know, for example, that the student table shouldn't have the overall marks for each year; these should be in a separate table. How should this work? Should it be like this:

OverallMark
studentNo (PK)
JFmark
SFmark
JSmark
SSmark

(JF stands for Junior Freshman etc)
Bear in mind that first year students will only have a JFmark value and second years will have a JFmark and SFmark etc etc etc. Or maybe someone has a much better suggestion? Also note that the overall marks absolutely must be stored in this database, because I'm not allowed run a query that will automatically calculate it from the studentresult table when needed.

Any tips appreciated,

sabatier

PS I will admit: I am not a database programmer; I'm a complete amateur at this. I just want my report to look like I know what I'm talking about and the database structure is actually good. The people who will be marking it are not experts either.

Is this just an assignment, or are people actually going to use this system?

Because your requirements are asinine. If this is what they are actually teaching you in your classes that is shameful.

Off the top of my head, you need to normalize the Marks into a separate table. What if someone repeats a year? Or graduates early? Aggregate functions such as summing or averaging the marks will also be easier to perform if your schema is normalized.

This is just an assignment. We were never taught about normalization. We were just given a brief overview of ER diagrams and referential integrity.
I have revised the database structure as you suggested. I'd be grateful if you could look at the attachment and tell me what you think.

Regards,

sabatier

Jesus dude - really? I hope this isn't an IT degree? Blindman ++.

Anyhoo - looking at your design - you have some unique constraints\ alternate keys that are not correct. It is not possible to work out any composite PKs or alternate keys either. Are all your PK columns underlined? Because if so there are errors - typically not enough columns for your PK.

Exam results - what's the deal with two year attributes?

It's a business degree with a bit of IT ;-)

In the ExamResults table, YearOfStudy refers to whether its Junior Freshman, Senior Freshman and so on. Year refers to the actual year the exam was taken e.g.2007. I know this isn't the optimal, but could you tell me how I could improve it, or where I could find a sample database for a student info system?

Best regards,

sabatier