Integrated Schema Design Project

Integrated Schema Design Project

This lab is the capstone of your Relational Design & Normalization course. You will work independently from a business requirements narrative through to a fully implemented, normalized, and documented PostgreSQL database. This mirrors exactly what a database administrator or data engineer does on the job when a new system is commissioned.

Step 1: Requirements Analysis

Before touching any design tool, read the veterinary clinic narrative carefully — twice. On the first pass, highlight every noun that could be an entity or attribute. On the second pass, note every verb phrase that could describe a relationship. Common entities in a veterinary context include Patient (the animal), Owner, Veterinarian, Appointment, Treatment, Medication, and Invoice. Attributes belong to exactly one entity; if you find yourself assigning the same attribute to two entities, that is a signal the model needs refinement.

Once you have a candidate list, draw your crow's-foot ERD in draw.io. Label every relationship line with its cardinality: one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N). Every M:N relationship must be resolved into an associative (bridge) entity with its own primary key before you proceed to the relational schema, because relational tables cannot directly represent M:N without an intermediate table.

Check your ERD for completeness: every entity has a primary key, every relationship has cardinality notation, and no attribute is left floating outside an entity.

Step 2: Mapping ERD to Relational Schema

Each entity becomes a table. Each single-valued attribute becomes a column. A 1:M relationship is implemented by placing the primary key of the "one" side as a foreign key in the "many" side table. Each resolved M:N associative entity becomes its own table containing foreign keys to both parent tables, plus any attributes that belong to the relationship itself (such as a dosage on a prescription link table).

Once you have a draft schema, run the normalization checklist:

• First Normal Form (1NF): Every column holds atomic (indivisible) values; there are no repeating groups. For example, a column storing `"feline, canine"` violates 1NF — split it into a species lookup table.
• Second Normal Form (2NF): Every non-key column is fully functionally dependent on the entire primary key. This only applies to tables with composite primary keys. If a column depends on only part of the composite key, move it to a separate table.
• Third Normal Form (3NF): No non-key column is transitively dependent on the primary key through another non-key column. Classic example: storing both `zip_code` and `city` in a table where `city` is determined by `zip_code` — move `city` to a ZIP lookup table.

Document every check. For each table, write one or two sentences confirming it satisfies each normal form or describing the change you made to achieve it. This documentation is required for the defense.

Step 3: Writing and Executing PostgreSQL DDL

Write your DDL in a single `.sql` script file. A disciplined structure prevents errors:

1. `DROP TABLE IF EXISTS` statements in reverse dependency order (child tables before parent tables).

2. `CREATE TABLE` statements in dependency order (parent tables before child tables).

3. All constraints inline or as named `ALTER TABLE` additions after creation.

Use `NOT NULL` wherever the business rules require a value. Use `CHECK` constraints to enforce domain rules (for example, `CHECK (weight_kg > 0)`). Use `REFERENCES` with `ON DELETE` behavior that matches business rules — for instance, you might use `ON DELETE RESTRICT` to prevent deleting an owner who still has active patients.

Execute the script against a clean PostgreSQL schema. A clean execution with zero errors is a hard requirement for passing this lab. If errors occur, read the PostgreSQL error message carefully: it will identify the line and the constraint violated. Fix and re-run.

Step 4: Building the Data Dictionary

Use the provided template to document every table and column: table name, column name, data type, constraints, and a plain-English description of what the column stores. A complete data dictionary is what allows another DBA — or your future self — to maintain the database without guessing the intent of a column named `stat_cd`.

Step 5: Oral Defense

Your five-minute presentation should cover four areas in order: (1) your ERD with entity and relationship justification, (2) your normalized schema diagram, (3) your normalization justification table, and (4) a selected excerpt from your data dictionary. Practice answering "why" questions: why did you choose that data type, why did you add that foreign key constraint, why did that column move during normalization? The instructor will ask two specific questions about your design decisions, and you must answer both correctly to pass the defense component.

Professional and Safety Standards

In a database administration role, data integrity is a safety issue whenever the database supports health, safety, or financial systems. A veterinary clinic database holds medication dosage records and patient medical histories. Incorrect schema design — for example, a missing NOT NULL constraint on a drug name column — can allow NULL medication records to reach clinical staff. Treat every constraint as a safeguard, not a formality. Additionally, when working in a shared lab environment, never execute DDL against a shared or production schema; always confirm you are connected to your personal lab schema before running DROP or CREATE statements. This habit protects other learners' work and mirrors the strict change-control procedures you will follow in the workplace.