HI520 Unit 10 Database Design Report Example

Five Questions From the Quality Team and a Database That Answers Them: A Unit 10 Database Design Report for a Four-Site Pediatric Asthma Program

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 10 Database Design Report

[Instructor Name]

[Date]

Calder Bay Pediatrics, its team and every count are synthetic or composite; the sources cited are real.

Summary

Calder Bay Pediatrics needed a database that could answer five questions about its asthma program across four sites. The design described here answers all five from a nine-table PostgreSQL schema with every integrity rule declared in the database, a set of tested queries and a stewardship table assigning each field to a responsible role. Its most important findings are not technical: 35 percent of second-quarter encounters carry no asthma control score, 165 of 412 children with persistent asthma had no spirometry in the past year, and the Pine Hollow site shows both the lowest mean control score and the highest share of children not well controlled.

The Five Questions

1. How many children with persistent asthma does each site serve, and how often are they seen?

2. What share of those children had spirometry in the past year?

3. What share have asthma that is not well controlled, by site?

4. How complete is control score documentation, and where is it weakest?

5. Can the practice share summary tables with its community advisory board without identifying any child?

Traceability

1. Question 1 reads PATIENT, ENCOUNTER, ENCOUNTER_DIAGNOSIS and SITE, answered by the grouping queries of Unit 7, statements 1 and 2.

2. Question 2 reads the same tables plus TEST_ORDER and TEST_RESULT, answered by the EXISTS version of the Unit 6 join.

3. Question 3 reads ENCOUNTER and the persistent asthma cohort, answered by the two-stage rate in Unit 7, statement 3.

4. Question 4 reads ENCOUNTER, answered by Unit 5 queries 5 and 6.

5. Question 5 reads PATIENT, ENCOUNTER and SITE, answered by Unit 7, statement 5, with suppression of groups under 11.

Every table in the schema is read by at least one question, and every question is answered by a tested query, which is the test of a design that is neither too thin nor padded.

Conceptual Design

The final diagram has eight entities and nine relationships, drawn in crow's foot notation with minimum and maximum at every end, following the entity approach that describes the program before choosing any storage (Chen, 1976). Its central decision links each result to the order behind it rather than to a visit or a calendar day, so a test reported on Friday still finds Tuesday's request. Five assumptions are recorded, including one medical record number per child across sites and one rendering provider per encounter.

Logical Design

Normalizing the practice's registry export to third normal form produced seven relations and two lessons. The diagnosis list held in one cell was split into one row per code, the only way to count children by condition reliably. An age column was dropped because it is derived from birth date and test date, and storing it would let the two disagree. The export also lacked an encounter, so the import was changed to carry the source system's encounter identifier.

Physical Design

The schema has nine tables created in dependency order. Identifiers that come from outside the database are stored as unique columns, while generated integer keys carry every relationship, so a corrected identifier never ripples through foreign keys. Every rule the database can enforce is a named constraint, and five inserts written to fail confirmed that each constraint refuses the row it targets. The relational model's promise that integrity belongs to the data, not to application screens, is the design's guiding principle (Codd, 1970). No column stores a value that can be derived: after the seminar discussion in Unit 8, the proposed stored score column gave way to a function taking a reference date as input, since a measurement year and a live dashboard want different answers to the word latest.

Answers

1. Question 1: the second quarter brought 1,724 encounters for 1,198 different children, about 1.4 visits per child; Harbor Street serves the most.

2. Question 2: lung function testing reached 247 of the 412 children in the cohort over the year, three in five.

3. Question 3: 120 of 370 children with a score, 32.4 percent, had asthma not well controlled at their latest scored visit, ranging from 29.0 percent at Harbor Street to 45.8 percent at Pine Hollow.

4. Question 4: 611 of 1,724 second-quarter encounters, 35 percent, carry no control score.

5. Question 5: a site-by-language table can be shared after suppressing 3 of 19 groups under 11 children.

Guidelines recommend regular assessment of asthma control and periodic lung function testing (National Asthma Education and Prevention Program [NAEPP], 2007), so answers 2 and 4 point to the practice's clearest improvement targets.

Governance

Twelve fields are assigned to roles that define, maintain and approve changes to them, and a five-step change process has been tested on one real change, the split of telehealth visits into audio and video. Separating these duties is what keeps field meanings from drifting as staff and needs change (Rosenbaum, 2010).

Will the Design Survive Next Year's Requests?

Two likely requests were tested against the schema. The first is tracking asthma action plans given to schools. This needs one new table, action_plan, with a foreign key to encounter and attributes for the date given, the school and whether a copy was sent. No existing table changes, and question 3 could then be broken down by whether a child has a current plan. The design survives.

The second is a merger with another pediatric group whose records carry their own medical record numbers. Here the design strains: the assumption of one record number per child across sites no longer holds. A patient_identifier table holding each number with its issuing organization would be needed, and the unique constraint on the record number would move there. The change is contained, because relationships use the generated patient key rather than the record number, but it is real work and should be planned before any merger.

Limits

All counts come from synthetic data loaded for the course, so they show the design working, not the practice's true performance. Performance testing at full production volume, and a review by the practice's privacy officer before any data from real records are loaded, are the next steps. A design can be correct and still be filled with poor data, which is why the completeness findings in answer 4 matter as much as the schema itself (Weiskopf & Weng, 2013).

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

National Asthma Education and Prevention Program. (2007). Expert panel report 3: Guidelines for the diagnosis and management of asthma. National Heart, Lung, and Blood Institute.

Rosenbaum, S. (2010). Data governance and stewardship: Designing data stewardship entities and advancing data access. Health Services Research, 45(5p2), 1442-1455. https://doi.org/10.1111/j.1475-6773.2010.01140.x

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