\set ON_ERROR_STOP on
/******************************************************************
* BSS SAMPLE DATASET 10,000 ROWS - PostgreSQL 12
* Run with: psql -U postgres -f bss_sample_10k_pg12.sql
* Note: This script uses \connect, so run it with psql.
******************************************************************/
/******************************************************************
* 1) CRM DATABASE
******************************************************************/
\connect crm
BEGIN;
CREATE TABLE IF NOT EXISTS customer (
customer_id SERIAL PRIMARY KEY,
customer_code VARCHAR(20) UNIQUE NOT NULL,
full_name VARCHAR(200) NOT NULL,
customer_type VARCHAR(20) NOT NULL CHECK (customer_type IN ('INDIVIDUAL','BUSINESS')),
segment VARCHAR(50),
national_id VARCHAR(30),
date_of_birth DATE,
gender VARCHAR(10),
phone VARCHAR(20) NOT NULL,
email VARCHAR(200),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
created_at TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS customer_address (
address_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
address_type VARCHAR(20) NOT NULL,
province VARCHAR(100) NOT NULL,
district VARCHAR(100) NOT NULL,
ward VARCHAR(100),
detail_address VARCHAR(255),
is_default BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE IF NOT EXISTS subscriber (
subscriber_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
msisdn VARCHAR(20) UNIQUE NOT NULL,
account_no VARCHAR(30) UNIQUE NOT NULL,
service_type VARCHAR(30) NOT NULL,
activation_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE IF NOT EXISTS interaction_log (
interaction_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
channel VARCHAR(30) NOT NULL,
subject VARCHAR(200) NOT NULL,
interaction_time TIMESTAMP NOT NULL,
result VARCHAR(50) NOT NULL,
note TEXT
);
CREATE INDEX IF NOT EXISTS idx_customer_phone ON customer(phone);
CREATE INDEX IF NOT EXISTS idx_subscriber_msisdn ON subscriber(msisdn);
CREATE INDEX IF NOT EXISTS idx_interaction_time ON interaction_log(interaction_time);
TRUNCATE TABLE interaction_log, subscriber, customer_address, customer RESTART IDENTITY CASCADE;
INSERT INTO customer (
customer_code, full_name, customer_type, segment, national_id, date_of_birth,
gender, phone, email, status, created_at
)
SELECT
'CUST' || lpad(g::text, 6, '0') AS customer_code,
CASE WHEN g % 10 = 0 THEN 'Business Customer ' || g ELSE 'Customer ' || g END AS full_name,
CASE WHEN g % 10 = 0 THEN 'BUSINESS' ELSE 'INDIVIDUAL' END AS customer_type,
(ARRAY['STANDARD','SILVER','GOLD','PREMIUM','VIP','ENTERPRISE'])[(1 + (g % 6))] AS segment,
CASE WHEN g % 10 = 0
THEN '01' || lpad(g::text, 8, '0')
ELSE '00' || lpad(g::text, 10, '0')
END AS national_id,
DATE '1980-01-01' + (g % 12000) AS date_of_birth,
(ARRAY['MALE','FEMALE'])[(1 + (g % 2))] AS gender,
'09' || lpad(g::text, 8, '0') AS phone,
'customer' || g || '@example.com' AS email,
(ARRAY['ACTIVE','ACTIVE','ACTIVE','SUSPENDED'])[(1 + (g % 4))] AS status,
now() - ((g % 365) || ' days')::interval AS created_at
FROM generate_series(1, 10000) AS g;
INSERT INTO customer_address (
customer_id, address_type, province, district, ward, detail_address, is_default
)
SELECT
c.customer_id,
CASE WHEN c.customer_id % 10 = 0 THEN 'OFFICE' ELSE 'HOME' END,
(ARRAY['Ha Noi','Ho Chi Minh','Da Nang','Hai Phong','Can Tho','Nghe An','Quang Ninh','Hue'])[(1 + (c.customer_id % 8))],
'District ' || ((c.customer_id % 20) + 1),
'Ward ' || ((c.customer_id % 15) + 1),
(100 + c.customer_id) || ' Sample Street',
TRUE
FROM customer c;
INSERT INTO subscriber (
customer_id, msisdn, account_no, service_type, activation_date, status
)
SELECT
c.customer_id,
'09' || lpad(c.customer_id::text, 8, '0') AS msisdn,
'ACC' || lpad(c.customer_id::text, 8, '0') AS account_no,
(ARRAY['PREPAID_MOBILE','POSTPAID_MOBILE','FIBER','ENTERPRISE_DATA'])[(1 + (c.customer_id % 4))] AS service_type,
DATE '2024-01-01' + (c.customer_id % 700),
(ARRAY['ACTIVE','ACTIVE','BARRED','SUSPENDED'])[(1 + (c.customer_id % 4))]
FROM customer c;
INSERT INTO interaction_log (
customer_id, channel, subject, interaction_time, result, note
)
SELECT
((g - 1) % 10000) + 1,
(ARRAY['CALL_CENTER','SHOP','APP','WEB','EMAIL'])[(1 + (g % 5))],
(ARRAY['Change tariff plan','SIM replacement','Top-up failed','Billing dispute','Roaming request','Bundle renewal'])[(1 + (g % 6))],
now() - ((g % 180) || ' days')::interval - ((g % 1440) || ' minutes')::interval,
(ARRAY['COMPLETED','PENDING','IN_PROGRESS','REJECTED'])[(1 + (g % 4))],
'Interaction sample #' || g
FROM generate_series(1, 10000) AS g;
COMMIT;
/******************************************************************
* 2) SALE DATABASE
******************************************************************/
\connect sale
BEGIN;
CREATE TABLE IF NOT EXISTS shop (
shop_id SERIAL PRIMARY KEY,
shop_code VARCHAR(20) UNIQUE NOT NULL,
shop_name VARCHAR(200) NOT NULL,
province VARCHAR(100) NOT NULL,
channel_type VARCHAR(30) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE IF NOT EXISTS product (
product_id SERIAL PRIMARY KEY,
product_code VARCHAR(30) UNIQUE NOT NULL,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50) NOT NULL,
price NUMERIC(18,2) NOT NULL CHECK (price >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE IF NOT EXISTS sales_order (
order_id SERIAL PRIMARY KEY,
order_no VARCHAR(30) UNIQUE NOT NULL,
customer_code VARCHAR(20) NOT NULL,
shop_id INT NOT NULL REFERENCES shop(shop_id),
order_date TIMESTAMP NOT NULL,
order_status VARCHAR(20) NOT NULL,
total_amount NUMERIC(18,2) NOT NULL CHECK (total_amount >= 0)
);
CREATE TABLE IF NOT EXISTS sales_order_item (
order_item_id SERIAL PRIMARY KEY,
order_id INT NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE,
product_id INT NOT NULL REFERENCES product(product_id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(18,2) NOT NULL CHECK (unit_price >= 0),
line_amount NUMERIC(18,2) NOT NULL CHECK (line_amount >= 0)
);
CREATE INDEX IF NOT EXISTS idx_sales_order_customer_code ON sales_order(customer_code);
CREATE INDEX IF NOT EXISTS idx_sales_order_order_date ON sales_order(order_date);
TRUNCATE TABLE sales_order_item, sales_order, product, shop RESTART IDENTITY CASCADE;
INSERT INTO shop (shop_code, shop_name, province, channel_type, status)
SELECT
'SHOP' || lpad(g::text, 4, '0'),
'Shop ' || g,
(ARRAY['Ha Noi','Ho Chi Minh','Da Nang','Hai Phong','Can Tho','Nha Trang','Quang Ninh','Nghe An'])[(1 + (g % 8))],
(ARRAY['DIRECT','DEALER','ONLINE'])[(1 + (g % 3))],
'ACTIVE'
FROM generate_series(1, 100) AS g;
INSERT INTO product (product_code, product_name, category, price, status)
SELECT
'PRD' || lpad(g::text, 5, '0'),
'Product ' || g,
(ARRAY['SIM','PACKAGE','FIBER','DIGITAL','DEVICE'])[(1 + (g % 5))],
((g % 20) + 1) * 50000::numeric,
'ACTIVE'
FROM generate_series(1, 200) AS g;
INSERT INTO sales_order (order_no, customer_code, shop_id, order_date, order_status, total_amount)
SELECT
'SO' || to_char(g, 'FM00000000'),
'CUST' || lpad((((g - 1) % 10000) + 1)::text, 6, '0'),
((g - 1) % 100) + 1,
now() - ((g % 180) || ' days')::interval - ((g % 1440) || ' minutes')::interval,
(ARRAY['NEW','COMPLETED','CANCELLED','PENDING'])[(1 + (g % 4))],
0
FROM generate_series(1, 10000) AS g;
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT
o.order_id,
((o.order_id - 1) % 200) + 1 AS product_id,
((o.order_id % 3) + 1) AS quantity,
p.price AS unit_price,
p.price * ((o.order_id % 3) + 1) AS line_amount
FROM sales_order o
JOIN product p ON p.product_id = (((o.order_id - 1) % 200) + 1);
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT
o.order_id,
((o.order_id + 49) % 200) + 1 AS product_id,
((o.order_id % 2) + 1) AS quantity,
p.price AS unit_price,
p.price * ((o.order_id % 2) + 1) AS line_amount
FROM sales_order o
JOIN product p ON p.product_id = (((o.order_id + 49) % 200) + 1);
UPDATE sales_order so
SET total_amount = x.total_amount
FROM (
SELECT order_id, SUM(line_amount) AS total_amount
FROM sales_order_item
GROUP BY order_id
) x
WHERE so.order_id = x.order_id;
COMMIT;
/******************************************************************
* 3) BILLING DATABASE
******************************************************************/
\connect billing
BEGIN;
CREATE TABLE IF NOT EXISTS tariff_plan (
plan_id SERIAL PRIMARY KEY,
plan_code VARCHAR(30) UNIQUE NOT NULL,
plan_name VARCHAR(200) NOT NULL,
monthly_fee NUMERIC(18,2) NOT NULL CHECK (monthly_fee >= 0),
data_quota_gb NUMERIC(10,2) DEFAULT 0,
voice_minutes INT DEFAULT 0,
sms_count INT DEFAULT 0,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE IF NOT EXISTS billing_account (
billing_account_id SERIAL PRIMARY KEY,
account_no VARCHAR(30) UNIQUE NOT NULL,
customer_code VARCHAR(20) NOT NULL,
msisdn VARCHAR(20) UNIQUE NOT NULL,
plan_id INT NOT NULL REFERENCES tariff_plan(plan_id),
billing_cycle_day INT NOT NULL CHECK (billing_cycle_day BETWEEN 1 AND 28),
account_status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
credit_limit NUMERIC(18,2) NOT NULL DEFAULT 0,
current_balance NUMERIC(18,2) NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS invoice (
invoice_id SERIAL PRIMARY KEY,
invoice_no VARCHAR(30) UNIQUE NOT NULL,
billing_account_id INT NOT NULL REFERENCES billing_account(billing_account_id),
invoice_month DATE NOT NULL,
amount_before_tax NUMERIC(18,2) NOT NULL CHECK (amount_before_tax >= 0),
tax_amount NUMERIC(18,2) NOT NULL CHECK (tax_amount >= 0),
total_amount NUMERIC(18,2) NOT NULL CHECK (total_amount >= 0),
due_date DATE NOT NULL,
payment_status VARCHAR(20) NOT NULL
);
CREATE TABLE IF NOT EXISTS payment (
payment_id SERIAL PRIMARY KEY,
payment_ref VARCHAR(30) UNIQUE NOT NULL,
invoice_id INT NOT NULL REFERENCES invoice(invoice_id),
payment_date TIMESTAMP NOT NULL,
amount NUMERIC(18,2) NOT NULL CHECK (amount >= 0),
channel VARCHAR(30) NOT NULL,
payment_status VARCHAR(20) NOT NULL
);
CREATE TABLE IF NOT EXISTS usage_cdr (
cdr_id BIGSERIAL PRIMARY KEY,
msisdn VARCHAR(20) NOT NULL,
usage_type VARCHAR(20) NOT NULL,
event_time TIMESTAMP NOT NULL,
duration_sec INT DEFAULT 0,
data_mb NUMERIC(12,2) DEFAULT 0,
charge_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
source_system VARCHAR(50) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_billing_account_customer_code ON billing_account(customer_code);
CREATE INDEX IF NOT EXISTS idx_invoice_month ON invoice(invoice_month);
CREATE INDEX IF NOT EXISTS idx_usage_cdr_msisdn_event_time ON usage_cdr(msisdn, event_time);
TRUNCATE TABLE payment, invoice, usage_cdr, billing_account, tariff_plan RESTART IDENTITY CASCADE;
INSERT INTO tariff_plan (plan_code, plan_name, monthly_fee, data_quota_gb, voice_minutes, sms_count, status)
SELECT
'PLAN' || lpad(g::text, 3, '0'),
'Tariff Plan ' || g,
(ARRAY[50000,80000,100000,150000,250000,500000])[(1 + (g % 6))]::numeric,
(ARRAY[5,10,15,30,100,500])[(1 + (g % 6))]::numeric,
(ARRAY[50,100,200,500,1000,2000])[(1 + (g % 6))],
(ARRAY[50,100,200,500,1000,2000])[(1 + (g % 6))],
'ACTIVE'
FROM generate_series(1, 20) AS g;
INSERT INTO billing_account (
account_no, customer_code, msisdn, plan_id, billing_cycle_day, account_status, credit_limit, current_balance
)
SELECT
'BA' || lpad(g::text, 8, '0'),
'CUST' || lpad(g::text, 6, '0'),
'09' || lpad(g::text, 8, '0'),
((g - 1) % 20) + 1,
((g - 1) % 28) + 1,
(ARRAY['ACTIVE','ACTIVE','ACTIVE','BARRED'])[(1 + (g % 4))],
(ARRAY[100000,300000,500000,1000000,5000000])[(1 + (g % 5))]::numeric,
((g % 20) * 1000)::numeric
FROM generate_series(1, 10000) AS g;
INSERT INTO invoice (
invoice_no, billing_account_id, invoice_month, amount_before_tax, tax_amount, total_amount, due_date, payment_status
)
SELECT
'INV' || lpad(ba.billing_account_id::text, 8, '0'),
ba.billing_account_id,
DATE '2026-03-01',
(tp.monthly_fee + ((ba.billing_account_id % 15) * 10000))::numeric(18,2),
((tp.monthly_fee + ((ba.billing_account_id % 15) * 10000)) * 0.10)::numeric(18,2),
((tp.monthly_fee + ((ba.billing_account_id % 15) * 10000)) * 1.10)::numeric(18,2),
DATE '2026-03-20' + (ba.billing_account_id % 5),
CASE
WHEN ba.billing_account_id % 10 IN (1,2,3,4,5,6) THEN 'PAID'
WHEN ba.billing_account_id % 10 IN (7,8) THEN 'PENDING'
ELSE 'PARTIAL'
END
FROM billing_account ba
JOIN tariff_plan tp ON tp.plan_id = ba.plan_id;
INSERT INTO payment (payment_ref, invoice_id, payment_date, amount, channel, payment_status)
SELECT
'PAY' || lpad(i.invoice_id::text, 8, '0'),
i.invoice_id,
now() - ((i.invoice_id % 30) || ' days')::interval,
CASE
WHEN i.payment_status = 'PAID' THEN i.total_amount
WHEN i.payment_status = 'PARTIAL' THEN round((i.total_amount * 0.6)::numeric, 2)
ELSE 0
END,
(ARRAY['BANKING','MOMO','AUTO_DEBIT','SHOP'])[(1 + (i.invoice_id % 4))],
CASE
WHEN i.payment_status IN ('PAID','PARTIAL') THEN 'SUCCESS'
ELSE 'PENDING'
END
FROM invoice i
WHERE i.payment_status IN ('PAID','PARTIAL');
INSERT INTO usage_cdr (
msisdn, usage_type, event_time, duration_sec, data_mb, charge_amount, source_system
)
SELECT
'09' || lpad((((g - 1) % 10000) + 1)::text, 8, '0'),
(ARRAY['VOICE','DATA','SMS'])[(1 + (g % 3))],
now() - ((g % 90) || ' days')::interval - ((g % 1440) || ' minutes')::interval,
CASE WHEN g % 3 = 1 THEN ((g % 600) + 1) ELSE 0 END,
CASE WHEN g % 3 = 2 THEN ((g % 5000) + 50) ELSE 0 END,
CASE
WHEN g % 3 = 1 THEN ((g % 600) + 1) * 10
WHEN g % 3 = 2 THEN (((g % 5000) + 50) * 5)::numeric
ELSE 500
END,
(ARRAY['OCS','PGW','SMSC'])[(1 + (g % 3))]
FROM generate_series(1, 10000) AS g;
COMMIT;
/******************************************************************
* 4) MARKETING DATABASE
******************************************************************/
\connect marketing
BEGIN;
CREATE TABLE IF NOT EXISTS segment (
segment_id SERIAL PRIMARY KEY,
segment_code VARCHAR(30) UNIQUE NOT NULL,
segment_name VARCHAR(200) NOT NULL,
criteria_desc TEXT
);
CREATE TABLE IF NOT EXISTS campaign (
campaign_id SERIAL PRIMARY KEY,
campaign_code VARCHAR(30) UNIQUE NOT NULL,
campaign_name VARCHAR(200) NOT NULL,
campaign_type VARCHAR(30) NOT NULL,
segment_id INT REFERENCES segment(segment_id),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
budget NUMERIC(18,2) NOT NULL CHECK (budget >= 0),
status VARCHAR(20) NOT NULL
);
CREATE TABLE IF NOT EXISTS campaign_target (
target_id SERIAL PRIMARY KEY,
campaign_id INT NOT NULL REFERENCES campaign(campaign_id) ON DELETE CASCADE,
customer_code VARCHAR(20) NOT NULL,
msisdn VARCHAR(20) NOT NULL,
offer_code VARCHAR(30) NOT NULL,
channel VARCHAR(30) NOT NULL,
target_status VARCHAR(20) NOT NULL
);
CREATE TABLE IF NOT EXISTS campaign_response (
response_id SERIAL PRIMARY KEY,
target_id INT NOT NULL REFERENCES campaign_target(target_id) ON DELETE CASCADE,
response_time TIMESTAMP NOT NULL,
response_type VARCHAR(30) NOT NULL,
converted_order_no VARCHAR(30),
notes TEXT
);
CREATE INDEX IF NOT EXISTS idx_campaign_target_customer_code ON campaign_target(customer_code);
CREATE INDEX IF NOT EXISTS idx_campaign_dates ON campaign(start_date, end_date);
TRUNCATE TABLE campaign_response, campaign_target, campaign, segment RESTART IDENTITY CASCADE;
INSERT INTO segment (segment_code, segment_name, criteria_desc)
SELECT
'SEG' || lpad(g::text, 3, '0'),
'Segment ' || g,
'Criteria for segment ' || g
FROM generate_series(1, 20) AS g;
INSERT INTO campaign (
campaign_code, campaign_name, campaign_type, segment_id, start_date, end_date, budget, status
)
SELECT
'CMP' || lpad(g::text, 4, '0'),
'Campaign ' || g,
(ARRAY['UPSELL','CROSS_SELL','RETENTION','ACQUISITION'])[(1 + (g % 4))],
((g - 1) % 20) + 1,
DATE '2026-01-01' + (g % 180),
DATE '2026-01-31' + (g % 180),
((g % 50) + 1) * 1000000::numeric,
(ARRAY['PLANNED','RUNNING','FINISHED'])[(1 + (g % 3))]
FROM generate_series(1, 100) AS g;
INSERT INTO campaign_target (
campaign_id, customer_code, msisdn, offer_code, channel, target_status
)
SELECT
((g - 1) % 100) + 1,
'CUST' || lpad(g::text, 6, '0'),
'09' || lpad(g::text, 8, '0'),
'OFF' || lpad((((g - 1) % 500) + 1)::text, 5, '0'),
(ARRAY['SMS','APP_PUSH','EMAIL','CALL_CENTER'])[(1 + (g % 4))],
(ARRAY['SENT','SENT','CONTACTED','FAILED'])[(1 + (g % 4))]
FROM generate_series(1, 10000) AS g;
INSERT INTO campaign_response (
target_id, response_time, response_type, converted_order_no, notes
)
SELECT
ct.target_id,
now() - ((ct.target_id % 60) || ' days')::interval,
CASE
WHEN ct.target_id % 5 IN (1,2) THEN 'ACCEPTED'
WHEN ct.target_id % 5 = 3 THEN 'INTERESTED'
ELSE 'REJECTED'
END,
CASE WHEN ct.target_id % 5 IN (1,2) THEN 'SO' || to_char(ct.target_id, 'FM00000000') ELSE NULL END,
'Response sample #' || ct.target_id
FROM campaign_target ct
WHERE ct.target_id % 2 = 0;
COMMIT;
/******************************************************************
* 5) ERP DATABASE
******************************************************************/
\connect erp
BEGIN;
CREATE TABLE IF NOT EXISTS department (
dept_id SERIAL PRIMARY KEY,
dept_code VARCHAR(20) UNIQUE NOT NULL,
dept_name VARCHAR(200) NOT NULL,
cost_center VARCHAR(30) NOT NULL
);
CREATE TABLE IF NOT EXISTS employee (
emp_id SERIAL PRIMARY KEY,
emp_code VARCHAR(20) UNIQUE NOT NULL,
full_name VARCHAR(200) NOT NULL,
dept_id INT NOT NULL REFERENCES department(dept_id),
title VARCHAR(100) NOT NULL,
hire_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE IF NOT EXISTS vendor (
vendor_id SERIAL PRIMARY KEY,
vendor_code VARCHAR(20) UNIQUE NOT NULL,
vendor_name VARCHAR(200) NOT NULL,
tax_code VARCHAR(30),
contact_phone VARCHAR(20),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE IF NOT EXISTS purchase_order (
po_id SERIAL PRIMARY KEY,
po_no VARCHAR(30) UNIQUE NOT NULL,
vendor_id INT NOT NULL REFERENCES vendor(vendor_id),
requester_emp_id INT NOT NULL REFERENCES employee(emp_id),
po_date DATE NOT NULL,
po_status VARCHAR(20) NOT NULL,
total_amount NUMERIC(18,2) NOT NULL CHECK (total_amount >= 0)
);
CREATE TABLE IF NOT EXISTS asset (
asset_id SERIAL PRIMARY KEY,
asset_code VARCHAR(30) UNIQUE NOT NULL,
asset_name VARCHAR(200) NOT NULL,
category VARCHAR(50) NOT NULL,
purchase_date DATE NOT NULL,
po_id INT REFERENCES purchase_order(po_id),
assigned_emp_id INT REFERENCES employee(emp_id),
status VARCHAR(20) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_employee_dept_id ON employee(dept_id);
CREATE INDEX IF NOT EXISTS idx_po_date ON purchase_order(po_date);
TRUNCATE TABLE asset, purchase_order, vendor, employee, department RESTART IDENTITY CASCADE;
INSERT INTO department (dept_code, dept_name, cost_center)
SELECT
'D' || lpad(g::text, 3, '0'),
'Department ' || g,
'CC' || lpad(g::text, 4, '0')
FROM generate_series(1, 20) AS g;
INSERT INTO employee (emp_code, full_name, dept_id, title, hire_date, status)
SELECT
'EMP' || lpad(g::text, 6, '0'),
'Employee ' || g,
((g - 1) % 20) + 1,
(ARRAY['DBA','System Engineer','Finance Specialist','Sales Executive','Manager','Analyst'])[(1 + (g % 6))],
DATE '2018-01-01' + (g % 2500),
(ARRAY['ACTIVE','ACTIVE','ACTIVE','INACTIVE'])[(1 + (g % 4))]
FROM generate_series(1, 1000) AS g;
INSERT INTO vendor (vendor_code, vendor_name, tax_code, contact_phone, status)
SELECT
'VEND' || lpad(g::text, 5, '0'),
'Vendor ' || g,
'01' || lpad(g::text, 8, '0'),
'02' || lpad(g::text, 8, '0'),
'ACTIVE'
FROM generate_series(1, 500) AS g;
INSERT INTO purchase_order (po_no, vendor_id, requester_emp_id, po_date, po_status, total_amount)
SELECT
'PO' || lpad(g::text, 8, '0'),
((g - 1) % 500) + 1,
((g - 1) % 1000) + 1,
DATE '2025-01-01' + (g % 365),
(ARRAY['DRAFT','APPROVED','RECEIVED','CLOSED'])[(1 + (g % 4))],
((g % 100) + 1) * 1000000::numeric
FROM generate_series(1, 10000) AS g;
INSERT INTO asset (asset_code, asset_name, category, purchase_date, po_id, assigned_emp_id, status)
SELECT
'ASSET' || lpad(g::text, 8, '0'),
'Asset ' || g,
(ARRAY['SERVER','NETWORK','STORAGE','LICENSE','LAPTOP'])[(1 + (g % 5))],
DATE '2025-01-01' + (g % 365),
g,
((g - 1) % 1000) + 1,
(ARRAY['IN_USE','ALLOCATED','IN_STOCK','REPAIR'])[(1 + (g % 4))]
FROM generate_series(1, 10000) AS g;
COMMIT;
/******************************************************************
* 6) POSTGRES DATABASE (OPS / ADMIN SAMPLE)
******************************************************************/
\connect postgres
BEGIN;
CREATE TABLE IF NOT EXISTS system_parameter (
param_id SERIAL PRIMARY KEY,
param_key VARCHAR(100) UNIQUE NOT NULL,
param_value VARCHAR(255) NOT NULL,
description VARCHAR(255)
);
CREATE TABLE IF NOT EXISTS job_run_log (
job_id SERIAL PRIMARY KEY,
job_name VARCHAR(100) NOT NULL,
run_time TIMESTAMP NOT NULL,
run_status VARCHAR(20) NOT NULL,
duration_sec INT NOT NULL DEFAULT 0,
details TEXT
);
CREATE TABLE IF NOT EXISTS backup_history (
backup_id SERIAL PRIMARY KEY,
backup_type VARCHAR(30) NOT NULL,
backup_start TIMESTAMP NOT NULL,
backup_end TIMESTAMP NOT NULL,
backup_status VARCHAR(20) NOT NULL,
backup_size_mb NUMERIC(18,2) NOT NULL,
storage_location VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS alert_event (
alert_id SERIAL PRIMARY KEY,
alert_level VARCHAR(20) NOT NULL,
source_component VARCHAR(100) NOT NULL,
alert_time TIMESTAMP NOT NULL,
alert_message TEXT NOT NULL,
resolved_flag BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE INDEX IF NOT EXISTS idx_job_run_log_time ON job_run_log(run_time);
CREATE INDEX IF NOT EXISTS idx_alert_event_time ON alert_event(alert_time);
TRUNCATE TABLE alert_event, backup_history, job_run_log, system_parameter RESTART IDENTITY CASCADE;
INSERT INTO system_parameter (param_key, param_value, description)
SELECT
'param_' || lpad(g::text, 3, '0'),
'value_' || g,
'Description for parameter ' || g
FROM generate_series(1, 20) AS g;
INSERT INTO job_run_log (job_name, run_time, run_status, duration_sec, details)
SELECT
'job_' || lpad(((g - 1) % 50 + 1)::text, 3, '0'),
now() - ((g % 365) || ' days')::interval - ((g % 1440) || ' minutes')::interval,
(ARRAY['SUCCESS','SUCCESS','FAILED','RUNNING'])[(1 + (g % 4))],
((g % 1800) + 10),
'Job execution sample #' || g
FROM generate_series(1, 10000) AS g;
INSERT INTO backup_history (
backup_type, backup_start, backup_end, backup_status, backup_size_mb, storage_location
)
SELECT
(ARRAY['FULL','INCREMENTAL','WAL'])[(1 + (g % 3))],
now() - ((g % 180) || ' days')::interval - interval '2 hours',
now() - ((g % 180) || ' days')::interval,
(ARRAY['SUCCESS','SUCCESS','FAILED'])[(1 + (g % 3))],
((g % 50000) + 100)::numeric,
'/backup/location/' || g
FROM generate_series(1, 1000) AS g;
INSERT INTO alert_event (
alert_level, source_component, alert_time, alert_message, resolved_flag
)
SELECT
(ARRAY['INFO','WARNING','CRITICAL'])[(1 + (g % 3))],
(ARRAY['postgresql','backup','monitoring','replication','storage'])[(1 + (g % 5))],
now() - ((g % 120) || ' days')::interval - ((g % 1440) || ' minutes')::interval,
'Alert sample #' || g,
CASE WHEN g % 3 = 0 THEN TRUE ELSE FALSE END
FROM generate_series(1, 10000) AS g;
COMMIT;
/******************************************************************
* 7) QUICK VALIDATION
******************************************************************/
\connect crm
SELECT 'crm.customer' AS table_name, count(*) AS row_count FROM customer
UNION ALL
SELECT 'crm.customer_address', count(*) FROM customer_address
UNION ALL
SELECT 'crm.subscriber', count(*) FROM subscriber
UNION ALL
SELECT 'crm.interaction_log', count(*) FROM interaction_log;
\connect sale
SELECT 'sale.shop' AS table_name, count(*) AS row_count FROM shop
UNION ALL
SELECT 'sale.product', count(*) FROM product
UNION ALL
SELECT 'sale.sales_order', count(*) FROM sales_order
UNION ALL
SELECT 'sale.sales_order_item', count(*) FROM sales_order_item;
\connect billing
SELECT 'billing.tariff_plan' AS table_name, count(*) AS row_count FROM tariff_plan
UNION ALL
SELECT 'billing.billing_account', count(*) FROM billing_account
UNION ALL
SELECT 'billing.invoice', count(*) FROM invoice
UNION ALL
SELECT 'billing.payment', count(*) FROM payment
UNION ALL
SELECT 'billing.usage_cdr', count(*) FROM usage_cdr;
\connect marketing
SELECT 'marketing.segment' AS table_name, count(*) AS row_count FROM segment
UNION ALL
SELECT 'marketing.campaign', count(*) FROM campaign
UNION ALL
SELECT 'marketing.campaign_target', count(*) FROM campaign_target
UNION ALL
SELECT 'marketing.campaign_response', count(*) FROM campaign_response;
\connect erp
SELECT 'erp.department' AS table_name, count(*) AS row_count FROM department
UNION ALL
SELECT 'erp.employee', count(*) FROM employee
UNION ALL
SELECT 'erp.vendor', count(*) FROM vendor
UNION ALL
SELECT 'erp.purchase_order', count(*) FROM purchase_order
UNION ALL
SELECT 'erp.asset', count(*) FROM asset;
\connect postgres
SELECT 'postgres.system_parameter' AS table_name, count(*) AS row_count FROM system_parameter
UNION ALL
SELECT 'postgres.job_run_log', count(*) FROM job_run_log
UNION ALL
SELECT 'postgres.backup_history', count(*) FROM backup_history
UNION ALL
SELECT 'postgres.alert_event', count(*) FROM alert_event;
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
