1

I am attempting to explain how the mock artist/soundtrack data (first image below) can be normalized from 1NF to 2NF to 3NF, step-by-step to get the result that I think is best for the database. It's almost as if normalization is getting in the way of what I want to do, but am I just not understanding something in the normalization process? I can definitely see how this mock data can be normalized to 1NF by making each row unique and removing duplicates, but at which stage, for example are we told to assign Composer ID as a foreign key for the tracks table or the movie table? Is that just something we do from experience? Is there no right or wrong?

In short, my question is, can anyone show or explain how the mock data here ...

enter image description here Was turned into this using all the first 3 stages of normalization?

enter image description here

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Victor Rodriguez
  • 531
  • 1
  • 10
  • 18
  • 1
    Normalization does not introduce new attributes like "Composer ID". It also does not mean you can normalize a relation to 2NF *and no higher*, then to 3NF *and no higher*. – Mike Sherrill 'Cat Recall' Jan 25 '17 at 20:05
  • 1
    Also one does not normalize by going through lower normal forms to get to higher ones. Also normalization to higher normal forms is per *functional dependencies*. Since you don't mention them, you don't seem to even understand what normalization is. What reference are you using? PS Assuming you want your 1NF to have Track be of type track title, under reasonable FD assumptions this is already in 5NF. – philipxy Jan 27 '17 at 04:14
  • @philipxy I guess by normalization, I mean reducing redundancy, so that for example if we were given two values in a cell in first table, we could find a way, through normalization, to set the data up in tables correctly. I am simply looking for advice on how we could take a table of data like the mock one show and turn it into a set of functional tables in a database – Victor Rodriguez Jan 28 '17 at 08:00
  • Your question is tantamount to asking for an introductory college chapter, slide set and/or course. Find. Many are free online, [eg](https://lagunita.stanford.edu/courses/DB/RD/SelfPaced/about). See [this answer](http://stackoverflow.com/a/31397135/3404097)'s whirlwind summary section Educate yourself about database design. (Google & browse my SO (favourite) answers re the relational model, database design, predicates, first normal form (1NF), atomic, functional dependencies (FDs), candidate keys (CKs), join depenencies (JDs), normalization, (lossless) decomposition, fifth normal form (5NF).) – philipxy Jan 28 '17 at 09:06
  • @philipxy Thank you, checking out the Stanford course now. Thanks again – Victor Rodriguez Jan 28 '17 at 13:53

1 Answers1

1

Well your 1NF would be to have a distinct record for each track name so essentially the mock data with the first record split into 2...

2NF is to take out the repeated keys which to my mind is what you've displayed as the 3 separate tables, and potentially that might be as far as you need to go.

You could add a further table to allow for a track to feature in more than one movie ie create a movie tracks table referencing the track id and movie id respectively (and removing movie id from the tracks table).

Similarly, you could go to the extreme of allowing for collaborative compositions by also having a track composers table but that is probably not sufficiently common to make the effort worthwhile

Normalization is something that definitely becomes easier with experience and can be taken as far as suits the purpose of the data as can be seen from the example.

Dave Towle
  • 41
  • 2
  • Thank you Dave! That does help – Victor Rodriguez Jan 26 '17 at 13:04
  • @VictorEliasRodriguez This answer is a mess of confused notions & poor writing. "repeated keys" means nothing. (The author shows they know this via "essentially" & "...".) "split into two" is unclear. Normalizing to higher NFs doesn't introduce ids. The current design already allows "for a track to feature in more than one movie" & "collaborative composition". "sufficiently common" is irrelevant, it's either ever or never. – philipxy Jan 27 '17 at 04:21