Avery’s Gym: Entity Relationship Diagram (ERD), database normalization and denormalization
Avery’s Gym has partnered with a physical therapy business, offering special sessions for gym members. Develop an ERD for this partnership and explain the significance of normalization and denormalization, using APA paragraph formatting for written portions.
- Normalize the data: organize the information into data fields suitable for a relational database; analyze the data and create a database model, considering unique IDs for primary keys.
- Construct an ERD: use Visio; identify primary and foreign keys and thorough attributes for each entity; use crow’s feet to show relationships.
- Data fields: physical therapy session, session date, session time, session details/purpose; member name, address, phone, age, email; insurance number, insurance name, insurance address, insurance plan details; trainer name, phone, email, specialty; therapist name, phone, email, specialty; insurance claim number, claim cost.
- Explain normalization (minimum 120 words): summarize the steps for 1NF, 2NF and 3NF and illustrate the importance of normalization for data integrity, minimizing redundancy and efficient retrieval, with real-world examples.
- Explain denormalization (minimum 60 words): describe it, its main reasons and scenarios where it is beneficial.
MEMBER, THERAPIST, TRAINER
PT_SESSION
INSURANCE_COMPANY, INSURANCE_PLAN, MEMBER_INSURANCE
INSURANCE_CLAIM
Model ERD
Reading the crow’s feet: two short bars mean “one and only one,” a circle with a bar means “zero or one,” a circle with a crow’s foot means “zero or many” and a bar with a crow’s foot means “one or many.” For example, one member can attend zero or many therapy sessions, but each session belongs to exactly one member.
Model answer
Original example for study purposes (about 820 words). The design reflects reasonable assumptions; explain any assumptions you make in your own submission.
Database Model and Entities
The original list of fields mixes information about members, staff, sessions, insurance and claims in a single record. Storing it that way would repeat a member’s address and insurance company details on every session and claim. The normalized model separates the data into eight entities, each with a surrogate primary key:
| Entity | Primary key | Foreign keys | Key attributes |
|---|---|---|---|
| MEMBER | member_id | None | first_name, last_name, street_address, city, state, zip_code, phone, email, date_of_birth |
| THERAPIST | therapist_id | None | first_name, last_name, phone, email, specialty, license_number |
| TRAINER | trainer_id | None | first_name, last_name, phone, email, specialty |
| PT_SESSION | session_id | member_id, therapist_id, trainer_id (optional) | session_date, session_time, session_details |
| INSURANCE_COMPANY | company_id | None | company_name, street_address, city, state, zip_code, phone |
| INSURANCE_PLAN | plan_id | company_id | plan_name, plan_details |
| MEMBER_INSURANCE | member_insurance_id | member_id, plan_id | insurance_number (member’s policy ID), effective_date |
| INSURANCE_CLAIM | claim_number | session_id, member_insurance_id | claim_cost, claim_date, claim_status |
Design decisions. Names and addresses are split into atomic fields so that the database can sort, search and validate them. Member age is replaced with date_of_birth, because age changes every year and can be calculated when needed; storing age directly would quickly produce inaccurate data. Insurance information is divided into three tables: the insurance company (name and address), the plan it offers (plan details) and the member’s coverage (the member’s insurance or policy number and plan). This allows one company to offer many plans and one member to have more than one plan over time. Each session is conducted by one therapist and may optionally include a trainer, and each session may generate at most one insurance claim, which is billed to a specific member’s coverage. Specialty is stored as an attribute in this model; if therapists and trainers can have multiple specialties, a separate SPECIALTY table with a linking table would be added.
Relationships. One member attends zero or many sessions; one therapist conducts zero or many sessions; one trainer assists zero or many sessions, and a session has zero or one trainer; one insurance company offers one or many plans; one plan covers zero or many member insurance records; one member holds zero or many insurance records; one session is billed as zero or one claim; and one member insurance record is billed for zero or many claims.
The Importance of Normalization
Normalization is the process of organizing data into tables in a way that reduces redundancy and protects data integrity. It is achieved in steps called normal forms. To reach first normal form (1NF), each table must have a primary key, each field must contain a single, atomic value and there can be no repeating groups; for example, a member’s name is divided into first and last names, and multiple sessions are stored as separate rows rather than in a list within one record. To reach second normal form (2NF), the table must be in 1NF and every non-key attribute must depend on the whole primary key, not just part of a composite key; for instance, therapist contact information depends only on the therapist, not on the session, so it moves to its own THERAPIST table. To reach third normal form (3NF), the table must be in 2NF and contain no transitive dependencies, meaning non-key attributes depend only on the key; the insurance company’s address depends on the company, not on the member, so it is stored once in INSURANCE_COMPANY.
Normalization matters because it maintains data integrity: if an insurance company moves, its address is updated in one place rather than on hundreds of claims, preventing conflicting records. It minimizes redundancy, saving storage and reducing the risk of update, insertion and deletion anomalies; for example, a new therapist can be added before she has any sessions, and deleting a canceled session does not erase the member’s information. It also supports efficient and accurate data retrieval, because clearly defined relationships allow queries such as “total claim costs by insurance plan this month” or “all sessions for a member” to return consistent results. In real-world health and fitness settings, these benefits help ensure accurate billing and support privacy and compliance requirements for health information.
Denormalization
Denormalization is the deliberate addition of redundancy to a normalized database, such as combining tables or storing calculated values, to improve read performance or simplify queries. Its main reasons are speed and simplicity: highly normalized designs can require many joins, which may slow reports on large data sets. Denormalization is beneficial in reporting and analytics, such as a data warehouse table that stores each session together with the member name, therapist name, plan name and claim cost so that managers can generate monthly dashboards quickly. It can also help when a frequently used value, such as a member’s total claims for the year, is expensive to calculate repeatedly. The trade-off is that redundant data must be kept synchronized, usually through scheduled updates or triggers, so denormalization is best used for read-heavy reporting rather than for the transactional system that records sessions and claims.
Reference
[Your course textbook on database design, in APA format.]
Before you submit: build the diagram in Visio using the Crow’s Foot Database Notation template, add data types if your instructor requires them and cite your course text for the definitions of 1NF, 2NF and 3NF.
Frequently asked questions
Why use date of birth instead of age?
Age changes over time and would become inaccurate without constant updates. Storing date of birth lets the system calculate the current age whenever it is needed.
Why separate the insurance company from the insurance plan?
One company offers many plans, and its name and address would otherwise repeat for every plan and member. Separating them removes a transitive dependency and supports 3NF.
Related reading: concurrent access and data integrity in databases.
More model assignments posted today
- CJUS 801 evaluability assessment (drug court)
- NRNP 6539 AI case vs. clinical guideline
- Serial killer analysis: Jeffrey Dahmer
- Profiling an alleyway homicide