3

enter image description here

I'm taking this term a DB course as part of my CS studies.
This week we began studying the subject of ER diagrams, which I find a bit confusing.

I have some uncertainty about the picture above (Note: the rounded arrows indicate "One and only one" - that is, every subject from B can and must be directed to exactly one subject in A, and vice versa).

I have to create a suitable relational schema for this diagram, and here is my try:

  • A(a, b, c, d) (b is the key)
  • B(e, f) (e is the key)

This is my uncertainty:

R(b, e) - no matter which key I'm creating out of those foreign keys, the "one and only one" constraint isn't held.

How can I create the schema where this constraint holds?

What about if you had "at most one" arrows, and not exactly?
How the answer would change?

2 Answers 2

2

Table a should reference (foreign key) b and vice versa. On the foreign keys you create a unique index, so you have a 1 on 1 relationship.

The following data respects these constraints, but is probably not what you'd like.

Tab a   Tab b
PK FK   PK FK
1   a   a   2
2   b   b   1

So you may need to be even more stringent, and put a foreign key on the primary key column, forcing both tables to have the same primary key.

"at most one": allow the column to be null.

A symmetric one on one relationship, as below, can't be enforced with foreign keys (unless logic is added, eg. triggers).

Tab a   Tab b
PK FK   PK FK
1   b   a   2
2   a   b   1
4
  • What are those a and b in your tables' columns? What do those numbers represent? I can't see the meaning of your tables.
    – HelpMe
    Oct 25, 2020 at 7:39
  • They are column values. The value of the primary key of the first rown of table a is 1. The value of the foreign key of the second row of table b is also 1. a(1) is related to b(a), b(a) is related to a(2), a(2) is related to b(b), and b(b) is related to a(1). I completely understand that you don't, because although there is a one to one relationship, it is not symmetric, which is probably what you are after. I think that can only be implemented by giving both tables the same PK, referencing each other. Oct 25, 2020 at 10:16
  • BTW, if you have notions of arithmetic, the relation you're after is called a "bijection". en.wikipedia.org/wiki/Bijection Oct 25, 2020 at 13:18
  • There is a term "shared primary key" that is used to indicate one of your options above. Oct 25, 2020 at 16:09
6

You have several possibilities, each of them with advantages and disadvantages.

Solution 1

Define a single table:

AB(a b c d e f)

with two candidate keys, b and e. You chose one of them as primary key, and declare the other as unique.

Advantages: All the constraints of the association among the two entities are satisfied.

Disadvantages: If there are other entities related to A or B, then the foreign key for the relation corresponding to this entity can be not “natural” if the primary key chosen for AB is the “other” key.

Solution 2

Define one of the two relations with a not nullable foreign key for the other, for instance add fke (“foreign key for attribute e in B”) to relation A. Then declare that this attribute is unique.

Advantages: it is more “natural” to keep the two entities in separate relations. You keep the constraints that each element of A is related to one and only one element of B (since the attribute fke is not nullable and it is unique in all the relation).

Disadvantages: you lose one constraints: that each element of B is related to some element of A. This constraint must be enforced in the applications developed on the database.

Solution 3

As before, exchanging the role of A and B.

Solution 4

As you have mentioned, with a bridge table R(b,e). Then declare both b and e as unique.

Advantages: you keep the constraint that each element of A can be related only to one element of B, and vice versa.

Disadvantages: you lose the constraints that each element of A is related to some element of B and vice versa. Those constraints must be enforced by the applications. Moreover, you have one more table, and this make the queries more complex.

Solution 5

You put a foreign key in each of the two tables to refer the other table. These foreign keys are also declared unique.

Advantages: you keep the constraint that each element in A is related to one and only one element in B, and vice versa.

Disadvantages: you lose the constraint that if a certain element b1 of B is associated to an element a1 of A, then a1 is associated to b1. In fact this solution allows inconsistences: an element a1 could be associated to b2, a2 could be associated to b1, while at the same time the foreign key for b2 could refer to a2, and the foreign key for b1 could refer to a1. So this constraint should be enforced by the application. Moreover, there is a certain redundancy in the data.


The solutions are listed in order from the more “secure”, to the more “dangerous”, at least in my opinion.

Finally, is worth to note an important point from this case: when translating from the ER model to the Relational Data Model, there are situations in which the translation is not completely satisfactory.

For your last question: what happen if at most one arrow. In this case the second or third solution can be used. And normally, if one arrow is total (i.e. you have exactly one element in the other entity set) while the other is partial, you put the foreign key in the relation for which the relation is total. If both are partial, then you can choose freely the relation in which to put the foreign key.

4
  • How do I draw this relational schema concretely? For example, for the first solution?
    – HelpMe
    Oct 25, 2020 at 7:02
  • Solution 5 does not allow inconsistencies if each PK references the PK in the other table. Oct 25, 2020 at 19:30
  • @ypercubeᵀᴹ , in solution 5 it is not sufficient to declare reciprocal foreign keys. It is necessary that if tuple a1 refers to tuple b1, then tuple b1 must refer exactly to tuple a1, and not to some other tuple in A. The inconsistency can happen if data is entered in a wrong way.
    – Renzo
    Oct 25, 2020 at 21:17
  • @HelpMe , you ask about drawing the relational schema. Are you thinking about some kind of graphical notation?
    – Renzo
    Oct 25, 2020 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.