Relational DBMS Scope: Enterprise data modeling classifies organizational data into three primary structural tiers:

• Structured Data: Data adhering to rigid tabular schemas (rows and columns) with explicit data types and length constraints.
• Semi-Structured Data: Data possessing self-describing structural tags without a fixed tabular schema (e.g., JSON documents, XML payloads, YAML configs).
• Unstructured Data: Data devoid of predefined conceptual structure (e.g., raw binary audio, video, free-text documents, image files).

Relational Database Management Systems (RDBMS) operate strictly within the domain of structured data, where every real-world entity is mapped into two-dimensional tables consisting of rows ($\text{tuples }t\in T$) and columns ($\text{attributes }a\in A$).

Formalization of ER Constructs: Developed for traditional RDBMS transaction processing between the early 1980s and late 1990s, Entity-Relationship (ER) modeling abstracts real-world business environments into three fundamental constructs:

1. Entities ($E$): Independent business objects or concepts represented physically as database tables (e.g., Employee, Student, Department, Account).
2. Attributes ($A$): Descriptive properties or characteristics belonging to an entity, represented physically as table columns (e.g., Employee_ID, First_Name, Date_Of_Birth).
3. Relationships ($R$): Structural associations or logical links defined between attributes across tables or within the same table.

Formalization of Keys and Relationship Constraints:

ER models enforce referential integrity and structural uniqueness using explicit key constructs:

• Primary Key (PK): An attribute or minimal set of attributes that uniquely identifies every distinct tuple in an entity table (e.g., Employee_ID).
• Foreign Key (FK): An attribute in a child entity table that references the Primary Key of a parent entity table, enforcing referential integrity.

Cardinality Ratio: Defines the numerical mapping between instances of related entities:

• One-to-One ($1:1$): Each row in Entity $A$ maps to at most one row in Entity $B$.
• One-to-Many ($1:N$): A single row in Entity $A$ maps to multiple rows in Entity $B$.
• Many-to-Many ($M:N$): Multiple rows in Entity $A$ map to multiple rows in Entity $B$ (requiring a junction/bridge table in physical implementation).

Multiplicity and Participation Bounds: Defined using minimum and maximum constraint notation $[\text{Min}\dots \text{Max}]$:

• Mandatory Participation: The minimum bound is 1 ($1\dots N$), requiring every entity instance to participate in the relationship (e.g., every Department entity must have at least 1 assigned Manager).
• Optional Participation: The minimum bound is 0 ($0\dots N$), allowing an entity instance to exist without linking to the related entity (e.g., an Employee can supervise $0\dots N$ Departments).

Degrees of Relationship:

• Unary Relationship: Links attributes within the exact same entity table (e.g., a Staff table where a Supervisor_ID column references the Staff_ID Primary Key of another row in the same table).
• Binary Relationship: Links attributes across 2 distinct entity tables (e.g., linking Finance_Records to HR_Records via Employee_ID).
• Ternary / N-ary Relationship: Links attributes across 3 or more distinct entity tables simultaneously (e.g., linking Student, Course, Faculty, and Classroom tables in a single scheduling relationship).

Inmon Top-Down Architecture Pipeline:

1. Operational Data Sources: Raw transactional data is extracted from heterogeneous OLTP source databases.
2. Enterprise Data Warehouse (EDW): Data is transformed and loaded into a single centralized Enterprise Data Warehouse (EDW) modeled strictly in normalized Third Normal Form (3NF). This EDW acts as the single source of truth for the entire corporation.
3. Downstream Data Marts: Departmental Data Marts (such as Finance, HR, Marketing, or Sales) are subsequently constructed by extracting, filtering, and summarizing data downstream from the central 3NF EDW.

Kimball Bottom-Up Architectural Pipeline:

1. Business Process Focus: The enterprise identifies specific high-priority business processes (e.g., Retail Sales, Order Fulfillment, Inventory Control) and constructs dimensional Data Marts directly for those processes first.
2. Conformed Dimensions: Multiple data marts are integrated into a single cohesive enterprise data warehouse by enforcing Conformed Dimensions — standardized, shared dimension tables (such as a single master Date_Dimension or Customer_Dimension) shared identically across all data marts.
3. Incremental Warehouse Union: The centralized enterprise warehouse emerges organically through the step-by-step physical union of these conformed dimensional data marts.

Star Schema Mechanics:

The fundamental structural pattern in dimensional modeling is the Star Schema. It is named for its visual appearance: a single central Fact Table connected directly to multiple radial Dimension Tables.

Key structural properties of a Star Schema:

• Direct Radial Connections: The central Fact Table connects directly to every surrounding Dimension Table via single-column Foreign Key to Primary Key joins.
• Star Join Execution: Every analytical query executes a single, highly optimized Star Join connecting the central fact table to the required dimension tables.
• Predictable Query Paths: Query JOIN paths are symmetrical and uniform. The SQL engine always filters dimension attributes first in a WHERE clause, retrieves matching Surrogate Keys, and scans the central Fact Table using integer index lookups.

Symbol Registry — Star Schema Formalization:

Geometry of Fact Tables: Fact tables exhibit a "Narrow and Deep" geometry — containing relatively few columns (mostly foreign key integers and numeric measures, typically 10–20 columns wide) but vast numbers of rows (millions to billions of tuples).

Fact Measures & Additivity Classification:

Fact measures are classified strictly by their mathematical additivity across dimension boundaries:

1. Fully Additive Facts: Measures that can be validly summed across all dimensions (e.g., Quantity_Sold, Sales_Amount, Cost_Price).
2. Semi-Additive Facts: Measures that can be validly summed across some dimensions (such as Product or Store), but cannot be summed across the Time dimension (e.g., Account_Balance, Inventory_On_Hand, Headcount). Summing daily account balances over 30 days yields a meaningless mathematical total.
3. Non-Additive Facts: Measures that cannot be validly summed across any dimension (e.g., unit prices, profit margin percentages, temperature, tax rates).
4. Factless Fact Tables: Fact tables containing zero numeric measure columns, consisting strictly of foreign keys. They record event occurrences (e.g., tracking student lecture attendance) or coverage maps (e.g., tracking products on promotion that did not sell).

Geometry of Dimension Tables: Dimension tables exhibit a "Wide and Shallow" geometry — containing large numbers of descriptive text columns (often 50 to 100+ attributes per row) but relatively small numbers of rows compared to fact tables.

Denormalized Structure: Dimension tables are deliberately kept unnormalized into a single flat table, ignoring traditional 3NF redundancy rules to eliminate multi-table JOIN operations during query filtering.

Surrogate Keys (${\text{SK}}_d$): Dimension tables use synthetic, auto-incrementing integer keys (${\text{SK}}_d\in {\mathbb{Z}}^{+}$, e.g., 1, 2, 3...) as primary keys rather than operational natural keys (such as SSNs, product SKUs, or credit card numbers).

Benefits of Surrogate Keys:

1. Performance Optimization: Joining tables on single 4-byte integers is drastically faster than joining on long string natural keys.
2. Slowly Changing Dimensions (SCD): Enables tracking historical attribute changes over time by assigning a new surrogate key when a dimension attribute changes (e.g., customer moving to a new state).
3. Operational Protection: Shields the warehouse schema from changes or recoding in source operational systems.
4. Handling Missing / Unknown Data: Allows assigning special reserved surrogate keys (e.g., -1 for "Not Applicable", -2 for "Unknown") to eliminate NULL foreign keys in fact tables.

Step 2 — Declaring the Grain:

Declaring the Grain is the single most critical architectural decision in dimensional design. The grain defines exactly what a single physical row in the fact table represents.

Iron Rule of Grain Declaration:

1. Architects must declare the finest atomic grain possible (e.g., "One row per individual line item on a retail point-of-sale receipt").
2. Atomic grain provides maximum analytical flexibility, allowing business users to drill down to the lowest level of detail.
3. Every fact measure and dimension foreign key added to the fact table must strictly conform to the declared grain. Mixing grains in a single fact table (such as placing daily transaction rows alongside monthly summary rows) corrupts query aggregations and produces invalid results.

Degenerate Dimensions (DD):

A Degenerate Dimension (DD) is a dimension key or transaction identifier (such as POS_Receipt_Number, Invoice_Number, or Bill_Of_Lading_ID) that resides directly inside the Fact Table without joining to a separate dimension table.

Degenerate dimensions occur when an operational transaction identifier has no remaining descriptive attributes after extracting Date, Product, Store, Customer, and Promotion context.

Roles of Degenerate Dimensions:

1. Forms part of the composite primary key of the fact table.
2. Groups individual line items belonging to the same parent transaction.
3. Provides direct operational traceability back to the source OLTP transaction system.

1. Star Schema (Standard Recommendation):

• Structure: Completely denormalized, flat dimension tables surrounding a central fact table.
• Advantages: Minimum query JOIN paths (1 join per dimension), maximum query performance, easy for business users to query and understand.
• Disadvantages: Redundant text string storage in dimension tables.

2. Snowflake Schema (Discouraged):

• Structure: Dimension tables are partially or fully normalized into Third Normal Form (3NF) hierarchies (e.g., splitting a Product dimension into separate Product, Subcategory, Category, and Brand normalized tables).
• Advantages: Eliminates redundant attribute text; reduces storage space in extremely sparse dimensions.
• Disadvantages: Increases schema complexity; requires multi-level JOIN paths (joining Fact $\to$ Product $\to$ Subcategory $\to$ Category $\to$ Brand), severely impairing OLAP query execution speed and confusing end users.

3. Starflake Hybrid Schema:

• Structure: A hybrid architecture where core high-frequency dimensions remain flat stars, while specific large, highly sparse, or rapidly changing sub-dimensions are selectively snowflaked.

Date Dimension Engineering Rules:

• Never use SQL date functions (e.g., YEAR(), MONTH(), DAYNAME(), DATEPART()) on raw timestamp columns during query execution! Doing so disables database indexes and forces full table scans over billions of rows.
• Pre-compute Date Attributes: Construct a dedicated, pre-populated lookup table containing pre-calculated attributes for every calendar date over a 20-year span (typically ~7,300 rows).

Date Dimension Attributes:

• Primary Key: Date_Key (Surrogate integer in YYYYMMDD format, e.g., 20260722).
• Attributes: Full_Date_Description ("July 22, 2026"), Day_Of_Week ("Wednesday"), Calendar_Month ("July"), Calendar_Quarter ("Q3"), Calendar_Year (2026), Fiscal_Month, Fiscal_Quarter, Fiscal_Year, Holiday_Indicator ("Holiday" vs "Non-Holiday"), Weekend_Indicator ("Weekend" vs "Weekday"), and Selling_Season ("Back to School", "Christmas", "Super Bowl").

Promotional Mechanisms & Measured Analytics:

• Mechanisms Tracked: Price Reductions (temporary markdowns), Coupons (digital/paper codes), Media Advertisements (TV/Radio/Web banners), and Store Display Placement (end-cap displays, front-aisle banners).

Analytical Effects Measured:

1. Lift: The percentage increase in sales volume achieved while a product is on promotion compared to its baseline non-promotional sales volume.
2. Cannibalization: The negative phenomenon where sales of a non-promoted competing brand drop because customers switch to a brand currently on promotion.
3. Time-Shifting: The phenomenon where customers delay purchases prior to a known sale or stockpile items during a sale, causing post-promotion sales dips.

The Three Inventory Fact Models:

1. Transaction-Based Inventory Fact Table:
   • Logs a row for every discrete inventory movement event (e.g., stock receipt, warehouse transfer, shipment, return).
   • Grain: One row per inventory movement transaction.
   • Characteristics: Highly detailed, fully additive facts, but generates extremely high row volume.
2. Periodic Snapshot Inventory Fact Table:
   • Captures total inventory stock balances at fixed regular time intervals (e.g., daily or weekly closing balances).
   • Grain: One row per product per store per time period.
   • Characteristics: Uses semi-additive balance facts (Quantity_On_Hand, Total_Dollar_Value). Quantities can be summed across products or stores, but cannot be summed across the Time dimension.
3. Accumulating Snapshot Inventory Fact Table:
   • Tracks a single business item as it progresses through defined pipeline milestones (e.g., an order moving through Order_Placed $\to$ Payment_Approved $\to$ Warehouse_Picked $\to$ Shipped $\to$ Delivered).
   • Grain: One row per discrete order/pipeline lifecycle.
   • Characteristics: Contains multiple date foreign keys corresponding to each milestone step; the physical row is updated iteratively as milestones complete.

Analytical Database Indexing Strategies:

• Integer Surrogate Key B-Tree Indexes: B-Tree indexes constructed on 4-byte integer foreign keys allow rapid JOIN execution between fact and dimension tables.
• Bitmap Indexes: Ideal for low-cardinality dimension attributes (columns containing few distinct values, such as Gender, State, Marital_Status, Holiday_Indicator, or Weekend_Indicator). Bitmap indexes store bit arrays for each attribute value, executing boolean AND / OR operations directly in memory at high speeds.
• Join Indexes: Pre-computed index structures that maintain physical pointer paths between fact table rows and dimension attributes, bypassing dynamic JOIN evaluation during analytical execution.

2. Core Formulas & Computational Rules to Master:

• Fact Table Storage Sizing Calculation: $$N_{\text{rows}}=S\times P\times T=\text{Stores}\times \frac{\text{Products}}{\text{Store}\cdot \text{Day}}\times \text{Days}$$ $$B_{\text{row}}=(N_{\text{dimensions}}\times 4\text{ bytes})+(N_{\text{facts}}\times 8\text{ bytes})$$ $$S_{\text{total (GiB)}}=\frac{N_{\text{rows}}\times B_{\text{row}}}{1024^3}$$
• Derived Non-Additive Ratios: Store raw additive numerators and denominators as separate facts in the fact table, calculating ratios dynamically during query execution via: $$\text{Gross Margin \%}=\frac{\sum \text{Gross\_Profit\_Amount}}{\sum \text{Sales\_Revenue\_Amount}}$$