Student Database Design
Posted by: x y
Date: February 27, 2008 06:41AM

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 have 4 tables in my database:

student
student_module
module
studentresult

'studentresult' stores the marks from individual exams, rather than an overall mark for the year. The 'student' table contains the overall mark:
studentNo
studentName
JFmark
SFmark
JSmark
SSmark

(JF stands for Junior Freshman, and so on. It's the British system. There's no GPA. Overall mark can be First Class Honours, Second Class etc)
Basically, I want to write in my report 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 (table name)

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.



Edited 2 time(s). Last edit at 02/27/2008 06:49AM by x y.

Options: ReplyQuote


Subject
Written By
Posted
Student Database Design
x y
February 27, 2008 06:41AM
February 29, 2008 12:38PM
July 10, 2008 08:31PM
February 22, 2009 09:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.