Database Reference
In-Depth Information
ing. Mutable joins are extremely common in modern-day object applica-
tions written in languages such as Java. Object applications and relational
databases require a complex mapping process between the two different
object and relational approaches. The reality is that object and relational
methodologies usually overlap. The result is mutable joins. At some point
mutable joins become complex joins. Complex joins can have 10 or even
more tables. Complex joins are usually indicative of other problems such as
a lack of Denormalization or use of a purely top-down design.
Following is a simple example of a multiple-table join using four tables.
Start by finding row counts. The only extra row count we have to find at
this stage is for the CDTRACK table.
SELECT COUNT(*) FROM CDTRACK;
MUSICCD has 13 rows.
CDTRACK has 125 rows.
ARTIST has 15 rows.
SONG has 118 rows.
SONG_GUESTARTIST has 5 rows.
Let's begin with an Oracle format query, the result of which is shown in
Figure 10.25. This query returns 125 rows, equivalent to the largest table,
validating this query as not being a Cartesian product.
COLUMN CD FORMAT A24 HEADING "CD"
COLUMN TRACK FORMAT 90 HEADING "Track"
COLUMN SONG FORMAT A40 HEADING "Song"
COLUMN NAME FORMAT A32 HEADING "Artist"
SELECT M.TITLE AS CD, C.TRACK_SEQ_NO AS TRACK
, S.TITLE AS SONG, A.NAME AS ARTIST
FROM ARTIST A, SONG S, CDTRACK C, MUSICCD M
WHERE A.ARTIST_ID = S.ARTIST_ID
AND S.SONG_ID = C.SONG_ID
AND C.MUSICCD_ID = M.MUSICCD_ID
ORDER BY 1,2,3,4;
