Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2007
    Posts
    24

    A question about this ternary relationship

    Hi,
    I'm not an expert, i'm just studing er modeling.
    On internet I found this example of ternary relationship
    Click image for larger version. 

Name:	graphics10.png 
Views:	15 
Size:	21.7 KB 
ID:	16667

    I think that this model doesn't allow a supplier to supply the same item for the same project in different dates.
    So i came to this modified version.
    What do you think?
    Click image for larger version. 

Name:	ER_modified.PNG 
Views:	12 
Size:	30.5 KB 
ID:	16668

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    In the initial diagram, the date is an attribute of Supply but not a member of Supply's NK (Natural Key). There can't be a date conflict as long as it is only an attribute, so I think that the initial diagram is correct as published.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2007
    Posts
    24
    hi, thanks for the reply.
    I'm confused.
    In the initial diagram the key of Supply is the combination of foreign key SID, Code and PartID (so the tern must be unique) and therefore cannot exist another tuple with the same key and different date.
    Last edited by Liuk; 12-16-15 at 06:16.

  4. #4
    Join Date
    Jun 2011
    Posts
    31

    Thumbs up

    The first diagram is correct.

    You have three entities: Supplier, Project and Part and Supply is making the relationship. Supply has the two attributes, data and Quantity. so this model this model allow a supplier to supply the same item for the same project in different dates because date is the attribute of supply not supplier.

  5. #5
    Join Date
    Feb 2007
    Posts
    24
    But the Supply relationship, as i can read from the relational schema below the diagram, has the tern SID, Code and PartID as its own primary key, and the primary key must be unique. what you say it's true if the SUPPLY relationship had no such key.

  6. #6
    Join Date
    Jun 2011
    Posts
    31
    Yes, it is possible.
    These three keys are worked as the foreign key for supply.

  7. #7
    Join Date
    Feb 2007
    Posts
    24
    I think the point here is the interpretation of the primary key for the junction table SUPPLY.

    Here are some examples of what i mean:

    Click image for larger version. 

Name:	DataRepresentation_tomjewett.PNG 
Views:	3 
Size:	124.7 KB 
ID:	16673

    Click image for larger version. 

Name:	StoringAssociation.PNG 
Views:	3 
Size:	324.1 KB 
ID:	16674


    From the MIT site, a solution to an exercise :

    Click image for larger version. 

Name:	MITMovie.PNG 
Views:	4 
Size:	157.2 KB 
ID:	16675

    Note the CharacterName as primary key to model the fact that “A person can play multiple characters in the same film”
    The same solution from the Cornell Tech site:

    Click image for larger version. 

Name:	ActorRole_CornellTech.png 
Views:	3 
Size:	25.0 KB 
ID:	16676

    Role is defined as primary because “ the same actor might have multiple roles in the same movie”.
    And last, from the Yukon College lectures :

    Click image for larger version. 

Name:	Yukon.PNG 
Views:	3 
Size:	93.3 KB 
ID:	16677

    And the solution is similar to the previous ones.

    If the foreign keys form the junction table's primary key, then this one must be unique. If we don't define a primary key for the junction table, then there is no conflict.

  8. #8
    Join Date
    Dec 2015
    Posts
    5
    On looking at the original ERD that you posted first (not your modified one), the foreign keys inside the SUPPLY table making up the primary key indicate that you can only have one of them. I am presuming that the date is not part of the key but it is not entirely clear. I would usually not use a date as a part of a key but it is personal preference. If the date is not being used as a key then that means is that the date is irrelevant.

    The date can be used as a primary key sometimes (if it is a one time event or if (somewhat dubiously) you use the whole time stamp right to the second) but it is not being used as a key here by the looks of it.

    If you try to do the same combination on a different date it would fail... if the date is not part of the key. The key implies that there is only one opportunity to have this exact combination of Part, Project and Supplier. If you wanted to have multiple entries then either use the timestamp or create an autonumber key for SUPPLY.

    I do not like the diagram because it is unclear. It looks like a flow diagram has been used where ERD notation should be used. I always recommend the use of some kind of official notation that clearly indicates what the keys are and what the relationship is.

    EDIT: Actually... if PartID is for the actual individual part itself then that would mean that all parts themselves are unique! I do not think this is the case going by convention though. Would someone give an individual id to each and every nail?? I highly doubt it. The id would imply the part type and not the actual part itself (unless it is a completely unique item each time).
    Last edited by Noodle Poodle; 12-18-15 at 14:43.

  9. #9
    Join Date
    Feb 2007
    Posts
    24
    Thank for replay.
    I agree with your remarks (? I'm not so good in english :-) ) on Date and PartID.
    That first ERD is just an example of what a ternary relationship is, nothing more.

    Just because date seem not to be part of the key of SUPPLY relationship (table) it's not possible to have more than one tuple with the same SID, Code and PartID. So i was wondering if my modified version solve that problem, or if there another answer to that question : let a supplier to provide the same part to the same project more then once, keeping track of when this occour.

    Let me wish you all a merry chirstmas. Have a nice holiday.

  10. #10
    Join Date
    Dec 2015
    Posts
    5
    Like I said it it not too clear because of the non-standard diagram notation. In your modified version there seems to be too many parts now. Surely you could just add a SupplyID (using timestamp as a PK would be a bad idea (you will see in a Google search)) but leave out all the other bits in between the entities.

    The "Supply" is the link entity - you seem to adding additional ones in your modified version.

    Within an application you could simply list them in order of date for a single day. In case of backdating it is probably inadvisable to use an autonumber as an ordering column.

Posting Permissions

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