Databases Reference
In-Depth Information
SP TI
┌─────┬─────┬──────┬─────┐ ┌──────┬──────┬────┐
│ SNO │ PNO │ TINO │ QTY │ │ TINO │ FROM │ TO │
├═════┼═════┼══════┼─────┤ ├══════┼──────┼────┤
│ S1 │ P1 │ T13 │ 300 │ │ TI1 │ t0 │ t1 │
│ S1 │ P1 │ T15 │ 100 │ │ TI2 │ t2 │ t3 │
│ S1 │ P2 │ T11 │ 200 │ │ TI3 │ t4 │ t5 │
│ S1 │ P3 │ T12 │ 400 │ │ TI4 │ t6 │ t7 │
│ S1 │ P4 │ T11 │ 200 │ │ TI5 │ t8 │ t9 │
│ S1 │ P5 │ T15 │ 100 │ └──────┴──────┴────┘
│ S1 │ P6 │ T14 │ 100 │
│ S2 │ P1 │ T13 │ 300 │
│ S2 │ P2 │ T14 │ 400 │
│ S3 │ P2 │ T11 │ 200 │
│ S3 │ P2 │ T13 │ 200 │
│ S4 │ P2 │ T11 │ 200 │
│ S4 │ P4 │ T13 │ 200 │
│ S4 │ P5 │ T12 │ 400 │
│ S4 │ P5 │ T11 │ 400 │
└─────┴─────┴──┴───┴─────┘
Fig. 8.1: Sample fact table (SP) and dimension table (TI)
.
┌─────┬───────┬────────┬──────┐ ┌──────┬──────┬────┐
S │ SNO │ SNAME │ STATUS │ CITY │ T1 │ TINO │ FROM │ TO │
└══▲══┴───────┴────────┴──────┘ └═══▲══┴──────┴────┘
└───────────────┐ ┌─────────┘
┌──┼──┬─────┬───┼──┬─────┐
SP │ SNO │ PNO │ TINO │ QTY │
└═════┴══╪══┴══════┴─────┘
┌───────────┘
┌──▼──┬───────┬───────┬────────┬──────┐
P │ PNO │ PNAME │ COLOR │ WEIGHT │ CITY │
└═════┴───────┴───────┴────────┴──────┘
Fig. 8.2: Star schema for suppliers and parts (with time intervals)
.
Now, you might be wondering what the difference is between a star schema and a conventional relational
design. In fact, a star schema for a simple example like the one under discussion is likely to be identical to a good
relational design. In more complex situations, however, the dimension tables are often less than fully normalized
(the objective here apparently being to avoid joins).
12
What's more, other relational design recommendations are
often violated, too (see the bullet list earlier in this section).
Detailed discussion of star schemas and related matters is beyond the scope of this topic; you can find a
slightly more extended discussion in my topic
An Introduction to Database Systems
(8th edition, Addison-Wesley,
2004).
12
In this connection, consider this advice from a topic on data warehouses: “[Resist] normalization ... Efforts to normalize any of the tables in a
dimensional database solely in order to save disk space [
sic!
] are a waste of time ... The dimension tables must not be normalized ... Normalized
dimension tables destroy the ability to browse” (from Ralph Kimball,
The Data Warehouse Toolkit
, John Wiley & Sons, 1996).
