[ILUG] Comments from DBA's requested.
johngay at eircom.net
Tue May 18 17:49:00 IST 2004
I've been trying to get my head around data normalization. The references I
MySQL Tutorial by Welling and Thomson
Managing & Using MySQL 2nd Edition from O'Reilly
Of the two, the O'Reilly book is slightly more helpful in explaining
I've followed the CD Catalog example but added my own requirements.
I want to track my CD Collection and store info about CD titles, song titles
Since each CD contains many songs, I split SONG into a seperate table from CD.
Since artists release many songs and CD's, I split ARTIST into a seperate
table. Now I've this relationship:
Each song has one and only one artist.
Each artist has one or more songs.
Each song is on one or more CD's.
Each CD contains many songs.
To resolve the many-to-many relationship between SONG and CD I created a
TRACK table to go between SONG and CD. This also serves to store track numbers
that I had overlooked. Now my tables look like this:
And the SQL to create these tables:
CREATE TABLE artist
( artistID int not null auto_increment primary key,
CREATE TABLE song
( songID int not null auto_increment primary key,
artistID int not null references artist(artistID)
CREATE TABLE cd
( cdID int not null auto_increment primary key,
CREATE TABLE track
( cdID int not null references cd(cdID),
trackNo int not null,
songID int not null references song(songID),
primary key(cdID, trackNo)
I added the directory attribute to song to hold the directory of the mp3/ogg
version of the song, if I had it.
So this is the database for my music collection. Populating it has been
interesting, though. I add the CD entry first, then the ARTIST entry, if one
does not yet exist. Then I need to use artistID when entering the SONG
entries. I then need both the songID and cdID when entering the TRACK
entries. I thought I might be able to use something like:
INSERT INTO song VALUES
(NULL, 'No Reply', NULL, SELECT artistID FROM artist WHERE
but this generates errors due to the SELECT query.This type of notation would
be even more useful when making the TRACK entries, as the songID's get higher
and when I have one song on multiple CD's.
I've also been playing with MySQLCC, which is not a million miles away from
Acess for ECDL purposes. I suppose, with time I'll learn plenty of tips and
tricks. I was just hoping for some comments, criticisms and suggestions from
any DBA's here.
More information about the ILUG