5 replies to this topic

[solartic]

Hi guys,

I had an assignment form designing a database in 3rd normal form. Bellow is a snapshot of the design that I had.

USER
country_id
first_name
last_name
date_of_birth

USER_MUSIC
user_id
music_genre_id
song_name
artist_name
rating

I was told that this design was not in third normal form and that I should move the song_name, music_genre_id, and rating to its own table.

NOTE: The USER_MUSIC table was originally called MUSIC_COLLECTION, however I explained that was just a poor name selection and that I should have I fact been USER_MUSIC as shown above.

I just can't seem to wrap my head around why the design show above is not in their normal form, it not a matter of me thinking that the lecture is wrong, I just need other input, to either help me better understand why I am wrong.

[WingedPanther]

Start by stating the requirements of third normal form (and therefore second and first normal forms).

Also, you might want to build up some sample data to see how you might violate any of the forms.

[solartic]

Thanks for the tip WingedPanther

For third normal form all none-keyed attribute must be dependent only on the key and since music_genre_id, artist_name and rating is dependent on the song it violated third normal form. That the basic rule for third normal form not sure how I missed that one.

[win4t4]

Another thing to consider.

1. What do you think the purpose of these Normal Forms or in other word, Why they invented these Normal Forms ?
2. What is the relation of USER and USER_MUSIC, is it one to many, many to many, etc ?

I think if you answered these questions, you'll sort out why "the design" wasn't in that normal form

Winata

[solartic]

Thanks

[wim DC]

I'm not too sure what's the rating about, so i left that away.

I find it weird that you started of with those 2 tables.
I think that normally you would start with this:

               *            *

USER           <------------>SONG

[U]id[/U]                           [U]id[/U]

country_id                   song_name

first_name                   artist_name

last_name                    music_genre(id)

date_of_birth

The problem that rises here is the *-*, or n-n relation. 1 user can have several favorite songs, but the same song can be the favorite of many users
n-n is never acceptable in a (relational) database.
It's often, if not always, solved with a 3th table between the current 2 to create a 1-n-1 relationship.
All this extra table contains is 2 foreign keys, pointing to the primary key of each table.

               1            *        *          1

USER           <------------>USERSONG<--------->SONG

[U]id[/U]                           [U]userId[/U]             [U]id[/U]

country_id                   [U]songId[/U]             song_name

first_name                                      artist_name

last_name                                       music_genre(id)

date_of_birth

And what you now have created with your 2 tables, is the table USER and USERSONG, and now they complain about the 3th form telling to to create the table SONG basicly