-- ============================================================ -- GMBA 612: Database Design & SQL -- Week 6: Prescription + Insurance Database -- Platform: Oracle APEX -- ============================================================ -- This script creates the complete normalized (3NF) database -- for the prescription + insurance scenario. -- -- NAMING CONVENTION: All tables prefixed with "w6_" -- ============================================================ -- ============================================================ -- STEP 1: DROP TABLES (in reverse dependency order) -- ============================================================ -- We must drop tables in REVERSE order of creation. -- Why? Because you can't drop a table that another table -- depends on. If Patient has children (Prescription, -- PatientInsurance), Oracle will refuse to drop it unless -- we drop the children first. -- -- Think of it like unstacking blocks: -- Built bottom-up --> Torn down top-down -- -- Drop order: children first, then parents. -- ============================================================ -- Level 3: Tables with the most dependencies (grandchildren) -- w6_prescription_item depends on w6_prescription AND w6_medication BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_prescription_item CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / -- Level 2: Tables that reference strong entities (children) -- w6_prescription depends on w6_patient, w6_doctor, w6_insurance BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_prescription CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / -- w6_patient_insurance depends on w6_patient AND w6_insurance BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_patient_insurance CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / -- Level 1: Strong entities (parents) - no dependencies on other tables BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_patient CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_doctor CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_insurance CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_medication CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / -- ============================================================ -- STEP 2: CREATE STRONG ENTITIES (independent tables) -- ============================================================ -- Strong entities are created FIRST because they don't depend -- on any other table. They have their own primary key and -- exist independently in the real world. -- -- A Patient exists whether or not they have a prescription. -- A Doctor exists whether or not they've written one. -- An Insurance company exists whether or not anyone is enrolled. -- A Medication exists whether or not it's been prescribed. -- -- These are the "parent" tables. Everything else references them. -- ============================================================ -- ---------------------------------------- -- PATIENT (Strong Entity) -- ---------------------------------------- -- A patient exists independently. No foreign keys needed. -- This is a parent table that will be referenced by -- w6_prescription and w6_patient_insurance. -- ---------------------------------------- CREATE TABLE w6_patient ( patient_id VARCHAR2(10) PRIMARY KEY, patient_name VARCHAR2(100) NOT NULL, date_of_birth DATE NOT NULL, phone VARCHAR2(15), street VARCHAR2(100), city VARCHAR2(50), state CHAR(2), zip_code VARCHAR2(10) ); -- ---------------------------------------- -- DOCTOR (Strong Entity) -- ---------------------------------------- -- A doctor exists independently. No foreign keys needed. -- DEA number is unique — no two doctors share the same -- license to prescribe controlled substances. -- This is a parent table referenced by w6_prescription. -- ---------------------------------------- CREATE TABLE w6_doctor ( doctor_id VARCHAR2(10) PRIMARY KEY, doctor_name VARCHAR2(100) NOT NULL, dea_number VARCHAR2(20) NOT NULL UNIQUE, specialty VARCHAR2(50), phone VARCHAR2(15) ); -- ---------------------------------------- -- INSURANCE (Strong Entity) -- ---------------------------------------- -- An insurance company exists independently. No foreign keys. -- This is a parent table referenced by w6_prescription -- and w6_patient_insurance. -- ---------------------------------------- CREATE TABLE w6_insurance ( insurance_id VARCHAR2(10) PRIMARY KEY, company_name VARCHAR2(100) NOT NULL, coverage_type VARCHAR2(20) NOT NULL CHECK (coverage_type IN ('PPO','HMO','Medicaid','Medicare','Tricare')), contact_phone VARCHAR2(15), website VARCHAR2(100) ); -- ---------------------------------------- -- MEDICATION (Strong Entity) -- ---------------------------------------- -- A medication exists independently. No foreign keys. -- This is a parent table referenced by w6_prescription_item. -- ---------------------------------------- CREATE TABLE w6_medication ( medication_id VARCHAR2(10) PRIMARY KEY, medication_name VARCHAR2(100) NOT NULL, dosage VARCHAR2(30) NOT NULL, form VARCHAR2(20) DEFAULT 'Tablet' CHECK (form IN ('Tablet','Capsule','Inhaler','Injection','Liquid','Patch','Cream')), manufacturer VARCHAR2(100) ); -- ============================================================ -- STEP 3: CREATE TABLES WITH FOREIGN KEYS -- ============================================================ -- These tables CANNOT be created before the strong entities -- because they contain FOREIGN KEY constraints that REFERENCE -- the strong entity tables. -- -- If we tried to create w6_prescription before w6_patient, -- Oracle would throw an error: -- ORA-00942: table or view does not exist -- -- The foreign key says "this column must point to a real -- record in another table." That other table must exist first. -- -- Rule: Parent tables FIRST, child tables AFTER. -- ============================================================ -- ---------------------------------------- -- PATIENT_INSURANCE (Bridge Table) -- ---------------------------------------- -- This resolves the many-to-many relationship between -- Patient and Insurance: -- - One patient can have multiple insurance plans -- - One insurance company covers many patients -- -- WHY foreign keys must come after strong entities: -- patient_id REFERENCES w6_patient --> w6_patient must exist -- insurance_id REFERENCES w6_insurance --> w6_insurance must exist -- -- The composite primary key (patient_id + insurance_id) -- ensures each patient-insurance combination is unique. -- -- Policy number, group number, and copay belong HERE because -- they describe the specific relationship between THIS patient -- and THIS insurer — not the patient alone or the insurer alone. -- ---------------------------------------- CREATE TABLE w6_patient_insurance ( patient_id VARCHAR2(10) NOT NULL REFERENCES w6_patient(patient_id), insurance_id VARCHAR2(10) NOT NULL REFERENCES w6_insurance(insurance_id), policy_number VARCHAR2(20) NOT NULL, group_number VARCHAR2(20), copay NUMBER(6,2) DEFAULT 0 CHECK (copay >= 0), -- PRIMARY KEY (patient_id, insurance_id) ); -- ---------------------------------------- -- PRESCRIPTION (Weak Entity) -- ---------------------------------------- -- A prescription CANNOT exist without a patient, a doctor, -- and an insurance plan. It depends on three strong entities. -- -- WHY foreign keys must come after strong entities: -- patient_id REFERENCES w6_patient --> w6_patient must exist -- doctor_id REFERENCES w6_doctor --> w6_doctor must exist -- insurance_id REFERENCES w6_insurance --> w6_insurance must exist -- -- If you try to INSERT a prescription for patient_id 'P999' -- and that patient doesn't exist in w6_patient, Oracle will -- reject it: ORA-02291: integrity constraint violated. -- -- This is referential integrity in action — no orphan records. -- ---------------------------------------- CREATE TABLE w6_prescription ( rx_number VARCHAR2(15) PRIMARY KEY, rx_date DATE NOT NULL, patient_id VARCHAR2(10) NOT NULL REFERENCES w6_patient(patient_id), doctor_id VARCHAR2(10) NOT NULL REFERENCES w6_doctor(doctor_id), insurance_id VARCHAR2(10) NOT NULL REFERENCES w6_insurance(insurance_id), status VARCHAR2(20) DEFAULT 'Active' CHECK (status IN ('Active','Completed','Cancelled','Expired')) ); -- ---------------------------------------- -- PRESCRIPTION_ITEM (Bridge Table / Weakest Entity) -- ---------------------------------------- -- This resolves the many-to-many relationship between -- Prescription and Medication: -- - One prescription can contain multiple medications -- - One medication can appear on multiple prescriptions -- -- WHY this table is created LAST: -- rx_number REFERENCES w6_prescription --> w6_prescription must exist -- medication_id REFERENCES w6_medication --> w6_medication must exist -- -- w6_prescription itself depends on w6_patient, w6_doctor, -- and w6_insurance — so this table sits at the very bottom -- of the dependency chain. -- -- Qty, refills, and instructions depend on the COMBINATION -- of which prescription and which drug — not on either alone. -- That's why they live here, not in Prescription or Medication. -- ---------------------------------------- CREATE TABLE w6_prescription_item ( rx_number VARCHAR2(15) NOT NULL REFERENCES w6_prescription(rx_number), medication_id VARCHAR2(10) NOT NULL REFERENCES w6_medication(medication_id), quantity NUMBER(5) NOT NULL CHECK (quantity > 0), refills NUMBER(2) DEFAULT 0 CHECK (refills BETWEEN 0 AND 12), instructions VARCHAR2(200), -- PRIMARY KEY (rx_number, medication_id) ); -- ============================================================ -- STEP 4: INSERT TEST DATA -- ============================================================ -- Data must be inserted in the SAME ORDER as table creation: -- strong entities first, then children, then grandchildren. -- -- You can't insert a prescription for a patient who doesn't -- exist yet. Referential integrity enforces this at all times. -- ============================================================ -- ---------------------------------------- -- PATIENT DATA (20 patients) -- ---------------------------------------- INSERT INTO w6_patient VALUES ('P001', 'Maria Lopez', TO_DATE('03/22/1985','MM/DD/YYYY'), '(814) 555-0123', '450 Peach St', 'Erie', 'PA', '16501'); INSERT INTO w6_patient VALUES ('P002', 'James Wilson', TO_DATE('11/05/1972','MM/DD/YYYY'), '(814) 555-0456', '120 State St', 'Erie', 'PA', '16502'); INSERT INTO w6_patient VALUES ('P003', 'Sarah Chen', TO_DATE('07/14/1990','MM/DD/YYYY'), '(814) 555-0789', '88 Maple Dr', 'Erie', 'PA', '16503'); INSERT INTO w6_patient VALUES ('P004', 'Robert Thompson', TO_DATE('01/30/1968','MM/DD/YYYY'), '(814) 555-1012', '305 Oak Ave', 'Erie', 'PA', '16504'); INSERT INTO w6_patient VALUES ('P005', 'Jennifer Garcia', TO_DATE('09/18/1995','MM/DD/YYYY'), '(814) 555-1345', '77 Elm St', 'Meadville', 'PA', '16335'); INSERT INTO w6_patient VALUES ('P006', 'Michael Brown', TO_DATE('12/03/1980','MM/DD/YYYY'), '(814) 555-1678', '412 Cedar Ln', 'Edinboro', 'PA', '16412'); INSERT INTO w6_patient VALUES ('P007', 'Emily Davis', TO_DATE('05/22/1988','MM/DD/YYYY'), '(814) 555-1901', '190 Pine St', 'Erie', 'PA', '16505'); INSERT INTO w6_patient VALUES ('P008', 'David Martinez', TO_DATE('08/11/1975','MM/DD/YYYY'), '(814) 555-2234', '555 Walnut Ave', 'Corry', 'PA', '16407'); INSERT INTO w6_patient VALUES ('P009', 'Lisa Anderson', TO_DATE('04/07/1992','MM/DD/YYYY'), '(814) 555-2567', '23 Birch Rd', 'Erie', 'PA', '16506'); INSERT INTO w6_patient VALUES ('P010', 'Christopher Lee', TO_DATE('06/25/1963','MM/DD/YYYY'), '(814) 555-2890', '701 River Dr', 'Fairview', 'PA', '16415'); INSERT INTO w6_patient VALUES ('P011', 'Amanda Taylor', TO_DATE('10/15/1987','MM/DD/YYYY'), '(814) 555-3123', '88 Highland Ave', 'Erie', 'PA', '16507'); INSERT INTO w6_patient VALUES ('P012', 'Daniel Harris', TO_DATE('02/28/1970','MM/DD/YYYY'), '(814) 555-3456', '340 Market St', 'Girard', 'PA', '16417'); INSERT INTO w6_patient VALUES ('P013', 'Jessica White', TO_DATE('11/20/1993','MM/DD/YYYY'), '(814) 555-3789', '15 Lake Rd', 'Erie', 'PA', '16508'); INSERT INTO w6_patient VALUES ('P014', 'Matthew Clark', TO_DATE('03/09/1982','MM/DD/YYYY'), '(814) 555-4012', '620 Broadway', 'North East', 'PA', '16428'); INSERT INTO w6_patient VALUES ('P015', 'Ashley Robinson', TO_DATE('07/01/1997','MM/DD/YYYY'), '(814) 555-4345', '42 Park Ave', 'Erie', 'PA', '16501'); INSERT INTO w6_patient VALUES ('P016', 'Kevin Walker', TO_DATE('09/14/1974','MM/DD/YYYY'), '(814) 555-4678', '180 Church St', 'Waterford', 'PA', '16441'); INSERT INTO w6_patient VALUES ('P017', 'Stephanie King', TO_DATE('12/22/1989','MM/DD/YYYY'), '(814) 555-4901', '95 Spring St', 'Erie', 'PA', '16509'); INSERT INTO w6_patient VALUES ('P018', 'Brian Scott', TO_DATE('04/17/1965','MM/DD/YYYY'), '(814) 555-5234', '730 Forest Ave', 'Union City', 'PA', '16438'); INSERT INTO w6_patient VALUES ('P019', 'Nicole Adams', TO_DATE('08/30/1991','MM/DD/YYYY'), '(814) 555-5567', '50 Sunset Blvd', 'Erie', 'PA', '16510'); INSERT INTO w6_patient VALUES ('P020', 'William Nguyen', TO_DATE('01/12/1978','MM/DD/YYYY'), '(814) 555-5890', '275 Washington St', 'Harborcreek', 'PA', '16421'); -- ---------------------------------------- -- DOCTOR DATA (10 doctors) -- ---------------------------------------- INSERT INTO w6_doctor VALUES ('D001', 'Dr. Angela Chen', 'AC1234563', 'Internal Medicine', '(814) 555-9000'); INSERT INTO w6_doctor VALUES ('D002', 'Dr. Raj Patel', 'BP7891234', 'Family Medicine', '(814) 555-9100'); INSERT INTO w6_doctor VALUES ('D003', 'Dr. Susan Miller', 'SM4567890', 'Cardiology', '(814) 555-9200'); INSERT INTO w6_doctor VALUES ('D004', 'Dr. Marcus Johnson', 'MJ2345678', 'Endocrinology', '(814) 555-9300'); INSERT INTO w6_doctor VALUES ('D005', 'Dr. Karen Williams', 'KW8901234', 'Psychiatry', '(814) 555-9400'); INSERT INTO w6_doctor VALUES ('D006', 'Dr. David Park', 'DP3456789', 'Pulmonology', '(814) 555-9500'); INSERT INTO w6_doctor VALUES ('D007', 'Dr. Maria Santos', 'MS6789012', 'Dermatology', '(814) 555-9600'); INSERT INTO w6_doctor VALUES ('D008', 'Dr. James O''Brien', 'JO1234567', 'Orthopedics', '(814) 555-9700'); INSERT INTO w6_doctor VALUES ('D009', 'Dr. Linda Kim', 'LK5678901', 'Gastroenterology', '(814) 555-9800'); INSERT INTO w6_doctor VALUES ('D010', 'Dr. Ahmed Hassan', 'AH9012345', 'Neurology', '(814) 555-9900'); -- ---------------------------------------- -- INSURANCE DATA (8 plans) -- ---------------------------------------- INSERT INTO w6_insurance VALUES ('INS-001', 'BlueCross BlueShield PA', 'PPO', '(800) 555-2583', 'www.bcbspa.com'); INSERT INTO w6_insurance VALUES ('INS-002', 'BlueCross BlueShield PA', 'HMO', '(800) 555-2583', 'www.bcbspa.com'); INSERT INTO w6_insurance VALUES ('INS-003', 'Aetna Health', 'PPO', '(800) 555-3264', 'www.aetna.com'); INSERT INTO w6_insurance VALUES ('INS-004', 'UnitedHealthcare', 'PPO', '(800) 555-4195', 'www.uhc.com'); INSERT INTO w6_insurance VALUES ('INS-005', 'UnitedHealthcare', 'HMO', '(800) 555-4195', 'www.uhc.com'); INSERT INTO w6_insurance VALUES ('INS-006', 'UPMC Health Plan', 'PPO', '(800) 555-5826', 'www.upmchealthplan.com'); INSERT INTO w6_insurance VALUES ('INS-007', 'Highmark', 'HMO', '(800) 555-6457', 'www.highmark.com'); INSERT INTO w6_insurance VALUES ('INS-008', 'Medicare', 'Medicare', '(800) 555-7088', 'www.medicare.gov'); -- ---------------------------------------- -- MEDICATION DATA (20 medications) -- ---------------------------------------- INSERT INTO w6_medication VALUES ('M001', 'Lisinopril', '10mg', 'Tablet', 'Lupin Pharmaceuticals'); INSERT INTO w6_medication VALUES ('M002', 'Lisinopril', '20mg', 'Tablet', 'Lupin Pharmaceuticals'); INSERT INTO w6_medication VALUES ('M003', 'Metformin', '500mg', 'Tablet', 'Teva Pharmaceutical'); INSERT INTO w6_medication VALUES ('M004', 'Metformin', '1000mg','Tablet', 'Teva Pharmaceutical'); INSERT INTO w6_medication VALUES ('M005', 'Atorvastatin', '20mg', 'Tablet', 'Pfizer'); INSERT INTO w6_medication VALUES ('M006', 'Atorvastatin', '40mg', 'Tablet', 'Pfizer'); INSERT INTO w6_medication VALUES ('M007', 'Amlodipine', '5mg', 'Tablet', 'Pfizer'); INSERT INTO w6_medication VALUES ('M008', 'Amlodipine', '10mg', 'Tablet', 'Pfizer'); INSERT INTO w6_medication VALUES ('M009', 'Metoprolol', '50mg', 'Tablet', 'AstraZeneca'); INSERT INTO w6_medication VALUES ('M010', 'Omeprazole', '20mg', 'Capsule', 'AstraZeneca'); INSERT INTO w6_medication VALUES ('M011', 'Losartan', '50mg', 'Tablet', 'Merck'); INSERT INTO w6_medication VALUES ('M012', 'Amoxicillin', '500mg', 'Capsule', 'GlaxoSmithKline'); INSERT INTO w6_medication VALUES ('M013', 'Azithromycin', '250mg', 'Tablet', 'Pfizer'); INSERT INTO w6_medication VALUES ('M014', 'Gabapentin', '300mg', 'Capsule', 'Greenstone'); INSERT INTO w6_medication VALUES ('M015', 'Levothyroxine', '50mcg', 'Tablet', 'AbbVie'); INSERT INTO w6_medication VALUES ('M016', 'Sertraline', '50mg', 'Tablet', 'Pfizer'); INSERT INTO w6_medication VALUES ('M017', 'Fluoxetine', '20mg', 'Capsule', 'Eli Lilly'); INSERT INTO w6_medication VALUES ('M018', 'Prednisone', '10mg', 'Tablet', 'Hikma Pharmaceuticals'); INSERT INTO w6_medication VALUES ('M019', 'Albuterol', '90mcg', 'Inhaler', 'Teva Pharmaceutical'); INSERT INTO w6_medication VALUES ('M020', 'Hydrochlorothiazide', '25mg', 'Tablet', 'Aurobindo Pharma'); -- ---------------------------------------- -- PATIENT_INSURANCE DATA -- ---------------------------------------- -- Each row represents one patient's enrollment in one plan. -- Some patients have multiple insurance plans (e.g., P001 has -- both a primary PPO and Medicare as secondary). -- ---------------------------------------- INSERT INTO w6_patient_insurance VALUES ('P001', 'INS-001', 'BC-90421', 'GRP-5500', 15.00); INSERT INTO w6_patient_insurance VALUES ('P002', 'INS-003', 'AE-77130', 'GRP-8800', 25.00); INSERT INTO w6_patient_insurance VALUES ('P003', 'INS-004', 'UH-44210', 'GRP-3300', 20.00); INSERT INTO w6_patient_insurance VALUES ('P004', 'INS-006', 'UP-88315', 'GRP-1100', 30.00); INSERT INTO w6_patient_insurance VALUES ('P004', 'INS-008', 'MC-55120', NULL, 0.00); INSERT INTO w6_patient_insurance VALUES ('P005', 'INS-001', 'BC-61034', 'GRP-5500', 15.00); INSERT INTO w6_patient_insurance VALUES ('P006', 'INS-005', 'UH-93201', 'GRP-7700', 20.00); INSERT INTO w6_patient_insurance VALUES ('P007', 'INS-007', 'HM-12045', 'GRP-4400', 25.00); INSERT INTO w6_patient_insurance VALUES ('P008', 'INS-002', 'BC-33762', 'GRP-5500', 10.00); INSERT INTO w6_patient_insurance VALUES ('P009', 'INS-001', 'BC-45098', 'GRP-2200', 15.00); INSERT INTO w6_patient_insurance VALUES ('P010', 'INS-008', 'MC-70215', NULL, 0.00); INSERT INTO w6_patient_insurance VALUES ('P010', 'INS-006', 'UP-22104', 'GRP-1100', 30.00); INSERT INTO w6_patient_insurance VALUES ('P011', 'INS-003', 'AE-50983', 'GRP-8800', 25.00); INSERT INTO w6_patient_insurance VALUES ('P012', 'INS-004', 'UH-18456', 'GRP-6600', 20.00); INSERT INTO w6_patient_insurance VALUES ('P013', 'INS-007', 'HM-62310', 'GRP-4400', 25.00); INSERT INTO w6_patient_insurance VALUES ('P014', 'INS-001', 'BC-80175', 'GRP-9900', 15.00); INSERT INTO w6_patient_insurance VALUES ('P015', 'INS-005', 'UH-71024', 'GRP-3300', 20.00); INSERT INTO w6_patient_insurance VALUES ('P016', 'INS-006', 'UP-40583', 'GRP-1100', 30.00); INSERT INTO w6_patient_insurance VALUES ('P017', 'INS-002', 'BC-92641', 'GRP-5500', 10.00); INSERT INTO w6_patient_insurance VALUES ('P018', 'INS-008', 'MC-31098', NULL, 0.00); INSERT INTO w6_patient_insurance VALUES ('P019', 'INS-003', 'AE-25417', 'GRP-8800', 25.00); INSERT INTO w6_patient_insurance VALUES ('P020', 'INS-004', 'UH-63089', 'GRP-6600', 20.00); INSERT INTO w6_patient_insurance VALUES ('P001', 'INS-008', 'MC-11234', NULL, 0.00); -- ---------------------------------------- -- PRESCRIPTION DATA (30 prescriptions) -- ---------------------------------------- -- Each prescription references a patient, a doctor, and -- the insurance plan used for billing. -- -- Notice: we can only insert these AFTER the patient, doctor, -- and insurance records exist. Try inserting a prescription -- for patient 'P999' — Oracle will reject it because P999 -- doesn't exist in w6_patient. That's referential integrity. -- ---------------------------------------- INSERT INTO w6_prescription VALUES ('RX-10001', TO_DATE('01/20/2025','MM/DD/YYYY'), 'P001', 'D001', 'INS-001', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10002', TO_DATE('01/22/2025','MM/DD/YYYY'), 'P002', 'D002', 'INS-003', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10003', TO_DATE('01/25/2025','MM/DD/YYYY'), 'P001', 'D002', 'INS-001', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10004', TO_DATE('01/26/2025','MM/DD/YYYY'), 'P003', 'D001', 'INS-004', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10005', TO_DATE('01/28/2025','MM/DD/YYYY'), 'P004', 'D003', 'INS-006', 'Completed'); INSERT INTO w6_prescription VALUES ('RX-10006', TO_DATE('01/29/2025','MM/DD/YYYY'), 'P005', 'D002', 'INS-001', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10007', TO_DATE('02/01/2025','MM/DD/YYYY'), 'P006', 'D004', 'INS-005', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10008', TO_DATE('02/02/2025','MM/DD/YYYY'), 'P007', 'D005', 'INS-007', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10009', TO_DATE('02/03/2025','MM/DD/YYYY'), 'P008', 'D001', 'INS-002', 'Completed'); INSERT INTO w6_prescription VALUES ('RX-10010', TO_DATE('02/05/2025','MM/DD/YYYY'), 'P009', 'D006', 'INS-001', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10011', TO_DATE('02/06/2025','MM/DD/YYYY'), 'P010', 'D003', 'INS-008', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10012', TO_DATE('02/07/2025','MM/DD/YYYY'), 'P011', 'D002', 'INS-003', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10013', TO_DATE('02/08/2025','MM/DD/YYYY'), 'P012', 'D007', 'INS-004', 'Cancelled'); INSERT INTO w6_prescription VALUES ('RX-10014', TO_DATE('02/10/2025','MM/DD/YYYY'), 'P013', 'D005', 'INS-007', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10015', TO_DATE('02/11/2025','MM/DD/YYYY'), 'P014', 'D008', 'INS-001', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10016', TO_DATE('02/12/2025','MM/DD/YYYY'), 'P015', 'D002', 'INS-005', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10017', TO_DATE('02/14/2025','MM/DD/YYYY'), 'P016', 'D009', 'INS-006', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10018', TO_DATE('02/15/2025','MM/DD/YYYY'), 'P017', 'D001', 'INS-002', 'Expired'); INSERT INTO w6_prescription VALUES ('RX-10019', TO_DATE('02/17/2025','MM/DD/YYYY'), 'P018', 'D010', 'INS-008', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10020', TO_DATE('02/18/2025','MM/DD/YYYY'), 'P019', 'D004', 'INS-003', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10021', TO_DATE('02/20/2025','MM/DD/YYYY'), 'P020', 'D002', 'INS-004', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10022', TO_DATE('02/21/2025','MM/DD/YYYY'), 'P001', 'D003', 'INS-001', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10023', TO_DATE('02/22/2025','MM/DD/YYYY'), 'P004', 'D004', 'INS-008', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10024', TO_DATE('02/24/2025','MM/DD/YYYY'), 'P006', 'D001', 'INS-005', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10025', TO_DATE('02/25/2025','MM/DD/YYYY'), 'P009', 'D006', 'INS-001', 'Completed'); INSERT INTO w6_prescription VALUES ('RX-10026', TO_DATE('02/26/2025','MM/DD/YYYY'), 'P002', 'D002', 'INS-003', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10027', TO_DATE('02/27/2025','MM/DD/YYYY'), 'P013', 'D009', 'INS-007', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10028', TO_DATE('02/28/2025','MM/DD/YYYY'), 'P007', 'D005', 'INS-007', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10029', TO_DATE('03/01/2025','MM/DD/YYYY'), 'P015', 'D002', 'INS-005', 'Active'); INSERT INTO w6_prescription VALUES ('RX-10030', TO_DATE('03/02/2025','MM/DD/YYYY'), 'P010', 'D003', 'INS-006', 'Active'); -- ---------------------------------------- -- PRESCRIPTION_ITEM DATA (55 items) -- ---------------------------------------- -- This is the LAST table to receive data because it depends -- on both w6_prescription (which itself depends on patient, -- doctor, and insurance) and w6_medication. -- -- Each row = one medication on one prescription. -- Quantity, refills, and instructions are specific to THIS -- drug on THIS prescription — that's why they live here. -- ---------------------------------------- -- RX-10001: Maria Lopez - Internal Medicine (Dr. Chen) INSERT INTO w6_prescription_item VALUES ('RX-10001', 'M001', 30, 3, 'Take once daily in the morning'); INSERT INTO w6_prescription_item VALUES ('RX-10001', 'M003', 60, 5, 'Take twice daily with meals'); -- RX-10002: James Wilson - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10002', 'M012', 21, 0, 'Take 3 times daily for 7 days'); -- RX-10003: Maria Lopez - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10003', 'M005', 30, 6, 'Take once daily at bedtime'); INSERT INTO w6_prescription_item VALUES ('RX-10003', 'M003', 60, 5, 'Take twice daily with meals'); -- RX-10004: Sarah Chen - Internal Medicine (Dr. Chen) INSERT INTO w6_prescription_item VALUES ('RX-10004', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast'); INSERT INTO w6_prescription_item VALUES ('RX-10004', 'M016', 30, 5, 'Take once daily in the morning'); -- RX-10005: Robert Thompson - Cardiology (Dr. Miller) INSERT INTO w6_prescription_item VALUES ('RX-10005', 'M009', 30, 3, 'Take once daily with food'); INSERT INTO w6_prescription_item VALUES ('RX-10005', 'M001', 30, 3, 'Take once daily in the morning'); INSERT INTO w6_prescription_item VALUES ('RX-10005', 'M020', 30, 6, 'Take once daily in the morning'); -- RX-10006: Jennifer Garcia - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10006', 'M013', 6, 0, 'Take 2 tablets day 1, then 1 daily for 4 days'); INSERT INTO w6_prescription_item VALUES ('RX-10006', 'M010', 30, 2, 'Take once daily before breakfast'); -- RX-10007: Michael Brown - Endocrinology (Dr. Johnson) INSERT INTO w6_prescription_item VALUES ('RX-10007', 'M004', 60, 5, 'Take twice daily with meals'); INSERT INTO w6_prescription_item VALUES ('RX-10007', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast'); -- RX-10008: Emily Davis - Psychiatry (Dr. Williams) INSERT INTO w6_prescription_item VALUES ('RX-10008', 'M016', 30, 5, 'Take once daily in the morning'); INSERT INTO w6_prescription_item VALUES ('RX-10008', 'M014', 90, 3, 'Take 3 times daily as needed for nerve pain'); -- RX-10009: David Martinez - Internal Medicine (Dr. Chen) INSERT INTO w6_prescription_item VALUES ('RX-10009', 'M011', 30, 6, 'Take once daily'); INSERT INTO w6_prescription_item VALUES ('RX-10009', 'M007', 30, 6, 'Take once daily'); -- RX-10010: Lisa Anderson - Pulmonology (Dr. Park) INSERT INTO w6_prescription_item VALUES ('RX-10010', 'M019', 1, 3, '2 puffs every 4-6 hours as needed'); INSERT INTO w6_prescription_item VALUES ('RX-10010', 'M018', 20, 0, 'Take as directed, taper over 10 days'); -- RX-10011: Christopher Lee - Cardiology (Dr. Miller) INSERT INTO w6_prescription_item VALUES ('RX-10011', 'M006', 30, 6, 'Take once daily at bedtime'); INSERT INTO w6_prescription_item VALUES ('RX-10011', 'M009', 30, 3, 'Take once daily with food'); INSERT INTO w6_prescription_item VALUES ('RX-10011', 'M007', 30, 6, 'Take once daily'); -- RX-10012: Amanda Taylor - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10012', 'M017', 30, 5, 'Take once daily in the morning'); -- RX-10013: Daniel Harris - Dermatology (Dr. Santos) [Cancelled] INSERT INTO w6_prescription_item VALUES ('RX-10013', 'M018', 30, 0, 'Take once daily for 14 days'); -- RX-10014: Jessica White - Psychiatry (Dr. Williams) INSERT INTO w6_prescription_item VALUES ('RX-10014', 'M016', 30, 5, 'Take once daily in the morning'); -- RX-10015: Matthew Clark - Orthopedics (Dr. O'Brien) INSERT INTO w6_prescription_item VALUES ('RX-10015', 'M014', 90, 2, 'Take 3 times daily for pain'); INSERT INTO w6_prescription_item VALUES ('RX-10015', 'M018', 15, 0, 'Take as directed, taper over 5 days'); -- RX-10016: Ashley Robinson - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10016', 'M012', 30, 1, 'Take twice daily for 10 days'); INSERT INTO w6_prescription_item VALUES ('RX-10016', 'M010', 30, 2, 'Take once daily before breakfast'); -- RX-10017: Kevin Walker - Gastroenterology (Dr. Kim) INSERT INTO w6_prescription_item VALUES ('RX-10017', 'M010', 30, 6, 'Take once daily before breakfast'); -- RX-10018: Stephanie King - Internal Medicine (Dr. Chen) [Expired] INSERT INTO w6_prescription_item VALUES ('RX-10018', 'M001', 30, 3, 'Take once daily in the morning'); INSERT INTO w6_prescription_item VALUES ('RX-10018', 'M003', 60, 5, 'Take twice daily with meals'); -- RX-10019: Brian Scott - Neurology (Dr. Hassan) INSERT INTO w6_prescription_item VALUES ('RX-10019', 'M014', 90, 3, 'Take 3 times daily'); -- RX-10020: Nicole Adams - Endocrinology (Dr. Johnson) INSERT INTO w6_prescription_item VALUES ('RX-10020', 'M003', 60, 5, 'Take twice daily with meals'); INSERT INTO w6_prescription_item VALUES ('RX-10020', 'M005', 30, 6, 'Take once daily at bedtime'); -- RX-10021: William Nguyen - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10021', 'M008', 30, 6, 'Take once daily'); INSERT INTO w6_prescription_item VALUES ('RX-10021', 'M011', 30, 6, 'Take once daily'); -- RX-10022: Maria Lopez - Cardiology (Dr. Miller) INSERT INTO w6_prescription_item VALUES ('RX-10022', 'M009', 30, 3, 'Take once daily with food'); -- RX-10023: Robert Thompson - Endocrinology (Dr. Johnson) INSERT INTO w6_prescription_item VALUES ('RX-10023', 'M004', 60, 5, 'Take twice daily with meals'); INSERT INTO w6_prescription_item VALUES ('RX-10023', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast'); -- RX-10024: Michael Brown - Internal Medicine (Dr. Chen) INSERT INTO w6_prescription_item VALUES ('RX-10024', 'M002', 30, 6, 'Take once daily in the morning'); INSERT INTO w6_prescription_item VALUES ('RX-10024', 'M020', 30, 6, 'Take once daily in the morning'); -- RX-10025: Lisa Anderson - Pulmonology (Dr. Park) INSERT INTO w6_prescription_item VALUES ('RX-10025', 'M019', 1, 3, '2 puffs every 4-6 hours as needed'); -- RX-10026: James Wilson - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10026', 'M005', 30, 6, 'Take once daily at bedtime'); INSERT INTO w6_prescription_item VALUES ('RX-10026', 'M007', 30, 6, 'Take once daily'); -- RX-10027: Jessica White - Gastroenterology (Dr. Kim) INSERT INTO w6_prescription_item VALUES ('RX-10027', 'M010', 30, 6, 'Take once daily before breakfast'); -- RX-10028: Emily Davis - Psychiatry (Dr. Williams) INSERT INTO w6_prescription_item VALUES ('RX-10028', 'M017', 30, 5, 'Take once daily in the morning'); -- RX-10029: Ashley Robinson - Family Medicine (Dr. Patel) INSERT INTO w6_prescription_item VALUES ('RX-10029', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast'); -- RX-10030: Christopher Lee - Cardiology (Dr. Miller) INSERT INTO w6_prescription_item VALUES ('RX-10030', 'M006', 30, 6, 'Take once daily at bedtime'); INSERT INTO w6_prescription_item VALUES ('RX-10030', 'M020', 30, 6, 'Take once daily in the morning'); -- ============================================================ -- STEP 5: VERIFY THE DATA -- ============================================================ -- Quick counts to confirm everything loaded SELECT 'w6_patient' AS table_name, COUNT(*) AS row_count FROM w6_patient UNION ALL SELECT 'w6_doctor', COUNT(*) FROM w6_doctor UNION ALL SELECT 'w6_insurance', COUNT(*) FROM w6_insurance UNION ALL SELECT 'w6_medication', COUNT(*) FROM w6_medication UNION ALL SELECT 'w6_patient_insurance', COUNT(*) FROM w6_patient_insurance UNION ALL SELECT 'w6_prescription', COUNT(*) FROM w6_prescription UNION ALL SELECT 'w6_prescription_item', COUNT(*) FROM w6_prescription_item ORDER BY table_name; -- ============================================================ -- SAMPLE QUERIES TO TEST THE DATABASE -- ============================================================ -- 1. Show all medications on Maria Lopez's prescriptions SELECT p.patient_name, rx.rx_number, rx.rx_date, m.medication_name, m.dosage, pi.quantity, pi.refills, pi.instructions FROM w6_patient p JOIN w6_prescription rx ON p.patient_id = rx.patient_id JOIN w6_prescription_item pi ON rx.rx_number = pi.rx_number JOIN w6_medication m ON pi.medication_id = m.medication_id WHERE p.patient_name = 'Maria Lopez' ORDER BY rx.rx_date, m.medication_name; -- 2. Show each patient with their insurance info SELECT p.patient_name, i.company_name, i.coverage_type, pi.policy_number, pi.copay FROM w6_patient p JOIN w6_patient_insurance pi ON p.patient_id = pi.patient_id JOIN w6_insurance i ON pi.insurance_id = i.insurance_id ORDER BY p.patient_name, i.company_name; -- 3. Which doctors prescribed the most medications? SELECT d.doctor_name, d.specialty, COUNT(*) AS total_items_prescribed FROM w6_doctor d JOIN w6_prescription rx ON d.doctor_id = rx.doctor_id JOIN w6_prescription_item pi ON rx.rx_number = pi.rx_number GROUP BY d.doctor_name, d.specialty ORDER BY total_items_prescribed DESC; -- 4. Most commonly prescribed medications SELECT m.medication_name, m.dosage, COUNT(*) AS times_prescribed FROM w6_medication m JOIN w6_prescription_item pi ON m.medication_id = pi.medication_id GROUP BY m.medication_name, m.dosage ORDER BY times_prescribed DESC; -- 5. Patients with multiple insurance plans SELECT p.patient_name, COUNT(*) AS plan_count FROM w6_patient p JOIN w6_patient_insurance pi ON p.patient_id = pi.patient_id GROUP BY p.patient_name HAVING COUNT(*) > 1 ORDER BY plan_count DESC;