mysql - 考试重新标准化歌曲(singerID、singerName、songID、歌曲名称、歌曲长度、歌曲流派)

Question

我在过去一篇有关关系数据库的论文中提出了以下问题。我完全陷入了问题(iv)。我认为我可能做错了第(iii)部分，这导致了这个问题。

Suppose you have the following database table called Songs:

Songs(singerID, singerName, songID, songName, songLength, songGenre)

where {singerID, songID} is a primary key and the following functional dependencies exist:

{singerID} -> {singerName}
{songID}   -> {songName, songLength, songGenre}
{songName} -> {songGenre}

i What normal form is the above table in?

答案:在 1NF 中。

ii Say why it is not in 2NF.

答案:SingerID 无法确定songName、songGenre 或songLength

iii Re-design the Songs table and make a table from it which is in 2NF.

答案:

Singer{singerID, SingerName}
Song{SongID, songName, songLength, songGenre}
Songs{singerID, SongID}

现在我认为所有非键属性在功能上都依赖于主键。

iv Say why the table you produced in part (iii) is in 2NF and not in 3NF. State any assumptions you need.

对我来说这是 3NF。

Answer 1

您有一个传递依赖:songID > SongName > SongGenre。这很棘手，因为我在现实世界中没想到会出现这种情况。

对于 3NF，您需要一个表 {songName, SongGenre} 并从 song 中删除 songGenre。