### Thread: [ERD] One-to-One VS. Mandatory One

1. #### [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. 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
3. 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)?
4. 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...
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...
6. 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?
7. No Profile Picture
Contributing User
Devshed Loyal (3000 - 3499 posts)

Join Date
Dec 2004
Posts
3,082
Rep Power
380
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
8. four tables linking together creating a cycle?
Yes, this is what I mean. Is that practice acceptable?
9. No Profile Picture
Contributing User
Devshed Loyal (3000 - 3499 posts)

Join Date
Dec 2004
Posts
3,082
Rep Power
380
post your tables and let us see, like mentioned before...
10. Here it is:
Code:
`http://i.imgur.com/jW6Zwdy.jpg`
Excluding the OFFICIAL RECEIPT entity, all other entities form a cycle.
11. No Profile Picture
Contributing User
Devshed Loyal (3000 - 3499 posts)

Join Date
Dec 2004
Posts
3,082
Rep Power
380
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.
12. 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.
13. No Profile Picture
Contributing User
Devshed Loyal (3000 - 3499 posts)

Join Date
Dec 2004
Posts
3,082
Rep Power
380
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.
14. 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