CODEWITHSUNDEEP
relational-databasedatabase-normalization
Meylis Matiyev
Meylis Matiyev

Reputation: 69

What's wrong in this normalized table?

I have the following album table:

album_id(PK) album_name artist_name year songs

My candidate keys are {id} and {album_name, artist_name}.

Now I am going to normalize the table till 3NF, and I would like to know the reason behind the data of artist_name column being redundant.

1NF

Goal: columns should be atomic.

Result:

album:

album_id(PK) album_name artist_name year

song:

song_id(PK) album_id(FK) song_name
2NF

Goal: No partial functional dependencies of non-prime attributes (columns that don't exist in any candidate key) on candidate keys.

Solution: I couldn't find any partial functional dependencies.

3NF

Goal: No transitive functional dependencies of non-prime attributes on candidate keys.

Solution: I couldn't find any transitive dependencies.

Problem

Although the tables above seem normalized, there's the following problem: the data in the artist_name column is redundant. An artist with multiple albums will have their name stored multiple times, which we are against.

What am I missing?

Upvotes: 0

Views: 273

Answers (1)

markfila
markfila

Reputation: 460

i would create a table called artist and in there store the artist id and name and in the album table have a reference to that using a foreign key constraint. So where you would have artist name in album this would change to artist id. It wouldn't be a issue if you just have the name like you do now but if you have additional data that you would need to store for a artist then you would have to create the table anyway which would break the current design as you would have the name in the album table and the rest of the information in the artist table.

The main goal of normalization is to reduce redundancy. With the artist name being in the album table if you ever needed the name of a artist and additional artist info then you would have to include the album table and the artist table which wouldn't make sense and you wouldn't have any other columns besides name to link the tables together or duplicate the data in two places both the album and artist table which would violate the 1st normal form.

Also, with the name being in the album table your data would be split across two tables. The artists name isn't really a dependency on album but on the artist entity. This violates values stored in a column should be of the same domain principle of the 1st normal form.

Upvotes: 1

Related Questions