Dbms Applications - Learning Module

Loading content...

0/241

Enterprise Systems: The Organizational Backbone

The Invisible Infrastructure of Business

When you order a product online, a cascade of database operations occurs across dozens of enterprise systems: inventory is checked, credit is verified, the order is scheduled for fulfillment, warehouse systems direct picking, shipping is coordinated, accounting entries are made, and customer service gets access to order status. All of this happens in seconds, invisibly coordinated by enterprise databases.

Enterprise Resource Planning (ERP) systems alone represent a $50 billion annual market. SAP, the world's largest ERP vendor, processes 77% of global transaction revenue through their systems. Oracle, Microsoft Dynamics, and Salesforce power the operations of virtually every major corporation. Behind every one of these systems lies sophisticated database architecture that must handle the complete operational data of enormous organizations.

Learning Objectives

By the end of this page, you will understand: (1) ERP database architecture and integration patterns, (2) CRM data models for customer relationship management, (3) Supply chain database challenges, (4) Business intelligence and data warehousing, and (5) How modern enterprises orchestrate multiple database systems.

Enterprise Resource Planning (ERP): The System of Record

ERP systems integrate all core business processes into a unified database: finance, human resources, manufacturing, supply chain, services, and procurement. The power of ERP lies in this integration—data entered once is immediately available across all business functions.

Why ERP Matters for Database Understanding:

ERP systems represent the most complex database schemas in commercial software. SAP S/4HANA has over 75,000 database tables. Oracle E-Business Suite contains over 30,000 tables. Understanding how these massive schemas are designed provides insight into industrial-strength database practices.

Converting Mermaid diagram...

Key ERP Database Concepts:

1. Master Data The foundational entities that don't change frequently: customers, vendors, employees, products, chart of accounts. Master data is the reference point for all transactional data. Maintaining master data quality is critical—duplicates or errors propagate throughout the system.

2. Transactional Data Records of business events: purchase orders, invoices, payments, shipments. Transactional data references master data and accumulates over time. A large enterprise generates millions of transactions daily.

3. Configuration Data Settings that control system behavior: tax rates, approval hierarchies, pricing rules, workflow configurations. Configuration data is tenant-specific in multi-tenant SaaS ERP systems.

4. Document Flow Business documents link together in chains: sales order → delivery → invoice → payment. The database must efficiently traverse these document flows for status queries and auditing.

erp_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- Simplified ERP Core Schema
 
-- Master Data: Customers
CREATE TABLE bp_customers (
    customer_id VARCHAR(20) PRIMARY KEY,
    company_code VARCHAR(4) NOT NULL,
    name VARCHAR(255) NOT NULL,
    customer_type VARCHAR(20) NOT NULL,
    credit_limit DECIMAL(15,2),
    payment_terms VARCHAR(10),
    tax_id VARCHAR(50),
    status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50) NOT NULL,
    modified_at TIMESTAMPTZ,
    modified_by VARCHAR(50)
);
 
-- Master Data: Materials/Products
CREATE TABLE mm_materials (
    material_id VARCHAR(40) PRIMARY KEY,
    material_type VARCHAR(4) NOT NULL,  -- FERT (finished), HALB (semi), ROH (raw)
    description VARCHAR(255) NOT NULL,
    base_unit VARCHAR(3) NOT NULL,
    material_group VARCHAR(10),
    gross_weight DECIMAL(13,3),
    net_weight DECIMAL(13,3),
    weight_unit VARCHAR(3),
    status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE'
);
 
-- Transactional: Sales Orders
CREATE TABLE sd_sales_orders (
    sales_order_id VARCHAR(20) PRIMARY KEY,
    order_type VARCHAR(4) NOT NULL,
    customer_id VARCHAR(20) NOT NULL REFERENCES bp_customers(customer_id),
    order_date DATE NOT NULL,
    requested_delivery_date DATE,
    sales_org VARCHAR(4) NOT NULL,
    distribution_channel VARCHAR(2) NOT NULL,
    division VARCHAR(2) NOT NULL,
    net_value DECIMAL(15,2),
    currency VARCHAR(3) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Sales Order Line Items
CREATE TABLE sd_sales_order_items (
    sales_order_id VARCHAR(20) NOT NULL REFERENCES sd_sales_orders(sales_order_id),
    item_number INT NOT NULL,
    material_id VARCHAR(40) NOT NULL REFERENCES mm_materials(material_id),
    quantity DECIMAL(13,3) NOT NULL,
    unit VARCHAR(3) NOT NULL,
    net_price DECIMAL(15,2) NOT NULL,
    net_value DECIMAL(15,2) NOT NULL,
    delivery_status VARCHAR(20) NOT NULL DEFAULT 'NOT_DELIVERED',
    billing_status VARCHAR(20) NOT NULL DEFAULT 'NOT_BILLED',
    PRIMARY KEY (sales_order_id, item_number)
);
 
-- Document Flow: Links related documents
CREATE TABLE document_flow (
    flow_id BIGSERIAL PRIMARY KEY,
    preceding_doc_type VARCHAR(20) NOT NULL,
    preceding_doc_id VARCHAR(20) NOT NULL,
    preceding_item INT,
    subsequent_doc_type VARCHAR(20) NOT NULL,
    subsequent_doc_id VARCHAR(20) NOT NULL,
    subsequent_item INT,
    quantity DECIMAL(13,3),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Index for document flow traversal
CREATE INDEX idx_docflow_preceding ON document_flow(preceding_doc_type, preceding_doc_id);
CREATE INDEX idx_docflow_subsequent ON document_flow(subsequent_doc_type, subsequent_doc_id);

Customer Relationship Management (CRM)

CRM systems manage an organization's interactions with current and potential customers. Salesforce, the dominant CRM platform, has 150 million active users and stores petabytes of customer data. CRM databases differ from ERP in their focus on relationships, activities, and customer journey rather than transactions.

CRM Data Categories:

Core CRM Data Types

•Accounts — Companies or organizations you do business with. Contains firmographic data: size, industry, revenue, location.
•Contacts — Individual people at accounts. Multiple contacts per account, tracking roles and relationships.
•Leads — Potential customers not yet qualified. May convert to accounts/contacts/opportunities.
•Opportunities — Potential sales deals. Tracks value, probability, stage, close date, competition.
•Activities — Calls, emails, meetings, tasks. The interaction history that builds relationship understanding.
•Cases — Customer service issues. Tracks problem, resolution, customer satisfaction.

crm_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- CRM Core Schema (Salesforce-style)
 
-- Accounts: Companies
CREATE TABLE accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    account_type VARCHAR(50),  -- Customer, Prospect, Partner, Competitor
    industry VARCHAR(100),
    annual_revenue DECIMAL(15,2),
    employee_count INT,
    website VARCHAR(255),
    phone VARCHAR(40),
    billing_address JSONB,
    shipping_address JSONB,
    owner_id UUID REFERENCES users(user_id),
    parent_account_id UUID REFERENCES accounts(account_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity_date DATE
);
 
-- Contacts: People at accounts
CREATE TABLE contacts (
    contact_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID REFERENCES accounts(account_id),
    first_name VARCHAR(40),
    last_name VARCHAR(80) NOT NULL,
    title VARCHAR(128),
    email VARCHAR(255),
    phone VARCHAR(40),
    mobile_phone VARCHAR(40),
    department VARCHAR(80),
    lead_source VARCHAR(40),
    owner_id UUID REFERENCES users(user_id),
    do_not_call BOOLEAN DEFAULT false,
    email_opt_out BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Opportunities: Sales deals
CREATE TABLE opportunities (
    opportunity_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(120) NOT NULL,
    account_id UUID NOT NULL REFERENCES accounts(account_id),
    stage VARCHAR(40) NOT NULL,  -- Prospecting, Qualification, Proposal, Negotiation, Closed Won/Lost
    amount DECIMAL(15,2),
    probability INT CHECK (probability BETWEEN 0 AND 100),
    close_date DATE NOT NULL,
    type VARCHAR(40),  -- New Business, Existing Business, Renewal
    lead_source VARCHAR(40),
    primary_contact_id UUID REFERENCES contacts(contact_id),
    owner_id UUID REFERENCES users(user_id),
    next_step VARCHAR(255),
    description TEXT,
    is_closed BOOLEAN DEFAULT false,
    is_won BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_stage_change_at TIMESTAMPTZ
);
 
-- Activities: Interactions (polymorphic relationship)
CREATE TABLE activities (
    activity_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    activity_type VARCHAR(20) NOT NULL,  -- CALL, EMAIL, MEETING, TASK
    subject VARCHAR(255) NOT NULL,
    description TEXT,
    
    -- Polymorphic "related to" - can link to account, opportunity, case
    related_to_type VARCHAR(20) NOT NULL,  -- ACCOUNT, OPPORTUNITY, CASE
    related_to_id UUID NOT NULL,
    
    -- Who is involved
    contact_id UUID REFERENCES contacts(contact_id),
    owner_id UUID REFERENCES users(user_id),
    
    -- Timing
    activity_date TIMESTAMPTZ NOT NULL,
    duration_minutes INT,
    is_completed BOOLEAN DEFAULT false,
    completed_at TIMESTAMPTZ,
    
    -- For tasks: priority and reminder
    priority VARCHAR(10),  -- HIGH, MEDIUM, LOW
    reminder_at TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Indexes for common CRM queries
CREATE INDEX idx_opps_account ON opportunities(account_id, stage);
CREATE INDEX idx_opps_owner ON opportunities(owner_id, is_closed, close_date);
CREATE INDEX idx_activities_related ON activities(related_to_type, related_to_id, activity_date DESC);
CREATE INDEX idx_activities_owner ON activities(owner_id, is_completed, activity_date);

Multi-Tenancy in CRM

Salesforce pioneered multi-tenant SaaS architecture. All customers share the same database infrastructure, with tenant isolation handled at the application level. This requires careful attention to row-level security, data isolation, and fair resource allocation between tenants.

Supply Chain Management: Orchestrating Complexity

Supply chain databases manage the flow of goods from raw materials to finished products to end customers. This involves tracking inventory across multiple locations, planning production, coordinating with suppliers, and optimizing logistics. The COVID-19 pandemic highlighted how critical—and fragile—supply chain visibility can be.

Supply Chain Database Challenges:

Supply Chain Data Volumes (Large Manufacturer)
Data Type	Volume	Update Frequency	Query Pattern
SKUs (Products)	500,000+	Weekly	Point lookups, hierarchical
Inventory Records	10M+ (SKU x location)	Real-time	Aggregations, availability checks
Orders (PO/SO)	1M/month	Daily	Status tracking, document flow
Shipments	500K/month	Real-time	Tracking queries, route optimization
IoT Sensor Data	1B+ events/day	Streaming	Time-series analytics, alerting

Inventory Management:

Inventory is deceptively complex. A single product might exist in multiple states simultaneously:

On hand: Physically in warehouse
On order: Ordered from supplier, not yet received
In transit: Between locations
Reserved: Allocated to customer orders
Available: On hand minus reserved
Committed: Promised but not yet shipped

A simple "what's our inventory?" question requires careful consideration of which definition applies.

inventory_management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- Supply Chain Inventory Schema
 
-- Locations: Where inventory can be stored
CREATE TABLE locations (
    location_id VARCHAR(20) PRIMARY KEY,
    location_type VARCHAR(20) NOT NULL,  -- WAREHOUSE, STORE, DC, SUPPLIER
    name VARCHAR(100) NOT NULL,
    address JSONB,
    timezone VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT true
);
 
-- Inventory by SKU and location
CREATE TABLE inventory (
    sku VARCHAR(50) NOT NULL,
    location_id VARCHAR(20) NOT NULL REFERENCES locations(location_id),
    
    -- Quantity breakdown
    quantity_on_hand DECIMAL(15,3) NOT NULL DEFAULT 0,
    quantity_reserved DECIMAL(15,3) NOT NULL DEFAULT 0,
    quantity_in_transit DECIMAL(15,3) NOT NULL DEFAULT 0,
    
    -- Derived quantity (calculated on read or via trigger)
    quantity_available DECIMAL(15,3) GENERATED ALWAYS AS 
        (quantity_on_hand - quantity_reserved) STORED,
    
    -- Tracking
    unit_of_measure VARCHAR(10) NOT NULL,
    last_receipt_date DATE,
    last_issue_date DATE,
    last_count_date DATE,
    
    PRIMARY KEY (sku, location_id)
);
 
-- Inventory transactions (ledger style)
CREATE TABLE inventory_transactions (
    transaction_id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    location_id VARCHAR(20) NOT NULL,
    
    transaction_type VARCHAR(20) NOT NULL,  -- RECEIPT, ISSUE, TRANSFER, ADJUSTMENT, RESERVE
    quantity DECIMAL(15,3) NOT NULL,  -- Positive = IN, Negative = OUT
    unit_cost DECIMAL(15,4),
    
    -- Reference to source document
    reference_type VARCHAR(20),  -- PO, SO, TRANSFER_ORDER, COUNT
    reference_id VARCHAR(30),
    
    -- Audit
    transaction_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50) NOT NULL,
    reason_code VARCHAR(20),
    notes TEXT
);
 
-- Real-time inventory update via triggers
CREATE OR REPLACE FUNCTION update_inventory_on_transaction()
RETURNS TRIGGER AS $$
BEGIN
    -- Update inventory based on transaction type
    IF NEW.transaction_type IN ('RECEIPT', 'TRANSFER_IN') THEN
        UPDATE inventory 
        SET quantity_on_hand = quantity_on_hand + NEW.quantity,
            last_receipt_date = CURRENT_DATE
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
        
    ELSIF NEW.transaction_type IN ('ISSUE', 'TRANSFER_OUT') THEN
        UPDATE inventory 
        SET quantity_on_hand = quantity_on_hand - ABS(NEW.quantity),
            last_issue_date = CURRENT_DATE
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
        
    ELSIF NEW.transaction_type = 'RESERVE' THEN
        UPDATE inventory 
        SET quantity_reserved = quantity_reserved + NEW.quantity
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
        
    ELSIF NEW.transaction_type = 'UNRESERVE' THEN
        UPDATE inventory 
        SET quantity_reserved = quantity_reserved - ABS(NEW.quantity)
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_inventory_transaction
AFTER INSERT ON inventory_transactions
FOR EACH ROW EXECUTE FUNCTION update_inventory_on_transaction();
 
-- Available-to-Promise (ATP) calculation
CREATE OR REPLACE FUNCTION get_available_to_promise(
    p_sku VARCHAR(50),
    p_location_id VARCHAR(20),
    p_as_of_date DATE
) RETURNS DECIMAL(15,3) AS $$
DECLARE
    v_on_hand DECIMAL(15,3);
    v_reserved DECIMAL(15,3);
    v_incoming DECIMAL(15,3);
    v_committed DECIMAL(15,3);
BEGIN
    -- Current on-hand minus reservations
    SELECT quantity_on_hand, quantity_reserved INTO v_on_hand, v_reserved
    FROM inventory
    WHERE sku = p_sku AND location_id = p_location_id;
    
    -- Expected receipts by the date
    SELECT COALESCE(SUM(quantity_ordered - quantity_received), 0) INTO v_incoming
    FROM purchase_order_lines pol
    JOIN purchase_orders po ON pol.po_id = po.po_id
    WHERE pol.sku = p_sku 
      AND po.receiving_location = p_location_id
      AND po.expected_delivery <= p_as_of_date
      AND po.status NOT IN ('CANCELLED', 'RECEIVED');
    
    -- Committed outbound (orders not yet shipped)
    SELECT COALESCE(SUM(quantity_ordered - quantity_shipped), 0) INTO v_committed
    FROM sales_order_lines sol
    JOIN sales_orders so ON sol.so_id = so.so_id
    WHERE sol.sku = p_sku
      AND so.ship_from_location = p_location_id
      AND so.requested_ship_date <= p_as_of_date
      AND so.status NOT IN ('CANCELLED', 'SHIPPED');
    
    RETURN (v_on_hand - v_reserved) + v_incoming - v_committed;
END;
$$ LANGUAGE plpgsql;

Business Intelligence and Data Warehousing

Operational databases (ERP, CRM, SCM) are optimized for transactions—many small reads and writes. But executives need analytics: trends, aggregations, comparisons across time. Running analytical queries against operational databases would cripple performance. This is why enterprises maintain separate data warehouses optimized for analytics.

OLTP (Operational)

•Optimized for transactions
•Row-oriented storage
•Normalized schema (3NF)
•Real-time, current data
•Many concurrent users
•Small queries, fast response

OLAP (Analytical)

•Optimized for analysis
•Columnar storage
•Denormalized (star/snowflake)
•Historical, periodic refresh
•Fewer concurrent users
•Complex queries, longer response

Dimensional Modeling:

Data warehouses use dimensional modeling, organizing data around business processes. The core concepts:

Fact Tables: Store measurable events (sales, orders, shipments). Each row is a transaction with numeric measures (amount, quantity, cost) and foreign keys to dimensions.

Dimension Tables: Describe the context of facts (who, what, when, where). Customer dimension, product dimension, time dimension, geography dimension.

Star Schema: Fact table in center, dimension tables radiating out like a star. Simple, fast queries.

Snowflake Schema: Dimensions are normalized into sub-dimensions. More storage efficient, more complex queries.

data_warehouse_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- Star Schema for Sales Analytics
 
-- Dimension: Time (pre-populated calendar)
CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,  -- YYYYMMDD format
    full_date DATE NOT NULL UNIQUE,
    day_of_week INT NOT NULL,
    day_name VARCHAR(10) NOT NULL,
    day_of_month INT NOT NULL,
    day_of_year INT NOT NULL,
    week_of_year INT NOT NULL,
    month_number INT NOT NULL,
    month_name VARCHAR(10) NOT NULL,
    quarter INT NOT NULL,
    year INT NOT NULL,
    is_weekend BOOLEAN NOT NULL,
    is_holiday BOOLEAN NOT NULL,
    fiscal_year INT NOT NULL,
    fiscal_quarter INT NOT NULL
);
 
-- Dimension: Product
CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,  -- Natural key from source
    product_name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    unit_cost DECIMAL(10,2),
    unit_price DECIMAL(10,2),
    -- SCD Type 2: Track historical changes
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);
 
-- Dimension: Customer
CREATE TABLE dim_customer (
    customer_key SERIAL PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_type VARCHAR(50),
    industry VARCHAR(100),
    region VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100),
    segment VARCHAR(50),  -- Enterprise, SMB, Consumer
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);
 
-- Dimension: Sales Rep
CREATE TABLE dim_sales_rep (
    sales_rep_key SERIAL PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL,
    rep_name VARCHAR(255) NOT NULL,
    team VARCHAR(100),
    region VARCHAR(100),
    hire_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);
 
-- Fact: Sales Transactions
CREATE TABLE fact_sales (
    sales_key BIGSERIAL PRIMARY KEY,
    
    -- Foreign keys to dimensions
    date_key INT NOT NULL REFERENCES dim_date(date_key),
    product_key INT NOT NULL REFERENCES dim_product(product_key),
    customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
    sales_rep_key INT REFERENCES dim_sales_rep(sales_rep_key),
    
    -- Degenerate dimensions (no separate table needed)
    order_number VARCHAR(30) NOT NULL,
    line_number INT NOT NULL,
    
    -- Measures
    quantity_sold INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    sales_amount DECIMAL(15,2) NOT NULL,  -- quantity * unit_price - discount
    cost_amount DECIMAL(15,2) NOT NULL,   -- quantity * unit_cost
    profit_amount DECIMAL(15,2) NOT NULL  -- sales_amount - cost_amount
);
 
-- Indexes optimized for analytical queries
CREATE INDEX idx_fact_sales_date ON fact_sales(date_key);
CREATE INDEX idx_fact_sales_product ON fact_sales(product_key);
CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_key);
 
-- Sample analytical query: Monthly sales by product category
SELECT 
    dd.year,
    dd.month_name,
    dp.category,
    SUM(fs.sales_amount) AS total_sales,
    SUM(fs.profit_amount) AS total_profit,
    SUM(fs.quantity_sold) AS total_units,
    AVG(fs.discount_amount / NULLIF(fs.sales_amount + fs.discount_amount, 0)) * 100 AS avg_discount_pct
FROM fact_sales fs
JOIN dim_date dd ON fs.date_key = dd.date_key
JOIN dim_product dp ON fs.product_key = dp.product_key
WHERE dd.year = 2024
GROUP BY dd.year, dd.month_number, dd.month_name, dp.category
ORDER BY dd.year, dd.month_number, total_sales DESC;

Enterprise Integration: Connecting the Systems

Large enterprises run dozens of database systems that must work together. The ERP must know about CRM customers. The data warehouse must receive transactions from all sources. The e-commerce system must check ERP inventory. Managing these integrations is a significant database challenge.

Converting Mermaid diagram...

Integration Patterns

•Point-to-Point — Direct connections between systems. Simple but becomes spaghetti at scale. N systems require N(N-1)/2 connections.
•Enterprise Service Bus (ESB) — Central hub routing messages between systems. Reduces connections to N. Can become bottleneck.
•Event-Driven Architecture — Systems publish events; interested parties subscribe. Loose coupling, eventual consistency.
•API-Led — Each system exposes APIs; integration platforms orchestrate. Modern, flexible, but requires API governance.
•Data Virtualization — Query across multiple sources without moving data. Real-time but can be slow for complex queries.

Master Data Management (MDM):

MDM solves a critical problem: when Customer "Acme Corp" is in the ERP as "ACME Corporation" and in CRM as "Acme Corp, Inc.", which is correct? MDM systems maintain the "golden record"—the authoritative master data that all other systems should use.

MDM databases store:

Master records: The authoritative entity data
Cross-references: Mappings from source system IDs to master IDs
Match rules: Algorithms for identifying duplicates
Merge history: Audit trail of entity consolidation
Data quality scores: Confidence in data accuracy

Integration Debt

Enterprise integration failures cause billions in losses annually. Legacy point-to-point integrations accumulate as 'integration debt'—undocumented connections that break unexpectedly when systems change. Modern enterprises invest heavily in integration platforms and governance to manage this complexity.

Modern Enterprise Database Platforms

The enterprise database landscape has evolved dramatically. Traditional on-premise databases are giving way to cloud platforms, and monolithic systems are being complemented by microservices architectures.

Enterprise Database Platform Evolution
Era	Approach	Examples	Key Characteristics
Traditional (1990s-2000s)	On-premise, monolithic	Oracle DB, DB2, SQL Server	High license cost, DBA-intensive, vertical scaling
Cloud IaaS (2010s)	Lift-and-shift to cloud VMs	RDS, Cloud SQL	Reduced hardware management, same database engines
Cloud-Native (2015+)	Purpose-built cloud databases	Aurora, Spanner, DynamoDB	Serverless options, auto-scaling, consumption pricing
Multi-Cloud (2020+)	Portable across clouds	CockroachDB, MongoDB Atlas	Vendor independence, global distribution

Modern Enterprise Trends

•Hybrid Transactional/Analytical Processing (HTAP) — Systems like TiDB and SingleStore handle both OLTP and OLAP workloads, reducing ETL complexity.
•Distributed SQL — Databases like CockroachDB and YugabyteDB provide PostgreSQL compatibility with global distribution.
•Data Mesh — Decentralized data ownership where each business domain manages its own data products.
•Lakehouse Architecture — Combines data lake storage economics with warehouse query capabilities (Databricks, Snowflake).
•Real-Time Analytics — Streaming databases like Apache Druid and ClickHouse enable sub-second analytics on live data.
•AI/ML Integration — Databases increasingly embed ML capabilities—vector search, in-database inference, feature stores.

The Rise of the Data Stack

Modern enterprises assemble best-of-breed components: cloud data warehouse (Snowflake), ELT tool (Fivetran), transformation (dbt), BI (Looker/Tableau), reverse ETL (Census). This 'Modern Data Stack' replaces monolithic solutions with composable, specialized tools connected via standard APIs.

Enterprise Database Security and Compliance

Enterprise databases contain an organization's most sensitive data: financial records, customer information, trade secrets, employee data. Security breaches can result in regulatory fines, lawsuits, and reputational damage. Major regulations affecting enterprise databases:

Key Regulatory Requirements for Enterprise Databases
Regulation	Scope	Key Database Requirements	Penalties
SOX	US public companies	Financial data integrity, audit trails, access controls	$5M+ fines, executives personally liable
GDPR	EU personal data	Consent tracking, right to deletion, data portability	Up to €20M or 4% of revenue
PCI-DSS	Payment card data	Encryption, access logging, network segmentation	Fines up to $500K/incident
SOC 2	Service providers	Security, availability, processing integrity	Loss of business trust
CCPA/CPRA	California consumers	Opt-out tracking, deletion capability, data inventory	$7,500 per intentional violation

enterprise_security.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- Enterprise Database Security Configuration
 
-- Role-based access control
CREATE ROLE finance_readonly;
CREATE ROLE finance_writer;
CREATE ROLE admin_full;
 
-- Grant minimal necessary permissions
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_readonly;
GRANT SELECT, INSERT, UPDATE ON finance.journal_entries TO finance_writer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA finance TO admin_full;
 
-- Row-level security for multi-business-unit access
CREATE POLICY business_unit_isolation ON transactions
    USING (business_unit_id = current_setting('app.business_unit')::INT);
 
-- Column-level encryption for sensitive data
-- Salary, SSN, bank account stored encrypted
CREATE TABLE employees_secure (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    -- Sensitive columns encrypted with column-level encryption
    ssn_encrypted BYTEA NOT NULL,
    salary_encrypted BYTEA NOT NULL,
    bank_account_encrypted BYTEA
);
 
-- Comprehensive audit trigger
CREATE TABLE audit_log (
    log_id BIGSERIAL PRIMARY KEY,
    event_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_name VARCHAR(100) NOT NULL DEFAULT CURRENT_USER,
    client_addr INET DEFAULT inet_client_addr(),
    application_name VARCHAR(100) DEFAULT current_setting('application_name'),
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE, SELECT
    table_schema VARCHAR(100) NOT NULL,
    table_name VARCHAR(100) NOT NULL,
    record_id TEXT,
    old_values JSONB,
    new_values JSONB,
    query_text TEXT  -- Careful: may contain sensitive data
);
 
-- Trigger function for audit logging
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (
        action, table_schema, table_name, record_id, old_values, new_values
    ) VALUES (
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME,
        CASE WHEN TG_OP = 'DELETE' THEN OLD.id::TEXT ELSE NEW.id::TEXT END,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD)::JSONB END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::JSONB END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
 
-- Apply audit trigger to sensitive tables
CREATE TRIGGER audit_financial_transactions
    AFTER INSERT OR UPDATE OR DELETE ON financial_transactions
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

Summary: DBMS in Enterprise Systems

Enterprise databases represent the operational backbone of modern organizations. From ERP systems managing core business processes to CRM tracking customer relationships to data warehouses enabling strategic decisions, databases integrate and coordinate the full scope of business operations. Let's consolidate the key insights:

Key Takeaways

•ERP systems integrate all business functions — Finance, HR, manufacturing, and sales share a unified database, enabling cross-functional visibility and consistency.
•CRM databases focus on relationships — Accounts, contacts, opportunities, and activities model the customer lifecycle. Multi-tenancy is a key architectural pattern.
•Supply chain requires real-time visibility — Inventory tracking across states (on-hand, reserved, in-transit) demands careful data modeling and transactional integrity.
•OLTP and OLAP serve different needs — Operational databases handle transactions; data warehouses with dimensional models enable analytics. Different schema designs optimize each.
•Enterprise integration is complex — Multiple systems must share data. ESB, event-driven, and API-led patterns each have tradeoffs.
•Modern platforms are cloud-native — HTAP, distributed SQL, and data mesh architectures are replacing monolithic systems.
•Security and compliance are mandatory — SOX, GDPR, PCI-DSS impose specific requirements for encryption, access control, and audit logging.

Module Conclusion:

This module has explored how DBMS technology powers critical applications across diverse industries: banking, e-commerce, healthcare, education, and enterprise systems. Each domain presents unique challenges—banking demands absolute consistency, e-commerce requires massive scale, healthcare prioritizes privacy and interoperability, education enables personalization, and enterprise systems must integrate complex business processes.

The common thread: modern organizations are fundamentally dependent on database technology. Understanding these application domains provides context for why specific database features exist and guides architectural decisions in your own work.

Module Complete

Congratulations! You've completed Module 6: DBMS Applications. You now understand how database technology enables critical applications across banking, e-commerce, healthcare, education, and enterprise systems. Each industry leverages DBMS capabilities differently based on their unique requirements for consistency, availability, scale, privacy, and integration. This foundational knowledge prepares you for deeper exploration of database architecture and design in subsequent chapters.

Enterprise Systems: The Organizational Backbone

The Invisible Infrastructure of Business

Learning Objectives

Enterprise Resource Planning (ERP): The System of Record

Why ERP Matters for Database Understanding:

Converting Mermaid diagram...

Key ERP Database Concepts:

erp_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- Simplified ERP Core Schema
 
-- Master Data: Customers
CREATE TABLE bp_customers (
    customer_id VARCHAR(20) PRIMARY KEY,
    company_code VARCHAR(4) NOT NULL,
    name VARCHAR(255) NOT NULL,
    customer_type VARCHAR(20) NOT NULL,
    credit_limit DECIMAL(15,2),
    payment_terms VARCHAR(10),
    tax_id VARCHAR(50),
    status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50) NOT NULL,
    modified_at TIMESTAMPTZ,
    modified_by VARCHAR(50)
);
 
-- Master Data: Materials/Products
CREATE TABLE mm_materials (
    material_id VARCHAR(40) PRIMARY KEY,
    material_type VARCHAR(4) NOT NULL,  -- FERT (finished), HALB (semi), ROH (raw)
    description VARCHAR(255) NOT NULL,
    base_unit VARCHAR(3) NOT NULL,
    material_group VARCHAR(10),
    gross_weight DECIMAL(13,3),
    net_weight DECIMAL(13,3),
    weight_unit VARCHAR(3),
    status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE'
);
 
-- Transactional: Sales Orders
CREATE TABLE sd_sales_orders (
    sales_order_id VARCHAR(20) PRIMARY KEY,
    order_type VARCHAR(4) NOT NULL,
    customer_id VARCHAR(20) NOT NULL REFERENCES bp_customers(customer_id),
    order_date DATE NOT NULL,
    requested_delivery_date DATE,
    sales_org VARCHAR(4) NOT NULL,
    distribution_channel VARCHAR(2) NOT NULL,
    division VARCHAR(2) NOT NULL,
    net_value DECIMAL(15,2),
    currency VARCHAR(3) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Sales Order Line Items
CREATE TABLE sd_sales_order_items (
    sales_order_id VARCHAR(20) NOT NULL REFERENCES sd_sales_orders(sales_order_id),
    item_number INT NOT NULL,
    material_id VARCHAR(40) NOT NULL REFERENCES mm_materials(material_id),
    quantity DECIMAL(13,3) NOT NULL,
    unit VARCHAR(3) NOT NULL,
    net_price DECIMAL(15,2) NOT NULL,
    net_value DECIMAL(15,2) NOT NULL,
    delivery_status VARCHAR(20) NOT NULL DEFAULT 'NOT_DELIVERED',
    billing_status VARCHAR(20) NOT NULL DEFAULT 'NOT_BILLED',
    PRIMARY KEY (sales_order_id, item_number)
);
 
-- Document Flow: Links related documents
CREATE TABLE document_flow (
    flow_id BIGSERIAL PRIMARY KEY,
    preceding_doc_type VARCHAR(20) NOT NULL,
    preceding_doc_id VARCHAR(20) NOT NULL,
    preceding_item INT,
    subsequent_doc_type VARCHAR(20) NOT NULL,
    subsequent_doc_id VARCHAR(20) NOT NULL,
    subsequent_item INT,
    quantity DECIMAL(13,3),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Index for document flow traversal
CREATE INDEX idx_docflow_preceding ON document_flow(preceding_doc_type, preceding_doc_id);
CREATE INDEX idx_docflow_subsequent ON document_flow(subsequent_doc_type, subsequent_doc_id);

Customer Relationship Management (CRM)

CRM Data Categories:

Core CRM Data Types

•Accounts — Companies or organizations you do business with. Contains firmographic data: size, industry, revenue, location.
•Contacts — Individual people at accounts. Multiple contacts per account, tracking roles and relationships.
•Leads — Potential customers not yet qualified. May convert to accounts/contacts/opportunities.
•Opportunities — Potential sales deals. Tracks value, probability, stage, close date, competition.
•Activities — Calls, emails, meetings, tasks. The interaction history that builds relationship understanding.
•Cases — Customer service issues. Tracks problem, resolution, customer satisfaction.

crm_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- CRM Core Schema (Salesforce-style)
 
-- Accounts: Companies
CREATE TABLE accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    account_type VARCHAR(50),  -- Customer, Prospect, Partner, Competitor
    industry VARCHAR(100),
    annual_revenue DECIMAL(15,2),
    employee_count INT,
    website VARCHAR(255),
    phone VARCHAR(40),
    billing_address JSONB,
    shipping_address JSONB,
    owner_id UUID REFERENCES users(user_id),
    parent_account_id UUID REFERENCES accounts(account_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity_date DATE
);
 
-- Contacts: People at accounts
CREATE TABLE contacts (
    contact_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID REFERENCES accounts(account_id),
    first_name VARCHAR(40),
    last_name VARCHAR(80) NOT NULL,
    title VARCHAR(128),
    email VARCHAR(255),
    phone VARCHAR(40),
    mobile_phone VARCHAR(40),
    department VARCHAR(80),
    lead_source VARCHAR(40),
    owner_id UUID REFERENCES users(user_id),
    do_not_call BOOLEAN DEFAULT false,
    email_opt_out BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Opportunities: Sales deals
CREATE TABLE opportunities (
    opportunity_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(120) NOT NULL,
    account_id UUID NOT NULL REFERENCES accounts(account_id),
    stage VARCHAR(40) NOT NULL,  -- Prospecting, Qualification, Proposal, Negotiation, Closed Won/Lost
    amount DECIMAL(15,2),
    probability INT CHECK (probability BETWEEN 0 AND 100),
    close_date DATE NOT NULL,
    type VARCHAR(40),  -- New Business, Existing Business, Renewal
    lead_source VARCHAR(40),
    primary_contact_id UUID REFERENCES contacts(contact_id),
    owner_id UUID REFERENCES users(user_id),
    next_step VARCHAR(255),
    description TEXT,
    is_closed BOOLEAN DEFAULT false,
    is_won BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_stage_change_at TIMESTAMPTZ
);
 
-- Activities: Interactions (polymorphic relationship)
CREATE TABLE activities (
    activity_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    activity_type VARCHAR(20) NOT NULL,  -- CALL, EMAIL, MEETING, TASK
    subject VARCHAR(255) NOT NULL,
    description TEXT,
    
    -- Polymorphic "related to" - can link to account, opportunity, case
    related_to_type VARCHAR(20) NOT NULL,  -- ACCOUNT, OPPORTUNITY, CASE
    related_to_id UUID NOT NULL,
    
    -- Who is involved
    contact_id UUID REFERENCES contacts(contact_id),
    owner_id UUID REFERENCES users(user_id),
    
    -- Timing
    activity_date TIMESTAMPTZ NOT NULL,
    duration_minutes INT,
    is_completed BOOLEAN DEFAULT false,
    completed_at TIMESTAMPTZ,
    
    -- For tasks: priority and reminder
    priority VARCHAR(10),  -- HIGH, MEDIUM, LOW
    reminder_at TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Indexes for common CRM queries
CREATE INDEX idx_opps_account ON opportunities(account_id, stage);
CREATE INDEX idx_opps_owner ON opportunities(owner_id, is_closed, close_date);
CREATE INDEX idx_activities_related ON activities(related_to_type, related_to_id, activity_date DESC);
CREATE INDEX idx_activities_owner ON activities(owner_id, is_completed, activity_date);

Multi-Tenancy in CRM

Supply Chain Management: Orchestrating Complexity

Supply Chain Database Challenges:

Supply Chain Data Volumes (Large Manufacturer)
Data Type	Volume	Update Frequency	Query Pattern
SKUs (Products)	500,000+	Weekly	Point lookups, hierarchical
Inventory Records	10M+ (SKU x location)	Real-time	Aggregations, availability checks
Orders (PO/SO)	1M/month	Daily	Status tracking, document flow
Shipments	500K/month	Real-time	Tracking queries, route optimization
IoT Sensor Data	1B+ events/day	Streaming	Time-series analytics, alerting

Inventory Management:

Inventory is deceptively complex. A single product might exist in multiple states simultaneously:

On hand: Physically in warehouse
On order: Ordered from supplier, not yet received
In transit: Between locations
Reserved: Allocated to customer orders
Available: On hand minus reserved
Committed: Promised but not yet shipped

A simple "what's our inventory?" question requires careful consideration of which definition applies.

inventory_management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- Supply Chain Inventory Schema
 
-- Locations: Where inventory can be stored
CREATE TABLE locations (
    location_id VARCHAR(20) PRIMARY KEY,
    location_type VARCHAR(20) NOT NULL,  -- WAREHOUSE, STORE, DC, SUPPLIER
    name VARCHAR(100) NOT NULL,
    address JSONB,
    timezone VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT true
);
 
-- Inventory by SKU and location
CREATE TABLE inventory (
    sku VARCHAR(50) NOT NULL,
    location_id VARCHAR(20) NOT NULL REFERENCES locations(location_id),
    
    -- Quantity breakdown
    quantity_on_hand DECIMAL(15,3) NOT NULL DEFAULT 0,
    quantity_reserved DECIMAL(15,3) NOT NULL DEFAULT 0,
    quantity_in_transit DECIMAL(15,3) NOT NULL DEFAULT 0,
    
    -- Derived quantity (calculated on read or via trigger)
    quantity_available DECIMAL(15,3) GENERATED ALWAYS AS 
        (quantity_on_hand - quantity_reserved) STORED,
    
    -- Tracking
    unit_of_measure VARCHAR(10) NOT NULL,
    last_receipt_date DATE,
    last_issue_date DATE,
    last_count_date DATE,
    
    PRIMARY KEY (sku, location_id)
);
 
-- Inventory transactions (ledger style)
CREATE TABLE inventory_transactions (
    transaction_id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    location_id VARCHAR(20) NOT NULL,
    
    transaction_type VARCHAR(20) NOT NULL,  -- RECEIPT, ISSUE, TRANSFER, ADJUSTMENT, RESERVE
    quantity DECIMAL(15,3) NOT NULL,  -- Positive = IN, Negative = OUT
    unit_cost DECIMAL(15,4),
    
    -- Reference to source document
    reference_type VARCHAR(20),  -- PO, SO, TRANSFER_ORDER, COUNT
    reference_id VARCHAR(30),
    
    -- Audit
    transaction_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50) NOT NULL,
    reason_code VARCHAR(20),
    notes TEXT
);
 
-- Real-time inventory update via triggers
CREATE OR REPLACE FUNCTION update_inventory_on_transaction()
RETURNS TRIGGER AS $$
BEGIN
    -- Update inventory based on transaction type
    IF NEW.transaction_type IN ('RECEIPT', 'TRANSFER_IN') THEN
        UPDATE inventory 
        SET quantity_on_hand = quantity_on_hand + NEW.quantity,
            last_receipt_date = CURRENT_DATE
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
        
    ELSIF NEW.transaction_type IN ('ISSUE', 'TRANSFER_OUT') THEN
        UPDATE inventory 
        SET quantity_on_hand = quantity_on_hand - ABS(NEW.quantity),
            last_issue_date = CURRENT_DATE
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
        
    ELSIF NEW.transaction_type = 'RESERVE' THEN
        UPDATE inventory 
        SET quantity_reserved = quantity_reserved + NEW.quantity
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
        
    ELSIF NEW.transaction_type = 'UNRESERVE' THEN
        UPDATE inventory 
        SET quantity_reserved = quantity_reserved - ABS(NEW.quantity)
        WHERE sku = NEW.sku AND location_id = NEW.location_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_inventory_transaction
AFTER INSERT ON inventory_transactions
FOR EACH ROW EXECUTE FUNCTION update_inventory_on_transaction();
 
-- Available-to-Promise (ATP) calculation
CREATE OR REPLACE FUNCTION get_available_to_promise(
    p_sku VARCHAR(50),
    p_location_id VARCHAR(20),
    p_as_of_date DATE
) RETURNS DECIMAL(15,3) AS $$
DECLARE
    v_on_hand DECIMAL(15,3);
    v_reserved DECIMAL(15,3);
    v_incoming DECIMAL(15,3);
    v_committed DECIMAL(15,3);
BEGIN
    -- Current on-hand minus reservations
    SELECT quantity_on_hand, quantity_reserved INTO v_on_hand, v_reserved
    FROM inventory
    WHERE sku = p_sku AND location_id = p_location_id;
    
    -- Expected receipts by the date
    SELECT COALESCE(SUM(quantity_ordered - quantity_received), 0) INTO v_incoming
    FROM purchase_order_lines pol
    JOIN purchase_orders po ON pol.po_id = po.po_id
    WHERE pol.sku = p_sku 
      AND po.receiving_location = p_location_id
      AND po.expected_delivery <= p_as_of_date
      AND po.status NOT IN ('CANCELLED', 'RECEIVED');
    
    -- Committed outbound (orders not yet shipped)
    SELECT COALESCE(SUM(quantity_ordered - quantity_shipped), 0) INTO v_committed
    FROM sales_order_lines sol
    JOIN sales_orders so ON sol.so_id = so.so_id
    WHERE sol.sku = p_sku
      AND so.ship_from_location = p_location_id
      AND so.requested_ship_date <= p_as_of_date
      AND so.status NOT IN ('CANCELLED', 'SHIPPED');
    
    RETURN (v_on_hand - v_reserved) + v_incoming - v_committed;
END;
$$ LANGUAGE plpgsql;

Business Intelligence and Data Warehousing

OLTP (Operational)

•Optimized for transactions
•Row-oriented storage
•Normalized schema (3NF)
•Real-time, current data
•Many concurrent users
•Small queries, fast response

OLAP (Analytical)

•Optimized for analysis
•Columnar storage
•Denormalized (star/snowflake)
•Historical, periodic refresh
•Fewer concurrent users
•Complex queries, longer response

Dimensional Modeling:

Data warehouses use dimensional modeling, organizing data around business processes. The core concepts:

Fact Tables: Store measurable events (sales, orders, shipments). Each row is a transaction with numeric measures (amount, quantity, cost) and foreign keys to dimensions.

Dimension Tables: Describe the context of facts (who, what, when, where). Customer dimension, product dimension, time dimension, geography dimension.

Star Schema: Fact table in center, dimension tables radiating out like a star. Simple, fast queries.

Snowflake Schema: Dimensions are normalized into sub-dimensions. More storage efficient, more complex queries.

data_warehouse_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- Star Schema for Sales Analytics
 
-- Dimension: Time (pre-populated calendar)
CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,  -- YYYYMMDD format
    full_date DATE NOT NULL UNIQUE,
    day_of_week INT NOT NULL,
    day_name VARCHAR(10) NOT NULL,
    day_of_month INT NOT NULL,
    day_of_year INT NOT NULL,
    week_of_year INT NOT NULL,
    month_number INT NOT NULL,
    month_name VARCHAR(10) NOT NULL,
    quarter INT NOT NULL,
    year INT NOT NULL,
    is_weekend BOOLEAN NOT NULL,
    is_holiday BOOLEAN NOT NULL,
    fiscal_year INT NOT NULL,
    fiscal_quarter INT NOT NULL
);
 
-- Dimension: Product
CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,  -- Natural key from source
    product_name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    unit_cost DECIMAL(10,2),
    unit_price DECIMAL(10,2),
    -- SCD Type 2: Track historical changes
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);
 
-- Dimension: Customer
CREATE TABLE dim_customer (
    customer_key SERIAL PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_type VARCHAR(50),
    industry VARCHAR(100),
    region VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100),
    segment VARCHAR(50),  -- Enterprise, SMB, Consumer
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);
 
-- Dimension: Sales Rep
CREATE TABLE dim_sales_rep (
    sales_rep_key SERIAL PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL,
    rep_name VARCHAR(255) NOT NULL,
    team VARCHAR(100),
    region VARCHAR(100),
    hire_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);
 
-- Fact: Sales Transactions
CREATE TABLE fact_sales (
    sales_key BIGSERIAL PRIMARY KEY,
    
    -- Foreign keys to dimensions
    date_key INT NOT NULL REFERENCES dim_date(date_key),
    product_key INT NOT NULL REFERENCES dim_product(product_key),
    customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
    sales_rep_key INT REFERENCES dim_sales_rep(sales_rep_key),
    
    -- Degenerate dimensions (no separate table needed)
    order_number VARCHAR(30) NOT NULL,
    line_number INT NOT NULL,
    
    -- Measures
    quantity_sold INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    sales_amount DECIMAL(15,2) NOT NULL,  -- quantity * unit_price - discount
    cost_amount DECIMAL(15,2) NOT NULL,   -- quantity * unit_cost
    profit_amount DECIMAL(15,2) NOT NULL  -- sales_amount - cost_amount
);
 
-- Indexes optimized for analytical queries
CREATE INDEX idx_fact_sales_date ON fact_sales(date_key);
CREATE INDEX idx_fact_sales_product ON fact_sales(product_key);
CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_key);
 
-- Sample analytical query: Monthly sales by product category
SELECT 
    dd.year,
    dd.month_name,
    dp.category,
    SUM(fs.sales_amount) AS total_sales,
    SUM(fs.profit_amount) AS total_profit,
    SUM(fs.quantity_sold) AS total_units,
    AVG(fs.discount_amount / NULLIF(fs.sales_amount + fs.discount_amount, 0)) * 100 AS avg_discount_pct
FROM fact_sales fs
JOIN dim_date dd ON fs.date_key = dd.date_key
JOIN dim_product dp ON fs.product_key = dp.product_key
WHERE dd.year = 2024
GROUP BY dd.year, dd.month_number, dd.month_name, dp.category
ORDER BY dd.year, dd.month_number, total_sales DESC;

Enterprise Integration: Connecting the Systems

Converting Mermaid diagram...

Integration Patterns

•Point-to-Point — Direct connections between systems. Simple but becomes spaghetti at scale. N systems require N(N-1)/2 connections.
•Enterprise Service Bus (ESB) — Central hub routing messages between systems. Reduces connections to N. Can become bottleneck.
•Event-Driven Architecture — Systems publish events; interested parties subscribe. Loose coupling, eventual consistency.
•API-Led — Each system exposes APIs; integration platforms orchestrate. Modern, flexible, but requires API governance.
•Data Virtualization — Query across multiple sources without moving data. Real-time but can be slow for complex queries.

Master Data Management (MDM):

MDM databases store:

Master records: The authoritative entity data
Cross-references: Mappings from source system IDs to master IDs
Match rules: Algorithms for identifying duplicates
Merge history: Audit trail of entity consolidation
Data quality scores: Confidence in data accuracy

Integration Debt

Modern Enterprise Database Platforms

Enterprise Database Platform Evolution
Era	Approach	Examples	Key Characteristics
Traditional (1990s-2000s)	On-premise, monolithic	Oracle DB, DB2, SQL Server	High license cost, DBA-intensive, vertical scaling
Cloud IaaS (2010s)	Lift-and-shift to cloud VMs	RDS, Cloud SQL	Reduced hardware management, same database engines
Cloud-Native (2015+)	Purpose-built cloud databases	Aurora, Spanner, DynamoDB	Serverless options, auto-scaling, consumption pricing
Multi-Cloud (2020+)	Portable across clouds	CockroachDB, MongoDB Atlas	Vendor independence, global distribution

Modern Enterprise Trends

•Hybrid Transactional/Analytical Processing (HTAP) — Systems like TiDB and SingleStore handle both OLTP and OLAP workloads, reducing ETL complexity.
•Distributed SQL — Databases like CockroachDB and YugabyteDB provide PostgreSQL compatibility with global distribution.
•Data Mesh — Decentralized data ownership where each business domain manages its own data products.
•Lakehouse Architecture — Combines data lake storage economics with warehouse query capabilities (Databricks, Snowflake).
•Real-Time Analytics — Streaming databases like Apache Druid and ClickHouse enable sub-second analytics on live data.
•AI/ML Integration — Databases increasingly embed ML capabilities—vector search, in-database inference, feature stores.

The Rise of the Data Stack

Enterprise Database Security and Compliance

Key Regulatory Requirements for Enterprise Databases
Regulation	Scope	Key Database Requirements	Penalties
SOX	US public companies	Financial data integrity, audit trails, access controls	$5M+ fines, executives personally liable
GDPR	EU personal data	Consent tracking, right to deletion, data portability	Up to €20M or 4% of revenue
PCI-DSS	Payment card data	Encryption, access logging, network segmentation	Fines up to $500K/incident
SOC 2	Service providers	Security, availability, processing integrity	Loss of business trust
CCPA/CPRA	California consumers	Opt-out tracking, deletion capability, data inventory	$7,500 per intentional violation

enterprise_security.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- Enterprise Database Security Configuration
 
-- Role-based access control
CREATE ROLE finance_readonly;
CREATE ROLE finance_writer;
CREATE ROLE admin_full;
 
-- Grant minimal necessary permissions
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_readonly;
GRANT SELECT, INSERT, UPDATE ON finance.journal_entries TO finance_writer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA finance TO admin_full;
 
-- Row-level security for multi-business-unit access
CREATE POLICY business_unit_isolation ON transactions
    USING (business_unit_id = current_setting('app.business_unit')::INT);
 
-- Column-level encryption for sensitive data
-- Salary, SSN, bank account stored encrypted
CREATE TABLE employees_secure (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    -- Sensitive columns encrypted with column-level encryption
    ssn_encrypted BYTEA NOT NULL,
    salary_encrypted BYTEA NOT NULL,
    bank_account_encrypted BYTEA
);
 
-- Comprehensive audit trigger
CREATE TABLE audit_log (
    log_id BIGSERIAL PRIMARY KEY,
    event_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_name VARCHAR(100) NOT NULL DEFAULT CURRENT_USER,
    client_addr INET DEFAULT inet_client_addr(),
    application_name VARCHAR(100) DEFAULT current_setting('application_name'),
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE, SELECT
    table_schema VARCHAR(100) NOT NULL,
    table_name VARCHAR(100) NOT NULL,
    record_id TEXT,
    old_values JSONB,
    new_values JSONB,
    query_text TEXT  -- Careful: may contain sensitive data
);
 
-- Trigger function for audit logging
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (
        action, table_schema, table_name, record_id, old_values, new_values
    ) VALUES (
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME,
        CASE WHEN TG_OP = 'DELETE' THEN OLD.id::TEXT ELSE NEW.id::TEXT END,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD)::JSONB END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::JSONB END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
 
-- Apply audit trigger to sensitive tables
CREATE TRIGGER audit_financial_transactions
    AFTER INSERT OR UPDATE OR DELETE ON financial_transactions
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

Summary: DBMS in Enterprise Systems

Key Takeaways

•ERP systems integrate all business functions — Finance, HR, manufacturing, and sales share a unified database, enabling cross-functional visibility and consistency.
•CRM databases focus on relationships — Accounts, contacts, opportunities, and activities model the customer lifecycle. Multi-tenancy is a key architectural pattern.
•Supply chain requires real-time visibility — Inventory tracking across states (on-hand, reserved, in-transit) demands careful data modeling and transactional integrity.
•OLTP and OLAP serve different needs — Operational databases handle transactions; data warehouses with dimensional models enable analytics. Different schema designs optimize each.
•Enterprise integration is complex — Multiple systems must share data. ESB, event-driven, and API-led patterns each have tradeoffs.
•Modern platforms are cloud-native — HTAP, distributed SQL, and data mesh architectures are replacing monolithic systems.
•Security and compliance are mandatory — SOX, GDPR, PCI-DSS impose specific requirements for encryption, access control, and audit logging.

Module Conclusion:

Module Complete