Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Newb stuck on Normalization

    Hi, I have to make a very simple database to record student details and the modules they take for a side line university module.
    I have read countless tutorials on Normalization but can't seem to get my head round it.
    I would really apprieciate a bit of help.
    The database basically records Students details, which modules they have chosen and which topics within each module they have chosen. There is a choice of 7 modules each containing 6 topics. A student can choose a total of 9 topics from any of the modules.
    This is how my normalization has progressed so far:

    Student ID*
    Last name - text
    First name - text
    E-mail - text
    Date profiled - date
    Date accepted - date
    Email sent requesting topics - date
    Email received - date
    Payment method - text
    Fee form sent - date
    Fee received - date
    Profile to faculty office - date
    On ISIS & User details sent - date
    UWE user ID - number
    UWE Password - text
    CPD or MA - text
    Module 1 ID -text
    Topic 1 - text
    Topic 2 - text
    Module 2 ID - text
    Topic 1 - text
    Module 3 ID - text
    Topic 1 - text
    Topic 2 - text
    Topic 3 - text
    Notes - text

    That's all the fields, I then tried to do 1NF and ended up with:

    tblStudent
    Student ID*
    Last name
    First name
    E-mail
    Date profiled
    Date accepted
    Email sent requesting topics
    Email received
    Payment method
    Fee form sent
    Fee received
    Profile to faculty office
    On ISIS & User details sent
    UWE user ID & Password
    CPD or MA
    Notes

    tblModule 1
    Student ID*
    Module 1 ID
    Topic 1
    Topic 2

    tblModule 2
    Student ID*
    Module 2 ID
    Topic 1
    Topic 2

    tblModule 3
    Student ID*
    Module 2 ID
    Topic 1
    Topic 2

    tblModule 1 topics
    Topic 1
    Topic 2
    Topic 3
    Topic 4
    Topic 5
    Topic 6
    Topic 7
    Topic 8
    Topic 9

    tblModule 2 topics
    Topic 1
    Topic 2
    Topic 3
    Topic 4
    Topic 5
    Topic 6
    Topic 7
    Topic 8
    Topic 9

    tblModule 3 topics
    Topic 1
    Topic 2
    Topic 3
    Topic 4
    Topic 5
    Topic 6
    Topic 7
    Topic 8
    Topic 9

    I'm pretty sure I have not grasped the concept of the Normalization proceedure!

    Any advice would be really appreciated!
    Regards,



    David

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109

    Two Cents

    Data normalization is not my strong point.

    Given that you repeat the Module and Topic fields, you may want to have a separate table for each. Use another table to associate the student with the module and/or topic.

  3. #3
    Join Date
    May 2008
    Location
    North Charleston, SC
    Posts
    20
    can a topic exist in multiple modules? for example:

    module1
    topic1
    topic2
    topic3

    module2
    topic4
    topic5
    topic6

    module3
    topic1
    topic4
    topic7

    depending on if the answer is yes or no, i would go two different ways when trying to normalize the data.

  4. #4
    protean_being is offline Novice
    Windows XP Access 2003
    Join Date
    May 2008
    Posts
    9
    1NF cannot have repeating groups. Topic is a repeating group (Topic1, Topic2, ect.).

    The following is 3NF:
    tblModules
    - Student ID *
    - Module ID
    - Topic ID
    - Topic

    It is 2NF because it has one primary key.
    It is 3NF because the non-key fields are not dependent on the other non-key fields.

    There is no need to have a seperate table for each Module. In fact by using this model you can add new modules at will. Use queries to view/edit specific modules.

  5. #5
    Join Date
    May 2008
    Location
    North Charleston, SC
    Posts
    20
    If a topic can be in multiple modules, then you need an additional table that contains moduleID and topicID to stay in normal form.

  6. #6
    protean_being is offline Novice
    Windows XP Access 2003
    Join Date
    May 2008
    Posts
    9
    Please explain why (which normalization form I have not met).

  7. #7
    Join Date
    May 2008
    Location
    North Charleston, SC
    Posts
    20
    I may be incorrect in my understanding of this statement, but 2NF says "Remove subsets of data that apply to multiple rows of a table and place them in separate tables." If topics to modules can be a many-to-many, then we would have an update anomaly if we tried to change the name of a given topic. I'm not an expert on DB design though, so take my advice with a grain of salt. Here is how I might set up the data structure.

    tblStudents
    - StudentID *
    - StudentFirstName
    - StudentLastName

    tblModules
    - ModuleID *
    - ModuleName

    tblTopics
    - TopicID *
    - TopicName

    tblCourses
    - CourseID *
    - ModuleID
    - TopicID

    tblStudentCourses
    - StudentCourseID *
    - StudentID

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table Normalization Help
    By newhelpplease in forum Database Design
    Replies: 1
    Last Post: 10-15-2007, 09:25 AM
  2. Normalization Assistance for Tables Please??
    By webmaniac in forum Database Design
    Replies: 10
    Last Post: 09-02-2006, 05:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums