13.9. Same Base A and B, 1 - 1 match over cols Aid(E),Bid(E)¶
There are some cases in a conceptual schema where a one-to-one relationship exists between two entities. When mapping to relations, you can choose which relation should have a foreign key into the other. We discussed this earlier in section 3.8 of this book, where we had the following LDS fragment and SQL implementation.
Here is a fragment of the conceptual schema showing the one-to-one relationship in this database.
The following code shows the SQL to create Skill and Team Skill data. Note how the foreign key is in TeamSkill, referencing back to Skill. An important point about doing Match Joins, however, is that each table contains a skillCode that we can match over, and that there is one skillCode in Skill for one skillCode in TeamSkill. In this one-to-one relationship, the foreign key can be implemented in either direction.
The type of Match Join between these two relations in this case is quite straightforward. The one-to-one relationship corresponds naturally to a 1 - 1 case of Match Join, working on the foreign key column.
Find each Skill with its TeamSkill data.
or equally correct:
Find each TeamSkill with its Skill data.
Each of these relations has the same identifier, so they are the same base.
The precedence chart for the Match Join, which is symmetric because the order of A and B produces the same result relation, looks like this:
Like the M - M example in the previous section, we use the half-house shape and the arrows from A and B both point to the peak of the half-house shape. This is the Symmetric-either case in Match Join, like the Intersect and Union set operators.
SQL for the Match Join: