Minimal FDs

Databases
1

I believe this table is 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 1NF, and are there more FDs I am missing?

2

what if a CD is a collection, like The Rough Guide to Blues Revival, which has multiple artists like Eugene “Hideaway” Bridges and Savoy Brown & Kim Simmonds

then the song id is also needed to identify the artist

1 Like
3

CDs are numbered to identify each record.
Includes information about individual songs as well.

“then the song id is also needed to identify the artist” Explain!

{song_id} → {artist}

That would be transitive dependency, right?

4

um… okay :thinking:

what do “title” and “country” describe? the CD or the artist?

5

Country = References the artist’s country or origin

6

if you have a song_id, do you really need to know which CD it is to be able to identify the song’s title?

7

Everything in a new line for every song, and use the primary key {cd_id, song_id}.

8

and what if a song is released without a cd? what if its so popular it goes on multiple cd’s?
why is the artist’s country a property of a cd? or do you mean it is the country the CD was recorded in?
if a cd has multiple artists, what country goes with the cd?

2 Likes
9

It’s a question of interpretation. Thanks!

10

well youre asking about database design, so yes, you need to interpret what these fields mean, what they contain.

you cant plop a list of fields down and say “normalize this” without knowing what can go into the fields.

11 
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
12

:trophy: :trophy: :trophy:

2 Likes
13

my statement to you at this point would be forget 1NF, aim for something at least 2nf if not 3nf; separate your cd info from your song info. your artist info from the cd info (if applicable, join table these two).

as it is, “length” makes no sense in this table if its a CD table.

14

I am trying to identify the functional dependencies here, {cd_id, song_id} = primary key.

15

why is cd_id required in order to define length or title? (EDIT: This is the same as Rudy’s post #6 )

Is this a table of CD’s, or is it a table of songs? (Answer: It’s both, which is why it fails every common-sense definition of a database table.)

16

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

Note: Bold means keys.

So that isn’t 1NF, but 2NF ?

17

It’s a 1NF table of Songs, because it satisfies the one basic definition of 1NF: For any given row, each column has exactly 1 value in it. (I am willing, for the moment, to assume that every song in the world has exactly 1 artist, and 1 country, and 1 length.)

2 Likes
18

But, {cd_id, song_id} = primary key, how does one interpret this?

19

that the combination of {song_id,cd_id} uniquely identifies a row. That is the definition of a primary (compound) key.

1 Like
20

So, {cd_id, song_id} = gives all, meaning it holds?

EDIT: I am looking at this website: https://opentextbc.ca/dbdesign01/chapter/chapter-12-normalization/