IM 310 Week 3 Relational Schemas and Normalization Example

From One Messy Spreadsheet to Clean Tables: Normalizing a Veterinary Group's Wellness Plan Data

[Student Name]

University of Phoenix

IM/310: Data Analytics & Modeling

Week 3 Assignment

[Instructor Name]

[Date]

Lone Star Pet Health, its clinics, data and figures are composites written for a model paper.

Week 2 built conceptual and logical data models for Lone Star's fourteen clinics. This paper turns part of that model into relational tables, using the group's wellness plan data as the example. Wellness plans, annual packages of exams, vaccines and dental care paid monthly, are a growing business for the group, with about 3,800 active plans. They are tracked in one shared spreadsheet that has become unreliable.

The Relational Model

Codd (1970) proposed the relational model, in which data are organized as relations, tables of rows and columns, and accessed by their values rather than by the physical paths through which they are stored. He argued that this separation protects users from changes in storage and that relations should be normalized to avoid redundancy and inconsistency. Every relational table needs a primary key, an attribute or set of attributes whose values uniquely identify each row, and relationships between tables are represented by foreign keys, attributes in one table that refer to the primary key of another.

The Starting Spreadsheet

The wellness plan spreadsheet has one row per plan with these columns: client name, client phone, client address, pet name, species, breed, plan type, included services, monthly price, start date, clinic name and clinic phone. Included services sit in a single cell, such as "2 exams; rabies; DHPP; dental cleaning." There is no identifier for clients or pets.

Anomalies

The spreadsheet shows all three classic anomalies.

Update anomaly. When the price of the canine adult plan rose from $39 to $42 a month, a manager had to change 1,140 rows; 63 were missed, so some members were billed the old price.

Insertion anomaly. A new plan type for senior cats could not be recorded until a client signed up for it, since every row requires a client and pet.

Deletion anomaly. When the last client on a discontinued plan canceled, deleting the row erased the only record of that plan's services and price.

Duplication also creates inconsistency. One client with three dogs appears on three rows, with two different phone numbers, because front desk staff updated only one row when she changed numbers.

Functional Dependencies

Kent (1983) explained the normal forms in plain language and showed that each rests on functional dependencies; his summary is that every fact in a table should be about the key, all of the key and only the key. In the spreadsheet, a client's phone and address depend on the client, not the plan; a pet's species and breed depend on the pet; the monthly price and included services depend on the plan type; and a clinic's phone depends on the clinic. Mixing these dependencies in one table produces the anomalies.

First Normal Form

First normal form requires that each cell hold a single value and that there be no repeating groups. The included services cell violates this. The fix moves services to their own table, plan type services, with one row for each service included in each plan type. A plan type with five services has five rows there.

Second Normal Form

Under second normal form, each attribute outside the key must rely on all of the primary key, not just part of it. Once client and pet identifiers are introduced, a plan row could be identified by pet identifier and start date. Price and services depend only on plan type, not on the pet or date, so plan type details move to a plan type table, with plan type identifier, name, species and monthly price. The plan table keeps pet identifier, plan type identifier, start date, status and clinic identifier.

Third Normal Form

Third normal form requires that no non-key attribute depend on another non-key attribute. Client name, phone and address depended on the pet row through the client; they move to a client table. Clinic phone depended on clinic name; it moves to a clinic table. The pet table keeps pet identifier, name, species, breed and home clinic, and a pet ownership table links pets and clients, as Week 2's model specified.

After normalization, a price change touches one row instead of 1,140.

The Resulting Schema

The final schema has seven tables: client, pet, pet ownership, clinic, plan type, plan type service and plan. Each has a primary key, and foreign keys link them: plan refers to pet, plan type and clinic; plan type service refers to plan type and service; pet ownership refers to pet and client. The update, insertion and deletion anomalies disappear: price changes in one row of plan type, new plan types can be added before anyone buys them and canceling a plan no longer erases its definition.

Testing With Queries

Three questions managers ask were tested. How many active plans does each clinic have, by plan type? A join of plan, plan type and clinic answers it. Which clients have more than one pet on a plan? A join of plan, pet and pet ownership grouped by client answers it. What revenue will plans generate next month? A join of active plans and plan type prices answers it in seconds, without the manual checks the spreadsheet required.

Moving the Data

Converting the spreadsheet is a project in itself. Hoffer et al. (2019) recommend profiling existing data before loading it into a new design. Profiling found 3,812 plan rows, 2,960 distinct clients after matching by phone and email and 41 rows with plan types that no longer exist. Those 41 were reviewed with clinic managers before loading.

When to Denormalize

Normalized tables suit day-to-day updates, but reports that must join many of them run slowly and confuse occasional users. For the reporting layer in Week 4, some data will be deliberately combined into wider tables designed for analysis, while the normalized tables remain the source of truth.

Conclusion

Lone Star's wellness plan spreadsheet suffered from update, insertion and deletion anomalies because it mixed facts about clients, pets, plans and clinics in one table. Applying the relational model and normalizing step by step to third normal form produced seven linked tables with clear keys, eliminating the anomalies and answering managers' questions reliably.

References

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685

Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern database management (13th ed.). Pearson.

Kent, W. (1983). A simple guide to five normal forms in relational database theory. Communications of the ACM, 26(2), 120-125. https://doi.org/10.1145/358024.358054