You are keeping personnel records on employees. For each employee, you want the database to store first name, last name, home address information, home phone number, office address information, and work phone number/extension. Each employee works at a single branch office. Everybody in a branch office has the same office address information and work phone number, except for a phone extension of up to 4 digits. You also want to store the names of the branch offices.
Each employee is assigned to one health plan. For each health plan, you want to store its name and a monthly premium. Each health plan is offered by a health plan provider, which has a name, address information, and a phone number. Some providers offer more than one plan; for example, Blue Cross offers a traditional plan, a PPO plan, and an HMO plan.
Assume that you have a national zip code data table available.
Design a normalized database to store all this information.