HI520 Unit 3 Normalization Exercise Example

One Wide Export, Sixteen Columns and a Cell Full of Semicolons: A Unit 3 Normalization Exercise Taking a Pediatric Asthma Registry Extract to Third Normal Form

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 3 Normalization Exercise

[Instructor Name]

[Date]

Calder Bay Pediatrics, its export and every value are composite; the sources cited are real.

The Starting Relation

The export, called RESULT_EXPORT, has sixteen attributes: MRN, patient_name, birth_date, age_at_collection, site_name, site_address, provider_name, provider_npi, order_number, order_date, local_test_code, test_name, units, result_sequence, result_value, collection_time and dx_list. One sample row reads: MRN 48211, Mia Torres, birth date 2017-08-04, age 8, Harbor Street, 410 Harbor Street, Dana Okafor NP, NPI 1912345678, order 77310, order date 2026-03-10, test SPIRO, spirometry, percent predicted, sequence 1, value 74, collected 2026-03-12 at 10:20, diagnoses J45.40; J30.2; L20.9. A second row for the same order holds the post-bronchodilator value with sequence 2 and value 86, repeating every other column.

Why the Wide Table Causes Trouble

The export works for reading one row at a time and fails for everything else. If the Harbor Street site moves, its address must be changed on every row that mentions it, thousands of them, and any row missed keeps the old address: an update anomaly. A new provider who has not yet ordered a test cannot be recorded at all, because a row needs an order: an insertion anomaly. If the only result for a rarely used test is deleted, the test's name and units disappear with it: a deletion anomaly. And a count of children with asthma requires searching text in the diagnosis cell, which miscounts any row where a code was typed with a space or in lowercase. Each normal form below removes one source of these problems.

Functional Dependencies

1. MRN determines patient_name and birth_date.

2. site_name determines site_address.

3. provider_npi determines provider_name.

4. order_number determines MRN, order_date, provider_npi, site_name, local_test_code and dx_list.

5. local_test_code determines test_name and units.

6. order_number together with result_sequence determines result_value and collection_time.

7. birth_date together with collection_time determines age_at_collection.

The key of RESULT_EXPORT is order_number plus result_sequence, since one order can return more than one result.

First Normal Form

Rule: every attribute holds a single value. dx_list breaks the rule, because one cell holds three codes, and no query can count children by a diagnosis buried inside a string without unreliable text matching. The diagnosis list is moved to its own relation, ORDER_DIAGNOSIS, with one row per code: order_number, dx_code and dx_sequence, keyed on order_number and dx_code. The sequence keeps the original order, so the first-listed code remains identifiable. age_at_collection is also removed at this step: it is not a stored fact but a value derived from birth_date and collection_time, and storing it invites the two to disagree after a correction. No query can reliably count children with persistent asthma while their diagnoses sit together in one text cell, which is why the semicolon list goes first.

Second Normal Form

Rule: every non-key attribute depends on the whole key, not part of it. With a key of order_number plus result_sequence, every attribute that depends on order_number alone violates the rule: MRN, order_date, provider_npi, site_name and local_test_code, along with everything they determine. The decomposition creates TEST_ORDER, keyed on order_number and holding MRN, order_date, provider_npi, site_name and local_test_code, and TEST_RESULT, keyed on order_number plus result_sequence and holding result_value and collection_time.

Third Normal Form

Rule: an attribute outside the key may not get its value by way of some other attribute outside the key. TEST_ORDER still carries attributes that depend on order_number only through another attribute: patient_name through MRN, site_address through site_name, provider_name through provider_npi, and test_name and units through local_test_code. Each transitive chain becomes its own relation: PATIENT with MRN, patient_name and birth_date; SITE with site_name and site_address; PROVIDER with provider_npi and provider_name; and TEST with local_test_code, test_name and units. TEST_ORDER keeps only foreign keys and order_date.

Final Relations

1. PATIENT: MRN (key), patient_name, birth_date.

2. SITE: site_name (key), site_address.

3. PROVIDER: provider_npi (key), provider_name.

4. TEST: local_test_code (key), test_name, units.

5. TEST_ORDER: order_number (key), MRN, order_date, provider_npi, site_name, local_test_code.

6. TEST_RESULT: order_number and result_sequence (key), result_value, collection_time.

7. ORDER_DIAGNOSIS: order_number and dx_code (key), dx_sequence.

Join Test

Joining TEST_RESULT to TEST_ORDER on order_number, then to PATIENT, SITE, PROVIDER and TEST on their keys, and to ORDER_DIAGNOSIS on order_number, reproduces every original row's values without adding rows that never existed, because each join follows a foreign key to exactly one parent row. Recomputing age from birth_date and collection_time reproduces the dropped column. Mia's two spirometry rows reassemble exactly: one order, two results, three diagnoses. The decomposition is lossless, a property the relational model treats as essential for any split (Codd, 1970).

Checking Against the Diagram

The Unit 2 diagram was drawn from the scenario before any data arrived, following the entity approach (Chen, 1976), so comparing it with relations derived from real data is a useful test of both. The normalized relations match most of it: PATIENT, SITE, PROVIDER, TEST_ORDER and TEST_RESULT appear in both. Two differences matter. First, the export has no encounter: diagnoses hang from orders, because the source system flattened the visit away when it produced the file. Normalization cannot recover an entity the data never contained. When the export is loaded, orders must be matched to encounters using the source system's encounter identifier, which the import request should add. Second, the site in TEST_ORDER is the ordering site, while the diagram also records a performing site for each result. Data quality work warns that an extract can look complete while silently dropping the context needed to interpret it (Weiskopf & Weng, 2013), and the missing encounter is exactly that kind of loss.

References

Chen, P. P.-S. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36. https://doi.org/10.1145/320434.320440

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

Weiskopf, N. G., & Weng, C. (2013). Methods and dimensions of electronic health record data quality assessment: Enabling reuse for clinical research. Journal of the American Medical Informatics Association, 20(1), 144-151. https://doi.org/10.1136/amiajnl-2011-000681