# Diagram 1
+-----+-----------------+--------+-----------+------+
| id  | title           | artist | album     | year |
+-----+-----------------+--------+-----------+------+
| 494 | Isobel          | Bjork  | Telegram  | 1996 |
| 495 | Cover Me        | Bjork  | Telegram  | 1996 |
| 498 | Crying          | Bjork  | Debut     | 1993 |
| 499 | One Day         | Bjork  | Debut     | 1993 |
+-----+-----------------+--------+-----------+------+

##</code><code>##

# Diagram 2
+-----+-----------------+--------+-----------+------+
| id  | titles          | artist | album     | year |
+-----+-----------------+--------+-----------+------+
| 494 | Isobel,Cover Me | Bjork  | Telegram  | 1996 |
| 495 | Crying,One Day  | Bjork  | Debut     | 1993 |
+-----+-----------------+--------+-----------+------+

##</code><code>##

CREATE TABLE songs (
  id int(11) NOT NULL auto_increment,
  title varchar(65) default NULL,
  artist varchar(65) default NULL,
  album varchar(65) default NULL,
  year varchar(4) default NULL,
  PRIMARY KEY  (id)
);

##</code><code>##

CREATE TABLE song (
  id int(11) NOT NULL auto_increment,
  title varchar(65) default NULL,
  PRIMARY KEY  (id)
);

##</code><code>##

CREATE TABLE artist (
  id int(11) NOT NULL auto_increment,
  name varchar(65) default NULL,
  PRIMARY KEY  (id)
);

##</code><code>##

CREATE TABLE album (
  id int(11) NOT NULL auto_increment,
  title varchar(65) default NULL,
  year varchar(4) default NULL,
  artist_id int(11) NOT NULL,
  PRIMARY KEY  (id),
  FOREIGN KEY index_artist_id(artist_id) 
    REFERENCES artist(id)
);

##</code><code>##

CREATE TABLE song (
  id int(11) NOT NULL auto_increment,
  title varchar(65) default NULL,
  album_id int(11) NOT NULL,
  PRIMARY KEY  (id),
  FOREIGN KEY index_album_id(album_id) 
    REFERENCES album(id)
);

##</code><code>##

mysql: select * from mp3.song limit 3;
+----+--------------------------------+----------+
| id | title                          | album_id |
+----+--------------------------------+----------+
|  1 | Until the End of the World     |        1 |
|  2 | So Cruel                       |        1 |
|  3 | Tryin to Throw Your Arms Aroun |        1 |
+----+--------------------------------+----------+

mysql: select * from mp3.album limit 3;
+----+--------------+------+-----------+
| id | title        | year | artist_id |
+----+--------------+------+-----------+
|  1 | Achtung Baby | 1991 |         1 |
|  2 | War          | 1983 |         1 |
|  3 | October      | 1981 |         1 |
+----+--------------+------+-----------+

mysql: select * from mp3.artist limit 3;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | U2          |
|  2 | Bjork       |
|  3 | Pink Floyd  |
+----+-------------+

##</code><code>##

mysql: select artist.name from artist 
       inner join album on artist.id=album.artist_id
       inner join song on album.id=song.album_id
       where song.title='So Cruel'; 
+------+
| name |
+------+
| U2   |
+------+

##</code><code>##

$VAR1 = {
   'Bjork' => {
      'Debut' => {
         'list' => [
            'Crying',
            'One Day',
            'Come To Me',
            'Violently Happy'
         ],
         'year' => '1993'
      },
      'Telegram' => {
         'list' => [
            'Isobel',
            'Cover Me',
            'Army Of Me',
            'Possibly Maybe'
         ],
         'year' => '1996'
      }
   }
   'U2' => { 
      #etc...
   }
};

##</code><code>##

use strict;

use DBI;

# connect
my $dbh = DBI->connect(
   qw(DBI:mysql:mp3:host user pass),
   { RaiseError => 1 }
);

# get existing data as one big ole 2-d array
my $songs = $dbh->selectall_arrayref("
   select artist,album,title,year
   from songs
");

# munge data into new data structure
my %hash;
foreach my $row (@$songs) {
   my ($artist,$album,$title,$year) = @$row;

   # auto-vivification is what makes this all so cool
   push @{$hash{$artist}->{$album}->{'list'}},$title;

   # getting the year is tricky - since i know they
   # are all the same - i can safely or-cache it ;)
   $hash{$artist}->{$album}->{'year'} ||= $year;
}

foreach my $artist (keys %hash) {

   # insert this artist - get unique id for albums
   $dbh->prepare("
      insert into artist(name) 
      values(?)
   ")->execute($artist);
   my $artist_id = last_id();

   foreach my $album (keys %{$hash{$artist}}) {
      my $year = $hash{$artist}->{$album}->{'year'};

      # insert this album - get unique id for songs
      $dbh->prepare("
         insert into album(title,year,artist_id)
         values(?,?,?)
      ")->execute($album,$year,$artist_id);
      my $album_id = last_id();

      foreach my $song (@{$hash{$artist}->{$album}->{'list'}}) {

         # insert this song
         $dbh->prepare("
            insert into song(title,album_id)
            values(?,?)
         ")->execute($song,$album_id);
      }
   }

}

$dbh->disconnect;

# mysql specific - other vendor's milleage will vary
sub last_id {
   $dbh->selectall_arrayref("
      select LAST_INSERT_ID()
   ")->[0]->[0]
}

##</code><code>##

# orginal First Normal Form table
mysql: select artist,album,title,year 
       from songs 
    order by artist,year,album,title 
    limit 2;
+----------------+-------------+-----------------+------+
| artist         | album       | title           | year |
+----------------+-------------+-----------------+------+
| 10,000 Maniacs | In My Tribe | A Campfire Song | 1987 |
| 10,000 Maniacs | In My Tribe | Cherry Tree     | 1987 |
+----------------+-------------+-----------------+------+
2 rows in set (0.02 sec)

# new Second Normal Form tables
mysql: select artist.name, album.title as album,
              song.title, album.year
       from artist
       inner join album on artist.id=album.artist_id
       inner join song on album.id=song.album_id
       order by artist.name,album.year,album.title,song.title 
       limit 2;
+----------------+-------------+-----------------+------+
| name           | album       | title           | year |
+----------------+-------------+-----------------+------+
| 10,000 Maniacs | In My Tribe | A Campfire Song | 1987 |
| 10,000 Maniacs | In My Tribe | Cherry Tree     | 1987 |
+----------------+-------------+-----------------+------+
2 rows in set (0.04 sec)

##</code><code>##

# 1st form
mysql: update songs set name="Bjork"
       where name="Bork";

Rows matched: 14  Changed: 14

# 2nd form
mysql: update artist set name="Bjork" 
       where name="Bork";

Rows matched: 1  Changed: 1