Objective
Create a specialized system for managing X-ray radiographic images with support for multiple views, anatomical regions, and diagnostic annotations.
Step 1: Create X-Ray Patients Table
Store patient information for X-ray studies.
CREATE TABLE xray_patients (
id INTEGER PRIMARY KEY,
patient_id VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(200),
date_of_birth DATE,
gender VARCHAR(20),
height_cm INTEGER,
weight_kg DECIMAL(5, 2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create X-Ray Exams Table
Store X-ray examination records.
CREATE TABLE xray_exams (
id INTEGER PRIMARY KEY,
patient_id INTEGER NOT NULL,
exam_date DATE NOT NULL,
exam_time TIME,
body_region VARCHAR(100) NOT NULL,
exam_type VARCHAR(100),
clinical_indication TEXT,
referring_physician VARCHAR(200),
radiologist VARCHAR(200),
technologist VARCHAR(200),
exam_status VARCHAR(50) DEFAULT 'acquired',
report_status VARCHAR(50) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (patient_id) REFERENCES xray_patients(id)
);
Step 3: Create X-Ray Images Table
Store individual X-ray images.
CREATE TABLE xray_images (
id INTEGER PRIMARY KEY,
exam_id INTEGER NOT NULL,
image_file IMAGE(PNG),
thumbnail IMAGE(JPEG),
view_position VARCHAR(50) NOT NULL,
laterality VARCHAR(20),
patient_orientation VARCHAR(50),
exposure_kvp INTEGER,
exposure_mas DECIMAL(6, 2),
distance_cm INTEGER,
image_quality VARCHAR(50),
rows INTEGER,
columns INTEGER,
bits_allocated INTEGER,
file_size BIGINT,
acquisition_time TIMESTAMP,
FOREIGN KEY (exam_id) REFERENCES xray_exams(id)
);
Step 4: Create Annotations Table
Store radiologist markings and findings.
CREATE TABLE xray_annotations (
id INTEGER PRIMARY KEY,
image_id INTEGER NOT NULL,
annotation_type VARCHAR(50),
x_position DECIMAL(6, 2),
y_position DECIMAL(6, 2),
width DECIMAL(6, 2),
height DECIMAL(6, 2),
label VARCHAR(255),
description TEXT,
severity VARCHAR(50),
annotated_by VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (image_id) REFERENCES xray_images(id)
);
Step 5: Create Reports Table
Store radiology reports.
CREATE TABLE xray_reports (
id INTEGER PRIMARY KEY,
exam_id INTEGER NOT NULL,
findings TEXT,
impression TEXT,
recommendations TEXT,
report_status VARCHAR(50) DEFAULT 'draft',
reported_by VARCHAR(200),
reported_at TIMESTAMP,
verified_by VARCHAR(200),
verified_at TIMESTAMP,
FOREIGN KEY (exam_id) REFERENCES xray_exams(id)
);
Step 6: Insert Sample Patients
Add patient records.
INSERT INTO xray_patients (id, patient_id, name, date_of_birth, gender, height_cm, weight_kg) VALUES
(1, 'XR-PAT-001', 'James Wilson', '1958-05-20', 'Male', 178, 82.5),
(2, 'XR-PAT-002', 'Emily Chen', '1985-11-12', 'Female', 165, 58.0),
(3, 'XR-PAT-003', 'Robert Taylor', '1972-03-08', 'Male', 182, 95.0),
(4, 'XR-PAT-004', 'Lisa Martinez', '1990-07-25', 'Female', 160, 55.5),
(5, 'XR-PAT-005', 'David Brown', '1965-09-30', 'Male', 175, 78.0);
Step 7: Insert X-Ray Exams
Add examination records.
INSERT INTO xray_exams (id, patient_id, exam_date, exam_time, body_region, exam_type, clinical_indication, referring_physician, radiologist, technologist, exam_status, report_status) VALUES
(1, 1, '2024-01-15', '09:30:00', 'CHEST', 'Chest X-Ray', 'Shortness of breath, rule out pneumonia', 'Dr. Anderson', 'Dr. Miller', 'Tech. Wilson', 'completed', 'final'),
(2, 1, '2024-01-15', '09:45:00', 'HAND', 'Hand X-Ray', 'Right hand pain after fall', 'Dr. Anderson', 'Dr. Miller', 'Tech. Wilson', 'completed', 'final'),
(3, 2, '2024-01-16', '10:15:00', 'SPINE', 'Lumbar Spine X-Ray', 'Lower back pain', 'Dr. Thompson', 'Dr. Garcia', 'Tech. Brown', 'completed', 'final'),
(4, 3, '2024-01-17', '14:00:00', 'CHEST', 'Chest X-Ray', 'Pre-operative evaluation', 'Dr. White', 'Dr. Miller', 'Tech. Wilson', 'completed', 'pending'),
(5, 4, '2024-01-18', '11:30:00', 'KNEE', 'Knee X-Ray', 'Left knee pain, possible fracture', 'Dr. Lee', 'Dr. Garcia', 'Tech. Brown', 'completed', 'final'),
(6, 5, '2024-01-19', '08:45:00', 'ABDOMEN', 'Abdominal X-Ray', 'Abdominal pain, constipation', 'Dr. Anderson', 'Dr. Miller', 'Tech. Davis', 'completed', 'draft');
Step 8: Insert X-Ray Images
Add image records with views.
INSERT INTO xray_images (id, exam_id, view_position, laterality, patient_orientation, exposure_kvp, exposure_mas, distance_cm, image_quality, rows, columns, bits_allocated, file_size, acquisition_time) VALUES
-- Chest X-Ray (PA and Lateral)
(1, 1, 'PA', NULL, 'STANDING', 120, 2.5, 180, 'good', 3000, 2500, 14, 15000000, '2024-01-15 09:31:00'),
(2, 1, 'LATERAL', 'LEFT', 'STANDING', 125, 4.0, 180, 'good', 3000, 2500, 14, 15000000, '2024-01-15 09:33:00'),
-- Hand X-Ray (3 views)
(3, 2, 'PA', 'RIGHT', 'PRONE', 55, 3.0, 100, 'excellent', 2048, 2048, 12, 8388608, '2024-01-15 09:46:00'),
(4, 2, 'OBLIQUE', 'RIGHT', 'PRONE', 55, 3.0, 100, 'excellent', 2048, 2048, 12, 8388608, '2024-01-15 09:47:00'),
(5, 2, 'LATERAL', 'RIGHT', 'LATERAL', 55, 3.0, 100, 'good', 2048, 2048, 12, 8388608, '2024-01-15 09:48:00'),
-- Lumbar Spine (AP and Lateral)
(6, 3, 'AP', NULL, 'SUPINE', 80, 40.0, 100, 'good', 2500, 2000, 14, 10000000, '2024-01-16 10:16:00'),
(7, 3, 'LATERAL', NULL, 'LATERAL', 90, 80.0, 100, 'acceptable', 2500, 2000, 14, 10000000, '2024-01-16 10:18:00'),
-- Knee X-Ray (3 views)
(8, 5, 'AP', 'LEFT', 'STANDING', 60, 5.0, 100, 'excellent', 2500, 2000, 12, 10000000, '2024-01-18 11:31:00'),
(9, 5, 'LATERAL', 'LEFT', 'LATERAL', 65, 6.0, 100, 'excellent', 2500, 2000, 12, 10000000, '2024-01-18 11:33:00'),
(10, 5, 'SUNRISE', 'LEFT', 'PRONE', 60, 4.0, 100, 'good', 2500, 2000, 12, 10000000, '2024-01-18 11:35:00');