Technical support

Re: Relational View with 0..1 to a 0..1

Matthew Curland — Thu, 03 May 2012 23:24:47 GMT

Sorry I didn't jump in here earlier.

This is a good idea, but in practice is harder to implement than it sounds because if there are multiple 1-1 fact types attached to the same object type the user settings can be contradictory and cause some horrific issues with the mapping algorithm.

There are a number of tie-breakers that are looked at to determine absorption direction. However, if the mandatory constraints are balanced (no mandatories, or mandatory on both sides), both role players are entity types, and tables exist for both role players, then you're basically in a tie.

With a tie in place, the first role in the fact type wins. Unfortunately, there is no way to change the role order in the tool. Fact type shapes and readings maintain their own orders, but the role order itself is rarely used and cannot be changed after the fact type has been created.

To change the role order in these cases:

Save a backup copy of the .orm file.
Open the .orm file with the Visual Studio .xml editor. You can do this by clicking the down arrow on the right edge of the Open button in the VS Open File dialog and choosing 'Open With...' and choosing the 'XML (text) Editor'. Alternately, I always turn on the 'Show Miscellaneous files in Solution Explorer' option (on the Environment/Documents page of the Tools/Options dialog). If you close an .orm file that is not in a project, it will appear in this list and you can right click to 'Open With...'. You cannot view the .orm file simultaneously in the XML and NORMA designers.
With the .orm file open in the .xml editor, find your fact type (search for the the condensed name shown in the model browser). You should see this in the _Name attribute of an orm:Fact tag.
You'll see orm:Role elements inside the orm:FactRoles block immediately inside the orm:Fact element. The order of the orm:Role nodes provides the primary fact type order.
Change the role order by switching the order of the orm:Role elements.
Save the file and reopen in the designer.
The fact type shapes may display with the lines crossed for the modified fact type. Just drag one role on the other to get the correct display order.
To force the relational model to regenerate:

Add a new entity type to the design surface and sets its refmode to an intrinsic ref mode (like .id), or add a value type and set its data type.
Delete the new type (do not Undo)
(Advanced alternative to force regeneration on load: You can change the number of the ormtooial:AbstractionModelGenerationSetting@AlgorithmVersion attribute to force regeneration on load, or just delete the full orm:GenerationState block)

At this point you should see the 1-1 going the other direction, but only if there really was a tie in the system.

-Matt

Re: Relational View with 0..1 to a 0..1

Looopy — Tue, 28 Feb 2012 22:20:40 GMT

Ken Evans:
Looopy:
. the thing i am try to accomplish is that both a employee and customer can share the same login and its not mandatory that they should be able to login and its not mandatory for a person to do a login either.

Please refer to my example.
If you:
1: remove the mandatory requirement in the fact type "Person has WebLogin"
2: remove the exclusion constraint between the employee and customer subtypes

Then:
* an employee and a customer can share the same login (i.e. one person can be both a customer and an employee)
* it is not mandatory that a person has a login
* it is not mandatory for a customer or employee to login (since both subtypes share the non-mandatory nature of the "Person has WebLogin" fact type.)
And
If you want to add "contacts" that are neither customers nor employees
Then then you could add a unary "Person is contact"
or add "Contact" as a new subtype of person and then add a fact type about "Contact" that is not true of Customer or Employee.
Ken

Thanks Ken i will look into this.

Re: Relational View with 0..1 to a 0..1

Looopy — Tue, 28 Feb 2012 22:19:12 GMT

Andy Carver:
Hi, Yes you are correct, there should be a way to do it and there isn't. ORM's Rmap algorithm stipulates that in relational-mapping a 1:1 fact type like yours with mandatory constraints either on both roles or on neither roles (as in your case), the user needs a way to input his / her preference as to which table that 1:1 should be mapped to. Unfortunately, as far as I know no such facility for user input has been incorporated into NORMA's Rmapping as yet. So it's not so much a bug, as an interaction-feature that's yet to be added in. As for when it will be added, perhaps Matt or Terry could comment on that.

I Still think it is a bug because it do not generate the same output on the same input, like any function will do. But if the algorithm do allow different output then maybe it is not a bug. Thank you for confirming it anyway, so i don't fool around and searching for a solution then there is none.

Re: Relational View with 0..1 to a 0..1

Ken Evans — Tue, 28 Feb 2012 09:33:41 GMT

Looopy:
. the thing i am try to accomplish is that both a employee and customer can share the same login and its not mandatory that they should be able to login and its not mandatory for a person to do a login either.

Please refer to my example.
If you:
1: remove the mandatory requirement in the fact type "Person has WebLogin"
2: remove the exclusion constraint between the employee and customer subtypes

Then:

* an employee and a customer can share the same login (i.e. one person can be both a customer and an employee)
* it is not mandatory that a person has a login
* it is not mandatory for a customer or employee to login (since both subtypes share the non-mandatory nature of the "Person has WebLogin" fact type.)

And

If you want to add "contacts" that are neither customers nor employees
Then then you could add a unary "Person is contact"
or add "Contact" as a new subtype of person and then add a fact type about "Contact" that is not true of Customer or Employee.

Ken

Re: Relational View with 0..1 to a 0..1

Andy Carver — Tue, 28 Feb 2012 02:26:36 GMT

Hi, Yes you are correct, there should be a way to do it and there isn't. ORM's Rmap algorithm stipulates that in relational-mapping a 1:1 fact type like yours with mandatory constraints either on both roles or on neither roles (as in your case), the user needs a way to input his / her preference as to which table that 1:1 should be mapped to. Unfortunately, as far as I know no such facility for user input has been incorporated into NORMA's Rmapping as yet. So it's not so much a bug, as an interaction-feature that's yet to be added in. As for when it will be added, perhaps Matt or Terry could comment on that.

Re: Relational View with 0..1 to a 0..1

Looopy — Mon, 27 Feb 2012 22:18:58 GMT

Hi Again, Thanks for the tips Ken about the book. But still i think i will get in a situation that i don't agree on how norma generates the DDL SQL. Like now then it got the foreign keys on weblogin but i could fool the tool by adding and removing the mandatory flag and resave the model and then i get the foreign keys at customer and employee. And the next time i do any change on the model by adding a new entity i need to do the same procedure to make it create them there i wish them to be. So i still thinks it is a bug, don't You agree? Either there should be a way to do it or it should not.

Re: Relational View with 0..1 to a 0..1

Ken Evans — Mon, 27 Feb 2012 10:27:30 GMT

If you want to learn ORM, then I recommend that you get Terry's book "Information Modeling and Relational Databases Second Edition". It has lots of examples and exercises so you can use it as a reference and also go through the exercises.

Looopy:
what if i wish to insert a person in my model that should not be able to login and is not a cusomer of a employee say for example a contact then this model fails.

Regarding my example, it is not quite correct to say that "it is the model that fails". A model is just as you define it to meet your requirements.
With ORM you need to be very clear about what you want and then express that in terms of atomic facts.

For example, if you want to have a Person in your database that cannot login, then one solution is to insert an additional subtype between Person and the Customer and Employee Semantic object types (you might call it WebsiteUser) - then change the Person(.id) has WebLogin(.code) to be something like WebsiteUser(.nr) has WebLogin(.code).

Looopy:
the thing i am try to accomplish is that both a employee and customer can share the same login and its not mandatory that they should be able to login and its not mandatory for a person to do a login either. So the main problem is that i wish to model it right but i do not succeed defining it in NORMA.

You should find it easier if you break down complex statements like this into a set of atomic facts and then model those.

Ken

Re: Relational View with 0..1 to a 0..1

Looopy — Sun, 26 Feb 2012 23:23:42 GMT

Hi Ken,

Ken Evans:
Looopy,
Maybe I'm wrong, but I get the impression that you have some notion of what the relational model "should" look like and then you are trying to work back from that to define an object-role model that fits what you think "should" be the case.

No i tried the correct approach do the model first without thinking of the database but i don't know really have to model it in a ORM tool - i am a beginner in this field.

Ken Evans:
For example you might want to say that a person has a first name and last name and a weblogin. Then you might say that a person can be an employee or a customer but not both. Then you might want to say things that only apply to "customer" or that only apply to "employee."

The example you made states that a person is able to login (weblogin) but what if i wish to insert a person in my model that should not be able to login and is not a cusomer of a employee say for example a contact then this model fails. the thing i am try to accomplish is that both a employee and customer can share the same login and its not mandatory that they should be able to login and its not mandatory for a person to do a login either. So the main problem is that i wish to model it right but i do not succeed defining it in NORMA.

Re: Relational View with 0..1 to a 0..1

Ken Evans — Sun, 26 Feb 2012 17:19:34 GMT

Looopy,

Maybe I'm wrong, but I get the impression that you have some notion of what the relational model "should" look like and then you are trying to work back from that to define an object-role model that fits what you think "should" be the case.

The recommended procedure is to begin by defining what you want to say (in an object-role model) and only then should you look at what kind of relational model this generates.

For example you might want to say that a person has a first name and last name and a weblogin.
Then you might say that a person can be an employee or a customer but not both.
Then you might want to say things that only apply to "customer" or that only apply to "employee."

The following diagram shows an example of this approach.
In summary, you start by "getting the facts" and only then do you generate DDL or other artefacts.

Ken

Re: Relational View with 0..1 to a 0..1

Looopy — Sun, 26 Feb 2012 12:55:31 GMT

Clifford Heath:

Do you intend that an Employee who is also a Customer can have two WebLogins? You do know that there is no implicit exclusion between two subtypes as in some O-O languages? You can add an exclusion constraint if necessary.

No they should be using the same weblogin but its not mandatory that they are able to login, that is the connection "has access to" should fix.

Clifford Heath:

If a Person can be both a Customer and an Employee but can only have one WebLogin, the WebLogin should be associated with the Person class, or a subtype (WebUser?) which is a supertype of both Employee and Customer, or should have an exclusion, or some other arrangement that enforces that.

But if i do it like this then both an employee and a customer can login, i would like to have them register then they wish to login, either if its an employee or a customer. Can you give me an example on how i can accomplish this?

Re: Relational View with 0..1 to a 0..1

Clifford Heath — Sun, 26 Feb 2012 05:11:42 GMT

A subtype cannot be independent (of its supertype). Try making the supertype independent first (not sure that'll work though, it depends on the interpretation NORMA applies).

Do you intend that an Employee who is also a Customer can have two WebLogins? You do know that there is no implicit exclusion between two subtypes as in some O-O languages? You can add an exclusion constraint if necessary.

If a Person can be both a Customer and an Employee but can only have one WebLogin, the WebLogin should be associated with the Person class, or a subtype (WebUser?) which is a supertype of both Employee and Customer, or should have an exclusion, or some other arrangement that enforces that.

Finally you might find that there are "absorption" options available which you can use to steer this.

Re: Relational View with 0..1 to a 0..1

Looopy — Sun, 26 Feb 2012 01:21:33 GMT

Clifford Heath:
Nothing in your model requires the FK to be in a particular direction. Just a thought; try setting Customer "Independent" in the Property pane. I think that will skew the absorption to decide that Customer is a table (while it's still making up its mind about a WebLogin). However, surely the WebLogin role on your one-to-one is mandatory? Otherwise you can have a WebLogin that logs-in no Customer?

The "Indipendent" was not an option it was grayed out so i could not select it. So that was nothing i could set. I have added a new more detailed description on my problem in my previous post. Thanks for the help!

Re: Relational View with 0..1 to a 0..1

Looopy — Sun, 26 Feb 2012 01:19:25 GMT

Ken Evans:
Thanks for your question, however, as I'm sure that you know, the orm diagram you posted is not the same as that which is reflected in the relational view.
The orm diagram that you posted just gives a single table.
So it would be helpful if you posted both the object-role model and the relational view of that model and then pointed out the problems you are having with that particular model.
If you change an object-role model, the change will be immediately reflected in the relational view of the model. In turn, this will be reflected in the DDL.
So a change in the relational view is not "random" - it is just a relational representation of the object-role model.
It would also be helpful if you explain what you mean by "correct" DDL?
Ken

I have attached the new more detailed view of my model and try to exploain in more detail what my problem is in the image.

Re: Relational View with 0..1 to a 0..1

Looopy — Sun, 26 Feb 2012 00:35:54 GMT

I have a Employee that can have a WebLogin, those weblogin should not be connected to a Customer if they are not the same. But i will try to set it as "Independent" and se what happens. Thanks for the tip!

Re: Relational View with 0..1 to a 0..1

Looopy — Sun, 26 Feb 2012 00:35:54 GMT