Comment by throwaway7783
10 hours ago
Can you please elaborate on the differences? They are practically interchangeable, but conceptually there might be another layer on top of entities and relationships for somewhat richer semantics (like describing a relation, or additional annotations on the entity)
Aside from what else is mentioned in the sibling comment, inheritance is another big one. Inheritance is not explicit in SQL and in fact, when going from ER to SQL, there are multiple choices you can make about how to materialize the inheritance hierarchy.
Another is that in ER diagrams, relationships themselves can have attributes. Personally I think it tends to make more sense to convert relationships to entities in this case most of the time, but it can be useful.
Finally, relationships in ER diagrams can be N-ary and connect more than two entities while foreign keys in SQL always reference one other table. Of course you can have multiple foreign keys on a table to represent this, but not without some loss of semantics.
One example is that in ER cardinality is specified on the relation. In SQL cardinality is implemented and can be largely reversed to ER by looking at where foreign keys are.
Many to many will lead to an extra table (which can have additional properties, requiring this table to be modelled as an entity), one to many leads to the inclusion of a foreign key to another unique key (referring all columns of that key, _id is an implementation decision, compound primary keys are possible). One to one can be implemented in multiple ways, like one to many with a uniqueness constraint by the referring table or even by merging entities to a single table.
The raw SQL can be revealing but when entities have merged into one table it’s harder to tell what is what, unless a certain set of columns appears over different tables.