#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5

    [ERD] One-to-One VS. Mandatory One


    Hi guys,

    Code:
    http://wc1.smartdraw.com/resources/tutorials/images/erdinfoeng.gif
    One-to-one - at most one
    Mandatory One - one and only one

    However guys, I can't seem to tell when to use them and their use seem to be overlapping for me.

    If an instance of an entity1 can have "at most" one instance related to entity2, then isn't it a "mandatory one" relationship?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by kathyrollo
    If an instance of an entity1 can have "at most" one instance related to entity2, then isn't it a "mandatory one" relationship?
    no, it could be optional

    a guy can have at most one wife (more than one is against the law), but a guy doesn't has to have a wife (i.e. it's optional)

    i should also like to point out that the diagram you linked to has neither an "at most one" relationship, nor a "one and only one mandatory" relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    I was just referring to the notations.

    Okay so if one-to-one is "at most" one meaning it's optional to have a wife or not, then how is "zero or one" relationship different (refer to notations)?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by kathyrollo
    Okay so if one-to-one is "at most" one meaning it's optional to have a wife or not, then how is "zero or one" relationship different (refer to notations)?
    um...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    There's a different notation for one-to-one and zero-or-one you see, why are there different notations if they mean the same thing? I've also seen separate notations for both in other books so that's why I'm asking...
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    Hi guys,

    Follow-up questions:
    1) Should a relationship between entities always have a cardinality constraint?
    2) Would a "cycle" in an ERD result to bad design?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    Originally Posted by kathyrollo
    There's a different notation for one-to-one and zero-or-one you see, why are there different notations if they mean the same thing? I've also seen separate notations for both in other books so that's why I'm asking...
    clearly they are different..

    1-1 and 0-1.. in one you can have only ONE relationship and in the other you can either have none or one.. i am confused with your q?

    Originally Posted by kathyrollo
    Follow-up questions:
    1) Should a relationship between entities always have a cardinality constraint?
    2) Would a "cycle" in an ERD result to bad design?
    1. this is how you design your database & cardinality is "implied" even if you dont depict it i.e. for 1 to M, you will design your tables so that the "id" in your "1" table is also present in your "m" table.

    2. Cycle? depends on what you mean again? you mean for example, four tables linking together creating a cycle? not sure about that depends i think, if it looks "ok" in terms of your design then maybe it is ok but maybe you would want to check with someone else
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    four tables linking together creating a cycle?
    Yes, this is what I mean. Is that practice acceptable?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    post your tables and let us see, like mentioned before...
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    Here it is:
    Code:
    http://i.imgur.com/jW6Zwdy.jpg
    Excluding the OFFICIAL RECEIPT entity, all other entities form a cycle.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    There are things wrong with it (as far as I know) so this will be a good revision if someone else comes and corrects me but:

    1. course and curriculum have 1 to M relation ship so the ID of 1 table should go in the MANY table you have this teh other way around

    2. you probably dont need to link subjects enlisted and schedule

    3. you are linking curriculum & subject i.e. M-M but you dont have any FK of one table in the other? More importantly it is ideal to break down the M-M with 1-M and M-1 i.e. have a table in the middle.

    4. also the 25 limit for email is very naive, most emails are way longer than this, increase it to at least 70.

    5. why are you using CHARS? why not varchar (if allowed by your db type)

    6. i am a bit confused about curriculum and form.. isnt that the same thing?

    7,. there is a confusion with regards to forms and curriculum.. because student is implicitly joined to BOTH.. the confusion is that you have curriculm linked to a subject and then form is linked with subject_enlisted, do you not see a problem with this? (maybe i am wrong but it means the students are enrolled on all subjects from that curriculum automatically thus negating the "subject enlisted" which i presume means they have a choice..
    Last edited by paulh1983; July 2nd, 2013 at 08:12 AM.
  22. #12
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    Thank you very much for your response.

    1) Noted, I will add COURSE_CODE as part of the PF in the CURRICULUM entity. Just to clarify, does this mean for any 1:M relationship, the PK of "1 table" must always be in the "many table"?

    2) If I don't link the SUBJECTS ENLISTED and SCHEDULE entities, how could it be known which section a particular student enlisted in (since a subject can have multiple sections/schedules)? This has been causing the confusion for me and resulted to the cycle.

    3) The { SUBJECT_LIST } is a multivalued attribute with several subject codes in it (e.g. INTCALC, TECWRIT, OBPROG, etc.). I designated { SUBJECT_LIST } as an FK, pertaining to the subject codes it contains, which makes SUBJECT_CODE the PK of the SUBJECT entity.

    3.a) Is it acceptable to designate a multivalued attribute as FK?
    3.b) Would you kindly illustrate how a "table in the middle" would look like?

    4) Noted.

    5) Noted.

    6) In our school's enlistment/enrollment process, there is an Enlistment Form (EF), Pre-Assessment Form (PAF), and Registration Form (RF).

    EF - lists the subjects (and their respective sections/schedules) the student enlisted in
    PAF - shows the cash amount and installment amount to be paid by the student
    RF - lists all the subjects the student has enrolled in

    The FORM_STATUS has the following values:
    1 - Approved (EF status)
    2 - Assessed (PAF status)
    3 - Paid (RF status)

    7) You are correct, the students have a choice, or more appropriately, must only enlist in the offered subjects for the term. How can this "implicit" issue be resolved?
    Last edited by kathyrollo; July 2nd, 2013 at 01:08 PM.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    1. if it is not, then how will you link the two tables together?
    3a). well ideally what you could do is create a primary_key (auto increment number) for that table and then use that as FK. Having said that i dont think there is a problem with multi-value FK just remember to use both FK in your query..
    3b) orders (order_id, date...)
    order_items (order_id, product_id, qty, ...)
    products (product_id, name,...)
    so order_items is the middle table

    7. i am still not 100% sure on your system/uni requirements or how things are done so i cant give you any advice.. if subjects change per term, then presumable you need a table called term, linked to form..
    Last edited by paulh1983; July 3rd, 2013 at 11:22 AM.
  26. #14
  27. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Manila, Philippines
    Posts
    64
    Rep Power
    5
    1) From my ERD, I established the link by making { CURRICULUM CODE } an FK in the COURSE entity, which in turn is part of the PF in the CURRICULUM entity. Do you think it is still necessary to add COURSE_CODE as part of the PF as well?

    2) (pending)

    3.a) We will be having our first hands-on for MySQL next week, so I still yet to learn the auto-increment. Our prof decided it was better to learn ER-Diagramming first, but thank you for confirming a multivalued attribute can be an FK.

    3.b) Paulh1983, is this okay?
    Code:
    CURRICULUM
    [PF] curriculum_code
    [PF] year_level
    [PF] term
    { subject_list }
    
    CURRICULUM SUBJECTS
    [FK/PF] curriculum_code
    [FK/PF] year_level
    [FK/PF] term
    [FK/PF] subject_code
    
    SUBJECT
    [PK] subject_code
    subject_desc
    no_of_units
    prerequisite
    Sources:
    Code:
    http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi50.htm
    https://support.ca.com/cadocs/0/CA%20ERwin%20Data%20Modeler%20r8-ENU/Bookshelf_Files/HTML/Methods/index.htm?toc.htm?254546.html
    6) (pending)

    7) I think I sort of understand what you mean here, I will see what I can do.
    Last edited by kathyrollo; July 3rd, 2013 at 10:54 PM.

IMN logo majestic logo threadwatch logo seochat tools logo