| Course | HINF 520 Data Management and Design in Health Administration (HINF/520) |
|---|---|
| Week | 3 |
| Paper type | Database design paper |
| Length | about 1,209 words, 4 double-spaced pages plus title page and references |
| Format | APA 7 student paper |
| School | University of Phoenix |
| Program | MHA |
| Updated | September 2026 |
Free sample paper for HINF 520 Week 3
Patients, Encounters, Results and Prescriptions: Designing the Tables Behind a Health System's Diabetes Data Mart, From Relational Model to Star Schema
[Student Name]
University of Phoenix
HINF/520: Data Management and Design in Health Administration
Week 3 Assignment
[Instructor Name]
[Date]
The health system, its data mart and design decisions are composites written for a model paper; research findings come from the sources listed.
The diabetes definition and value set described in earlier papers were first applied in a spreadsheet. By the time it held 43,000 patients, it had 212 columns, including separate columns for each of a patient's last eight A1c results, clinic names typed four different ways and formulas that broke when rows were sorted. The composite health system's analytics team decided to build a proper diabetes data mart, a focused database drawn from the data warehouse. This paper describes its design.
Starting From the Questions
The team began with the questions the data mart must answer. How many patients with diabetes does each clinic have? What share had an A1c above 9% at their most recent test, and how has that changed by quarter? Which patients have not had a test in six months? Which patients are on insulin, and which have kidney disease? How do results differ by age, sex, race, ethnicity and preferred language? The design follows from these questions.
The Relational Model
Most health care databases rest on the relational model, proposed in 1970, which represents data as tables of rows and columns and relationships between tables through shared values rather than physical links, so that users can ask questions without knowing how data are stored (Codd, 1970). Each table describes one kind of thing, each row one instance of it and each column one attribute. A primary key uniquely identifies each row; a foreign key in one table refers to the primary key of another.
Six Entities
The team defined six entities:
Patient: patient identifier as primary key, birth date, sex, race, ethnicity, preferred language and assigned primary clinic.
Clinic: clinic identifier as primary key, name, address, hospital affiliation and neighborhood income category.
Encounter: encounter identifier as primary key, patient identifier and clinic identifier as foreign keys, date, visit type and clinician.
Diagnosis: diagnosis identifier as primary key, patient identifier, encounter identifier, code, code system and date.
Laboratory result: result identifier as primary key, patient identifier, encounter identifier, LOINC code, value, unit and collection date.
Medication: medication identifier as primary key, patient identifier, RxNorm ingredient, start date and end date.
Relationships
One patient can have many encounters, diagnoses, results and medications, so each of those tables carries the patient identifier as a foreign key: a one-to-many relationship. One clinic has many encounters. One encounter can produce many diagnoses and many results. An entity relationship diagram shows these links as lines between boxes, with notation for one and many at each end.
Normalizing the Design
The spreadsheet violated basic rules. Its eight A1c columns were a repeating group: a patient with a ninth result had nowhere to put it, and a query for all results above 9% had to check eight columns. Moving results into a separate table, one row per result, solved this and satisfied the first normal form. Clinic names repeated in every patient row, so a renamed clinic had to be changed thousands of times; storing each clinic once in the clinic table, referenced by its key, removed that duplication. After normalization, each fact is stored in one place.
Why Reporting Needs a Different Shape
Normalized designs are ideal for recording transactions, but reporting queries across many tables can be slow and complex. For analysis, the team built a star schema on top of the normalized tables. At its center, a fact table holds one row per A1c result with keys to dimension tables for patient, clinic and date and the result value. Dimensions hold descriptive attributes: the date dimension, for example, lists each date's month, quarter and year, so quarterly trends need no date calculations. A normalized design is built to record the truth once; a star schema is built to ask questions of it quickly.
Building Quality Into the Design
Design can prevent some errors. Research on record data quality identifies plausibility as one of five dimensions, alongside completeness, correctness, concordance and currency (Weiskopf & Weng, 2013). The data mart enforces rules: A1c values must fall between 3% and 20%, units must be percent or a convertible unit, dates cannot precede birth or fall in the future and every result must carry a valid LOINC code from the value set. Values that fail go to an exception table for review instead of silently entering reports. The first load placed 212 results in exceptions, including several values of 45 that turned out to be entered in the wrong unit.
Handling Time and History
Diabetes care unfolds over years, so the design must handle history. A patient who moves from one clinic to another should be counted in the old clinic for past quarters and the new one going forward. The team solved this by storing the patient's clinic assignment in a separate table with start and end dates, so each report can attribute patients to the clinic responsible at the time. Medication records similarly carry start and end dates, allowing the question of who was on insulin on any given date. Designs that store only the current value lose this history and produce misleading trends.
Choosing the Grain
Every table must have a clear grain, the level of detail one row represents. The fact table's grain is one A1c result, not one patient or one visit, which lets analysts count tests, find a patient's most recent value or compute averages without double counting. The team documented each table's grain in the data dictionary after an early draft mixed patient-level and result-level rows in the same table and inflated counts.
Protecting Privacy in the Design
The data mart contains protected health information, so design includes security. Patient names and addresses are stored only in the patient table, with access limited to care management staff; analysts working on trends use a view that replaces identifiers with study numbers. Every query is logged.
Aligning With a Common Data Model
The team considered a shared structure. A study that transformed 10 observational databases into one common data model with standardized vocabularies found acceptable representation of the data and was able to run analytic methods across them with useful performance (Overhage et al., 2012). Aligning the data mart's tables and vocabularies with such a model would let the system join research networks and reuse tools built by others. The team adopted the model's standard vocabularies now and will evaluate full conversion next year.
Testing the Design
Before release, the team ran each of the starting questions as queries and compared answers with the validated counts from the first paper. Clinic-level counts matched within 0.5%, and queries that had taken minutes in the spreadsheet ran in seconds.
Maintenance
The data mart is refreshed nightly from the warehouse. Changes to tables go through a documented change process, so reports do not break without warning.
Conclusion
A 212-column spreadsheet became a normalized relational design with six entities linked by keys, a star schema for fast reporting and rules that stop implausible values at the door. Starting from users' questions, applying the relational model and normalization and aligning with a common data model gave the health system a database that answers its diabetes questions reliably and can grow to other conditions.
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
Overhage, J. M., Ryan, P. B., Reich, C. G., Hartzema, A. G., & Stang, P. E. (2012). Validation of a common data model for active safety surveillance research. Journal of the American Medical Informatics Association, 19(1), 54-60. https://doi.org/10.1136/amiajnl-2011-000376
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
What the HINF 520 Week 3 instructions ask
HINF 520 Week 3 generally asks students to design a database or data structure for a health care information need. Students may be asked to identify entities and attributes, define relationships and keys, apply normalization, describe a diagram such as an entity relationship diagram and explain how the design supports reporting and data quality. Some versions ask students to draw the diagram in a tool and submit it with the paper. Strong papers start from the questions the database must answer, define each entity and its key clearly, explain relationships such as one-to-many, show normalization with an example, address how analytical designs such as star schemas differ from transactional ones and build data quality rules into the design.
How this HINF 520 Week 3 example is built
The paper opens with an analyst's spreadsheet of diabetes patients that has grown to 43,000 rows and 212 columns, with repeated clinic names spelled four ways. The relational model's core ideas, tables, rows, keys and relationships, are introduced. Six entities are defined with their keys and attributes, and relationships are described as a diagram would show them. Normalization removes repeated groups and duplicated clinic data. A star schema with a fact table of A1c results and dimension tables for patient, clinic and date serves reporting. Constraints block implausible values such as an A1c of 45%. Alignment with a common data model, and tests showing clinic counts matched validated figures within 0.5%, close the paper.
HINF 520 Week 3 grading rubric: where the points go
The database design week is usually graded on whether the design is correct, clear and fit for purpose. Instructors look for entities and attributes tied to the information need, primary and foreign keys, correct relationships, normalization explained with an example, a diagram or clear description of one and attention to how the design supports queries and data quality. Distinguishing transactional from analytical design shows depth. Citing foundational or current research on data models earns credit. APA format and organization complete the grade. Designs that put everything in one wide table, or that list entities without keys and relationships, generally lose points, as do designs that ignore privacy.
HINF 520 Week 3 help: mistakes to avoid
A common problem in HINF 520 Week 3 is designing tables before defining the questions they must answer. List the questions first, such as how many patients in each clinic had an A1c above 9% last quarter. Identify the entities those questions involve and give each a primary key. Define relationships and the foreign keys that implement them. Normalize to remove repeated groups and duplicated facts, and show an example. Explain whether the database supports transactions or analysis, since reporting databases often use star schemas. Add rules that block impossible values. Finally, consider aligning with a common data model so your work can be compared and reused, and explain how the tables will be refreshed and maintained.
Related HINF 520 sample papers
Other HINF 520 week samples
- HINF 520 Week 1: Data, Information and Knowledge
- HINF 520 Week 2: Data Taxonomies and Classifications
- HINF 520 Week 4: Systems Operations and Maintenance
- HINF 520 Week 5: Reporting and Data Exchange
- HINF 520 Week 6: Data Governance and Quality
More MHA sample papers
- HCS 504 Week 3: Communication, Teamwork and Portfolio
- HCS 529 Week 3: Renovation Plan
- HINF 500 Week 3: How Data Are Collected and Reported
- HINF 510 Week 3: Key Design Elements
HINF 520 Week 3 questions, answered
What does HINF/520 Week 3 usually ask for?
Many sections ask students to design a database for a health care need, including entities, attributes, keys, relationships, normalization and how the design supports reporting.
Where can I find a free HINF 520 Week 3 sample paper?
The diabetes data mart design is published above for free reading, with notes in the margin on each design choice. If you have your own data need, the first paper we write for it is free.
What is normalization in database design?
A process of organizing tables to reduce duplication and dependency, for example by storing each clinic's name once in a clinic table rather than repeating it in every patient row.
What is a star schema?
An analytical design with a central fact table of measurements, such as A1c results, linked to dimension tables such as patient, clinic and date, which makes reporting queries simpler and faster.
What is a common data model?
A shared structure and set of standard vocabularies into which different organizations transform their data, so the same analysis can run across many databases.
Write yours, or have the desk draft it
This paper is an original model document written by our desk, not a submitted student paper and not an official University of Phoenix document. Read it for the moves, then write your own to the instructions in your classroom. If you want one built to your exact prompt and rubric, the first custom sample is free and arrives in 24 to 48 hours.
Request this one custom, free · All HINF 520 week samples · All courses