Which normal form does the ER Diagram guarantee?

问题:

Whenever a proper ER diagram is drawn for a database and then mapped to the relational schema, I was informed that it guarantees 3NF.

Is this claim true?

If not, can anyone provide me a counter example.

Also, please tell me whether any normal form can be claimed to be strictly followed when relational schema is mapped from a perfect ER diagram?

回答1:

The short answer is no. Depending on the analysis and design approach there could be examples of ER models that appear perfectly sound in ER terms but don't necessarily translate to a relational schema in 3NF. ER modelling and notation is not really expressive enough or formal enough to guarantee that all functional dependencies are correctly enforced in database designs. Experienced database designers are conscious of this and apply other techniques to come up with the "proper" design.

Terry Halpin devised a formal method for database design that guarantees a relational schema satisfying 5th Normal Form (see orm.net). He uses the Object Role Modelling approach, not ER modelling.

回答2:

The diagram just shows what entities and attributes you have and how entities relate to one-another. Your attributes can violate the normal forms. An ER diagram is just a representation, it does not enforce any rules.

There is nothing about representing a model in an ER diagram that implies satisfaction of 3NF.

The thinking behind the erroneous claim may be based on the idea that when you, for example, convert a repeating group from columns to rows in a child table, or remove partially dependent columns to another table, you are increasing the normal form of your relations. However, the diagrammatic convention doesn't enforce this in any way.

回答3:

Let's see an example (in oracle):

CREATE TABLE STUDENT (
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR2(64) NOT NULL,
  RESIDENCE_STREET VARCHAR2(64),
  RESIDENCE_CITY VARCHAR2(64),
  RESIDENCE_PROVINCE VARCHAR2(64),
  RESIDENCE_POSTALCODE NUMBER(8)
);

In some countries postal code uses prefixes to identify the region or province, so RESIDENCE_PROVINCE has a functional dependency from RESIDENCE_POSTALCODE. But RESIDENCE_POSTALCODE is a non-prime attribute. Then this easy and common example is "legal" and it is not in 3NF.