ghstshdw
77 lines
3.6 KiB
Transact-SQL
77 lines
3.6 KiB
Transact-SQL
-- MSSQL PHI Seed Script
|
|
-- Run against target MSSQL instance to populate test data
|
|
|
|
-- Create database if not exists
|
|
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'phi_test')
|
|
BEGIN
|
|
CREATE DATABASE phi_test;
|
|
END
|
|
GO
|
|
|
|
USE phi_test;
|
|
GO
|
|
|
|
-- Drop tables if they exist for clean re-seeding
|
|
IF OBJECT_ID('dbo.BreachLog', 'U') IS NOT NULL DROP TABLE dbo.BreachLog;
|
|
IF OBJECT_ID('dbo.Claims', 'U') IS NOT NULL DROP TABLE dbo.Claims;
|
|
IF OBJECT_ID('dbo.Patients', 'U') IS NOT NULL DROP TABLE dbo.Patients;
|
|
GO
|
|
|
|
CREATE TABLE dbo.Patients (
|
|
PatientID INT PRIMARY KEY,
|
|
FullName NVARCHAR(100),
|
|
SSN VARCHAR(11),
|
|
DateOfBirth DATE,
|
|
MedicalRecordNumber VARCHAR(20),
|
|
Phone VARCHAR(14),
|
|
Email VARCHAR(100),
|
|
Address VARCHAR(200),
|
|
InsurancePolicyNumber VARCHAR(30),
|
|
Diagnosis NVARCHAR(200)
|
|
);
|
|
|
|
INSERT INTO dbo.Patients VALUES
|
|
(10001, 'John M. Whitfield', '573-44-9281', '1983-07-15', 'MRN-77441', '(414) 555-0174', 'jwhitfield@email.com', '4421 Elm Street Milwaukee WI 53202', 'Aetna-A992881', 'Type 2 Diabetes'),
|
|
(10002, 'Dr. Sarah K. Chen', '819-77-3341', '1971-03-22', 'MRN-33018', '(312) 555-9912', 'schen@midwestmed.org', '1010 Lake Shore Dr Chicago IL 60611', 'Cigna-448172', 'Hypertension'),
|
|
(10003, 'James O. O''Brien', '819-77-3341', '1955-11-08', 'MRN-66109', '(513) 555-2288', 'jobrien@gmail.com', '892 Vine Street Cincinnati OH 45202', 'UnitedHealth-77223', 'Chronic Back Pain'),
|
|
(10004, 'Maria Elena Rodriguez', '622-11-0099', '1990-04-30', 'MRN-91503', '(214) 555-6610', 'mrodriguez@texashealth.net', '7715 Oak Park Ave Dallas TX 75235', 'BCBS-338190', 'Anxiety Disorder'),
|
|
(10005, 'Robert D. Washington', '441-28-7763', '1967-09-14', 'MRN-55221', '(313) 555-0044', 'rwashington@email.com', '2034 Michigan Ave Detroit MI 48226', 'Medicaid-119204', 'COPD'),
|
|
(10006, 'Lisa Ann Nakamura', '441-28-7763', '1988-01-03', 'MRN-88712', '(206) 555-3390', 'lnakamura@uwnmed.org', '556 Pine Rd Seattle WA 98101', 'Premera-661733', 'Asthma'),
|
|
(10007, 'Michael T. O''Sullivan', '733-90-1122', '1979-06-19', 'MRN-22909', '(503) 555-8711', 'msullivan@hrcare.org', '188 Crescent Bay Dr Portland OR 97201', 'Regence-442180', 'Arthritis'),
|
|
(10008, 'Angela R. Petit', '662-41-0039', '1962-12-25', 'MRN-77660', '(615) 555-2200', 'apetit@nhsmail.org', '9900 Broadway Nashville TN 37203', 'Humana-993371', 'Breast Cancer Stage IIA');
|
|
GO
|
|
|
|
CREATE TABLE dbo.Claims (
|
|
ClaimID VARCHAR(20) PRIMARY KEY,
|
|
PatientID INT,
|
|
ServiceDate DATE,
|
|
BilledAmount DECIMAL(10,2),
|
|
DiagnosisCode VARCHAR(10),
|
|
ProviderNPI VARCHAR(10),
|
|
PolicyNumber VARCHAR(30),
|
|
SSN VARCHAR(11),
|
|
FK_PatientID INT FOREIGN KEY REFERENCES dbo.Patients(PatientID)
|
|
);
|
|
|
|
INSERT INTO dbo.Claims VALUES
|
|
('CLM-2026-001881', 10001, '2026-01-15', 2840.00, 'E11.9', '1154998722', 'AET-772-441-0091', '573-44-9281'),
|
|
('CLM-2026-001882', 10002, '2026-01-22', 550.00, 'M54.5', '1154998722', 'UHG-992-448-1177', '819-77-3341'),
|
|
('CLM-2026-001883', 10003, '2026-02-03', 1275.00, 'F41.1', '1154998722', 'BCBS-TX-772-441-9914', '622-11-0099'),
|
|
('CLM-2026-001884', 10004, '2026-02-18', 320.00, 'J45.20', '1154998722', 'PREM-WS-992-441-8817', '441-28-7763');
|
|
GO
|
|
|
|
-- HIPAA Breach Log
|
|
CREATE TABLE dbo.BreachLog (
|
|
LogID INT PRIMARY KEY IDENTITY(1,1),
|
|
BreachDate DATETIME,
|
|
PatientID INT,
|
|
Description NVARCHAR(500),
|
|
SSN VARCHAR(11),
|
|
ReportedTo VARCHAR(50)
|
|
);
|
|
|
|
INSERT INTO dbo.BreachLog VALUES
|
|
('2026-03-01 14:22:00', 10003, 'Unauthorized access to patient record by staff member', '819-77-3341', 'HHS OCR'),
|
|
('2026-03-15 09:45:00', 10001, 'Email containing unencrypted PHI sent to wrong recipient', '573-44-9281', 'HHS OCR');
|
|
GO
|