A database is a collection of relations. A relation is a logical data structure that can be represented by the metaphor of two dimensional tables and which encapsulates the required semantic content.

Why Normalize?

Maximize query flexibility. Eliminate insertion, deletion, and modification anomalies. Examples: insertion anomaly - canŐt add new faculty member without adding courses he will teach and students that will take those courses. Deletion anomaly - delete a course, delete a faculty member. update anomaly - canŐt change employee address without changing zipcodes.

Classical symptoms of un-normalized database: repeating groups [i.e. not a table], some attributes FD on other non-key attributes, some key attributes FD on other key attributes, some non-key attributes FD on part of the key structure, and the existence of MVD.

JamalŐs Normalization Method

The synthesis method of normalization uses the idea of ÔelementalŐ tables. First list all the entities and their attributes. Next select the attributes one at a time and ask ŇWhat do I need to know to be able to identify an instance of this entity type?Ó. Then pick those attributes from the attribute list and construct the key for that one single non-key (or partial key) attribute. Put the two (key and one non-key attribute) into an elemental table. Then go on to the next attribute and keep constructing elemental tables until all attributes have been accounted for.

Next, delete relations that are semantically redundant and collapse relations that have exactly the same key structure. The result will be a set of tables that are in the domain key normal form (DKNF).

Example of JamalŐs Normalization Method

Consider ChenŐs famous example (Chen, P., ACM TODS, vol. 1, no. 1)

EMPLOYEES (SSN, NAME,AGE,DEPT,BUDGET,PROJECT,PROJNAME,PCTIME)

The functional dependencies are described in the paper. There are no MVD in this case. First we produce the elemental tables as follows:

ER7 contains all the information in ER4. Therefore ER4 is semantically redundant and is dropped. ER1, ER2, and ER3 contain identical keys and have no MVD. They are collapsed into a single table. The resultant DKNF tables are: