Datasheet

If we have another CD to store, should another track table be added to the system, or should the tracks
be added to the existing CD_tracks table? If we add a track to the CD_tracks table, how do we keep track
of the fact that some of the tracks relate to specific CDs in the CD table?
We need to add a foreign key to the CD_tracks table; it will relate to the primary key in the CD table.
Here’s what the new CD_tracks table looks like:
create table CD_tracks (
ID int not null primary key auto_increment,
cd_id int,
track varchar(256)
);
Using this schema, the Rush CD’s tracks appear as follows:
+----+------+----------------------------+
| ID | cd_id| Track |
+----+------+----------------------------+
| 1 | 1 | Distant Early Warning |
+----+------+----------------------------+
| 2 | 2 | Afterimage |
+----+------+----------------------------+
| 3 | 3 | Red Sector A |
+----+------+----------------------------+
With the addition of the cd_id column, we can relate the two tables needed to fully map the CD object to
permanent storage. The addition of the new CD_tracks table expands our UML diagram, as shown in
Figure 1.2.
Figure 1.2
<<class model>>
CD
CD
-title : String
-artist : String
+getTitle : String
+getArtist : String
+setTitle()
+setArtist()
<<Physical Data Model>>
ID : int
title : varchar(256)
artist : varchar(256)
cd_tracks
ID : int
cd_id : int
name : varchar(256)
length : int
6
Chapter 1
03_576771_c01.qxd 9/1/04 12:09 PM Page 6