Joining Tables and Data Modeling

Why Joins and Data Modeling Matter

Real business data rarely lives in a single table. A permits office tracks applicants, contractors, permit types, and inspections — all separate ideas that relate to one another. Relational databases store each idea in its own table and use keys to connect them. Knowing how to design those tables well and query across them is a foundational skill tested on CompTIA Data+, Google Data Analytics, and the IBM Data Science Professional certificate.

Normalization: Designing Tables That Hold Together

Normalization is the process of organizing columns and tables to reduce redundant data and prevent update anomalies. For this course, focus on two key rules:

• No repeating groups. Each column should hold one value, not a comma-separated list of values. If a permit can have multiple inspections, inspections belong in their own table.
• Every non-key column depends on the whole primary key. If a column describes the contractor rather than the permit, it belongs in a contractors table, linked by a foreign key.

The result is a schema where each fact is stored once. Changing a contractor's phone number means updating one row in one table, not hunting through thousands of permit records.

Entity-Relationship Diagrams

Before writing any SQL, draw an entity-relationship (ER) diagram. An ER diagram shows:

• Entities — the things you track (Permit, Contractor, PermitType, Inspection).
• Attributes — the columns that describe each entity (permit_id, issue_date, status).
• Relationships — how entities connect, including cardinality (one permit can have many inspections; each inspection belongs to exactly one permit).

Draw the diagram on paper first. Instructors review diagrams before students write SQL because fixing a design flaw on paper takes seconds; fixing it after tables are loaded takes much longer.

Primary Keys and Foreign Keys

A primary key (PK) uniquely identifies each row. Choose a surrogate key (a serial integer or UUID generated by the database) when no natural column is reliably unique.

A foreign key (FK) is a column in one table whose values must match a primary key value in another table — or be NULL. PostgreSQL enforces this with the `REFERENCES` keyword. If you try to insert a permit row that references a contractor_id that does not exist, the database rejects the insert. This constraint protects data integrity automatically.

```sql

CREATE TABLE contractor (

contractor_id SERIAL PRIMARY KEY,

company_name VARCHAR(120) NOT NULL,

license_number VARCHAR(30) UNIQUE NOT NULL,

phone VARCHAR(20)

);

CREATE TABLE permit (

permit_id SERIAL PRIMARY KEY,

permit_type_id INT REFERENCES permit_type(permit_type_id),

contractor_id INT REFERENCES contractor(contractor_id), -- NULL allowed

issue_date DATE NOT NULL,

status VARCHAR(20) NOT NULL

);

```

Notice that `contractor_id` in the permit table allows NULL. A permit may be filed by an individual homeowner with no licensed contractor. This design choice drives the LEFT JOIN example below.

The Four JOIN Types

INNER JOIN returns only rows where the join condition matches in both tables. Permits with no contractor and contractors with no permits are both excluded.

LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right. Non-matching right-side columns are NULL. Use this to find permits that have no associated contractor.

RIGHT JOIN (RIGHT OUTER JOIN) returns all rows from the right table and matching rows from the left. Less common; you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.

FULL OUTER JOIN returns all rows from both tables; unmatched sides are NULL. Use this when you need a complete picture — every permit and every contractor, matched where possible.

```sql

-- INNER JOIN: permits with a known contractor

SELECT p.permit_id, p.issue_date, c.company_name

FROM permit p

INNER JOIN contractor c ON p.contractor_id = c.contractor_id;

-- LEFT JOIN: all permits, contractor name or NULL

SELECT p.permit_id, p.status, c.company_name

FROM permit p

LEFT JOIN contractor c ON p.contractor_id = c.contractor_id;

-- Filter to permits with NO contractor

SELECT p.permit_id, p.status

FROM permit p

LEFT JOIN contractor c ON p.contractor_id = c.contractor_id

WHERE c.contractor_id IS NULL;

```

Exporting Results as CSV in pgAdmin 4

After running a query in pgAdmin 4, use File → Download as CSV from the query results panel to save the output. Keep exported files named clearly (for example, `query3_unmatched_permits.csv`). This is the format your instructor will verify.

Data Integrity and Safe Practices

Although data analytics work does not involve physical hazards, good data practice has its own safety rules:

• Never modify or drop tables in a shared or production database without permission. In this lab you work in your own named PostgreSQL schema to avoid affecting classmates.
• Test DDL in a transaction. Wrap `CREATE TABLE` and `INSERT` statements in `BEGIN; ... ROLLBACK;` to inspect results before committing. Only run `COMMIT` when output looks correct.
• Back up your work. Use pgAdmin's backup tool or `pg_dump` to save your schema before making structural changes.

What the Grader Checks

The instructor compares your query output row-for-row against a reference database loaded with the same sample data. Seven of your eight JOIN queries must return the correct result set. Pay attention to NULLs — an INNER JOIN and a LEFT JOIN on the same tables return different row counts, and that difference is exactly what is being tested.