Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    20

    Thumbs up Normalization question

    I have created a table after normalization which has the foll. attributes:-
    +---------+-------+-------+-----+
    |Test/Exam|Reg.No.|Subject|Marks|
    +---------+-------+-------+-----+
    | t1 | 1 | s1 | 82 |
    | t1 | 1 | s2 | 82 |
    | t1 | 2 | s1 | 82 |
    | t1 | 2 | s2 | 82 |
    | t2 | 1 | s1 | 82 |
    | t2 | 1 | s2 | 82 |
    | t2 | 2 | s1 | 82 |
    | t2 | 2 | s2 | 82 |
    | t3 | 1 | s1 | 82 |
    | t3 | 1 | s2 | 82 |
    | t3 | 2 | s1 | 82 |
    | t3 | 2 | s2 | 82 |
    +---------+-------+-------+-----+

    Do I have this table with first 3 attributes as a multiattribute primary key? Maybe this is right but looks funny to me due to lot of redundant entries as it's the first time I'm designing a DB.
    For each batch of students, subjects and exams are known. So as an alternative do I create a new table each time for every new batch as shown below or maybe otherwise?
    +-------+----+----+----+----+----+----+
    |Reg.No.|t1s1|t1s2|t2s1|t2s2|t3s1|t3s2|
    +-------+----+----+----+----+----+----+
    | 1 | 82 | 82 | 82 | 82 | 82 | 82 |
    | 2 | 82 | 82 | 82 | 82 | 82 | 82 |
    +-------+----+----+----+----+----+----+

    This is also unusually awkward. So what do I do?
    Please do help me out.
    Last edited by trss; 09-26-06 at 16:55.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Your composite unique key (and most likely primary) key on your table would be composed of Test/Exam|Reg.No.|Subject. This is pretty standard.

    You do NOT create new tables for "batch" of students. I don't even know what you mean by that, but if you intend to combine students into groups then you would need a table to do that.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    A few wee questions:
    Can a single exam really be in many subjects? And if so - can each student achieve different marks for the different subjects in each exam? (your sample data doesn't make this clear).
    Could you give an example of where you have identified "redundant entries" and why you think they are redundant?

    And yes - defo don't even think about implementing your second idea
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2006
    Posts
    20
    I thank blindman and pootle flump for their kind help. Sorry for not mentioning that batch refers to the the actual period of study(For eg:2004-2008 or 2010-2012).

    The test/exam I refer to is not that which is held on a particular date. It is actually a series of exams like Cycle Test I or Model Exam which is to be held for each subject. Anyway, the actual problem is that, I forgot to consider the dependency Class -> Test/Exam,Subject. Forget it if you're not able to get it. Anyway, I've got new problems and have to start working on it again.

    Before I tell you the problem, I'd like to know where to post my questions and who moved my message here(Is it automated?!!). If I'm creating problems posting my problems in the wrong place, please do move it this one last time alone after which I will learn more about the layout of this community!

    Also, how do I show tables(The one I had shown was terribly aligned due to automatic annihilation of a group of spaces to a single space when it gets posted!)

    Now to my next problem:-
    I've got this dependency, Department -> HOD(Head-Of-Department). But what do I do in case of change in the HOD of a department? Should I consider this dependency at all? Or is it better that the old HOD is overwritten by the new HOD? Same goes for class teacher. But this method reduces the usability of the software since we can't view which HOD a particular batch was under.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by trss
    Before I tell you the problem, I'd like to know where to post my questions and who moved my message here(Is it automated?!!). If I'm creating problems posting my problems in the wrong place, please do move it this one last time alone after which I will learn more about the layout of this community!
    Normalisation Qs are best here (DB Concepts and Design). One of the Mods will have moved it.
    Quote Originally Posted by trss
    Also, how do I show tables(The one I had shown was terribly aligned due to automatic annihilation of a group of spaces to a single space when it gets posted!)
    Use the code tags as spaces are retained.
    Quote Originally Posted by trss
    Now to my next problem:-
    I've got this dependency, Department -> HOD(Head-Of-Department). But what do I do in case of change in the HOD of a department? Should I consider this dependency at all? Or is it better that the old HOD is overwritten by the new HOD? Same goes for class teacher. But this method reduces the usability of the software since we can't view which HOD a particular batch was under.
    I would start a new thread
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by trss
    I've got this dependency, Department -> HOD(Head-Of-Department). But what do I do in case of change in the HOD of a department? Should I consider this dependency at all? Or is it better that the old HOD is overwritten by the new HOD? Same goes for class teacher. But this method reduces the usability of the software since we can't view which HOD a particular batch was under.
    Sounds to me you have answered your own question - your answer is driven by business rules and it sounds like your business rules require a history.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    (In repsonse to your PM):
    My point was that whether or not you record this as part of your design is dictated by your needs. If you will need to know point in time HoD information then you need to record an HoD history. If not then you don't.

    Frankly - we would record this in our personnel systems (and query this if required) but an HoD takes ownership of everything within their department (and therefore the database) irrespective of whether or not it predates their appointment so I would want the change to propogate throughout the company database.

    Since this is homework I would avoid maintining a history - it only complicates matters and, as I said, may be considered outside the required scope of the project.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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