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.
Best Answer
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)