Question

Foothills Athletics is an athletic facility offering services in the greater Highlands Ranch, Colorado, area. All property owners living in Highlands Ranch are members of the Recreation Function of the Highlands Ranch Community Association (HRCA). Foothills Athletics consists of a recreation facility where residents have the opportunity to participate in athletic activi- ties, enroll their children in day camp or preschool, or participate in an HRCA program.
Personnel: Foothills Athletics has a number of employees, primarily fitness course in- structors and administrative personnel (e.g., billing clerks, equipment managers, etc.). Records are kept on each employee, past and present, detailing employee name, address, phone number, date of hire, position, and status as either a current or former employee. Employees are assigned a unique four-digit Employee ID number when they are hired.
Members: When joining the Foothills Athletic center, individuals are assigned a unique four-digit Member ID number. This information along with their name, address, phone number, gender, birth date, and date of membership are recorded. At the time of enroll- ment, each member decides on one of three available membership types along with a fixed membership fee: Platinum ($400), Gold ($300), and Silver ($200). This is a one-time fee that establishes a lifetime membership.
Facilities and Equipment: Foothills Athletics has a variety of facilities and equipment choices. Each facility has a unique room number and a size limitation associated with it. Some of the rooms contain pieces of exercise equipment; all have a serial number (pro- vided by its manufacturer) that is used for inventory purposes. In addition, for each piece of equipment, purchase date and the date of its last maintenance are recorded. Each piece of equipment belongs to a specific equipment type, such as stair master machine, and is assigned a unique three-digit identification number. The description, the manufacturer’s model number, and the recommended maintenance interval for that model of equipment are also kept on file. Each equipment type is associated with a single manufacturer that is referenced by a unique two-digit manufacturer ID number. Additional information main- tained on each manufacturer is the company name, address, and phone number.
The Task: You have been hired to assist Foothills Athletics with creating a database structure that will incorporate all the features and business rules mentioned above. You should start out developing an ERD and then proceed to create a normalization structure in 3NF.

Answer 1

Next we start with the 1Normal form where all the data fields are in a single table, We move on to break the data into multiple tables based on the unique keys they have as shown below.