Describe the characteristics and purpose of fact and dimension tables and explain how you recognise that this data mart is based on a star schema design. Illustrate your answer using the data mart tables in Figure 32.3

What will be an ideal response?


The fact table has relatively few attributes but many records and constitutes the largest part of the
decision-support database. The fact table is made up of foreign keys and (usually) one or more metrics.
Each dimension table has relatively more attributes but few records compared with the fact table. The
more attributes contained by dimensions the wider the range of analysis supported. The primary key for
each dimension table is a single simple surrogate key, which is copied to the fact table as foreign key.
The purpose of the fact table is to contain any important metrics (streamDuration) and any additional
descriptors (customerRating) about the TV programmes/films streamed. The attributes of the
dimension tables allow a range of queries about this events being analysed. For example, the
dimensional tables allow analysis of the timing of streaming.
The purpose of the dimension tables is to contain attributes to allow streaming to be queried from
different perspectives.
The schema that describes this data mart is referred to as a ‘star schema’ because of the star shape that
results from surrounding the fact table in the centre with the dimension tables.
The schema is a star schema because the dimension tables are de-normalised and contain repeating data
such as that found in the dimFilm and dimTVProgramme dimension.
The purpose of the star schema is to reduce the number of joins between dimension tables and hence
speed up queries.

Computer Science & Information Technology

You might also like to view...

Explain how DFDs can be used in requirements modeling.

What will be an ideal response?

Computer Science & Information Technology

Express the ratio in simplest form. 9 ft : 12 yd

A. :
B. :
C. :
D. :
E. :

Computer Science & Information Technology

A computer system consists of hardware and ____.

A. an operating system B. software C. primary storage D. printer E. keyboard

Computer Science & Information Technology

A __________ disk is permanently mounted in the disk drive, such as the hard disk in a personal computer.

A. nonremovable B. movable-head C. double sided D. removable

Computer Science & Information Technology