Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Convert ternary relationship to binary relationships

    Hi, I'm trying to convert a ternary relationship between Crash, Vehicle and Person to binary relationships.

    The ternary relationship is as follows:

    1 Person in 1 Crash can be only in one vehicle.
    1 Person in 1 Vehicle can be only in many crashes.
    1 Vehicle in 1 Crash can have many persons.

    I think it's call (Crash:Person:Vehicle)=(M:N:1)

    I need to not loose any information.
    For example I need to know which persons were in Vehicle1 in the Crash1

    How can I decompose it to a binary relationship?

    Thanks in advance!!! Guillermo.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd have three base tables (crashes, vehicles, and people), and one "many-to-many" relationship. The many-to-many relationship links a vehicle and crash to each of the people involved. You need to allow for a NULL vehicle because people can be injured by an accident even if they are not in a vehicle.

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

  3. #3
    Join Date
    Nov 2010
    Posts
    3

    One or two many-to-many?

    Quote Originally Posted by Pat Phelan View Post
    I'd have three base tables (crashes, vehicles, and people), and one "many-to-many" relationship. The many-to-many relationship links a vehicle and crash to each of the people involved. You need to allow for a NULL vehicle because people can be injured by an accident even if they are not in a vehicle.

    -PatP
    Sorry I didn't get it....
    Which tables would you be linking in a new many-to-many table? I mean, a many-to-many table has the keys of the two other tables...which ones are you suggesting?
    Thanks! Guillermo.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I can think of so many possible answers, and I can see that my original answer leaves out some important possibilities.

    Why don't you take a stab at what you think is correct, then I'll offer my observations about it. You probably know more about your needs than I do, so you'll almost certainly get closer on the first try than I did.

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

  5. #5
    Join Date
    Nov 2010
    Posts
    3

    Example

    Quote Originally Posted by Pat Phelan View Post
    I can think of so many possible answers, and I can see that my original answer leaves out some important possibilities.

    Why don't you take a stab at what you think is correct, then I'll offer my observations about it. You probably know more about your needs than I do, so you'll almost certainly get closer on the first try than I did.

    -PatP
    The problem is that each model I came into, seems to be not lossless or not the best one at least...
    For example

    The ternary relationship (with just one ternary table) could be
    Crash Person Vehicle
    1 1 1
    1 2 1
    2 1 1
    2 2 1
    1 3 2
    3 4 1
    3 5 1

    Then I need a way to make it binary tables

    From the theory I have if I call
    Crash = X Person = Y Vehicle = Z
    (XYZ) = (M:N:1)

    Then the two posible decompositions are:
    1) (XY)(XZ) with a binary imposition of (X:Z)=(M:1)

    And the other one is
    2) (XY)(XZ) -or- (XY)(YZ) with binary impositions of
    (X:Z)=(M:1) and
    (Y:Z)=(M:1)

    Using 1)

    Crash-Person Crash-Vehicle
    1 1 1 1
    1 2 2 1
    2 1 1 2
    2 2 3 1
    1 3
    3 4
    3 5

    I don't get what should I do with the binary imposition of (X:Z)=(M:1) here.

    Now if I want to get all the people involved in crash 1 that were in the vehicle 1....
    And it brings different results between the ternary option and this one...
    Please run the attached script (inside the zip) and you'll be able to see what I'm talking about...
    Thanks!!! Guillermo.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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