Star, snowflake and fact constellation schemas can look like minor naming variants. Under time pressure, it is also easy to swap fact tables, factless fact tables, dimensions and cuboids. Start from a table's role and grain: those clues separate schema structure, measure behaviour and cube counts more reliably than a remembered option position. Answer each item before opening its explanation. Use the DBMS MCQ hub for wider practice, or browse the CS Fundamentals category for lessons across DBMS and related core-CS topics.
1. Warehouse modelling concepts to separate before the questions
A fact table stores business events or measurements at a declared grain. Dimension tables describe those facts. A star schema keeps dimensions comparatively denormalised, a snowflake normalises dimension hierarchies, and a fact constellation lets multiple fact tables share dimensions. A factless fact table records that an event or condition occurred without storing a numeric measure.
Take the grain as one product sold in one store on one date. FactSales(DateKey, ProductKey, StoreKey, UnitsSold) contains (20260719, P17, S2, 3). Its dimensions contain DimDate(20260719, 19-Jul-2026, July, 2026), DimProduct(P17, Keyboard, C3, Accessories) and DimStore(S2, Pune, Maharashtra). The fact says that three keyboards were sold; the dimensions explain when, what and where.
For a snowflake, use DimProduct(P17, Keyboard, C3) and move the category to DimCategory(C3, Accessories). For a constellation, add FactInventory(DateKey, ProductKey, StoreKey, ClosingStock) with (20260719, P17, S2, 17). Both facts share Date, Product and Store. The keys make the join paths explicit: DateKey, ProductKey and StoreKey identify dimension rows, while UnitsSold and ClosingStock remain measures attached to the chosen grain. If these roles are not yet comfortable, revise them through the DBMS concept hub.
2. Questions 1-2: fact tables and the snowflake normalisation clue
Question 1
Which of the following table contains the primary information in the data warehouse?
A. Primary table
B. Dimension table
C. Lookup table
D. Fact table
Correct answer: D. Fact table.
At the declared grain, FactSales(20260719, P17, S2, 3) records the business event and its measure. Date, Product and Store dimensions explain when, what and where. A lookup or dimension table supplies context, while “primary table” is not a dimensional-modelling role.
Question 2
Which of the following related to snowflake schema is true?
A. Each dimension is represented by a single dimensional table
B. Maintenance efforts are less
C. Dimension tables are normalised
D. It is not an extension of star schema
Correct answer: C. Dimension tables are normalised.
Snowflaking splits the product hierarchy. DimProduct(P17, Keyboard, C3, Accessories) becomes DimProduct(P17, Keyboard, C3) plus DimCategory(C3, Accessories). This normalisation makes C correct. A fits a single-table dimension, B is not a defining guarantee, and D is false because snowflake extends star schema.
3. Questions 3-4: fact constellation versus the traditional star
Question 3
Which data warehouse schema is designed such that multiple fact tables share common dimension tables?
A. Star Schema
B. Snowflake Schema
C. Hierarchical Schema
D. Relational Schema
E. Fact Constellation Schema
Correct answer: E. Fact Constellation Schema.
FactSales(..., UnitsSold 3) and FactInventory(..., ClosingStock 17) share DimDate, DimProduct and DimStore. A star centres on one fact table, while a snowflake changes the dimension hierarchy. A constellation supports several fact tables around common dimensions.
Question 4
Which schema model is widely used in traditional data warehouse design for organizing fact tables and dimension tables?
A. Star Schema
B. Mesh Schema
C. Hierarchical Schema
D. Graph Schema
E. None of the above
Correct answer: A. Star Schema.
Use the visual test. Central FactSales links directly to DimDate, DimProduct and DimStore, forming a star. Mesh and graph are distractors. Hierarchical is a general organisation model, not the named dimensional schema asked for.
4. Questions 5-6: factless facts and the shuffled fact-table repeat
Question 5
Fact-less fact table in a data warehouse contains
A. only measures
B. only dimensions
C. keys and measures
D. only surrogate keys
Correct answer: B. only dimensions.
A factless fact table holds foreign keys to dimensions and no numeric measure. Attendance(StudentKey, CourseKey, DateKey) can contain (S101, DBMS201, 20260719). It records that attendance occurred without Units or Amount, so B uses “dimensions” as shorthand for dimension keys without a measure. Option D is not the definition: surrogate keys may be used, but “factless” describes the absence of measures, not a rule about key implementation.
Question 6
Which of the following table contains the primary information in the data warehouse?
A. Dimension table
B. Fact table
C. Lookup table
D. Primary table
Correct answer: B. Fact table.
The fact-table question uses a different option order from Question 1. Answer from the role of FactSales, not the earlier letter D. The concept is unchanged, but the correct letter is now B, which checks positional memorisation.
5. Question 7: derive the maximum data-cube cell count
Question 7
A data cube \(𝐶\), has 𝑛 dimensions, and each dimensions has exactly \(𝑝\) distinct values in the base cuboid. Assume that there are no concept hierarchies associated with the dimensions. What is the maximum number of cells possible in the data cube, \(𝐶\)?
A. \(p^n\)
B. \(𝑝\)
C. \((2^n-1)(p+1)\)
D. \((p+1)^n\)
Correct answer: D. \((p+1)^n\).
Each dimension can use one of its p base values or the aggregate value ALL. That gives p + 1 independent choices for each of n dimensions, hence (p + 1)^n cells.
For n = 3 and p = 4, the base cuboid has 4^3 = 64 cells. The three two-dimensional roll-ups contribute 3 × 4^2 = 3 × 16 = 48. The three one-dimensional roll-ups contribute 3 × 4 = 12. The apex contributes 1. The total is 64 + 48 + 12 + 1 = 125 = (4 + 1)^3. Option A counts only the base cuboid.
6. Questions 8-10: descriptive modelling, schema trade-offs and OLAP design
Question 8
Explain the concept of Multi-dimensional Data Modeling and the different types of measures used in Fact Tables.
This is a descriptive question with no options.
A multidimensional model stores facts at a declared grain for analysis across dimensions. In FactSales, one product sold in one store on one date is analysed by Date, Product and Store, with UnitsSold as its measure.
Measures can be additive, semi-additive or non-additive. UnitsSold is additive: 3 + 2 = 5. Snapshot AccountBalance is semi-additive. Balances of 100 on 18 July and 120 on 19 July may be added across accounts on one date, not across time; 220 would double-count snapshots.
A rate is non-additive. If one batch has 2/10 = 20% defects and another has 3/30 = 10%, combine the underlying counts: (2 + 3)/(10 + 30) = 5/40 = 12.5%. Neither 20% + 10% = 30% nor the unweighted average 15% is correct. Declare the grain before deciding how a measure may be aggregated.
Question 9
Compare and Contrast Star Schema and Snowflake Schema in Data Warehouse Design.
This is a descriptive question with no options.
A star keeps category beside the product: DimProduct(P17, Keyboard, C3, Accessories). Grouping by category follows FactSales -> DimProduct. This short path repeats hierarchy data: if 100 products share C3, Accessories can appear in 100 rows.
A snowflake separates the hierarchy. DimProduct(P17, Keyboard, C3) points to DimCategory(C3, Accessories). Grouping follows FactSales -> DimProduct -> DimCategory, requiring an extra join. The category is stored once, reducing repetition and enforcing the hierarchy.
Neither design is universally superior. A star favours simpler queries. A snowflake favours less repeated hierarchy data and more explicit maintenance of that hierarchy.
Question 10
Which database design is commonly used in OLAP systems?
A. ER-based
B. Relational model
C. Star/snowflake schema
D. Hierarchical model
Correct answer: C. Star/snowflake schema.
OLAP users aggregate facts by dimensions and roll up hierarchies. Here, UnitsSold can be analysed by Date, Product category or Store location because star and snowflake schemas make those paths explicit. ER-based and relational are broader terms, while hierarchical is not the intended dimensional design.
7. Answer check, error log and the next DBMS step
The objective answer key is 1-D, 2-C, 3-E, 4-A, 5-B, 6-B, 7-D, 10-C. Questions 8 and 9 are descriptive, so self-check them against the required points: fact grain and measure types for Question 8, then structure, join path, redundancy and maintenance trade-offs for Question 9.
Suppose a learner misses Questions 2, 3 and 7, and gives an incomplete Question 9 answer. The objective score is 5/8. Those misses map to normalised dimensions, shared dimensions across multiple fact tables, and counting all cuboids rather than only p^n. The descriptive answer is incomplete because it omits the join path and redundancy trade-off.
Redraw the P17/C3 product split. Add FactInventory(..., ClosingStock 17) beside FactSales(..., UnitsSold 3). Recompute 64 + 48 + 12 + 1 = 125, and write both schema query paths before reattempting the four items.
For the next step, use the NTA-UGC-NET Paper 2 course when you want a topic-matched exam route. Choose CS Fundamentals for Placements for a broader DBMS and core-CS route. The short version is simple: identify the grain, separate facts from dimensions, and derive cube counts instead of memorising isolated letters.
