Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Migrating a 1NF table to multiple 2NF tables

by jeffa (Chancellor)
on Dec 05, 2001 at 01:38 UTC ( #129454=CUFP: print w/ replies, xml ) Need Help??

Help for this page

Select Code to Download


  1. or download this
    # Diagram 1
    +-----+-----------------+--------+-----------+------+
    ...
    | 498 | Crying          | Bjork  | Debut     | 1993 |
    | 499 | One Day         | Bjork  | Debut     | 1993 |
    +-----+-----------------+--------+-----------+------+
    
  2. or download this
    # Diagram 2
    +-----+-----------------+--------+-----------+------+
    ...
    | 494 | Isobel,Cover Me | Bjork  | Telegram  | 1996 |
    | 495 | Crying,One Day  | Bjork  | Debut     | 1993 |
    +-----+-----------------+--------+-----------+------+
    
  3. or download this
    CREATE TABLE songs (
      id int(11) NOT NULL auto_increment,
    ...
      year varchar(4) default NULL,
      PRIMARY KEY  (id)
    );
    
  4. or download this
    CREATE TABLE song (
      id int(11) NOT NULL auto_increment,
      title varchar(65) default NULL,
      PRIMARY KEY  (id)
    );
    
  5. or download this
    CREATE TABLE artist (
      id int(11) NOT NULL auto_increment,
      name varchar(65) default NULL,
      PRIMARY KEY  (id)
    );
    
  6. or download this
    CREATE TABLE album (
      id int(11) NOT NULL auto_increment,
    ...
      FOREIGN KEY index_artist_id(artist_id) 
        REFERENCES artist(id)
    );
    
  7. or download this
    CREATE TABLE song (
      id int(11) NOT NULL auto_increment,
    ...
      FOREIGN KEY index_album_id(album_id) 
        REFERENCES album(id)
    );
    
  8. or download this
    mysql: select * from mp3.song limit 3;
    +----+--------------------------------+----------+
    ...
    |  2 | Bjork       |
    |  3 | Pink Floyd  |
    +----+-------------+
    
  9. or download this
    mysql: select artist.name from artist 
           inner join album on artist.id=album.artist_id
    ...
    +------+
    | U2   |
    +------+
    
  10. or download this
    $VAR1 = {
       'Bjork' => {
    ...
          #etc...
       }
    };
    
  11. or download this
    use strict;
    
    ...
          select LAST_INSERT_ID()
       ")->[0]->[0]
    }
    
  12. or download this
    # orginal First Normal Form table
    mysql: select artist,album,title,year 
    ...
    | 10,000 Maniacs | In My Tribe | Cherry Tree     | 1987 |
    +----------------+-------------+-----------------+------+
    2 rows in set (0.04 sec)
    
  13. or download this
    # 1st form
    mysql: update songs set name="Bjork"
    ...
           where name="Bork";
    
    Rows matched: 1  Changed: 1
    

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://129454]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (11)
As of 2014-10-31 21:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (225 votes), past polls