Entity Modelling

www.entitymodelling.org - entity modelling introduced from first principles - relational database design theory and practice - dependent type theory

Double Diamond Example

In figure 7 we give an example logical ER model whose pattern of relationships1 follows a pattern of morphisms that is given in an exercise in category theory to students at Cambridge by Julia Goedecke2 and which can be used as a striking illustration of the inadequacy of taking a ‘one relationship at a time’ approach to achieve transformation of logical model into a physical model — an approach which Chen describes and which we provide an alternative for in our Relational Data Design section.

In this example ER model there are twelve relationships connecting eight entity types as the twelve edges of a cube connect its vertices. Each of the squares of relationships corresponding to the faces of the cube forms a commutative diagram; thus there are six in all corresponding to the six faces of a cube. In fact each such square is a pullback square and so with this example we also illustrate the occurrence of pullbacks in data modelling. Some of these commuting diagrams are scope diagrams and this gives us a form in which we can express them declaratively in an ER model. Other of the commuting diagrams can be expressed as key constraints. From the combined declarations of scope and of key constraint, a physical design which are redundancy free can be generated - in this way, using these insights from category theory, the normalisation stage is rendered unnecessary.

Figure 7
A physical hierarchical ER model illusrating the double-diamond pattern. Assume, as the narrative for this extended model, a situation where there are many tablular data tables and many different ways of presenting that data. All the multiple data tables and all the multiple presentations are described by the model. The starting point for this example is the example given in section Commutative Diagrams of a model of data presented in tabular form i.e in rows and columns. The right hand side diamond is just that earlier the physical hierarchical model with ascending relationships relabelled as ‘..’ (a familiar notation and one used for eaxmple in xpath).
Figure 8
1st Commutative Diagram - The right hand diamond representing many tables of data organised by row and column. Relationship R14 has this diagram as scope square - a fact that is represented by the equation ~/..=../.. where ~ represents the relationship R14 itself.
Figure 9
2nd Commutative Diagram - Many ways of presenting each table are modelled by the tablep. Each table presentation has as subject - the table that it is a presentation of. Within each table presentation there are many presentations of rows one for each row in the subject table. Each rowp has a subject which is rowp. The foregrounded relationships in this figure show the scope square of the row presentation subject relationship R4. This diagram is a pullback diagram: for every tablep and for every row of its subjecttable there is a unique rowp of the tablep having the row as subject.
Figure 10
3rd Commutative Diagram - The scope diagram for the columnpsubject relationship R6. As in the previous figure the foregrounded diagram is a pullback diagram.
Figure 11
4th Commutative Diagram - The scope diagram for the cellpsubject relationship R9. As in the previous figure the foregrounded diagram is a pullback diagram.
Figure 12
5th Commutative Diagram - This is a key constraint diagram for the reference relationship that relates a cellp to the columnp whose subject is the column its subjectdata cell is in. This diagram is a pullback diagram.
Figure 13
6th Commutative Diagram - Similar to the 1st Commutative diagram but at the presentation level. This is a scope diagram for the relationship associating a cellp to its associated columnp. This diagram is a pullback diagram.

the 1st, 2nd, 3rd, 4th and 6th diagrams are pullback diagrams by defintion. The fifth diagram is a pullback by implication. The former are scope constraints. the latter a key constraint.

1I first came across this pattern as a data modelling pattern in the implementation of an application supporting the analysis and review of chromatography data from laboratory based LCMS(MS) systems
2the student is asked to prove that ‘the pullback of a pullback square is a pullback’