A new, formal, foundation for the Entity/(Attribute/)Relationship (ER) Model is proposed. In general, it is difficult to justify the need for a new foundation of a well-established model: can there be anything seriously wrong with the current foundation? It is perhaps even more difficult to demonstrate the value of a proposed foundation, for the payoff of such a theory is not simple and single, but complex and various.

What is wrong with the ER model? In practical terms, not much. Although many of us analyse and design for Relational database management systems, we use the ER model for our analysis, and for our interpretations of the relational structures: tables, rows, and columns. It works very well. But some of us have serious doubts, not of what we are doing, but of what we say about what we are doing, and especially of what we say when we are teaching how to do it.

We are rather like those extraordinary teachers of singing. They are, no doubt, good singers; and more than competent teachers. But listen to them when they are teaching: When you sing; sing from the forehead. When you breathe; breathe from the hips. This may be effective advice; given their results it presumably is effective. But as a theory of singing it leaves something to be desired. And we really do say equally extraordinary things.

What sort of problems do we have?

We are hard put to say what we mean by our terms of art: entity, attribute, relationship. Each record (row in a table) is to represent an entity or relationship (instance). But, in general, x can represent an E, without there being anything, y, such that x represents y, and y is an E. The noun phrase, Moonwind the Unicorn, represents a unicorn; but (alas) there is no unicorn that it represents. Likewise “the enterprise is interested in an E” does not imply that there is an E that they are interested in: they want to employ a clerk, but it does not follow that there is a clerk that they want to employ. We are tempted to say, “Not a specific clerk”, and somehow we understand that: but there are no non-specific clerks about; any attempt to employ such an indeterminate creature will be vain. Yet an entity is supposed to be an existent. Also, an attribute is intended to be, not an entity, but a “property” of an entity: but why should a record not represent such a property? why should the enterprise not be interested in such a property? why should such a property not exist? So are attributes really distinct from entities? In analysis we readily redefine entities as attributes; and relationships as entities: what are we doing (if anything) other than merely designing records?

We may begin analyses with unproblematic entities: persons, houses, cars. Plausibly, a person is an existent and a person record represents a person. But we soon move on to vacancies: a vacancy record represents a vacancy, but is a vacancy an existent? Or to invoices: an invoice (record) does not represent an invoice, nor a demand for payment: it is an invoice; it is a demand for payment (and copying it from a magnetic medium to paper does not make it something different). It makes sense to say, “Is there really a person represented by this registration?” but not to say, “Is there really a demand for payment represented by this invoice?”
 

If a user asks an analyst to interpret a record (that “represents an entity”), the analyst gives an explanation of what the record means, in English (say). Yet, strictly, the analyst has failed to give the interpretation if they do not produce the person or the house (or the vacancy?) that – according to a straightforward understanding of the ER model – is represented by the record. But if the analyst can give an interpretation in words, why should we not say: it is the words that constitute the record’s interpretation? Why need we go any further, and insist on having a (more or less dubious) entity, existing in the “real world”, and represented by the record?

But suppose we can, ignoring all these quibbles, interpret each base record (each row in each stored table) as an entity (or a relationship). Now, using a data manipulation language (DML) we construct other records – by restriction, projection, and joins. How do we derive the interpretations of these records? How do we know what entities (or relationships) they represent? If a PERSON table has columns PERSON_ID, SURNAME, and RELIGION, and we join it to itself on equality of RELIGION value, and project the distinct SURNAME columns: what entity does each row represent? or is it a relationship, and – if so – between what entities?

Oddly enough, before we had the ER model, we had a standard form of interpretation of records. With the introduction of the Relational model came (although it was rarely made explicit) an interpretation of normal records and of their manipulations in a DML that was – slightly sugared – the first order predicate calculus (FOPC). The interpretation is that of the Domain Relational Calculus. A relation, in the logician’s sense, is a predicate, of one or more places; for instance:

... is father of ...

Abraham is father of Isaac.

Abraham is father of Isaac; Isaac is father of Jacob; Isaac is father of Esau; ...

FATHERHOOD:    FATHER   PERSON
 

               Abraham  Isaac

               Isaac    Jacob

               Isaac    Esau

               ...      ...

... is father of ..., AND the former is Isaac.

x is father of y, AND x is Isaac.

x is father of y AND x´ is father of y´ .

$y x is father of y
 

(In English, “... is father of someone”, or, “... is a father”. Notice that only the unbound variables – those that are not quantified – represent columns, places of the predicate.)

$x´ x is father of y and y = x´ and x´ is father of y´ (or equivalently, x is father of y and y is father of y´ ).

$y x is father of y and y is father of y´ .
 

(In English: ... is paternal grandfather of ....)

SUPPLY:    SUPPLIER   PART
 

           S1         P1

           S2         P1

           S2         P2

           ...        ...
 
 

USE:    PART   PROJECT
 

        P1     J1

        P2     J1

        P1     J2

        ...    ...

(SUPPLY)   x supplies y.
 

(USE)        y´ is used in z.

$y $y´ x supplies y and y = y´ and y´ is used in z.
 

or $y x supplies y and y is used in z.

Alas, the FOPC, the foundation of this interpretation, requires that the names inserted in the places of predicates be proper names. But “P1” is a common name (like, say, “dog” – a perfectly good name, but it designates each of many objects). The system (the data, as shown) distinguishes two different things, both named “P1” (the one supplied by S1, the other by S2): so “P1” is not a proper name.

We do not generally ensure that all the values we insert in databases are interpretable as proper names. So we always have potential join traps, most of which are avoided – quite informally – by users who understand their application domain. In the relational model, a join trap is not a misinterpretation by a user: it is the failure of a user to reject the misleading interpretation that follows from the interpretation of the model. This does not bode well for intelligent (i.e. stupider than human) front-ends.

A row of a table, in the ER model (assuming that relational structures, but not the interpretations, are used), is said to “represent”, or “stand for”, an entity (a thing? an existent?) in “the real world”; or (in the case of some tables) a relationship between two (or, in some versions of the ER model, two or more) entities. The columns hold attributes of the entity, or identifiers of the related entities (and, if attribute-bearing relationships are allowed, attributes of the relationship).

We can understand restriction well enough: it gives us some of the entities or relationships that we started with. But what entity or relationship does projection give? What entity or relationship does Cartesian product, natural join, or composition give? Consider that projection of the Surnames of the join on Religion of PERSON with itself:

P1.SURNAME   P2.SURNAME
 

Robinson     Robinson

Smith        Smith

Smith        Thomas

Thomas       Smith

Thomas       Thomas

Most neglected of all the fields on our Entity Definition forms is: Entity Description. Let us replace it by “Entity Definition”. What does it take to define an entity? We certainly need a criterion of application. When we know the criterion of application of a concept, e.g. “cat” or “moon” or “book”, we know when we have got one (i.e. when we have spotted a cat, or seen the moon, or found a book – when we have got one and not none). A criterion of application of “book” might include talking books, but exclude graphic novels, magazines, and newspapers. Sometimes a criterion of application is obvious (but we must be careful: is a lion a “cat”?) A criterion of application decreases the vagueness of a concept; but is it enough to define the concept? Imagine a child that said “cat” every time it saw a cat (and at no other time), and likewise said “moon” when it saw the moon. We are tempted to say: the child has grasped the concepts, “cat” and “moon”. But suppose the child thinks that there is only one cat, with an extensive wardrobe of fur coats; and that there are many moons – round, half-round, crescent; golden, silver, blue perhaps.

So a criterion of application is not enough: we also need a criterion of identity. This tells us what counts as the same cat, or the same moon. It tells us when we have got one (i.e. one and not two); it removes, not vagueness, but ambiguity. Is my copy of “Persuasion” the same book as your copy of “Persuasion”? When we do data analysis we (formally or informally) postulate criteria of identity (they are largely undefined in natural languages).

The use of these criteria of definition – of application and identity – shows considerable promise in the teaching and performance of ER analysis. For example, the dependence of an order line entity on its order entity (a many-to-one relationship with cascade delete) is explicable in terms of their criteria of identity. If I pick out an order line and you pick out an order line, my order line is the same order line as yours only if the order containing my order line is the same order as the order containing yours. The criterion of identity of “order line” involves, or is dependent on, the criterion of identity of “order”: it is this dependence of identity that causes the dependence of entity (of existence); hence the cascade delete – but the dependence would still hold even if no deletes were allowed. In a subtype (“ISA”) relationship, the supertype and subtype entities have the same criterion of identity, but different criteria of application: to be a person is not to be a house owner; but if A is a person that owns a house, and so is B, then A is the same person as B if and only if A is the same house owner as B.