E-R Model and Normalization…………
When we carefully define an E-R diagram, identifying all entities correctly, the tables
generated from the E-R diagram should not need further normalization. However,
there can be functional dependencies between attributes of an entity. For instance,
suppose an employee entity had attributes department-number and department-address,
and there is a functional dependency department-number → department-address. We
would then need to normalize the relation generated from employee.
Most examples of such dependencies arise out of poor E-R diagram design. In the
above example, if we did the E-R diagram correctly, we would have created a department
entity with attribute department-address and a relationship between employee and
department. Similarly, a relationship involving more than two entities may not be in a
desirable normal form. Since most relationships are binary, such cases are relatively
rare. (In fact, some E-R diagram variants actually make it difficult or impossible to
specify nonbinary relations.)
Functional dependencies can help us detect poor E-R design. If the generated relations
are not in desired normal form, the problem can be fixed in the E-R diagram.
That is, normalization can be done formally as part of data modeling. Alternatively,
normalization can be left to the designer’s intuition during E-R modeling, and can be
done formally on the relations generated from the E-R model.