Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2013
    Posts
    8

    Question Unanswered: Access and Normalization

    I have a question on normalization using Access 2010. I have StudentID, LastName, FirstName in one table called tblStudentID. In the other table, I have ClassID, ClassGrade, and StudentID. One student can be enrolled in multiple classID. ClassID and ClassGrade are not unique fields. How best can I normalize using this data that I have?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    remover duplication of data
    read up on normalistion till you understand what its about, what its trying to achieve, why its usefull

    The Relational Data Model, Normalisation and effective Database Design
    Fundamentals of Relational Database Design -- r937.com
    are good overviews
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    8

    Post Access and Normalization

    But I have read and I do not see what the problem is. Here is my problem. When I looked at the tables I separated, ClassID which are the classes like Eng Math etc can be taken multiple times by the same student ID because they are different classes. But the class ID are repeating so how do I resolve this
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what do you think is the problem with your current design of studentgrades?
    what other tables are there in your desing
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2013
    Posts
    8

    Access and Normalization

    I have no other data or tables other than that
    I tried to assign the primary key to one table StudentID - this is unique
    and the other table I was unable to assign as there is no unique table - so not sure what to do now

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would have expected a table for classes, that woiudl containanythign releavtn to the class (eg the class name, which department 'owned' / provided the class and so on).

    however in a table for student grades what woudl you neede to uniquely identify a students grades

    well, you'd need
    the student
    the class
    ...and
    well it depends, can a student have more tan one grade for a specific class (ie how would you expect to model resits (whether that be just an element,. the exam the coursework or both)

    what you have in studentgrades is effectively an intersection table, its a practical way of modelling a many to many realtionship. it models the intersection between students and classes and contains data relevant to that intersection. in any relational table the primary key is the bit of information that uniquely identifies each and every single row. udually you want to use a natural key (ie elements of data stream which are unique at the row level. soemtimes its not easy to identify such bits of data, sometimes there may be multiple bits of data that can uniquely identify a row, soemtimes what comprises the uniqueness in the data may change making it not the best for use as a primary key. these are so called natural keys, there is always the option of using a surrogate key, something the db makes up to create a unique stream of data. surrogate keys are often an attractive soltuion to a problem (effectively an order number, GRN, receipt are surrogate keys but have been around for a long long time before computers)
    there is nothing that says a primary key must be made of a single column in the table.

    is it 'right'?
    define 'right'...
    well its hard to say because we don't know your assignment we don't know how close this is to the real world
    is it workable, well yes as it allows for the system to assign grades for students in their classes.

    in this case, as its an intersection table then the natural key is the student and class id's, they uniquely identify a row. using this as the PK works as it also enforces a single grade per student per class. but it fails if you have to take into account resits as in the real world its possible to have multiple marks for a specific intersection of student and class

    so bone up on primary keys, natural keys, surrogate keys....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2013
    Posts
    8

    Post Access and Normalization

    Hi again

    There is a ClassID that specifies the different classes a student can take. In this perfect world, no student is going to repeat. The one table called StudentInfo has 3 fields - StudentID which can be the PK and then LastName and First Name. No problem in this table.

    The other table I created is StudentGrades. 3 fields - ClassID with classes like MATH101, MATH102, ENGR101 etc and a grade for each class. there can only be one grade for each class. and the other field is like the one above StudentID.

    The problem I faced in the 2nd table StudentGrades is ClassID can be repeated as two different students can take the same class, also Student ID can be repeated as one student can be taking 3 classes. So what become the PK in this table - when I tried to enforce referential integrity it gave me error messages.

    So now what. Please help, thanks heaps! Shirley

  8. #8
    Join Date
    Oct 2013
    Posts
    8

    Help with Access - Normalization

    Hi anyone, please help I posted yesterday and that does not work, I am quite new at all this and really could use some help
    thanks
    Shirley

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem is that you haven't understood normalisation
    I doubt you have followed up the idea of an intersection table

    the problem with these assignment questions is two fold
    one they don't represent the real world
    you are not going to get 'gimme' answers on this forum. if yo9u have a specific problem yes lots of people will help.


    there is nothing wrong with your proposed studentgrades tables as their is no repeating data the way you originally laid it out. according to your assignment ruloes there can only ever be one grade per student per course.

    go back to the references in post #2 and understand what is meant by repeating data
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2013
    Posts
    8

    Cool Access and Normalization

    Thanks heaps. I understood normalization for the most part and read the links you sent. Also I never heard of intersection - I will read on that. This DB is a perfect world - yes I agree. I was up till 2 am working on it. I am quite satisfied at this stage. Thanks for the feedback and suggestions, it helped me tremendously.

    Regards
    Shirley

Posting Permissions

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