[ILUG] Comments from DBA's requested.

[John Gay]

I've been trying to get my head around data normalization. The references I 
have are:

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 
normalization. 

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 
and artists. 

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:

ARTIST---<SONG>----<CD

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:

ARTIST---<SONG---<TRACK>---CD

And the SQL to create these tables:

CREATE TABLE artist
( artistID int not null auto_increment primary key,
  name varchar(50),
) type=InnoDB;

CREATE TABLE song
( songID int not null auto_increment primary key,
  name varchar(50),
  directory varchar(128),
  artistID int not null references artist(artistID)
) type=InnoDB;

CREATE TABLE cd
( cdID int not null auto_increment primary key,
  title varchar(50),
) type=InnoDB;

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)
) type=InnoDB;

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 
	name='The Beatles');

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.

Cheers,

	John Gay