Results 1 to 13 of 13
  1. #1
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7

    relationship and 3rd normalised form help needed

    Hi guys I need help for my exam or project here are the requirments
    *Table for students
    *table for subjects ( Subject_ID and Subject_Name)


    *Subject costs (not sure if I put this in the same table as subjects)
    *Students need to at least do 3 subjects and max of 4 subjects
    *must be in 3rd normalised form


    any help will be appreciated thanks! ,the 3rd normalised form is really hard to get around to

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    What have you done so far?
    What specifically are you having issues with?
    Normalization

    Tutorial on Design

  3. #3
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Pretty much only the design part

    for my students table entities I have:
    stuID
    stuFirstName
    stuLastName
    stuAddress
    stuPostCode
    stuEmail
    stuPhone

    and for the Subjects Table I have:
    subID
    subName

    I need help to enrol the students to minimum of 3 subjects and maximum of 4 and I don't know how to do it using 3rd Normalised for my idea before was have to add 4 entities (1st,2nd,3rd,4th(not required) subjects) on the student table but I can't

    Thanks for the reply

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    What level course is this?
    Did you look at the links provided in previous post?

    You may get some ideas from this link on Cardinality

  5. #5
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7
    yeh helped out a lot man I'm starting to get it, year 12 project

    thanks!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Keep at it.

    Your current tables suggest Many to Many--- Many Students take Many Subjects.
    Here's an article that should help.
    Good luck.

  7. #7
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Quote Originally Posted by orange View Post
    Keep at it.

    Your current tables suggest Many to Many--- Many Students take Many Subjects.
    Here's an article that should help.
    Good luck.
    Thanks a lot I did I was wondering if you could help me out with last bit cause the constraints are hard

    - query that lists Subject name in ascending order and then Last name and Given name (Every time I do it the students that are not doing the subject are being listed)
    - The max number of students doing a subject is 25
    - Students must be doing at least 3 subjects and maximum of 4 subjects


    My tables are
    - List (FK= Subject Code, Student ID)
    - Subject PK = Subject Code
    - Student PK = Student ID

    using many to many relationship

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715

  9. #9
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Quote Originally Posted by orange View Post
    What have you done so far?
    Can you post a jpg?

    This material should be helpful re ER Diagram and Cardinality
    Good luck.
    http://imgur.com/8ebou9S
    I only have the design part sorry

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    I was trying to use a tool to simulate your set up generally. But I'm not familiar with Toad Data Modeler and it is not letting put in Min Cardinality.

    Here is the best I can do with the tool in the graphic.
    However, I think Student 3:4 min 3 max 4, and Subject would be 1:25 min 1 student max 25

    Click image for larger version. 

Name:	StudentAndSubjects.jpg 
Views:	11 
Size:	18.2 KB 
ID:	27857

    Update: I found this via Google that may help with concept...??
    Q: I can see how maximum cardinality is used when creating relationships between data tables. However, I don't see how minimal cardinality applies to database design. What am I missing?

    A: You are correct in noticing that maximum cardinality is a more important characteristic of a relationship than minimum cardinality is. All minimum cardinality tells you is the minimum allowed number of rows a table must have in order for the relationship to be meaningful. For example, a basketball TEAM must have at least five PLAYERS, or it is not a basketball team. Thus the minimum cardinality on the PLAYER side is five and the minimum cardinality on the TEAM side is one.

    One can argue that a person cannot be a player unless he/she is on a team, and thus the minimum cardinality of TEAM is mandatory. Similarly an organization cannot be a basketball team unless it has at least five players. The minimum cardinality of PLAYERS is mandatory also. One could argue in the opposite direction too. When a player quits a team, does it cease to be a team until a replacement is recruited? It cannot engage in any games, but does it cease to be a team? This is an example of the fact that each individual situation must be evaluated on its own terms. What is truth in THIS particular instance? The next time a similar situation arises, the decision might be different, due to different circumstances.

  11. #11
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Quote Originally Posted by orange View Post
    I was trying to use a tool to simulate your set up generally. But I'm not familiar with Toad Data Modeler and it is not letting put in Min Cardinality.

    Here is the best I can do with the tool in the graphic.
    However, I think Student 3:4 min 3 max 4, and Subject would be 1:25 min 1 student max 25

    Click image for larger version. 

Name:	StudentAndSubjects.jpg 
Views:	11 
Size:	18.2 KB 
ID:	27857

    Update: I found this via Google that may help with concept...??
    Q: I can see how maximum cardinality is used when creating relationships between data tables. However, I don't see how minimal cardinality applies to database design. What am I missing?

    A: You are correct in noticing that maximum cardinality is a more important characteristic of a relationship than minimum cardinality is. All minimum cardinality tells you is the minimum allowed number of rows a table must have in order for the relationship to be meaningful. For example, a basketball TEAM must have at least five PLAYERS, or it is not a basketball team. Thus the minimum cardinality on the PLAYER side is five and the minimum cardinality on the TEAM side is one.

    One can argue that a person cannot be a player unless he/she is on a team, and thus the minimum cardinality of TEAM is mandatory. Similarly an organization cannot be a basketball team unless it has at least five players. The minimum cardinality of PLAYERS is mandatory also. One could argue in the opposite direction too. When a player quits a team, does it cease to be a team until a replacement is recruited? It cannot engage in any games, but does it cease to be a team? This is an example of the fact that each individual situation must be evaluated on its own terms. What is truth in THIS particular instance? The next time a similar situation arises, the decision might be different, due to different circumstances.
    Thanks a lot I'n kinda new to access where do I enter this? Validation rule, input mask?

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    I'm not aware of any place you can add specific cardinality range in Access.
    I think you could have to do it with some code.

  13. #13
    PsiClown is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Quote Originally Posted by orange View Post
    I'm not aware of any place you can add specific cardinality range in Access.
    I think you could have to do it with some code.
    oh ok thanks a lot for your help I really appreciate it

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

Similar Threads

  1. Is a many to many relationship needed?
    By jsaddiction in forum Access
    Replies: 9
    Last Post: 01-26-2015, 04:29 PM
  2. Help needed : Library catalogue in access format is needed
    By dealers in forum Sample Databases
    Replies: 3
    Last Post: 01-16-2014, 02:03 PM
  3. Newb Relationship help needed
    By mpreston14 in forum Access
    Replies: 10
    Last Post: 04-22-2013, 07:12 AM
  4. Relationship Help Needed
    By justgeig in forum Database Design
    Replies: 21
    Last Post: 04-12-2012, 06:19 AM
  5. Sub form help needed
    By wildthingcg in forum Access
    Replies: 10
    Last Post: 08-30-2011, 11:49 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