DB Reverse FK -> 1 vs 0..1 multiplicity

[Guillaume]

Hi,

I ran a reverse on a SQL Server DB

I noticed that when a foreign key is declared as not null or nullable, it generates a 1 multiplicity with the linked table. I would expect a 0.1 multiplicity when it is declared as NULL.
Am I missing something ?

[Modesto Vega]

Could you please be more specific? What type of notation? Which version of Sparx EA?

Also, cardinality is placed on both ends of a foreign key and can also be placed on both ends of any association. So, if I have tables A and B linked via an FK joining fields that can be set to NULL on both ends, I would expect
A 0..1 —>0..* B when the DB is reversed engineered.

If the fields on both ends cannot be set to NULL, I would expect
A 1 —>1..* B when the DB is reserved engineered (But it should really be A 1..1).

If the field on A cannot be set to NULL the field on B can, I would expect
A 1 —>0..* B when the DB is reversed engineered (But is should really be A 1..1)

2 caveats/health warnings:
1) Without customising it Sparx does not do 1..1, even when you customise it does not play nicely
2) I have not tested the scenarios about with any database

[Guillaume]

Hi,
I found a sample DB here that illustrates what I'm trying to do:
https://www.sqlservertutorial.net/sql-server-sample-database/

Looking at the Staffs table selfrelationship, its DB cardinality is "0.1 to *" as shown in the above link
This is due to the fact that the manager_id FK is not declared as NOT NULL.

Code: [Select]

CREATE TABLE sales.staffs (
staff_id INT IDENTITY (1, 1) PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
store_id INT NOT NULL,
[b]manager_id INT[/b],
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES sales.staffs (staff_id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Having run the reverse in EA (latest build), I'm getting a 1 to 0.* cardinality, not a 0.1 to 0.*.

Same applies to the Customers to Orders tables ; the customer_id FK declared in Orders can be null. In EA the cardinality is 1 on the customers table.

[Geert Bellekens]

I just checked with one of my models, and indeed, I'm getting multiplicity [1] even for optional relations (nullable FK's)

It would be better if EA made those [0..1]

Geert

[Modesto Vega]

This works as I described above in models reversed engineered with v13 (Oracle and SQL Server). Hopefully, they ain't broken it.

[SNIP]
I just checked with one of my models, and indeed, I'm getting multiplicity/cardinality [1] even for optional relations (nullable FK's)

It would be better if EA made those [0..1]

Are you sure that you don't have a primary key on the column with a multiplicity of [1]? And my apologies for asking such a dumb question.

[SNIP]

Code: [Select]

CREATE TABLE sales.staffs (
staff_id INT IDENTITY (1, 1) PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
store_id INT NOT NULL,
manager_id INT,
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES sales.staffs (staff_id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Having run the reverse in EA (latest build), I'm getting a 1 to 0.* cardinality, not a 0.1 to 0.*.

Same applies to the Customers to Orders tables ; the customer_id FK declared in Orders can be null. In EA the cardinality is 1 on the customers table.

I am assuming that sales.stores.store_id also has a PK on it, like sales.staffs.staff_id. I am correct to assume that you are expecting the FKs to be created as follows:
sales.staffs (manager_id) > sales.staffs (staff_id)
sales.staffs (store_id) > sales.stores (store_id)

i am inclined to think the FKs have created the other way around.

[Geert Bellekens]

This works as I described above in models reversed engineered with v13 (Oracle and SQL Server). Hopefully, they ain't broken it.

[SNIP]
I just checked with one of my models, and indeed, I'm getting multiplicity/cardinality [1] even for optional relations (nullable FK's)

It would be better if EA made those [0..1]

Are you sure that you don't have a primary key on the column with a multiplicity of [1]? And my apologies for asking such a dumb question.

Yes, sure, my PK columns are all mandatory[1] but some of my FK columns are optional [0..1].
I would expect the association that EA creates when reverse engineering a database to also have a [0..1] multiplicity at the target table end.

Now I don't see a difference in the multiplicities of the associations for mandatory FK columns or optional FK columns.
They all have [1] as multiplicity.

Geert

[Modesto Vega]

[SNIP]
Yes, sure, my PK columns are all mandatory[1] but some of my FK columns are optional [0..1].
I would expect the association that EA creates when reverse engineering a database to also have a [0..1] multiplicity at the target table end.

Now I don't see a difference in the multiplicities of the associations for mandatory FK columns or optional FK columns.
They all have [1] as multiplicity.

My point is that if there is a PK involved in the FK, the PK side will always have  [1] as its multiplicity. Where there is a PK, I would expect to see only variations on the FK side [0..*] or [1..*]. If one of the 2 ends of an FK is a PK, I would expect the constraint to be created PK > FK not the other way around.

P.S.: I am not seeing those variations on the FK side, but it has taken a while to find an example of the FK not allowing a NULL value.

[Geert Bellekens]

[SNIP]
Yes, sure, my PK columns are all mandatory[1] but some of my FK columns are optional [0..1].
I would expect the association that EA creates when reverse engineering a database to also have a [0..1] multiplicity at the target table end.

Now I don't see a difference in the multiplicities of the associations for mandatory FK columns or optional FK columns.
They all have [1] as multiplicity.

My point is that if there is a PK involved in the FK, the PK side will always have  [1] as its multiplicity. Where there is a PK, I would expect to see only variations on the FK side [0..*] or [1..*]. If one of the 2 ends of an FK is a PK, I would expect the constraint to be created PK > FK not the other way around.

P.S.: I am not seeing those variations on the FK side, but it has taken a while to find an example of the FK not allowing a NULL value.

You have completely lost me 
All my FK's reference a single a single (mandatory) PK column, but I don't see why there should be a [1] multiplicity on the association.

Geert

[Guillaume]

Hi,

Thanks for your replies. In line with Geert's comments, I reported it as a bug to Sparx.
Somehow I'm surprised this hasn't been seen before

[Modesto Vega]

[SNIP]
You have completely lost me 
All my FK's reference a single a single (mandatory) PK column, but I don't see why there should be a [1] multiplicity on the association.

You are the one who lost me  . If all your FKs refer to a PK column - i.e., a mandatory column - why would you want a multiplicity other than 1 on the PK side of the association representing an FK. After all, the FK can only be enforced if there is a row on PK side, a row must be there on the PK side, having a row in the PK side of an FK is not optional. Of course, having a corresponding row on the FK side of the association could be optional.

I think there is a subtle difference between class cardinality and table cardinality, between UML cardinality and entity relationship cardinality.

 

[Guillaume]

Hi Modesto,

Below are my thoughts using the sample DB diagram here: https://www.sqlservertutorial.net/sql-server-sample-database/
- The DB cardinality from table customers to orders is 0.1 to * i.e. a customer can have many orders, but an order is associated with 0 or 1 customers. This is correct in my opinion and I expect the same from a reverse in EA.
- This is implemented in the SQL create orders table by setting  customer_id INT FK to nullable i.e. if customer_id is null then my order is not associated with a customer (0), whereas if customer_id is populated with a valid customer ID PK, then the order is associated with a customer (1).

Hence the need and my expectation to support both alternatives
- when the FK (customer_id) is NOT set to NULL -> DB cardinality = 0.1 to * (not currently supported)
- when the FK (customer_id) is set to NOT NULL -> DB cardinality = 1 to *

[Modesto Vega]

[SNIP]
Hence the need and my expectation to support both alternatives
- when the FK (customer_id) is NOT set to NULL -> DB cardinality = 0.1 to * (not currently supported)
- when the FK (customer_id) is set to NOT NULL -> DB cardinality = 1 to *

I can only think of one reason why anybody would want to create an FK with an end on a column which is not part of a PK: Referential integrity constraints are going to be handled by the code writing data to the database. In that case, the most common practice is not to bother creating FK constraints.

It is a generally accepted good practice that any FK would reference a PK - i.e., an orders table will reference a [customer] table via a [customer_id] column where the [customer_id] column on the [customer] table is a PK. This means that although a [customer] may have no [orders], a [customer] must exist before an order is written to the database. As a result the cardinality/multiplicity should be 1 to 0..*, indicating that a [customer] must have zero or many [orders].

[Guillaume]

Sparx confirmed that "EA simply sets the cardinality of all Foreign Keys connector imported during the reverse engineering process to 0..1"
The expected behaviour I described will be treated as a feature request.

[Modesto Vega]

Sparx confirmed that "EA simply sets the cardinality of all Foreign Keys connector imported during the reverse engineering process to 0..1"
The expected behaviour I described will be treated as a feature request.

Hope the feature request gets considered, certainly reversed engineered cardinality can be improved, good luck with it. Having said this I do not think it is as simple as you make it sound, object cardinality and database cardinality are not similar but not the same.