In-Class Activity: Advanced SQL Systems Challenge

Big Idea

You are moving beyond “writing SQL queries” into understanding SQL as a full system for defining, manipulating, securing, and maintaining data integrity.

This activity requires you to:

• Outline (command term: brief but precise distinctions)

• Construct (command term: produce correct SQL code)

• Explain (command term: give reasons/mechanisms)

• Describe (command term: detailed features and characteristics)

These command terms are explicitly defined in the IB guide and will be enforced strictly.

Scenario: Distributed Hospital System

You are working on a hospital management system with the following schema:

PATIENT(patient_id, name, date_of_birth, insurance_type)
DOCTOR(doctor_id, name, specialty)
APPOINTMENT(appointment_id, patient_id, doctor_id, date, cost)
TREATMENT(treatment_id, appointment_id, description, success)

Constraints:

• A patient may have many appointments

• A doctor may handle many appointments

• Each appointment may include multiple treatments

• The system is used in real-time, requiring high integrity and performance

Part A — SQL Language Types 

Task:

QUESTION 1: Outline the differences between:

• DDL

• DML

• TCL 

Requirements:

• Include purpose

• Include example commands

• Include effect on database state

Example:

Weak (1–2 marks):

DDL creates tables, DML changes data.

Strong (3 marks):

DDL defines database structure (e.g., CREATE, ALTER) and modifies schema, while DML manipulates data within tables (e.g., SELECT, INSERT). TCL manages transactions (e.g., COMMIT, ROLLBACK), controlling persistence and consistency.

Part B — Multi-Table Query Construction

Command Term: Construct (8 marks)

Task:

Write a query that returns:

• Patient name

• Doctor name

• Total cost of all appointments per patient

• Only include patients with total cost > 500

• Only include doctors with specialty = 'Cardiology'

• Sort by total cost descending

Requirements:

• Must include:

  • JOIN

  • GROUP BY

  • HAVING

  • ORDER BY

  • Aggregate function

Part C — Updating Data 

Command Term: Explain (6 marks)

Task:

Explain how SQL updates data in this system.

Include:

• INSERT

• UPDATE

• DELETE

Then answer:

What are the performance and integrity risks of updating indexed columns in a high-volume hospital system?

Example:

Weak:

UPDATE changes values in a table.

Strong:

UPDATE modifies existing tuples and may trigger index restructuring; frequent updates to indexed columns degrade performance due to rebalancing and can introduce temporary inconsistencies if not transaction-controlled.

Part D — Aggregate Calculations (A3.3.4 HL)

Command Term: Construct (6 marks)

Task:

Write a query that calculates:

• Average treatment cost per doctor

• Number of treatments per doctor

• Only include doctors with more than 10 treatments

Part E — Database Views (A3.3.5 HL)

Command Term: Describe (6 marks)

Task:

Describe:

1. Virtual views

2. Materialized views

Then evaluate:

Which is more appropriate for a hospital dashboard showing real-time patient loads?

Expected Depth:

• Performance implications

• Data freshness

• Storage trade-offs

Part F — Transactions and Data Integrity (A3.3.6 HL)

Command Term: Describe (8 marks)

Task:

A patient undergoes a treatment:

1. Appointment is created

2. Treatment is recorded

3. Billing is updated

Write a transaction sequence using SQL.

Then:

Explain how ACID properties ensure correctness.

Required SQL:

BEGIN TRANSACTION;

-- your operations

COMMIT;
-- OR ROLLBACK;

Required Explanation:

• Atomicity

• Consistency

• Isolation

• Durability

Extension Challenge (Very Difficult — Synthesis)

Task:

Design a failure scenario where:

• A transaction partially completes

• Data becomes inconsistent

Then:

• Explain precisely how ACID prevents this

Assessment Rubric (Student Checklist)