Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Migrating a 1NF table to multiple 2NF tables

by jeffa (Bishop)
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?
[marioroy]: At the Fransiscan monastery, got stuck up high in a tree from pruning and the chainsaw with large branch fell and broke the latter, but not me fortunately. Was stuck there for a while until a firetruck came by.
[Corion]: marioroy: So you live dangerously ...
[Lady_Aleena]: s/latter/ladder/; # ? marioroy
[karlgoethebier]: marioroy: Praise the Lord
[marioroy]: Well, that's why there must be angels around, invisible or not.
[marioroy]: Lady_Aleena yes, ladder.
[marioroy]: Corion, no not intentionally. not at all.
[Lady_Aleena]: /ne tries to decide whther or not to take a hammer to hubby's alarm clock.
[Lady_Aleena]: He never hears it.
[marioroy]: Back in HighSchool, I came late to Gym class. There was a fellow who was picked last, no team wanted him for racketball. So it was us two against everybody. I looked at him in the eye and said to let your body loose and let the powers to be help us win.

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (11)
As of 2017-05-29 08:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?