You have it backwards.
- The relationships in an E-R model are explicitly defined, while they
are implicit in a relational model.
No. Each Relational Model (RM) database base table and query result represents an application relationship. Entity-Relationship Modeling (E-RM) schemas are just a way of organizing (but under-using and under-specifying) (but with misunderstanding) relational tables and constraints.
- Relational models require an intermediate table (often called a "junction table") to hold two foreign keys that implement the
many-to-many relationship.
No. It is Object-Relational Mapping (ORM) approaches that obscure their underlying straightforward relational application relationships, tables and constraints. The notion of "junction table" arose from ORM misunderstandings of confused presentations of the E-RM which itself misunderstands the RM.
As C J Date put it An Introduction to Database Systems, 8th ed:
a charitable reading of [Chen's original paper] would suggest that the E/R model is indeed a data model, but one that is essentially just a thin layer on top of the basic relational model [p 426]
It is a sad comment on the state of the IT field that simple solutions
are popular even when they are too simple. [p 427]
The Relational Model
Every relational table represents an application relationship.
-- employee EID has name NAME and ...
E(EID,NAME,...)
The mathematical term for such a thing, and also for a mathematical ordered-tuple set representing one, is a "relation". Hence the "Relational Model" (and "Entity-Relationship Modeling"). In mathematics relations are frequently described by parameterized statement templates for which one mathematical term is "characteristic predicate". The parameters of the predicate are columns of the table. In the RM a DBA gives a predicate for each base table and users put the rows that make a true statement from column values and the predicate into the table and leave the rows that make a false statement out.
/* now also employee 717 has name 'Smith' and ...
AND employee 202 has name 'Doodle' and ...
*/
INSERT INTO E VALUES (EID,NAME,...)
(717,'Smith',...),(202,'Doodle',...)
A query expression also has a predicate built from the relation operators and logic operators (in conditions) in it. Its value also holds the rows that make its predicate true and leaves out the ones that make it false.
/* rows where
FOR SOME E.*, M.*,
EID = E.EID AND ... AND MID = M.MID
AND employee E.EID has name E.NAME and ...
AND manager M.MID has
AND E.DEPT = M.DEPT AND E.NAME = 'Smith'
/*
SELECT E.*, M.MID
FROM E JOIN M ON E.DEPT = M.DEPT
WHERE E.NAME = 'Smith'
Present rows of tables making true statements and absent rows making false statements is how we record about the application situation in the database and how we interpret what the database is saying about the application situation. One can't use or interpret the database without having and understanding the predicates ie application relationships.
Entity-Relationship Modeling
E-RM (which does not really understand the RM) is essentially a(n unnecessary, restricted and restrictive) diagramming notation for describing (some parts of) (limited forms of) relational databases. Originally there were "entity (class)" icons/relations where the candidate key (CK) values were 1:1 with application entities plus other columns ("properties" of the "entity") and there were "relationship (class)" icons/tables which had foreign keys (FKs) to entity tables representing application relationships on multiple entities plus other things ("properties" of the "association"). An application relationship was represented by an icon with lines to the various entity icons that participated in it. (Ie the lines represented FKs. Which are not relationships but statements about constraints on tables.)
E-RM doesn't understand the relational model. It makes a pointless and misleading distinction between application entities and relationships. After all, every superkey (unique column set) of every base table or query result is in 1:1 correspondence with some application entity, not just the ones that have entity tables. Eg people can be associated by being married; but each such association is 1:1 with an entity called a marriage. This leads to inadequate normalization and constraints, hence redundancy and loss of integrity. Or when those steps are adequately done it leads to the E-R diagram not actually describing the application, which is actually described by the relational database predicates, tables and constraints. Then the E-R diagram is both vague, redundant and wrong.
Shorthand E-RM and ORMs
A lot of presentations and products claiming to be E-RM warp the E-RM, let alone the RM. They use the word "relationship" to mean a FK constraint. This arises as follows. When an E-RM relationship is binary it is a symbol with two lines to its FKs. So those three things can be replaced by one line between FKs. This kind of line represents that particular binary relationship and its FKs but now the E-R relationship is not explicit in the diagram although the E-R relationship is explicit in the longhand version and it is reflected by a table in what the diagrams are pictures of, namely the relational database they are describing. This gets called a "junction table". And people talk about that line/table being/representing "an X:Y relationship" between entities and/or associations without actually ever noticing that it's a particular application relationship. And there can be many such application relationships between the same two entities and/or associations.
ORMs do this too but also replace n-ary associations by just their FKs so that the associated application relationship and table are further obscured. Active Records goes even further by defining several shorthand relationships and their tables at once, equivalent to a chain of FK lines and association icons in the longhand E-RM diagram. This is exacerbated by many modeling techniques, including versions of E-RM and ORMs, also thinking that application relationships can only be binary. Again, this arose historically from lack of understanding of the RM.
