HI520 Unit 4 Schema Build Example

Nine CREATE TABLE Statements and Five Inserts Built to Fail: A Unit 4 Schema Build That Declares Every Rule of a Pediatric Asthma Design in the SQL Itself

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 4 Schema Build

[Instructor Name]

[Date]

Calder Bay Pediatrics and every sample row are composite; the sources cited are real.

Design Principles

The schema follows the Unit 2 diagram and the Unit 3 relations, with ENCOUNTER restored because the source system can supply its identifier. Each table uses a generated integer key, and natural identifiers such as the medical record number and the national provider identifier are kept as unique attributes, so a correction to an identifier does not ripple through every foreign key. Every rule a database can enforce is declared in the database. Rules left to application code tend to be enforced in one screen and forgotten in the next, and the relational model's promise is that integrity belongs to the data itself (Codd, 1970).

The Script

CREATE TABLE site (site_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, site_name VARCHAR(60) NOT NULL UNIQUE, street_address VARCHAR(120) NOT NULL, phone VARCHAR(20)); CREATE TABLE provider (provider_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, npi CHAR(10) NOT NULL UNIQUE, full_name VARCHAR(100) NOT NULL, credential VARCHAR(20) NOT NULL, home_site_id INTEGER NOT NULL REFERENCES site (site_id), CONSTRAINT provider_npi_length CHECK (char_length(npi) = 10)); CREATE TABLE diagnosis_code (code VARCHAR(8) PRIMARY KEY, description VARCHAR(200) NOT NULL, code_system VARCHAR(20) NOT NULL DEFAULT 'ICD-10-CM'); CREATE TABLE test_type (test_code VARCHAR(12) PRIMARY KEY, test_name VARCHAR(80) NOT NULL, units VARCHAR(30) NOT NULL); CREATE TABLE patient (patient_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, mrn VARCHAR(12) NOT NULL UNIQUE, first_name VARCHAR(60) NOT NULL, last_name VARCHAR(60) NOT NULL, birth_date DATE NOT NULL, sex CHAR(1) NOT NULL, primary_language VARCHAR(30), CONSTRAINT patient_sex_values CHECK (sex IN ('F', 'M', 'X', 'U'))); CREATE TABLE encounter (encounter_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP, patient_id INTEGER NOT NULL REFERENCES patient (patient_id), site_id INTEGER NOT NULL REFERENCES site (site_id), provider_id INTEGER NOT NULL REFERENCES provider (provider_id), visit_type VARCHAR(20) NOT NULL, control_score SMALLINT, CONSTRAINT encounter_times CHECK (end_time IS NULL OR end_time >= start_time), CONSTRAINT encounter_score_range CHECK (control_score BETWEEN 0 AND 27)); CREATE TABLE encounter_diagnosis (encounter_id INTEGER NOT NULL REFERENCES encounter (encounter_id), code VARCHAR(8) NOT NULL REFERENCES diagnosis_code (code), dx_sequence SMALLINT NOT NULL, PRIMARY KEY (encounter_id, code), CONSTRAINT encounter_dx_sequence_unique UNIQUE (encounter_id, dx_sequence), CONSTRAINT encounter_dx_sequence_positive CHECK (dx_sequence >= 1)); CREATE TABLE test_order (order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, encounter_id INTEGER NOT NULL REFERENCES encounter (encounter_id), ordering_provider_id INTEGER NOT NULL REFERENCES provider (provider_id), test_code VARCHAR(12) NOT NULL REFERENCES test_type (test_code), order_time TIMESTAMP NOT NULL, status VARCHAR(12) NOT NULL DEFAULT 'ordered', CONSTRAINT test_order_status_values CHECK (status IN ('ordered', 'collected', 'resulted', 'canceled'))); CREATE TABLE test_result (result_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES test_order (order_id), result_sequence SMALLINT NOT NULL, performing_site_id INTEGER REFERENCES site (site_id), result_time TIMESTAMP NOT NULL, result_value NUMERIC(8,2), interpretation VARCHAR(200), CONSTRAINT test_result_order_sequence UNIQUE (order_id, result_sequence)); Every rule in the design that a database can enforce appears in the script as a named constraint, so a refused row reports exactly which rule it broke.

Inserts Built to Fail

After loading reference rows for two sites, two providers, three diagnosis codes and the spirometry test type, five bad inserts were run, each written to break one rule.

1. INSERT INTO patient (mrn, first_name, last_name, birth_date, sex) VALUES ('CB48211', 'Mia', 'Torres', DATE '2017-08-04', 'female'); refused by patient_sex_values, because the code allows one character from a fixed list.

2. A second patient row reusing mrn 'CB48211' for a different child; refused by the unique constraint on mrn, which protects against the duplicate records the course discussed in Unit 1.

3. An encounter whose end_time is earlier than its start_time; refused by encounter_times.

4. An encounter_diagnosis row with code 'J45.99X' that does not exist in diagnosis_code; refused by the foreign key, so no visit can carry an invented code.

5. A second spirometry result for order 1 with result_sequence 1, already used by the pre-bronchodilator value; refused by test_result_order_sequence.

Each insert produced an error naming its constraint, and the tables held no partial rows afterward.

Design Note

Several choices deserve a sentence each. The control_score check allows 0 to 27 because the childhood asthma control instrument the practice uses scores up to 27 (Liu et al., 2007); a different instrument would need its own range or its own table. end_time may be null because an encounter can still be open when the record is saved. performing_site_id may be null because outside laboratories are not Calder Bay sites, matching the zero minimum in the diagram. Status values are listed rather than free text, so a report counting resulted orders cannot miss rows typed as "Resulted" or "done," a conformance problem data quality frameworks single out as a common barrier to reuse (Weiskopf & Weng, 2013). Indexes on foreign keys are added in a separate script, since PostgreSQL does not create them automatically and join performance depends on them. No column stores a patient's age or latest score; both are derived when needed, for reasons the later query units will show.

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

Liu, A. H., Zeiger, R., Sorkness, C., Mahr, T., Ostrom, N., Burgess, S., Rosenzweig, J. C., & Manjunath, R. (2007). Development and cross-sectional validation of the Childhood Asthma Control Test. Journal of Allergy and Clinical Immunology, 119(4), 817-825. https://doi.org/10.1016/j.jaci.2006.12.662

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