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.

Community
  • 1
Erik Eriksson
  • 171
  • 8

1 Answers1

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)

John Herbert
  • 689
  • 4
  • 3