Eliminating Repeating Groups

From InterBase
Jump to: navigation, search

Go Up to IBConsole - Designing a Database


When a field in a given row contains more than one value for each occurrence of the primary key, then that group of data items is called a repeating group. This is a violation of the first normal form, which does not allow multi-valued attributes.

Refer to the DEPARTMENT table. For any occurrence of a given primary key, if a column can have more than one value, then this set of values is a repeating group. Therefore, the first row, where DEPT_NO = "100," contains a repeating group in the DEPT_LOCATIONS column.

DEPT_NO DEPARTMENT HEAD_DEPT BUDGET DEPT_LOCATION
100 Santa Cruz
600 Salinas
900 Francisco

In the next example, even if you change the attribute to represent only one location, for every occurrence of the primary key "100," all of the columns contain repeating information except for DEPT_LOCATION, so this is still a repeating group.

DEPT_NO DEPARTMENT HEAD_DEPT BUDGET DEPT_LOCATION
100
100 Cruz
600 Francisco
100

To normalize this table, we could eliminate the DEPT_LOCATION attribute from the DEPARTMENT table, and create another table called DEPT_LOCATIONS. We could then create a primary key that is a combination of DEPT_NO and DEPT_LOCATION. Now a distinct row exists for each location of the department, and we have eliminated the repeating groups.

DEPT_NO DEPT_LOCATION
100
100 Cruz
600 Frisco
100