668 lines
24 KiB
SQL
Executable File
668 lines
24 KiB
SQL
Executable File
/*
|
|
================================================================================
|
|
ORACLE DATA POPULATION SCRIPT - E-COMMERCE ORDER MANAGEMENT SYSTEM
|
|
Generates 5-10MB of realistic random data
|
|
================================================================================
|
|
*/
|
|
|
|
SET ECHO OFF
|
|
SET FEEDBACK OFF
|
|
SET TIMING ON
|
|
SET DEFINE OFF
|
|
|
|
-- ============================================================================
|
|
-- DISABLE CONSTRAINTS FOR FASTER LOADING
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE orders DISABLE CONSTRAINT fk_ord_cust;
|
|
ALTER TABLE orders DISABLE CONSTRAINT fk_ord_emp;
|
|
ALTER TABLE order_items DISABLE CONSTRAINT fk_oi_ord;
|
|
ALTER TABLE order_items DISABLE CONSTRAINT fk_oi_prod;
|
|
ALTER TABLE products DISABLE CONSTRAINT fk_prod_cat;
|
|
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;
|
|
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_mgr;
|
|
ALTER TABLE inventory DISABLE CONSTRAINT fk_inv_prod;
|
|
ALTER TABLE customer_reviews DISABLE CONSTRAINT fk_rev_cust;
|
|
ALTER TABLE customer_reviews DISABLE CONSTRAINT fk_rev_prod;
|
|
|
|
-- ============================================================================
|
|
-- HELPER FUNCTION: Generate Random String
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION fn_random_string(p_length NUMBER) RETURN VARCHAR2
|
|
IS
|
|
v_result VARCHAR2(4000) := '';
|
|
v_chars VARCHAR2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
|
|
BEGIN
|
|
FOR i IN 1..p_length LOOP
|
|
v_result := v_result || SUBSTR(v_chars, TRUNC(DBMS_RANDOM.VALUE(1, 27)), 1);
|
|
END LOOP;
|
|
RETURN v_result;
|
|
END fn_random_string;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- HELPER FUNCTION: Generate Random Email
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION fn_random_email RETURN VARCHAR2
|
|
IS
|
|
BEGIN
|
|
RETURN LOWER(fn_random_string(8) || '@' || fn_random_string(6) || '.com');
|
|
END fn_random_email;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- HELPER FUNCTION: Generate Random Name
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION fn_random_name RETURN VARCHAR2
|
|
IS
|
|
v_names VARCHAR2(1000) := 'John,Jane,Michael,Sarah,David,Emma,Robert,Lisa,James,Maria,William,Jennifer,Richard,Linda,Charles,Patricia,Daniel,Barbara,Matthew,Susan,Anthony,Jessica,Mark,Karen,Donald,Nancy,Frank,Lisa,Steven,Betty,Paul,Margaret,Andrew,Sandra,Joshua,Ashley,Kenneth,Cynthia,Kevin,Kathleen,Brian,Diane,George,Julie,Edward,Joyce,Ronald,Evelyn';
|
|
v_name_array SYS.ODCIVARCHAR2LIST;
|
|
v_random_idx NUMBER;
|
|
BEGIN
|
|
v_name_array := SYS.ODCIVARCHAR2LIST(
|
|
'John', 'Jane', 'Michael', 'Sarah', 'David', 'Emma', 'Robert', 'Lisa',
|
|
'James', 'Maria', 'William', 'Jennifer', 'Richard', 'Linda', 'Charles',
|
|
'Patricia', 'Daniel', 'Barbara', 'Matthew', 'Susan', 'Anthony', 'Jessica'
|
|
);
|
|
|
|
v_random_idx := TRUNC(DBMS_RANDOM.VALUE(1, v_name_array.COUNT + 1));
|
|
RETURN v_name_array(v_random_idx);
|
|
END fn_random_name;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 1: POPULATE DEPARTMENTS
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
v_dept_names VARCHAR2(1000) := 'Sales,Marketing,IT,HR,Finance,Operations,Logistics,Customer Service,Product Development,Quality Assurance';
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 1: Populating Departments...');
|
|
|
|
INSERT INTO departments (department_id, department_name, budget_allocation)
|
|
SELECT
|
|
seq_departments.NEXTVAL,
|
|
TRIM(REGEXP_SUBSTR(v_dept_names, '[^,]+', 1, LEVEL)),
|
|
ROUND(DBMS_RANDOM.VALUE(100000, 500000), 2)
|
|
FROM DUAL
|
|
CONNECT BY LEVEL <= LENGTH(v_dept_names) - LENGTH(REPLACE(v_dept_names, ',', '')) + 1;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Departments created: ' || SQL%ROWCOUNT);
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 2: POPULATE EMPLOYEES (500 employees)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
TYPE dept_array IS TABLE OF NUMBER;
|
|
v_job_titles VARCHAR2(1000) := 'Manager,Senior Analyst,Analyst,Specialist,Coordinator,Executive,Technician,Developer,Consultant,Officer';
|
|
v_dept_ids dept_array;
|
|
v_emp_count NUMBER := 0;
|
|
v_batch_size NUMBER := 100;
|
|
v_max_employees NUMBER := 500;
|
|
v_dept_id NUMBER;
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 2: Populating Employees (500 records)...');
|
|
|
|
-- Get department IDs
|
|
SELECT department_id BULK COLLECT INTO v_dept_ids FROM departments;
|
|
|
|
FOR i IN 1..v_max_employees LOOP
|
|
-- Get department ID outside the SQL statement
|
|
v_dept_id := v_dept_ids(MOD(i - 1, v_dept_ids.COUNT) + 1);
|
|
|
|
INSERT INTO employees (
|
|
employee_id, first_name, last_name, email, phone_number, hire_date,
|
|
job_title, salary, department_id, manager_id
|
|
) VALUES (
|
|
seq_employees.NEXTVAL,
|
|
fn_random_name,
|
|
fn_random_string(TRUNC(DBMS_RANDOM.VALUE(5, 10))),
|
|
fn_random_email,
|
|
'555-' || LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 10000)), 4, '0'),
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(365, 3650)),
|
|
TRIM(REGEXP_SUBSTR(v_job_titles, '[^,]+', 1, MOD(i, 10) + 1)),
|
|
ROUND(DBMS_RANDOM.VALUE(35000, 150000), 2),
|
|
v_dept_id,
|
|
CASE WHEN i > 20 THEN seq_employees.CURRVAL - TRUNC(DBMS_RANDOM.VALUE(1, 21)) ELSE NULL END
|
|
);
|
|
|
|
IF MOD(i, v_batch_size) = 0 THEN
|
|
COMMIT;
|
|
END IF;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Employees created: 500');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 3: POPULATE PRODUCT CATEGORIES (50 categories)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
v_categories VARCHAR2(2000) := 'Electronics,Clothing,Home & Garden,Books,Sports & Outdoors,Toys & Games,Beauty,Health,Automotive,Food & Beverage,Music,Movies,Office Supplies,Pet Supplies,Furniture,Kitchen,Jewelry,Shoes,Bags,Tools,Industrial,Software,Hardware,Networking,Mobile,Computers,Tablets,Accessories,Storage,Memory,Video,Audio,Photography,Lighting,Heating,Cooling,Security,Safety,Cleaning,Gardening,Pet Care,Baby Products,Kids Clothing,Maternity,Sports Equipment,Outdoor Gear,Travel,Luggage';
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 3: Populating Product Categories (50 records)...');
|
|
|
|
INSERT INTO product_categories (category_id, category_name, description)
|
|
SELECT
|
|
ROWNUM,
|
|
TRIM(REGEXP_SUBSTR(v_categories, '[^,]+', 1, LEVEL)),
|
|
'Category: ' || TRIM(REGEXP_SUBSTR(v_categories, '[^,]+', 1, LEVEL))
|
|
FROM DUAL
|
|
CONNECT BY LEVEL <= LENGTH(v_categories) - LENGTH(REPLACE(v_categories, ',', '')) + 1;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Categories created: ' || SQL%ROWCOUNT);
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 4: POPULATE PRODUCTS (5000 products)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
TYPE cat_array IS TABLE OF NUMBER;
|
|
v_product_names VARCHAR2(4000) := 'Widget,Gadget,Device,Tool,Kit,Set,Pack,Bundle,Deluxe,Pro,Max,Lite,Ultra,Supreme,Elite,Premium,Standard,Basic,Advanced,Professional,Industrial,Commercial,Residential,Portable,Compact,Mini,Maxi,Super,Mega,Hyper';
|
|
v_cat_ids cat_array;
|
|
v_product_count NUMBER := 5000;
|
|
v_batch_size NUMBER := 500;
|
|
v_cat_id NUMBER;
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 4: Populating Products (5000 records)...');
|
|
|
|
SELECT category_id BULK COLLECT INTO v_cat_ids FROM product_categories;
|
|
|
|
FOR i IN 1..v_product_count LOOP
|
|
-- Get category ID outside the SQL statement
|
|
v_cat_id := v_cat_ids(MOD(i - 1, v_cat_ids.COUNT) + 1);
|
|
|
|
INSERT INTO products (
|
|
product_id, product_name, description, category_id, unit_price,
|
|
cost_price, reorder_level, discontinued_flag
|
|
) VALUES (
|
|
seq_products.NEXTVAL,
|
|
TRIM(REGEXP_SUBSTR(v_product_names, '[^,]+', 1, MOD(i, 30) + 1)) || ' #' || i,
|
|
'Premium product variant ' || i || ' with high quality standards',
|
|
v_cat_id,
|
|
ROUND(DBMS_RANDOM.VALUE(9.99, 999.99), 2),
|
|
ROUND(DBMS_RANDOM.VALUE(5, 450), 2),
|
|
TRUNC(DBMS_RANDOM.VALUE(5, 100)),
|
|
CASE WHEN MOD(i, 50) = 0 THEN 'Y' ELSE 'N' END
|
|
);
|
|
|
|
IF MOD(i, v_batch_size) = 0 THEN
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ... ' || i || ' products created');
|
|
END IF;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Products created: 5000');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 5: POPULATE INVENTORY (one entry per product)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
v_batch_size NUMBER := 500;
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 5: Populating Inventory (5000 records)...');
|
|
|
|
INSERT INTO inventory (
|
|
inventory_id, product_id, warehouse_location, quantity_on_hand,
|
|
quantity_reserved, quantity_available, reorder_quantity
|
|
)
|
|
SELECT
|
|
seq_inventory.NEXTVAL,
|
|
p.product_id,
|
|
CASE MOD(p.product_id, 5)
|
|
WHEN 0 THEN 'WH-A1'
|
|
WHEN 1 THEN 'WH-A2'
|
|
WHEN 2 THEN 'WH-B1'
|
|
WHEN 3 THEN 'WH-B2'
|
|
ELSE 'WH-C1'
|
|
END,
|
|
TRUNC(DBMS_RANDOM.VALUE(0, 500)),
|
|
0,
|
|
TRUNC(DBMS_RANDOM.VALUE(0, 500)),
|
|
TRUNC(DBMS_RANDOM.VALUE(10, 100))
|
|
FROM products p;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Inventory created: ' || SQL%ROWCOUNT);
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 6: POPULATE CUSTOMERS (10000 customers)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
v_customer_count NUMBER := 10000;
|
|
v_cust_types DBMS_SQL.VARCHAR2_TABLE;
|
|
v_batch_size NUMBER := 1000;
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 6: Populating Customers (10000 records)...');
|
|
|
|
v_cust_types(1) := 'RETAIL';
|
|
v_cust_types(2) := 'WHOLESALE';
|
|
v_cust_types(3) := 'CORPORATE';
|
|
|
|
FOR i IN 1..v_customer_count LOOP
|
|
INSERT INTO customers (
|
|
customer_id, first_name, last_name, email, phone_number,
|
|
billing_address, shipping_address, customer_type, credit_limit, account_status
|
|
) VALUES (
|
|
seq_customers.NEXTVAL,
|
|
fn_random_name,
|
|
fn_random_string(TRUNC(DBMS_RANDOM.VALUE(5, 12))),
|
|
fn_random_email,
|
|
'555-' || LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 10000000)), 7, '0'),
|
|
TRUNC(DBMS_RANDOM.VALUE(100, 999)) || ' Main St, City ' || MOD(i, 50),
|
|
TRUNC(DBMS_RANDOM.VALUE(100, 999)) || ' Oak Ave, City ' || MOD(i, 50),
|
|
v_cust_types(MOD(i, 3) + 1),
|
|
ROUND(DBMS_RANDOM.VALUE(1000, 50000), 2),
|
|
CASE WHEN MOD(i, 100) < 95 THEN 'ACTIVE' ELSE 'INACTIVE' END
|
|
);
|
|
|
|
IF MOD(i, v_batch_size) = 0 THEN
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ... ' || i || ' customers created');
|
|
END IF;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Customers created: 10000');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 7: POPULATE ORDERS (100000 orders - Main data driver)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
TYPE num_array IS TABLE OF NUMBER;
|
|
v_order_count NUMBER := 100000;
|
|
v_cust_ids num_array;
|
|
v_emp_ids num_array;
|
|
v_batch_size NUMBER := 1000;
|
|
v_order_statuses DBMS_SQL.VARCHAR2_TABLE;
|
|
v_payment_methods DBMS_SQL.VARCHAR2_TABLE;
|
|
v_cust_id NUMBER;
|
|
v_emp_id NUMBER;
|
|
v_order_status VARCHAR2(20);
|
|
v_payment_method VARCHAR2(20);
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 7: Populating Orders (100000 records)...');
|
|
|
|
SELECT customer_id BULK COLLECT INTO v_cust_ids FROM customers
|
|
WHERE account_status = 'ACTIVE' AND ROWNUM <= 9000;
|
|
|
|
SELECT employee_id BULK COLLECT INTO v_emp_ids FROM employees;
|
|
|
|
v_order_statuses(1) := 'PENDING';
|
|
v_order_statuses(2) := 'CONFIRMED';
|
|
v_order_statuses(3) := 'SHIPPED';
|
|
v_order_statuses(4) := 'DELIVERED';
|
|
v_order_statuses(5) := 'CANCELLED';
|
|
|
|
v_payment_methods(1) := 'CREDIT_CARD';
|
|
v_payment_methods(2) := 'BANK_TRANSFER';
|
|
v_payment_methods(3) := 'CASH';
|
|
v_payment_methods(4) := 'DIGITAL_WALLET';
|
|
|
|
FOR i IN 1..v_order_count LOOP
|
|
-- Extract values outside SQL statement
|
|
v_cust_id := v_cust_ids(MOD(i - 1, v_cust_ids.COUNT) + 1);
|
|
v_emp_id := v_emp_ids(MOD(i - 1, v_emp_ids.COUNT) + 1);
|
|
v_order_status := v_order_statuses(MOD(i, 5) + 1);
|
|
v_payment_method := v_payment_methods(MOD(i, 4) + 1);
|
|
|
|
INSERT INTO orders (
|
|
order_id, customer_id, sales_employee_id, order_date, delivery_date,
|
|
order_status, total_amount, tax_amount, discount_amount, payment_method
|
|
) VALUES (
|
|
seq_orders.NEXTVAL,
|
|
v_cust_id,
|
|
v_emp_id,
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 30)),
|
|
v_order_status,
|
|
ROUND(DBMS_RANDOM.VALUE(50, 5000), 2),
|
|
ROUND(DBMS_RANDOM.VALUE(5, 500), 2),
|
|
ROUND(DBMS_RANDOM.VALUE(0, 200), 2),
|
|
v_payment_method
|
|
);
|
|
|
|
IF MOD(i, v_batch_size) = 0 THEN
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ... ' || i || ' orders created');
|
|
END IF;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Orders created: 100000');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 8: POPULATE ORDER ITEMS (avg 7 items per order = 700000 items)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
TYPE num_array IS TABLE OF NUMBER;
|
|
v_order_ids num_array;
|
|
v_product_ids num_array;
|
|
v_batch_size NUMBER := 10000;
|
|
v_item_count NUMBER := 0;
|
|
v_orders_count NUMBER;
|
|
v_order_id NUMBER;
|
|
v_product_id NUMBER;
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 8: Populating Order Items (~700000 records)...');
|
|
|
|
SELECT order_id BULK COLLECT INTO v_order_ids FROM orders ORDER BY DBMS_RANDOM.VALUE;
|
|
SELECT product_id BULK COLLECT INTO v_product_ids FROM products WHERE discontinued_flag = 'N';
|
|
|
|
v_orders_count := v_order_ids.COUNT;
|
|
|
|
FOR i IN 1..v_orders_count LOOP
|
|
-- Create 5-10 items per order
|
|
FOR j IN 1..TRUNC(DBMS_RANDOM.VALUE(5, 11)) LOOP
|
|
v_item_count := v_item_count + 1;
|
|
|
|
-- Extract values outside SQL statement
|
|
v_order_id := v_order_ids(i);
|
|
v_product_id := v_product_ids(MOD(v_item_count - 1, v_product_ids.COUNT) + 1);
|
|
|
|
INSERT INTO order_items (
|
|
order_item_id, order_id, product_id, quantity, unit_price, line_total
|
|
) VALUES (
|
|
seq_order_items.NEXTVAL,
|
|
v_order_id,
|
|
v_product_id,
|
|
TRUNC(DBMS_RANDOM.VALUE(1, 20)),
|
|
ROUND(DBMS_RANDOM.VALUE(10, 800), 2),
|
|
ROUND(TRUNC(DBMS_RANDOM.VALUE(1, 20)) * DBMS_RANDOM.VALUE(10, 800), 2)
|
|
);
|
|
|
|
IF MOD(v_item_count, v_batch_size) = 0 THEN
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ... ' || v_item_count || ' order items created');
|
|
END IF;
|
|
END LOOP;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Order Items created: ' || v_item_count);
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- PHASE 9: POPULATE CUSTOMER REVIEWS (100000 reviews)
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
TYPE num_array IS TABLE OF NUMBER;
|
|
v_cust_ids num_array;
|
|
v_product_ids num_array;
|
|
v_review_count NUMBER := 100000;
|
|
v_batch_size NUMBER := 1000;
|
|
v_review_texts VARCHAR2(4000) := 'Excellent product,Very satisfied,Good quality,Poor quality,Amazing,Terrible,Works as expected,Defective,Highly recommend,Do not buy,Worth the price,Overpriced,Great value,Disappointing,Exceptional,Awful,Perfect fit,Wrong size,Exceeded expectations,Not as described';
|
|
v_review_array DBMS_SQL.VARCHAR2_TABLE;
|
|
v_cust_id NUMBER;
|
|
v_product_id NUMBER;
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> PHASE 9: Populating Customer Reviews (100000 records)...');
|
|
|
|
SELECT customer_id BULK COLLECT INTO v_cust_ids FROM customers WHERE ROWNUM <= 8000;
|
|
SELECT product_id BULK COLLECT INTO v_product_ids FROM products WHERE ROWNUM <= 4000;
|
|
|
|
FOR i IN 1..v_review_count LOOP
|
|
-- Extract values outside SQL statement
|
|
v_cust_id := v_cust_ids(MOD(i - 1, v_cust_ids.COUNT) + 1);
|
|
v_product_id := v_product_ids(MOD(i - 1, v_product_ids.COUNT) + 1);
|
|
|
|
INSERT INTO customer_reviews (
|
|
review_id, customer_id, product_id, review_rating, review_text, review_status
|
|
) VALUES (
|
|
i,
|
|
v_cust_id,
|
|
v_product_id,
|
|
TRUNC(DBMS_RANDOM.VALUE(1, 6)),
|
|
TRIM(REGEXP_SUBSTR(v_review_texts, '[^,]+', 1, MOD(i, 20) + 1)) || ' - ' || i,
|
|
CASE WHEN MOD(i, 10) < 8 THEN 'APPROVED'
|
|
WHEN MOD(i, 10) = 8 THEN 'PENDING'
|
|
ELSE 'REJECTED'
|
|
END
|
|
);
|
|
|
|
IF MOD(i, v_batch_size) = 0 THEN
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ... ' || i || ' reviews created');
|
|
END IF;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Customer Reviews created: 100000');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- RE-ENABLE CONSTRAINTS
|
|
-- ============================================================================
|
|
|
|
DECLARE
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> RE-ENABLING CONSTRAINTS...');
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE employees ENABLE CONSTRAINT fk_emp_dept';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE employees ENABLE CONSTRAINT fk_emp_mgr';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE products ENABLE CONSTRAINT fk_prod_cat';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE orders ENABLE CONSTRAINT fk_ord_cust';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE orders ENABLE CONSTRAINT fk_ord_emp';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE order_items ENABLE CONSTRAINT fk_oi_ord';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE order_items ENABLE CONSTRAINT fk_oi_prod';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE inventory ENABLE CONSTRAINT fk_inv_prod';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE customer_reviews ENABLE CONSTRAINT fk_rev_cust';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'ALTER TABLE customer_reviews ENABLE CONSTRAINT fk_rev_prod';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ All constraints re-enabled');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- GATHER STATISTICS
|
|
-- ============================================================================
|
|
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('>> GATHERING TABLE STATISTICS...');
|
|
DBMS_STATS.GATHER_SCHEMA_STATS('', DEGREE => 8);
|
|
DBMS_OUTPUT.PUT_LINE(' ✓ Statistics gathered');
|
|
END;
|
|
/
|
|
|
|
-- ============================================================================
|
|
-- DATA SUMMARY REPORT
|
|
-- ============================================================================
|
|
|
|
SET ECHO ON
|
|
SET FEEDBACK ON
|
|
SET PAGESIZE 50
|
|
SET LINESIZE 120
|
|
|
|
SPOOL /tmp/data_summary.txt
|
|
|
|
PROMPT
|
|
PROMPT ================================================================================
|
|
PROMPT E-COMMERCE SCHEMA - DATA POPULATION SUMMARY
|
|
PROMPT ================================================================================
|
|
PROMPT
|
|
|
|
SELECT 'Departments' AS table_name, COUNT(*) AS row_count FROM departments
|
|
UNION ALL
|
|
SELECT 'Employees', COUNT(*) FROM employees
|
|
UNION ALL
|
|
SELECT 'Product Categories', COUNT(*) FROM product_categories
|
|
UNION ALL
|
|
SELECT 'Products', COUNT(*) FROM products
|
|
UNION ALL
|
|
SELECT 'Customers', COUNT(*) FROM customers
|
|
UNION ALL
|
|
SELECT 'Inventory', COUNT(*) FROM inventory
|
|
UNION ALL
|
|
SELECT 'Orders', COUNT(*) FROM orders
|
|
UNION ALL
|
|
SELECT 'Order Items', COUNT(*) FROM order_items
|
|
UNION ALL
|
|
SELECT 'Customer Reviews', COUNT(*) FROM customer_reviews
|
|
ORDER BY 2 DESC;
|
|
|
|
PROMPT
|
|
PROMPT ================================================================================
|
|
PROMPT TABLE SIZES (in MB)
|
|
PROMPT ================================================================================
|
|
PROMPT
|
|
|
|
SELECT
|
|
table_name,
|
|
ROUND(num_rows * avg_row_len / 1024 / 1024, 2) AS size_mb
|
|
FROM user_tables
|
|
WHERE table_name IN ('DEPARTMENTS', 'EMPLOYEES', 'PRODUCT_CATEGORIES', 'PRODUCTS',
|
|
'CUSTOMERS', 'INVENTORY', 'ORDERS', 'ORDER_ITEMS', 'CUSTOMER_REVIEWS')
|
|
ORDER BY num_rows DESC;
|
|
|
|
PROMPT
|
|
PROMPT ================================================================================
|
|
PROMPT DATA QUALITY CHECKS
|
|
PROMPT ================================================================================
|
|
PROMPT
|
|
|
|
PROMPT Active Customers:
|
|
SELECT COUNT(*) FROM customers WHERE account_status = 'ACTIVE';
|
|
|
|
PROMPT Active Employees:
|
|
SELECT COUNT(*) FROM employees;
|
|
|
|
PROMPT Non-Discontinued Products:
|
|
SELECT COUNT(*) FROM products WHERE discontinued_flag = 'N';
|
|
|
|
PROMPT Approved Reviews:
|
|
SELECT COUNT(*) FROM customer_reviews WHERE review_status = 'APPROVED';
|
|
|
|
PROMPT Confirmed Orders:
|
|
SELECT COUNT(*) FROM orders WHERE order_status IN ('CONFIRMED', 'SHIPPED', 'DELIVERED');
|
|
|
|
PROMPT Average Items per Order:
|
|
SELECT ROUND(AVG(item_count), 2) AS avg_items_per_order
|
|
FROM (
|
|
SELECT order_id, COUNT(*) AS item_count
|
|
FROM order_items
|
|
GROUP BY order_id
|
|
);
|
|
|
|
PROMPT
|
|
PROMPT ================================================================================
|
|
PROMPT SCHEMA RELATIONSHIPS VALIDATION
|
|
PROMPT ================================================================================
|
|
PROMPT
|
|
|
|
PROMPT Orders with Valid Customer Reference:
|
|
SELECT COUNT(*) FROM orders o
|
|
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
|
|
|
|
PROMPT Order Items with Valid Order Reference:
|
|
SELECT COUNT(*) FROM order_items oi
|
|
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.order_id = oi.order_id);
|
|
|
|
PROMPT Reviews with Valid Product Reference:
|
|
SELECT COUNT(*) FROM customer_reviews cr
|
|
WHERE EXISTS (SELECT 1 FROM products p WHERE p.product_id = cr.product_id);
|
|
|
|
PROMPT Inventory with Valid Product Reference:
|
|
SELECT COUNT(*) FROM inventory i
|
|
WHERE EXISTS (SELECT 1 FROM products p WHERE p.product_id = i.product_id);
|
|
|
|
PROMPT
|
|
PROMPT ================================================================================
|
|
PROMPT COMPLETION TIME
|
|
PROMPT ================================================================================
|
|
PROMPT
|
|
|
|
SPOOL OFF
|
|
|
|
-- Clean up helper functions
|
|
DROP FUNCTION fn_random_string;
|
|
DROP FUNCTION fn_random_email;
|
|
DROP FUNCTION fn_random_name;
|
|
|
|
PROMPT
|
|
PROMPT ✓ DATA POPULATION COMPLETE!
|
|
PROMPT
|
|
PROMPT Total data size: 5-10 MB
|
|
PROMPT Records populated:
|
|
PROMPT - Departments: 10
|
|
PROMPT - Employees: 500
|
|
PROMPT - Product Categories: 50
|
|
PROMPT - Products: 5,000
|
|
PROMPT - Customers: 10,000
|
|
PROMPT - Inventory: 5,000 (1:1 with products)
|
|
PROMPT - Orders: 100,000
|
|
PROMPT - Order Items: ~700,000 (7-10 items per order)
|
|
PROMPT - Customer Reviews: 100,000
|
|
PROMPT ═══════════════════════════════════════════════════════════════════════════════
|
|
PROMPT TOTAL: ~821,560 records
|
|
PROMPT
|
|
PROMPT Summary report saved to: /tmp/data_summary.txt
|
|
PROMPT
|