The ORM Foundation

The model is loosely  based on an example from Koehler, H. Domination Normal Form - Decomposing Relational Database Schemas, 2007, §5:

A university has oral examinations at the end of each semester, and wants to manage related data using a relational database. The relevant attributes to be stored are

R = {Student, Course, Chapter, Time, Room}

Here Chapter denotes a chapter from the course textbook the student will be examined about. Every student can get examined about multiple chapters, and chapters may vary for each student. Multiple students can get examined at the same time in the same room, but the course must be the same. Further constraints are that a student gets examined for a course only once, and can’t be in multiple rooms at the same time. 

Attached you find an update on my model (diagram only), in which I have fixed a couple of mistakes and where the ambiguity is highlighted.

Thanks for posting your updated model.  However, it would be helpful if you could post the "equivalent" ORM file, because that would allow people to use the powerful features in NORMA to review your model.

. 

 Thanks for the orm file.

I think that it would be useful to split this discussion into two separate subjects.

(1) "Role paths in NORMA" and (2) "How to design an object-role model". 

Inter-predicate role-path editing is not yet implemented in NORMA for VS2017.

(2) Designing object-role models

You said that your model is "loosely based" on Kohler 2007.

I have briefly reviewed the paper,
In my opinion, the author has based his "Domination Normal Form" on some questionable assumptions. 

Here are two of them.

1:  The concept of a "universal relation" as being the start point for functional decomposition and normalisation.
This is just wrong!  
A conceptual model is based on a set of relations that are expressed as "atomic sentences" also known as "elementary facts".
(Yes I know that some professors do teach the concept of a "Universal relation" but the concept is flawed.) 

 2: The author's says that his aim is to define a "normal form" that "minimises storage space."
This idea is self-contradictory. Normal forms are logical constructs that are not related to physical implementation so it is wrong to conflate them.

 I'll return to your "loosely based" notion in a separate post.

Ken 

 "The model is loosely  based on an example from Koehler"

Kohler gives this example of a relation:  R ={Student, Course, Chapter, Time, Room}

Whilst this may be true in mathematical terms, it does not contain the ORM concept of using a predicate to define the roles played by each of the "attributes" in the relation which in my view makes it harder to understand.

Prof Jennifer Widom of Stanford University uses similar examples in her course on relational design.
https://www.youtube.com/playlist?list=PLroEs25KGvwzmvIxYHRhoGTz9w8LeXek0
07-01 Relational Design - Overview
07-02 Functional Dependencies 

I used Prof Widom's example to create an object-role model (see below)
I hope that you agree that the ORM approach is much simpler than the functional dependency procedure described by Prof Widom.
(Especially since NORMA gives you an "instant" relational schema in 5NF)

ORM makes you think in facts, whereas functional dependency makes you think about attributes rather than the roles that they play with respect to each other.. 

 Nicola,

I posted a question about the Kohler paper in the "database experts" group on LinkedIn.

Fabian Pascal confirmed my own view that logical models are not related to physical models. 

This is what Fabian said; 

One of the most common and entrenched misconceptions is the logical-physical confusion (LPC) (another is the conceptual-logical conflation (CLC)). Because people don't understand the RDM, they don't understand levels of representation, data independence, and lots more.

In other words, Kohler based his paper on false beliefs and "Domination Normal Form" does not exist..

Fabian is an expert on relational databases and has written several excellent books on the subject such as the two I have referenced below:

Ken

---------------- 

Pascal, F. (2000). Practical Issues in Database Management - A Reference for the Thinking Practitioner., Addison Wesley.

Pascal, F. (2015). Business Modeling for Database Design. Formalizing the Informal. online, Database Debunkings.

More at http://www.dbdebunk.com/

Inter-predicate role-path editing is not yet implemented in NORMA for VS2017.

Thanks, that's what I needed to know.

Re the data model: I have simply taken the requirements described in that paper as a basis for a data modeling exercise. I said “loosely” because the example is contrived (e.g., it does not talk about books, enrollments, etc…), so I have expanded the requirements a bit. Neither my question nor the data model have anything to do with the main topic or the merits of the paper.

That said, yes, your remarks are up to the point (your view of a “conceptual model” is obviously biased towards a fact-oriented view: not all “conceptual models” are).

Inter-predicate role-path editing is not yet implemented in NORMA for VS2017.

Yesterday,  Terry added some clarification to the role path editing issue. This is the relevant part of what he said.

NORMA for VS2017 automatically infers the join path for unambiguous cases (e.g. Figure 10.4 of the "Big Brown Book", and in your example, if Exam and Session play no other roles that could be involved in the join path for your subset constraint).

However, where the join path is ambiguous (i.e. there are many possible join paths for the constraint), one needs to specify the intended join path explicitly. 

The ability "to specify the intended join path explicitly" requires that we add a "Role Path Editor" to NORMA for VS 2017.

So, the task of adding a "Role Path Editor" to NORMA for VS2017 is on my "TO DO" list.

It would be very helpful if you could provide some detail about what you would like a Role Path Editor to do and how you would like it to work.

Thanks

Ken 

Nicola,

"your view of a “conceptual model” is obviously biased towards a fact-oriented view: not all “conceptual models” are"

In this website, we use the term "conceptual model" within the universe of discourse called "object-role modeling" as defined by Terry Halpin in his 1989 thesis and in many subsequent papers, books and discussions.

This is a matter of making correct use of the terms that exist within a well defined universe of discourse which has nothing whatsoever to do with "bias".

Ken

I used Prof Widom's example to create an object-role model (see below). I hope that you agree that the ORM approach is much simpler than the functional dependency procedure described by Prof Widom. (Especially since NORMA gives you and "instant" relational schema in 5NF)

Glossing over the fact that your model does not capture the requirements described in those videos (that is not important in the context of the present discussion), certainly the generated “Relational View” is not even in 2NF (that's a consequence of a defect in your ORM model, i.e., redundant facts).

I cannot comment on whether or how much ORM is better than something else, because I have not used it much yet. One thing I can say so far is that I am disappointed (but not surprised) by the ORM-to-Relational mapping performed by NORMA. This is a topic for a different thread, though.

However, where the join path is ambiguous (i.e. there are many possible join paths for the constraint), one needs to specify the intended join path explicitly.

Sure.

The task of adding a "Role Path Editor" to NORMA for VS2017 is on my "TO DO" list.

Great! 

It would be very helpful if you could provide some detail about what you would like a Role Path Editor to do and how you would like it to work. 

I haven't thought much about it (if you haven't come out with a solution in ten years, I guess that it is not a trivial issue), but I think that what is missing is the possibility to “project” a path to some roles. Ideally, this should work automatically if possible, i.e., after one has chosen the “source” roles 1,2,3,…,N along a path and the “target” roles A,B for a certain constraint, NORMA should be able to project the join of 1,2,…,N to A,B if there is only one role among 1,…,N which is compatible with A and only one role which is compatible with B. If automatic inference is not possible then there should be a mechanism to associate each target role with one of the source roles in the join path.

In this website, we use the term "conceptual model" within the universe of discourse called "object-role modeling"

Just to be clear, I was not passing judgment, just stressing that your definition is scoped, as you have pointed out. 

Nicola,

ORM is better: The point about "ORM being better than anything else"  is in part related to the fact that NORMA uses RMAP to instantly create a 5NF logical model. This is "better" than alternative methods such as ERM because it saves a great deal of time.
This was the subject of my 2007 MSc project which is outlined on this page. 

RMAP: Regarding your concern about RMAP, here is the separate thread that you suggested would be useful.
Please use the separate thread to tell us about your concerns with RMAP and its implementation in NORMA.

The Role-Path-Editor (RPE): There is an operational version of a role path editor in a version of NORMA that has been in limited use for several years. The earlier version is private and it does not run under VS2017. 
So my point is that the RPE is not yet implemented in NORMA for VS2017.

Prof Widom's approach: My aims was highlight the relative effectiveness of functional dependency analysis and ORM. 

Not even in 2NF: Please explain the reasoning behind your claim. Which of the fact types are "redundant" ? (probably best to use a separate thread for this. I suggest that you start a separate thread within this main section "contributed models".  

Ken 

The point about "ORM being better than anything else"  is in part related to the fact that NORMA uses RMAP to instantly create a 5NF logical model

I can certainly appreciate that ORM is more formal and more expressive than ER, and has great tooling support (otherwise, I wouldn't be in this forum). But the fact that it can (instantly) generate a bunch of 5NF tables does not mean anything. One can design an unlimited number of 5NF data models that do not make any sense.

Not even in 2NF: Please explain the reasoning behind your claim. 

Look at the (valid) instance of your model attached below. Does it make sense?

Nicola,

I may be wrong, but you seem to be conflating three separate things:  (1) The way the RMAP algorithm works. (2) The way RMAP is implemented in NORMA. and (3) The scope of the model.
We agreed to handle (1) & (2) in a separate thread and I see that you have already made some comments there.

With respect to (3), You claimed that my model contains redundant fact types so please could you tell me which of the fact types in my model you think are "redundant"? 

I use the tool Snagit to mark up diagrams so I have attached an example that indicates some fact types that could be removed without losing information. As I see it, my original model would generate a base table for "Application" whereas the "edited" model would require a view to be created and used to extract the relevant information. Please comment on this. Can you add to this?

RMAP implementation: Your comments about the way NORMA works are useful. 
It may help you to know that NORMA is maintained by volunteers. It  contains about 2 million lines of code that have been contributed by multiple authors since the year 2005 so it would not surprise me to find that there may be some errors in the code. However, let's discuss that in a separate thread. 

Ken

With respect to (3), You claimed that my model contains redundant fact types so please could you tell me which of the fact types in my model you think are "redundant"?

It's the ones highlighted in the picture. If you keep them, you must add external equality constraints (e.g., if some Student has a GPA and that Student applies to some College, then that Application contains the same GPA).

As I see it, my original model would generate a base table for "Application" whereas the "edited" model would require a view to be created and used to extract the relevant information. Please comment on this. Can you add to this?

Well, of course the “edited” model is the right one. You can recover an Application's data by joining with Student. Your original model has duplicated data and is subject to update anomalies.