Nine tables declared in SQL, with keys and CHECK rules written into each, make up the HI520 Unit 4 schema build, which is then tested by inserts designed to fail. Searches like "hi 520 unit 4 assignment example", "hi520 unit 4 sample" and "hi520 unit 4 example" land here.
What a finished HI520 Unit 4 schema build looks like
A script of about 140 lines targeting PostgreSQL, followed by a four-page design note. Reference tables come first: site, provider, diagnosis_code and lab_test, the last holding a local test code, a display name and a units column. Patient follows, with a generated integer key and the MRN declared UNIQUE and NOT NULL. Encounter references patient, site and the rendering provider; encounter_diagnosis carries a composite primary key of encounter and sequence number, with a CHECK keeping the sequence at one or above. Lab_result holds result_numeric as DECIMAL(10,3) and result_text as VARCHAR(60), plus a CHECK requiring that exactly one of the two be filled, since a result can read as a number or as a phrase such as not detected. Each foreign key states its ON DELETE behavior. Synthetic INSERT rows load last, then eight statements meant to fail.
How a HI520 Unit 4 example is structured
Statements run in the order the dependencies require, so each referenced table exists before any table that points to it, and the script executes in one pass on an empty database. Within each CREATE TABLE, the key column comes first, then descriptive columns, then foreign keys, then table-level constraints, each given a readable name so an error message identifies it. The design note mirrors the script table by table and gives a reason for every type: DATE for birth date because no time is meaningful, a timestamp for specimen collection because the order of draws matters within a day, fixed-length CHAR only where the length never varies. Key choices are argued next, including why the MRN is unique but not primary, since record numbers change when duplicate charts are merged. The rejected-insert block closes the note, one statement per constraint.
Reference tables first
Site, provider, diagnosis code and lab test are created before anything that points at them, so the script runs start to finish without a forward reference.
A surrogate key beside the MRN
Patient uses a generated integer as its primary key, and the medical record number is declared unique and required, since chart merges can change it.
Numbers and phrases in one result
Separate numeric and text columns, guarded by a CHECK that exactly one holds a value, keep not detected out of a DECIMAL column without discarding it.
Constraints that carry names
Every key and rule is named, for example chk_result_one_value, so a refused insert reports in plain terms which rule fired and on which table.
Eight inserts expected to fail
A duplicate MRN, an orphan encounter, a missing birth date and five more, each paired with the constraint name that should appear in the error and the error that did.
Where marks go in HI520 Unit 4
Declared constraints are what separate a schema build from a list of tables, and keys described in the design note but absent from the SQL are the deduction graders mention first. A primary key on the MRN alone reads as a missed health data point, because merged charts change it and every child row then breaks. Foreign keys typed differently from the columns they reference fail at creation or at the first join. Result values stored as text throughout lose credit for turning every numeric comparison into a conversion, while forcing all results into a number column loses the qualitative ones. Scripts that only run in an order someone discovered by trial, with statements rearranged after errors, draw comments. Missing NOT NULL on fields the scenario calls required, and constraints never tested at all, round out the usual comments.
Get a HI520 Unit 4 example written to your instructions
Mention the database platform the HI520 section uses, whether PostgreSQL, SQL Server, MySQL or an online sandbox, because data types and constraint syntax differ between them. Attach the Unit 4 instructions, the rubric and the model from earlier units if one exists. Script and design note come back in 24-48h, and the first custom sample is free.
HI520 Unit 4 questions, answered
Should the MRN be the primary key?
Usually not. A medical record number looks unique and stable, but organizations merge duplicate charts and occasionally reissue numbers, and a primary key that changes forces updates through every table that references it. A generated surrogate key stays fixed, while a UNIQUE constraint on the MRN still blocks two patients sharing one. Where instructions insist on natural keys, the sample uses them and records what that choice risks.
What is the difference between a CHECK constraint and a foreign key?
A foreign key tests a value against rows in another table, such as confirming that an encounter's site exists. A CHECK tests a condition within the row itself, such as a sequence number of one or more, or exactly one of two result columns being filled. Both reject bad rows at insert time, which is the point of declaring them in the schema rather than trusting the application.
Does the sample include data, or only table definitions?
Both, unless your prompt says otherwise. A small set of synthetic rows loads after the tables exist, enough for each relationship to have matching records and for later queries to return something. The rows are invented, carry no real identifiers, and are designed so that each constraint has at least one valid row and one invalid attempt to test against.