Conceptual modeling

I have an ApprovalInfo table with approval criteria in other tables.

mnnoon — Fri, 10 May 2013 18:02:18 GMT

I'm looking for best practice in orm by asking Is there a good way in orm to check that all the required fields for the approval process (final stage for approval) is completed and ready to go? I have credit card account authorization tables and one of the last tables that gets populated is an approval table which has the date the account was approved and who approved it. So when I hit approved, it now checks that all the tables and required fields for the approval process are correctly filled with appropriate data. I'm thinking that maybe a trigger on the approval should be checked which then creates a transaction to see if all mandatory fields have been completed. This includes on sub tables such to check for required fields.

Note: I have a similar dilimea for changing an approved account. Closing an approved account and changing an approved account to a declined state or to some other state. I'm using a status field to track these mode changes.

Also I noticed someone was interested in a draft mode which makes sense because its not mandatory in the initial stage of the account creation b/c operators will constantly make changes or retrieve new information until its ready for final approval.

Thank you,

Marc Noon

Conditional mandatory constraints

Clifford Heath — Sat, 19 Jan 2013 01:54:40 GMT

When modeling, I often find one or more objects which map to records having a significant number of mandatory fields. In constructing a user interface for a user to populate one of these records, there's a need to be able to save the record in a kind of "draft" mode, without all its mandatory fields being yet populated. A similar situation arises with many kinds of state transition, not just from draft to complete, and these can sometimes be handled as subtype transitions. Simply place the conditionally-mandatory roles on a subtype, so that the supertype instance acquires a subtype instance dynamically after those roles are filled. However, this subtype approach relies on natural domain terminology to name the supertype, and doesn't work where some but not all mandatories are provided in one go.

But back to the draft situation, it's possible to add a unary "is draft", and then to define join mandatory constraints (using NORMA Pro, or using CQL) saying that the role is mandatory only if the object is not a draft. These constraints should get mapped as CHECK constraints (CHECK(field is not null or is_draft = true). However it's enough of a nuisance and is common enough to want to streamline it a bit in the modeling languages.

When developing applications using Ruby on Rails, it's common practise to simply omit the mandatory constraints, and to handle these validations in the Rails "model" (application code for the record type), but I prefer to enforce constraints in the DBMS where possible

How do other people handle this type of constraint?

Do joins make the most sense when determining states.

mnnoon — Wed, 06 Jul 2011 19:38:34 GMT

Hi, I've been trying to put joins into a case statement in SQL. The current version of SQL code has joins between a state rates table and several other tables that have states in them. But since there is an agreed upon order to how these rates are applied applied depending on which state a table is from, I have been having trouble moving this to a case statement.

My questions are there situation that are more suited for joins than to case statements in SQL Server? And which one is more efficient? (Details below)

Here is a banking example on which fee should be applied:

State of Residence, Banking State, Insurance State, License State, Highschool state

each of these states are in a separate table, and there is a fee table that has each state listed with its associated fee.

So I have matrix:

Case #

Residense State Length=2

Banking State Length=2

Insurance State Length=2

Drivers License State Length=2

Highschool State Length=2

TRUE

n/a

FALSE

TRUE

n/a

FALSE

TRUE

n/a

FALSE

TRUE

n/a

FALSE

TRUE

So my join would look have to match each case. I'm using empty string '' for situation where that information was not provided and NULL are never allowed in this model. The constraint is that there can only be either two characters A-Z and must be listed in StateList table, or an empty string. Since each of the states above are in a separate table, I need to choose the rate from the above matrix. Where each item is not n/a it must be a binary AND with each state.

STATE PENALTY TABLE looks like this:

STATE PENALTY ASSESSED

AK $12.95

AL $13.95

...

There is also a state called NA which stands for Not Applicable with a penalty of $1.00.

In the above case if all states are empty string, then a penalty of $1.00 is assessed.

select sp.State, sp.Penalty Assessed from Residence as r inner join Banking as b inner join Insurance as i inner join DriversLicense as d inner join HighSchool as h inner join StatePenalty as sp

on r.State = sp.State -- Case 1

or r.State = '' and b.State = sp.State -- Case 2

or r.State = '' and b.State = '' and I.State = sp.State -- Case 3

or r.State = '' and b.State = '' and I.State = '' and d.Sate = sp.State -- case 4

or r.State = '' and b.State = '' and I.State = '' and d.State = '' and h.State = sp.State - case 5

or r.State = '' and b.State = '' and I.State = '' and d.State = '' and h.State = '' and sp.State = NA - case 5

Thanks,

Marc

Informal references to entity types

Clifford Heath — Wed, 10 Oct 2012 06:15:03 GMT

I often encounter the situation where I have an Entity Type, say Person or Staff Member, which has a lot of associated details of which many are mandatory, but I also need to record references to individuals that are more informal, such as using their nickname. The Payroll department might need all the info, but if all I want to do is to make a record of who made a certain phone call (to jog the memory for another employee) etc, I often don't want a formal reference. Or in another example, an insurance company requires many facts about a person who is applying for insurance, but has many fewer facts about a witness to an accident, or the name of a third-party - even though a Person is the common supertype here.

In these informal situations, I don't want to record the ID of a person in my Party table (because that implies a lot of knowledge about them), I just want to record their name with no enforcement. The name however is a value type, so fact type readings which use just the name are awkward ("Reference Check Phone Call was made by employee called Name"). If I do it using a supertype of the full Person ET which allows the limited and/or informal identification, the identification pattern must also apply in the case of the subtype.

Basically I feel that all the correct models are a bit awkward. How do you model this kind of situation?

Cardinality constraints

JB4times4 — Tue, 05 Jun 2012 20:58:18 GMT

I've got a question about cardinality:

Is there a way to say # in {0,10} for example, a jury has either 12 members or 0 members.

In the book, Information Modeling and Relational Databases it says you can use # = 12, but this doesn't account for the empty set when a table is created, but doesn't show an example of how to account for the empty set, only to use a <=.

Thanks in advance.

it is possible to link more than one object type to a label type

mushtaha — Sun, 27 May 2012 10:26:15 GMT

I would like to ask whether it is possible to ink more than one object type to a label type as it appears in the attached picture? many thanks...

Is it possible to make ORM value types redundant?

michel — Mon, 29 Mar 2010 11:49:49 GMT

Being new to ORM I am wondering if it is possbile to make ORM value types redundant? Having read http://www.proace.com/2010/02/17/orm-basics-entities-values-roles-and-facts/, I do understand there is a difference between value and entity types in the UoD, but is it really needed?

Why not have one object type, draw values and entities the same and let users interpret which object type (value/entity) it is.

Binary fact type with spanning uniqueness constraint in co-referenced form. Independent?

JackStoneS — Wed, 24 Aug 2011 08:08:47 GMT

I have a question regarding schema equivalence.

In the attached image, I have put two versions of a simple schema.
An actor (with an id and name) can play in more than one play.
A play (with a code and title) can involve multiple actors.

When I write the fact type with a spanning uniqueness constraint in co-referenced form, I get something like the second schema.
Is it necessary to mark the entity type ActorPlaysInPlay is independent? It plays no roles except for those that identify it, but we need to populate this in the database.

Nulls and Foreign key relationships

mnnoon — Fri, 24 Jun 2011 19:42:24 GMT

Quick questions on Nulls and foreign key relationship. I've currently been asked to default all fields and even foreign key relationships to some default value. Even on left outer joins between rows that may not exist yet we have to set default values types using the isnull feature. My question is this, if a table requires referential integrity, is it ok to set foreign key fields to a 0?

Also, since I'm fairly new at not using Nulls with Microsoft Sql Server, is this a fairly common practice?

Thanks,
Marc Noon

Semantics of subtyping value types

Robert Schmaal — Wed, 01 Jun 2011 10:05:31 GMT

ORM2 allows subtyping of value types but what are the semantics of this, is it subset semantics or coercion semantics? Hence, if I specify that a value type A is a subtype of value type B, does this constitutes that values of A are a "subset" of values of B, or that there exists a unique function to convert values of A into values of B?

Modeling based an existence chain

mnnoon — Thu, 12 May 2011 00:19:49 GMT

Hi, I am running into a problem modeling a table that covers where a county code exists or not in 5 different situations where a penalty is applied based on what the first in the chain country is not null and is mapped correctly. I want to remodel the database but keep the business logic clearly defined in one or more tables and am looking for the best in practice method.

I want to follow a chain of whether the field is null or not null for 5 different cases, and remodel the database. Here is an example.

Highest priority: Federal Agency's Country code

Next Highest: State Agency's Country code

next : County organization's Country code

next: City's Country code

next: Claimants Country code

based on which one is found first in the chain a penalty is assigned and a maintenance fee is also attributed for each country where by a mapping of every country and every penalty and maintenance fee is already predetermined and agreed upon by all parties using the system. At any point in the chain if a null is found it drops to the next lower priority in the chain. Is there a good way to keep this type of chain logic clearly defined when redesigning a database and how would I go about doing that in orm? thx

To Null or not to Null. That is the question!

Andy Carver — Sat, 21 May 2011 05:44:41 GMT

Dear Ken,

You have my deepest apologies for using language that might be construed, if mistakenly, as a put-down of Chris Date. I myself am a big fan of Mr. Date, whom I regard in precisely the same way as your Amazon reviewer does, and I meant no disrespect in calling him a "religious zealot" -- which, for me, is a term lacking any negative connotation. (I will, myself, admit quite readily to being a religious zealot, who if anything, could probably use a bit more zeal.)

My terminology was not about Mr. Date so much, as about this "nulls" issue -- and all I meant by it was, that people seem to come at it with some deeply-held presuppositions -- or perhaps a mere blind spot(s) -- that seem to determine or colour their thinking on the issue. For example, I hope you'll allow me to critique Hugh Darwen's argument, the one that you quoted.

Why does Mr. Darwen think that if you allow a NULL column in a database, you're ipso facto allowing propositions to be recorded that have an indeterminate truth value? A very little bit of analysis will allow us to see that this is far from being true.

As Clifford has just implied, the necessity of "missing values" in a relational database comes from, and only from, the mapping of more thant one fact type to the same table, at least one of which is non-mandatory for that type of object about which the table records facts. (Which is something that ORM's Rmap procedure prescribes.) That means that the NULL "value" is not representing a proposition, or part of one, at all, but rather, precisely the LACK of any proposition, of that type, about the object being discussed! This is in fact THE BASIC ISSUE, which Mr. Darwen overlooks: what do you do about that missing proposition?? (Put a NULL there? Put a Default Value there?) So, as much as I may respect Hugh Darwen, his argument is based on complete lack of comprehension of why you'd have the NULL there in the first instance.

Hope this helps. Now, I'm enjoying this discussion (I must admit), but I'm just wondering whether, if Ken feels it should be continued, it might not deserve its own thread. Just a thought,

Cheers,

Andy

Modeling intransitivity

JackStoneS — Wed, 06 Apr 2011 07:42:03 GMT

In the BBB, the definition of intransitivity is the following: (see p284)
R is intransitive, iff for all x,y,z xRy & yRz -> ~xRz.

An example of an intransitive relation is 'is a parent of'.

Now, given the following model:

Person(.firstname) is a parent of Person
With a spanning uniqueness constraint on the fact type
And an intransitive constraint on the middle of the fact type

I would say that the following population is allowed in the model:
Ann     Bill
Bill      Colin
Colin  Dirk
Ann     Dirk
Is there a way to specify the model so that the last entry is disallowed?

Value Type or Fact Type for population-wide aggregates

Clifford Heath — Wed, 19 Jan 2011 05:46:26 GMT

Supposing I have a Blog model where Author writes Post, and Author commented on Post. Now I want to calculate the average number of Comments per Post. That sounds to me very like I need a derived value of some (new?) value type, which seems unnatural in ORM. I can model it as a unary fact type on a value type; perhaps a VT having a cardinality constraint of one. The use of a fact type means I can add a derivation for this fact type (and it also makes sense in a relational mapping)... but it seems wrong when it's the value instance itself that's derived, not a fact relating to a value type.

SBVR provides for such calculated values

What's the ORM way to define them?

Don't understand restriction on objectified binaries

jmagaram — Wed, 05 Jan 2011 23:21:24 GMT

It is my understanding that a binary can only be objectified if it has a uniqueness constraint that spans both roles or if each role has a uniqueness constraint. I don't understand this limitation. Suppose I am modeling the process of assigning work to someone else. Here are the relevant facts:

TaskRequest(id) is sent by [1] Person
TaskRequest(id) is request to [1] Person
TaskRequest(id) includes [1] WorkDescription
TaskRequest(id) received response on [0 or 1] DateTime

Now with regard to last predicate, suppose a TaskRequest can only receive a single response. So there is a unique constraint on the TaskRequest role but NO unique constraint on the DateTime role. It seems natural to objectify that predicate as a "Response". And then I'll have some other predicates like:

Response included [1] Note
Response was of [1] ResponseType(code) 'yes' or 'no'.

Why isn't it legal to objectify the binary this way?

There are other ways to model this that seems less natural and more cumbersome. For example, I can create a subtype of TaskRequest called TaskRequestWithResponse, where TaskRequestWithResponse is a TaskRequest that received response on some DateTime. And then I can attach the response roles to that sub-type. Another alternative is to attach the response predicates to the TaskRequest directly, like TaskRequest received response of ResponseType, but then I need to add a lot of complex subset constraints to indicate that all the response facts only apply if the TaskRequest received response on some DateTime.

I'd also be happy to add a new fact TaskRequest(id) received [0 or 1] Response, in which each role has a uniqueness constraint. And then I can attach the response roles to the Response object. But I'm not sure how to specify the reference mode of the Response. If anything, the reference mode on the Response should be the same as the reference mode on the Request. In other words, a TaskRequest with identity 3 would only have a Response with identity 3; the Response would not have an identity outside of the TaskRequest it is associated with. Is this approach legal ORM?

Help with boolean logic and constraints

subkamran — Mon, 25 Oct 2010 15:01:45 GMT

Given these business rules, I am having a hard time conceptualizing how to model this in NORMA:

A Class can be active [or inactive]
A Class can be primary [or "normal"]
A primary class can only be active
There can't be more than one primary class

The end result of some time thinking about this produce an entity of Class (with some other facts), a subtype entity of "Active Class" with a single binary fact "Is Primary" value type (Logical: True or False) hanging off the subtype with a 1:1 role mapping.

I don't know if that is a good way to do it; I used non-absorbed mapping so we have a separate table for active classes. I had some pushback from team members asking why we needed whole other table and I tried to explain that it enforces the 3rd business rule, but a check constraint on one table with two boolean columns was the argument against that.

Does anyone have any feelings about either approach?

In which step should artificial identities (IDs) be introduced if not mentioned by domain experts?

icmd.org — Thu, 16 Sep 2010 06:41:41 GMT

I wonder in which step should artificial identities (IDs) be introduced if not mentioned by domain experts? Although I haven't finished reading, but the question arises. I hope I can get a certain answer before I read on. Or, introducing artificial identities should be strictly avoided in conceptual modeling process, because they're logical, or even physical? Thanks, everybody!

Are subset and equality constraints dispensable?

jakob.voss — Fri, 27 Aug 2010 12:12:44 GMT

Models get hard to read if you use subset constraints and equality constraints between roles. Sure there are good reasons to use them, but I found that you do not need special symbols and line styles to depict them. Suptyping and subset constraints are the same concept - one for object types, the other for (combinations of) roles. So why not just mark the subset and equality symbols as deprecated and use one visual method instead of using two distinct diagram notations for nearly the same?

In addition we get another way to depict duplicated object types in a model (the both-sided arrow is implied if you use the same name for two object types).

P.S: I added the third row with splitted arrows later.

Reference Modes explained

jakob.voss — Mon, 09 Aug 2010 10:09:01 GMT

Reference modes are not described very deeply in Haplin and Morgan (2008) - there is an introduction to the different reference mode kinds (popular, general, and unit based) at page 85 and page 98, but the ORM syntax cheatsheet does not distinguish. I tried to unwrap the semantics of reference modes in a picture (source):

I also added a non-standard extension to implicitely specify a supertype similar to the syntax that implicitly defines a dimension supertype (unit-based reference modes). Please correct me if I am wrong or if the picture contains errors. As I understand reference modes, they just abbreviate binary 1-to-1 relationships that are used to uniquely identify an entity by a value.

Concept Maps

JohnBorowiec — Sun, 25 Apr 2010 13:35:01 GMT

Hey

Well it seems that ORM is quite similar to Concept Map. In ORM you only have to add constraints.

Why should people use / teach ORM (instead)?

Andy Carver — Mon, 16 Feb 2009 21:22:36 GMT

Today in the NORMA forum, Ken Evans responded to Andy Reiser regarding (among other things) a wish which he had expressed, that NORMA would get "picked up by universities". Ken answered with the below three questions about how we could argue for ORM being taught at universities:

" It would help us to help you if you could post your answers to the following questions:

...<snip>

Getting "picked up" at Universities: My recent experience shows that this is easier said than done.
On 5 January this year, I joined the University of Lincoln http://www.lincoln.ac.uk/home as a Senior Fellow.
For the last few weeks I have been discussing ORM with people in the University who I think "should" be interested.

Teaching ORM: I explained ORM to the instructor who teaches database management. But he seems to be wedded to teaching ER modeling and manual normalization. What would you say to him to change his mind?

Using ORM: The IT Director is also wedded to ER modeling and does not think that ORM has a role to play.
What would you say to him to change his mind?

Promoting ORM: On 3 March I will be giving a presentation about ORM to the whole of the 3rd year (all disciplines).
If you were giving this presentation, what are the key points that you would make to the students? "

I thought maybe this forum might be a good place to pursue these three questions. I also wanted to respond a bit to the first one:

To a teacher wedded to teaching ER and manual normalization. I'd say that manual normalization simply doesn't work: Not only does it make it "too hard" to carry normalization through to 5NF (5th Normal Form); the theory of 4NF and 5NF are actually wrong (see Terry Halpin's and my EMMSAD '08 paper on "Atomicity and Normalization", http://sunsite.informatik.rwth-aachen.de/Publications/CEUR-WS/Vol-337/paper4.pdf).

So Normalization Theory cannot actually take you beyond BCNF (Boyce-Codd Normal Form); only ORM will get you there, unless the schema you're working on happens to be already in some higher Normal Form. The normalization theory he's teaching is simply false, as well as ineffective.

Using data-type for rule modeling

Brian Nalewajek — Mon, 25 Aug 2008 15:02:46 GMT

Hi,

First, I'm putting this in the nORMa forums, as we do not yet have a forum for general ORM questions or comments (and nORMa was used to create the examples).

The question: Is it legitimate to use an assignment of a data-type to implement a rule, while modeling an application domain in Object Role Modeling?

This issue came out of contemplating a mention of "current" limitations of ORM rule modeling in a paper or article by Dr. Halpin. I've lost track of the source, but his comment used the example rule: An Employee's Salary can never be less than their starting Salary - or words to that effect. As I can't recall the source, I don't know when it was written, and don't know to what extent his comment is in line with current ORM capabilities. I believe the topic comes under Dynamic Constraints, and State Transition.

The first figure below shows a typical way to model starting salary and salary adjustments. You should find it matches the terms and general semantic implication of a typical (if incomplete) business UofD.

To model the no salary decrease rule, an alternative conception of the actual workings in the application domain is applied. The term "Starting Salary" is commonly used and commonly understood; so a Starting Salary of $50,000 seems an appropriate value (if it matches the pay level of the job in that market). However, an alternative view would have a starting salary of zero, that has an initial increase of $50,000 on hiring. At least in some ways, the later is a more accurate assessment of what actually happens. Be that as it may, the second form allows the modeling of the salary can not decrease rule - as is shown in the second figure below.

The key feature of the second model fragment is that the Value Object Type "Amount" is given an unsigned integer data-type assignment. Therefore, all salary adjustments are non-negative; and so salary can never decrease.

There are other aspects of a comparison of the two model fragments that are worth considering; but for now, the main question: Is the use of data-type assignment a legitimate method to use in modeling application domain rules in ORM?

Thanks,

BRN..

Verbalisations for unary facts (CWA, OWA and OWN)

mishad — Mon, 07 Sep 2009 19:53:14 GMT

What are the best verbalisations for unary facts (in NORMA or CQL or any other tool) in "schema" form -- i.e. verbalising the fact type, rather than verbalising specific fact instances?

Binary fact verbalisation extends nicely from fact instance verbalisation:

Person 'Marie' was born in Country 'FR'

Person 'Mike' lives in Country 'UK' ; Person 'Mike' lives in Country 'US'

Person 'Jeff' is domiciled for tax purposes in Country 'CH'

to fact type verbalisation including constraints:

Each Person was born in exactly one Country

Each Person lives in at least one Country

Each Person is domiciled for tax purposes in at most one Country

But for unary facts there are no constraints to qualify fact type verbalisation; this leaves the fact type verbalisations "plain":

fact instance: Person 'Jeff' is a smoker

fact type: Person is a smoker

This tends to confuse business people when validating the model -- they think it means "Each (every) Person is a smoker" rather than "facts of the form 'Person <X> is a smoker' may be asserted.

I was thinking of verbalisations such as "Person is (or is not) a smoker" but there are couple of problems with that:

i) there are practical difficulties caused by having to identify and correctly negate the verb to form the negative verbalisation -- this could be worked around with a little clumsiness (e.g. "Person is a smoker (or not)") and/or by having the modeler provide alternate readings (as we do for reverse readings on binaries)

ii) this leads directly into the issue of CWA vs OWA vs OWN and how to verbalise them.The "(or not)" verbalisation could represent CWA, for example

I've read IM&RDB 2ed [Halpin2008] and can't recall (or find in the index) any discussion of issues with unary verbalisation, or of how to verbalise OWA/OWN facts. Can anyone point me in the right direction?

Misha

ORM models and OO

PeterC — Mon, 06 Apr 2009 05:33:08 GMT

Hi,

In my mind, the conceptual model in ORM is modelling the "problem space", not the "solution space". Because ORM is aimed at being able to generate a (pure, i.e. 5NF?) relational database solution, this means that there are certain OO constructs, such as mutually exclusive supertypes, that are not supported.

Statement: When I want to conceptually model my "problem space", I may be limited in what I can model because there may be constructs (if that is the correct term) that I need that are not available.

Q1. Is the above Statement true or am I only limited on the form my model can take, not what I can model?

Q2. Has an analysis been done on the gap between <what ORM would be like if it supported OO> and <what it needs to support the relational paradigm>?

Q3. Just as there are constructs in ORM (such as some types of constraints) that do not automatically translate to DDL statements but must be manually written as procedures or code, is it reasonable to have ORM constructs that support OO - knowing that the constructs must be implemented either in code or perhaps in less than 3NF?

The reason I raise these questions is that given a "problem", I want to be able to model it free of the solution; and the constructs used in the model may (or may not) indicate whether an OO and/or relational solution is feasible or preferable.

Thanks, Peter