5

I believe this relation/table is in first normal form (1NF):

Collection (cd_id, title, label, artist, type, country, song_id, song_title, length)

Note: Bold means keys.

What I have managed to identify:

{cd_id, song_id} → {song_title, length}
{cd_id} → {title, label, artist, type, country}
  • Is this correct, is it correct to say that it meets 1NF?
  • Are there more functional dependencies I am missing?

Additional details

Contextual information and/or sample values of the attributes/columns involved:

  • CD_id: 009329
  • Title: Title of the CD
  • Artist: Nickelback
  • Type: Group | Solo
  • Country: References the artist’s country or origin
  • Song_title: "Burn It to the Ground"
  • Length: 3:32 mins

Extras

Per song under a CD, meaning that there will be duplicates. Every time a song belonging to a CD is added.

12
  • 3
    Functional dependences hold according to the meaning of the data. Is difficult, probably impossible, from a set of names of attributes, to infer such meaning. For instance, is country the country of the label, or it is the country in which the label has published the cd, assuming that a label can publish in several countries? What kind of attribute is type? Is the genre of the music, the type of the cd, or what else? There is only one artist per cd or more then one? So, the only people that can answer your question if there are others FD is someone who knows exactly the meaning of the data.
    – Renzo
    Sep 30, 2019 at 15:47
  • 2
    Some reflections, this appears to be in 1NF but if for example artist is not atomic it maybe not (let's say that two artists cooperate on one album and are represented by artist: "Bob Dylan, The Rolling Stones". For FD, more info is needed Sep 30, 2019 at 15:52
  • 2
    If country is the country of the artist, then it depends on artist, so there is another dependency (artist -> country). The same for type, an artist is either a solo or a group, so it depends from artist. In any case, the relation is not in 2NF.
    – Renzo
    Sep 30, 2019 at 16:17
  • 1
    Every relation is in 1NF, by definition. A table, on the other hand can be not in 1NF, but than you should have more then one value in some cell. I assumed that your example is a relation, not a table, since you are talking about FD.
    – Renzo
    Sep 30, 2019 at 16:25
  • 2
    The concepts of candidate keys, functional dependencies and normal forms are defined properly for relations, not for tables.
    – Renzo
    Sep 30, 2019 at 16:29

1 Answer 1

1

A concern that exists is that a song may be released as a single, then as part of an album, then re-released in a "greatest hits" album. This would lead me to have a song table, then create a join table that would connect the album table to the song table. This join table would allow for a single song to be connected to a number of albums. The PK of that table could be the Album_ID and Song_ID, making it a composite. I would also look at creating an Artist table, which would separate data like the artist's name, country of origin, and group/solo. This could also head off the "Prince Effect," where a singer/group changes their name. Tables (PKs are bold)

Albums (Album_ID, Artist_ID, Album_Title, Release_Date)

Artist (Artist_ID, Country_Of_Origin, Type, Artist_Name)

Song (Song_ID, Song_Name, Song_Duration)

Song_Album (Album_ID, Song_ID, Track_Number)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.