I got confused when someone spoke of relational models when I asked a question about EER models. I've learned about the difference between ER and EER diagrams, but I'd like to understand the whole modelling process... I know EER are enhanced ER models, i.e. ER models with specialization/generalization.
When someone says ER modelling does he also imply EER modelling?
And what about database normalization? Does that only apply to relational diagrams?
The ER model was originally introduced in 1976 by Peter Chen, although it was influenced by earlier work. In the early 1980s it was almost exclusively used to model data at the conceptual level, where its principal value was that it was unbiased with regard to implementation. While it was, and remains, very easy to convert an ER model into a relational model, the ER model has also been seen as useful in some cases where the final implementation was to be some kind of pre-relational DBMS like IMS. It has also been used in a preliminary stage in projects where the final implementation was to be in some kind of unstructured or post-relational DBMS, or an Object Database.
A great many practitioners merge ER modeling and relational modeling, and come up with a single model that serves both purposes. While the two models have a lot of overlap, the differences are important enough so that merging the two of them waters them both down. This merging is most visible when it comes to ER diagrams. Many, perhaps most, of the so called ER diagrams are really relational models, even if they use ER diagramming conventions.
In the Wikipedia article on ER, it mentions the classic three layers: conceptual, logical, and physical, and treats them as all variants on the ER model. That's not how it was in the 1980s. The ER model was conceptual. The logical model was relational, provided the final target was to be a relational database. The physical level was DBMS specific, and tried to meet performance and volume goals as well as the more abstract goals of the logical and conceptual levels.
All this may be ancient history, or even pre-history in the world of IT, which is forever young.
The biggest differences are that foreign keys are not present in an ER model. Relationships are visible in an ER model, but ER is silent on how they are to be implemented. Foreign keys are just one way to implement relationships. In a relational database, they are the only way that makes sense. ER also models many-to-many relationships directly, without putting another entity in the middle. Relational models require an intermediate table (often called a "junction box") to hold two foreign keys that implement the many-to-many relationship.
The enhancements that are included in the EER consist mainly in adding gen-spec (superclass/subclass) and unions to the modelling conventions. These are nearly universally part of ER by now, so the term EER is really a historical accident.
Normalization as originally developed is properly part of relational database design. It can't really be applied in non-relational situations, without substantially messing around with the normal forms (1NF through 5NF and DKNF). Properly speaking, normalization is irrelevant in ER modeling. However, there is a modelling error that's easy to make In ER modelling that almost always correlates with normalization errors at the logical level: it's associating an attribute with the wrong entity, or conflating two distinct attributes into a single one.
I could go on, but this is already too long.
