0

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Meylis Matiyev
  • 69
  • 5
  • 1
    Beware that ["1NF" has many meanings.](https://stackoverflow.com/a/40640962/3404097) (All involve replacing some table with parameterized structure by some table(s) with a column per parameter.) Neither does "relation". Even when 1NF removes so-called "non-atomic" attributes that word has no standard meaning & there's no standard way of doing it. If songs has type list of x then typically we would expect a 1NF design to have an attribute song of type x. We can't know whether what you did is OK unless you give relevant type information, definition of "atomic" & how you are to normalize to 1NF. – philipxy Sep 03 '21 at 22:51
  • 1
    There is nothing wrong per se with a value appearing more than once & "redundant" has no particular meaning & normalization removes only certain redundancy. There are lots of duplicate questions re your "problem". Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Sep 03 '21 at 23:10
  • 1
    Possible duplicate of [Understanding Normalization & Duplicates - I Guess I Don't - Adding Artist & Title Ids](https://stackoverflow.com/q/44530971/3404097) – philipxy Sep 03 '21 at 23:21
  • 1
    You don't clearly state your "problem". You don't explain what "redundant data" or "stored multiple times" in a way that "we are against" mean or what they have to do with DB normalization or 3NF or how the example artist circumstance is a "problem", what precise thing it is an example of. (Do you really mean by "multiple times" that you think no value should appear more than once in a relation or column? Ie that every column must be a superkey/unique? Otherwise, what exactly do you mean? And if so, why? Please say exactly.) – philipxy Sep 04 '21 at 00:06
  • 1
    Not sure what you mean re Codd' description. His relations could have relation-valued attributes & he defined 1NF as getting rid of them. However his first papers were sloppy. You haven't given us reason to think the original album isn't a relation. I hope you'll edit your question to clarify what you are asking about. PS Only the 1st @ per comment works but the poster (& followers) get notice of every comment. (Ha ha I just got a warning about that that when I submitted the 1st version of this comment.) [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) – philipxy Sep 04 '21 at 22:17
  • Say artist_name identifies an artist (unrealistic) & year is album year & an album has 1 name & 1 year. Say a recording can only be on an album once, so songs is say a set of recording ids. Say we want a "1NF" with a column like rec_id not songs. We have (album_id, album_name, artist_name, year, rec_id). The 1 CK is {album_id, artist_name, rec_id}. {album_id, album_name, year}+{album_id, artist_name, rec_id} is 3NF. Also BCNF. But not 4NF & presumably we would want to decompose the latter table to {album_id, rec_id}+{artist_name, rec_id}. But that's not due to problem FDs. – philipxy Oct 12 '22 at 21:50

1 Answers1

1

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.

markfila
  • 440
  • 2
  • 9
  • 2
    @Meylis 1. What anomalies? 2. Why would 3NF have no anomalies? (Higher NFs are free of more anomalies than lower until ETNF--between 4NF & 5NF--after which no higher NF removes more.) 3. NFs only remove certain anomalies. 4. Not all things reasonably described as redundancies are bad. 5. There are bad redundancies that aren't anomalies. PS If the only FDs in the original `album` are those implied by its CKs then the 1NF `album` has no problems. – philipxy Sep 04 '21 at 04:06
  • Thanks @markfila and @philipxy for the inputs. I also reread Codd's description of relation model and noticed that the original `album` doesn't fit his description of relations. – Meylis Matiyev Sep 04 '21 at 12:40