---
title: "OLAP vs OLTP; schema modeling; star DMQL"
subject: "Data Mining"
chapter: 2
key: "semester_7/data_mining:olap-oltp-schema-star-dmql"
source: https://www.bctengineer.com/q/semester_7/data_mining/olap-oltp-schema-star-dmql
site: BCT Engineering
---

# OLAP vs OLTP; schema modeling; star DMQL

Data Mining · Chapter 2: Data Pre-processing

- Asked in: year not given, 3+4+3 marks
- Video: [Lec - 5 : What is Star Schema | Schema for Multi Dimensional Data Model | Data Warehouse](https://www.youtube.com/watch?v=ot-2GXJXYMM) — Gate Smashers
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_7/data_mining/olap-oltp-schema-star-dmql
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-2

## Question

Differentiate between OLAP and OLTP. Explain the conceptual modeling of Data Warehouse Schemas. How do you define Star Schema using DMQL?

## Short answer

OLTP is for transactions; OLAP for analysis. Warehouse conceptual model is multidimensional with star, snowflake and fact constellation schemas. In DMQL, a star schema is defined by define cube with measures and define dimension for each dimension, e.g. define cube sales_star [time, item, branch, location] with dollars_sold = sum(sales_in_dollars).

## Answer

#### OLAP vs OLTP
| Point | OLTP | OLAP |
|---|---|---|
| Purpose | Day-to-day transactions | Analysis, decision support |
| Users | Clerks, clients, DBAs | Managers, analysts, executives |
| Data | Current, detailed, flat relational | Historical, summarised, multidimensional |
| Database design | ER model, normalised, application-oriented | Star/snowflake, subject-oriented |
| Access | Read/write, short transactions | Mostly read, complex queries |
| Unit of work | Short, simple transaction | Complex query with aggregates |
| Records per access | Tens | Millions |
| Number of users | Thousands | Hundreds |
| DB size | MB to GB | GB to TB |
| Metric | Transaction throughput | Query response time |

#### Conceptual modelling of data warehouse schemas
The conceptual model of a warehouse is **multidimensional**, made of a fact table and dimension tables. There are three common schemas:
1. **Star schema**: one central fact table linked to one denormalised table per dimension. Simple and fast.
2. **Snowflake schema**: a star whose dimension tables are normalised into more tables to reduce redundancy; more joins.
3. **Fact constellation (galaxy)**: multiple fact tables share dimension tables; models several related business processes.

```text
 Star:                       Snowflake:
 Time   Item                 Item -> Supplier
   \   /                       \
   SALES -- Branch            SALES -- Location -> City
```

#### Defining a star schema in DMQL
DMQL defines a cube and its dimensions. Example (sales):
```text
define cube sales_star [time, item, branch, location]:
    dollars_sold = sum(sales_in_dollars),
    units_sold   = count(*)

define dimension time as
    (time_key, day, day_of_week, month, quarter, year)
define dimension item as
    (item_key, item_name, brand, type, supplier_type)
define dimension branch as
    (branch_key, branch_name, branch_type)
define dimension location as
    (location_key, street, city, province_or_state, country)
```
The measures become the fact table columns and each dimension a table joined through its key.

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. Which database design is typical of OLTP rather than OLAP?
   A. Normalised ER model, application-oriented
   B. Star or snowflake schema, subject-oriented
   C. Multidimensional cube of summarised data
   D. Fact constellation
   Answer: A. OLTP uses normalised ER design; OLAP uses star/snowflake subject-oriented design. (Key point: OLTP vs OLAP design)

2. What is a fact constellation (galaxy) schema?
   A. One fact table with normalised dimensions
   B. Multiple fact tables sharing dimension tables
   C. One fact table with denormalised dimensions
   D. A schema with no dimension tables
   Answer: B. A galaxy schema models several related business processes by sharing dimensions among fact tables. (Key point: Fact constellation)

3. In a star schema, how are the dimension tables arranged?
   A. Normalised into many sub-tables per dimension
   B. Stored inside the fact table
   C. One denormalised table per dimension linked to the central fact table
   D. Linked to each other but not to the fact table
   Answer: C. A star has a central fact table linked to one denormalised table per dimension, which is simple and fast. (Key point: Star schema structure)

4. In DMQL, how is a star schema defined?
   A. With 'mine associations' and support thresholds
   B. With CREATE TABLE only, DMQL has no schema commands
   C. With 'use database' and 'from' clauses only
   D. With 'define cube' listing dimensions and measures, plus 'define dimension' for each dimension
   Answer: D. define cube names the cube, dimensions and measures; define dimension lists each dimension's attributes. (Key point: DMQL cube definition)

## Related in Data Mining

- [OLAP operations and star schema in DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-operations-star-dmql) — Data Mining, chapter 2
- [OLAP vs OLTP; schema, fact, dimension](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-fact-dimension) — Data Mining, chapter 2
- [OLAP vs OLTP; data cube, operations](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-data-cube-fact-dimension) — Data Mining, chapter 2
- [Data warehouse schemas](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Adw-schema-types) — Data Mining, chapter 1
- [DMQL; transactional vs warehouse database](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Admql-transactional-vs-dw) — Data Mining, chapter 2
- [OLAP vs OLTP; OLAP operations, fact, dimension](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-operations-fact-dimension) — Data Mining, chapter 2

## Related in other subjects

- [Differentiate OLTP and OLAP](https://www.bctengineer.com/question.md?key=semester_8%2Finformation_system%3Aoltp-vs-olap) — Information System, chapter 4, asked 5×