Database Reference
In-Depth Information
allows one song to be included on more than one CD. For example,
The Beatles' song “Let It Be” is on the
White Album
CD and on
The
Beatles' Greatest Hits
CD.
GENRE
. Music CDs can be categorized into genres or types of
music. Genres are hierarchical in nature, where one genre can be a
subset of another genre.
STUDIOTIME
. When a musician (artist) comes into the studio to
record a song, the studio charges the artist for time spent in the stu-
dio. This table contains information needed for billing the artist. An
artist may have many studio sessions, and each session is a row in the
STUDIOTIME table.
GUESTAPPEARANCE
. A musician seldom records a song alone.
Even though the musician owns the song, he or she often asks other
musicians to collaborate on the recording. This table keeps track of
which musician (called the guest artist) played on what other musi-
cian's songs.
INSTRUMENTATION
. When a guest artist plays on a song, he or
she plays one or more instruments. This table keeps track of which
instrument each guest artist played on each song. For example, Jim
played drums and sang backup vocals (the voice is considered an
“instrument” in these tables) on Amy's song. Later Amy played guitar
on Jim's song.
INSTRUMENT
. The instrument table assigns an identifying num-
ber to each instrument. The number is used in the INSTRUMEN-
TATION table. So, instruments are actually stored in two different
ways in the schema: (1) as a collection in the ARTIST table and (2)
as individual rows in the INSTRUMENT table. This is done to
illustrate the variety of methods you can use when designing a data-
base system.
1.7.1
The MUSIC Schema Sales Data Warehouse
The OLTP schema in Figure 1.22 is expanded in Figure 1.23 to create a
data warehouse
5
structure for CD sales. In general, data warehouse tables
can be broken into dimension and fact tables. Fact tables contain facts
such as sales record history, and dimensions describe the facts such as the
countries in which sales took place. Roughly, dimensions are equivalent to
OLTP static tables such as a table of customers. Facts are roughly equiva-
lent to OLTP transactional tables such as sales transactions. A data ware-
