Question

You handle administrative support for CyberToys, a small chain that sells computer hardware and software and specializes in personal service. The company has four stores located in malls and is planning more. Each store has a manager, a technician, and between one and four sales reps.Bruce and Marcia Berns, the owners, want to create a personnel records database, and they asked you to review a table that Marcia designed. She suggested fields for store number, location, store telephone, manager name, and manager home telephone. She also wants fields for technician name and technician home telephone and fields for up to four sales rep names and sales rep home telephones.

Draw Marcia’s suggested design and analyze it using the normalization concepts discussed in the chapter.

What do you think of Marcia’s design and why?

What changes would you propose?

Answer 1

The probable issue can be seen as the record design of Marcia might not be normalized. On the basis of the fact statements presented in the case, a record of Marcia can be given as below:-

PERSONNEL (STORE NO, LOCATION, STORE PHONE, MANAGER NAME, MANAGER TELEPHONE, TECHNICIAN NAME, TECHNICIAN HOME TELEPHONE (SALES REP NAME, SALES REP TELEPHONE)).

This record design can be seen as unnormalized. This is mainly due to the presence of a repeating group. The primary key which is used for every entity must be a unique identifier, for example, a certain number. A primary key should not include any name as there can be a number of individuals having similar names. Thus for every manager, a certain MANAGER NO, for every technician a unique TECHNICIAN NO and for every sales representative, there must be a SALES REP NO.