Database Design Using Normalization - Database Processing: Fundamentals, Design, and Implementation - page 169

Consider another example for an employee parking application. Suppose the EMPLOYEE_
AUTO table includes basic employee data plus columns for license numbers for up to three
cars. The following is the typical table structure:

EMPLOYEE ( EmployeeNumber , EmployeeLastName, EmployeeFirstName, Email,
Auto1_LicenseNumber, Auto2_LicenseNumber, Auto3_LicenseNumber)

Other examples of this strategy are to store employees' children's names in columns such
as Child_1, Child_2, Child_3, and so forth, for as many children as the designer of the table
thinks appropriate, to store a picture of a house in a real estate application in columns labeled
Picture_1, Picture_2, Picture_3, and so forth.
Storing multiple values in this way is convenient, but it has two serious disadvantages. The
more obvious one is that the number of possible items is fixed. What if there are three contacts
at a particular vendor? Where do we put the third name if only columns VendorContact_1 and
VendorContact_2 are available? Or, if there are only three columns for child names, where do
we put the name of the fourth child? And so forth.
The second disadvantage occurs when querying the data. Suppose we have the following
EMPLOYEE table:

EMPLOYEE ( EmployeeNumber , EmployeeLastName, EmployeeFirstName, Email, Child_1,
Child_2, Child_3, . . . {other data})

Further, suppose we want to know the names of employees who have a child with the first
name Gretchen. If there are three child name columns as shown in our EMPLOYEE table, we
must write:

/* *** EXAMPLE CODE-DO NOT RUN *** */
/* *** SQL-Query-CH04-03 *** */
SELECT *
FROM EMPLOYEE

WHERE Child_1 = 'Gretchen'

OR Child_2 = 'Gretchen'

OR Child_3 = 'Gretchen';

Of course, if there are seven child names . . . well, you get the picture.
These problems can be eliminated by using a second table to store the multivalued attri-
bute. For the employee-child case, the tables are:

EMPLOYEE ( EmployeeNumber , EmployeeLastName, EmployeeFirstName,
Email, . . . {other data})
CHILD ( EmployeeNumber , ChildFirstName , . . . {other data})

Using this second structure, employees can have an unlimited number of children, and storage
space will be saved for employees who have no children at all. Additionally, to find all of the
employees who have a child named Gretchen, we can code:

/* *** EXAMPLE CODE-DO NOT RUN *** */
/* *** SQL-Query-CH04-04 *** */
SELECT *
FROM EMPLOYEE

WHERE EmployeeNumber IN

(SELECT EmployeeNumber

FROM CHILD

WHERE ChildFirstName = 'Gretchen');