Deadlock at Conceptual Design Level, Entity-Relationship Modeling (MER)

Question

At conceptual design level we can use the approach (relationship entity modeling (MER)) to describe the behavior of future tables that will appear in the logical project. Within that context a doubt arose.

In entity and relationship modeling (MER) there is cardinality 1:M

I would like to know if there is any real example of this kind of relationship (between two entities with this cardinality).

See the MER design, Example:

If there is, then don’t we have a deadlock principle? since one depends on the other existing first and vice versa.

How to resolve the deadlock of entities that have this type of relationship on both sides?

Whenever we have 1 as the minimum cardinality on one side, it indicates that there is a degree of total or partial dependence between the entities involved?

I just read chapter 7

Source: Database Systems 6th edition, authors: Elmasri, Ramez Navathe, Shamkant B. Year: 2011

Answer 1

You can accept the eventual inconsistency in a physical modeling [0,N] as in the Reply from @Victorstafusa. Or use a database that accepts inconsistencies in the middle of transactions. This option is not common, I particularly only know the Sqlite that does this.

How to proceed in this case? Well, in Sqlite they call this foreign key consistency "deferred foreign key". It’s in section 4.2 of that Article explaining about foreign keys.

The concept of the "deferred key" is:

If I’m in a transaction, then occasionally consistency will be achieved, I don’t need to check every instant that everything is consistent

Usually the concept used with "foreign key" is what Sqlite calls "instant foreign key":

No matter where I am or where I’m going, everything needs to be consistent at any time!!!

In this "foreign key deferred" consistency model, just start a transaction before the inserts and then commit when you finish operating.

I find this consistency modeling particularly valuable only after transaction termination when you have circular data.

Imagine that you are modelling an echidna. These strange animals, not only are they one of the few oviparous mammals, they have a multiple-headed penises. And the males use these heads in a very interesting way: they take turns which head is going to be used to copulate, so that all the heads are used before repeating one of them.

ERRATA: the equidna does not take turns from the head of the penis as I had mentioned in the paragraph above. In fact, it expels through the seminal ducts up to two waves of semen by separate heads, but it is worth the mental exercise

For those who don’t know these strange beings, with the body of a hedgehog and the tongue of an anteater that lays eggs and feeds his descendants, follow his photo (courtesy dug by @Bacco): link to the equidna

In this case, we could model like this:

create table equidna (
  id int primary key,
  tag varchar(50) not null
)

create table penis_equidna (
  id int primary key,
  equidna_id int not null,
  prox_penis_id int not null,
  em_uso int not null default 0,
  FOREIGN KEY (equidna_id) REFERENCES equidna(id),
  FOREIGN KEY (prox_penis_id) REFERENCES penis_equidna(id) NOT DEFERRABLE
)

Note that I am here creating a circular list of echidna penises. To update what the next penis will be in use for the next copulation of equidna 13, just run the following update:

WITH p_em_uso AS (
  SELECT
    id,
    prox_penis_id,
    anterior_penis_id,
    equidna_id
  FROM
    penis_equidna
  WHERE
    em_uso = 1
)
UPDATE penis_equidna
SET
  em_uso = CASE
    WHEN EXISTS (SELECT 1 FROM p_em_uso WHERE p_em_uso.prox_penis_id = penis_equidna.id AND p_em_uso.equidna_id = penis_equidna.equidna_id) THEN 1
    ELSE 0
  END
WHERE
  equidna_id = 13

Not checked query yet, I hope to check soon and correct any and possible error contained in it

In this case, to register the equidna 13 and its 4 penises, it would have been necessary:

INSERT INTO equidna(id, tag) VALUES (13, 'abc')(

BEGIN; -- começa uma transação SQLite

INSERT INTO penis_equidna(id, equidna_id, prox_penis_id, em_uso) VALUES (1, 13, 2, 1);
INSERT INTO penis_equidna(id, equidna_id, prox_penis_id, em_uso) VALUES (2, 13, 4, 0), (4, 13, 3, 0), (3, 13, 1, 0)

COMMIT;

Note that consistency will only be achieved when the last line is inserted, thus closing the circle.

---

Having said this consistency model, I can’t model in any obvious way without using check constraints an abusive relationship of [1,N] for [1,N]. I think it would be better to allow this inconsistency from scratch. It doesn’t seem right after removing a house I be required to remove a person (using the @Victorstafusa response template).

A real case of this modeling

I was chatting when @pussycat arose with a problem of structure modeling in the database (beginning more or less here).

His problem I identified more or less as the following:

I have fire sensors in several places. For example, km 12 (in the state of SP) of the BR-116 is the location identified by 1, The km 13 of it is the place identified by 2.

These sensors trigger alerts, which should be stored in the bank. These alerts come with the sensor identifier that triggered it and the intensity of the fire identified. However, in theory, a single fire point can trigger alarms on several sensors at the same location. So, each location can only have one fire point "on" at a time, and every warning always refers to a fire point.

I was able to infer the following modeling in the MER model:

In this case, I also detected an important detail: the insertion of a alerta can generate a new ponto_queimada! As if it were a creation trigger!

So, how do you do that? Well, I thought I’d actually use a trigger. It could have been a procedure in SQL or some programmatic solution at the application level. Trigger it seemed more natural to me, however.

The idea of mine trigger is:

1. check whether a burn point already exists in that region
   that burning point can’t have been contained
2. if there is no such burn point, insert it
3. now the burn point simply exists (if it did not exist before, now it exists), so I can enter the alert pointing to the correct alert

I thought about that trigger replacing an insert. In case, I thought to have the alertas_view_insercao, in which she would not have the information of id_ponto_queimada/foreign key to burn point. Follow a pseudocode for this trigger (original):

create trigger insteadof insert on alertas_view_insercao as
begin trigger

insert into ponto_queimada
select
     'N'as CONTIDA
   ... informações
where not exists (
     select top 1 1
     from ponto_queimada pq
        inner join alerta a on pq.id_ponto_queimada = a.id_ponto_queimada
        inner join sensor s on a.id_sensor = s.id_sensor
        inner join local l on l.id_local = s.id_local
     where pq.contida = 'N' and l.id_local = inserted.id_local
);

with q as ( --se não existia um ponto_queimada antes, agora existe...
     select top 1 pq.id_ponto_queimada
     from ponto_queimada pq
        inner join alerta a on pq.id_ponto_queimada = a.id_ponto_queimada
        inner join sensor s on a.id_sensor = s.id_sensor
        inner join local l on l.id_local = s.id_local
     where pq.contida ='N' and l.id_local = inserted.id_local
)
inser into alerta
select
     q.id_ponto_queimada,
   ... informações
from q


end trigger

The idea of trigger in view is:

1. how is a view, the "user" can only inform the columns that ME want
2. unused insteadof, the effect of the insertion is replaced by the content of the trigger

Several banks support operations in views (Sqlite, SQL Server, etc). The moment insteadof is known as instead of (with space) by Sqlite.

With that trigger, when registering a new alerta through the alertas_view_insercao, the bank itself will be concerned to inform which ponto_queimada this alert refers. The bank itself checks whether there is a ponto_queimada and, in its non-existence, will create a new.

Answer 2

You have to look at the physical model. For example:

CREATE TABLE casa (
    id INT,
    endereco VARCHAR(200),
    PRIMARY KEY (id)
);

CREATE TABLE pessoa (
    id INT,
    nome VARCHAR(200),
    PRIMARY KEY (id)
);

CREATE TABLE moradia (
    id_pessoa INT,
    id_casa INT,
    PRIMARY KEY (id_pessoa, id_casa),
    FOREIGN KEY (id_pessoa) REFERENCES pessoa (id),
    FOREIGN KEY (id_casa) REFERENCES casa (id),
);

INSERT INTO casa (id, endereco) VALUES (1, 'Avenida do código, 1233');
INSERT INTO casa (id, endereco) VALUES (2, 'Rua do SQL, 478');
INSERT INTO casa (id, endereco) VALUES (3, 'Avenida do banco de dados, 32');

INSERT INTO pessoa (id, nome) VALUES (1, 'Carlos');
INSERT INTO pessoa (id, nome) VALUES (2, 'Maria');
INSERT INTO pessoa (id, nome) VALUES (3, 'Pedro');
INSERT INTO pessoa (id, nome) VALUES (4, 'Julia');

-- Carlos mora na Avenida do código.
INSERT INTO moradia (id_pessoa, id_casa) VALUES (1, 1);

-- Pedro também mora na Avenida do código, na mesma casa que Carlos.
INSERT INTO moradia (id_pessoa, id_casa) VALUES (3, 1);

-- Maria mora na rua do SQL.
INSERT INTO moradia (id_pessoa, id_casa) VALUES (2, 2);

-- Carlos mora em dois lugares, e faz companhia a Maria na rua do SQL.
INSERT INTO moradia (id_pessoa, id_casa) VALUES (1, 2);

-- Julia mora na Avenida do banco de dados.
INSERT INTO moradia (id_pessoa, id_casa) VALUES (4, 1);

The above instructions run on Mysql, but should work on other databases with minimal modifications. Note that there is no deadlock because the relationship is built after the entities to be related already exist. N-to-N relationships are modeled using an intermediate table, which in the above case is the table moradia.