Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Student Database Design

    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.
    Attached Files Attached Files
    Last edited by sabatier; 02-27-08 at 08:22.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2007
    Posts
    6
    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
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sabatier
    I'm doing a final year project for university
    Quote Originally Posted by sabatier
    This is just an assignment. We were never taught about normalization. We were just given a brief overview of ER diagrams and referential integrity.
    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?

  5. #5
    Join Date
    Nov 2007
    Posts
    6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •