Understanding 2NF & 3NF?

[Ani]

Hi Guys
Im getting lots of help which I really appreciate but I am trying to properly understand 3NF.
Ive read Alans Relational DB link and looked at A Langers tutorials. TBH his tutorials confuse me!
This is the clearest description of 3NF to me
"Then, to be in 3NF, a table must be in 2NF, and all columns depend directly on the primary key.
Tables violate the 3NF if one column depends at least partly on another column, which in turn depends
on the primary key (a transitive dependency)."

Looking at my Tables to try and understand how this works, for example
tblCustomerDetails
tblCustomerService

tbl CustomerService has its own AutoNo PK then the foreign key from CustDetailsID.
All the data in the tblCustomerService fields depend on the FK to enable them to be connected to the tblcustomerdetails?
So does this violate 2NF? I need to get this clear in my head, can someone explain or point me to an explanation please?
Why am I finding this difficult to understand......
Ani

 

[Ani]

Oh NO! I feel like a proper idiot now! I missed the obvious didnt I!
We have to restore the relationship by creating something called a foreign key (indicated in our diagram by (FK)) in the orders table.
The important bit!
A foreign key is essentially a column that points to the primary key in another table.
Ani

 

[RainLover]

A foreign key is essentially a column that points to the primary key in another table.

Correct.

Although the use of the word Column is not wrong and is used by many Database Engines, Access uses FIELD.

However when you slip back into code you may have to use Column. Makes life easy doesn't it.

 

[Ani]

Cheers Rain! I may be over thinking but I would much rather understand than just 'do'!
I dont even want to think about 'code', not sure I will ever get that far.
Ani

 

[RainLover]

I would think that you will be using Code within days.

Just some basic stuff to start with.

 

[Ani]

I would think that you will be using Code within days.

Just some basic stuff to start with.

LOL

 

[RainLover]

You will need a Command Button to close the Form.

This button will require some code.

Three very short lines.

 

[Ani]

You will need a Command Button to close the Form.

This button will require some code.

Three very short lines.

Oh...more reading!

 

[RainLover]

I have a Uni exam in the AM so I might call it a night.

 

[Ani]

G'Night! And Good Luck!
Ani

 

[ButtonMoon]

This is the clearest description of 3NF to me
"Then, to be in 3NF, a table must be in 2NF, and all columns depend directly on the primary key.
Tables violate the 3NF if one column depends at least partly on another column, which in turn depends
on the primary key (a transitive dependency)."

No, that's not an accurate description of 3NF. To satisfy 3NF the determinants for every non-trivial functional dependency of non-key attributes must include a candidate key. That's not the same as what you have written because: i) a non-key attribute could be determined by any number of candidate key attributes; ii) 3NF is concerned only with dependencies on non-key attributes - partial-key determinants for key attributes don't actually violate 3NF even though they are usually undesirable.

If this leaves you more puzzled than before then here's a much simpler way to understand it. Just write down the functional dependencies you expect to enforce like this:

{A}->{B}
{C}->{D}
...
etc

You need to ensure that the left-hand side (determinant) of every dependency includes a candidate key (you are allowed to have other attributes on the left as well but you can keep the list short by using Armstong's Axioms to eliminate them). When every one of those determinants includes a candidate key then you have satisfied Boyce-Codd Normal Form (BCNF), which means you have satisfied 3NF as well.

If you want an even simpler description of BCNF then remember this little mnemonic (attributable to C.J.Date I think): the dependencies must be "arrows out of superkeys".

 

[Ani]

No, that's not an accurate description of 3NF. To satisfy 3NF the determinants for every non-trivial functional dependency of non-key attributes must include a candidate key. That's not the same as what you have written because: i) a non-key attribute could be determined by any number of candidate key attributes; ii) 3NF is concerned only with dependencies on non-key attributes - partial-key determinants for key attributes don't actually violate 3NF even though they are usually undesirable.

If this leaves you more puzzled than before then here's a much simpler way to understand it. Just write down the functional dependencies you expect to enforce like this:

{A}->{B}
{C}->{D}
...
etc

You need to ensure that the left-hand side (determinant) of every dependency includes a candidate key (you are allowed to have other attributes on the left as well but you can keep the list short by using Armstong's Axioms to eliminate them). When every one of those determinants includes a candidate key then you have satisfied Boyce-Codd Normal Form (BCNF), which means you have satisfied 3NF as well.

If you want an even simpler description of BCNF then remember this little mnemonic (attributable to C.J.Date I think): the dependencies must be "arrows out of superkeys".

Thanks but I didn't write it. It was taken from a link someone provided, that is why it is in inverted commas.
Ani

 

[ButtonMoon]

Thanks but I didn't write it. It was taken from a link someone provided, that is why it is in inverted commas.

Obviously there's a great deal of misleading and incorrect information available online. Be selective about what you read. Don't take my word for it either. If you are serious about learning then take a course or read a good book. Here's the website of one excellent book on database principles and database design (not spam - I have no affiliation to them): infolab.stanford.edu/~ullman/dscb.html

 

[Ani]

Obviously there's a great deal of misleading and incorrect information available online. Be selective about what you read. Don't take my word for it either. If you are serious about learning then take a course or read a good book. Here's the website of one excellent book on database principles and database design (not spam - I have no affiliation to them): infolab.stanford.edu/~ullman/dscb.html

I do not think that the information I quoted is intentionally misleading or incorrect. It is a simplified explanation of Normalisation that allows those of us that are dabbling with creating an Access database, as a hobby (at least initially) to have some understanding of how it works.
I am not unintelligent and have read as many sources of information as is freely available. Whilst I appreciate your detailed explanation of normalisation I do not have the time to ingest the finer more technical explanation, at present.
People on the forum have, most kindly, looked at my Tables and they seem to think I have done enough to continue to the next stage.
Your detailed explanation is somewhat wasted on me, perhaps you could start a thread with the other more knowledgeable experts, on the forum, whom are able to discuss it with you in a more coherent and intelligible manner?
Regards
Ani
BSc (Hons) MSc PGCE

 

[RainLover]

Ani,
ButtonMoon is new here so I know little of his/her abilities.
So let me tell you a story about myself.

I learnt Access out of a case of necessity. At that time I was out bush. No internet, No one to ask, No Forums, No books, and most of all No idea what I was doing. But it had to happen. I had a year or two before written a Database to help me when I was in the Scouting movement. That was it.

I did have Access help files, but not online.

We had employees who had names and lived somewhere.
I put the employees into a table together with the other information. I realised quickly that the girls entering this information were lazy. There were a bunch of towns where the Employees lived and for each town the girls found various way of spelling. So my filtering and sorting was not good.

I created a new Table and put the Towns in there which forced the girls to select the correct spelling. I also added the Post Code and State.

I again realised that they would spell the States differently. E.g. Queensland, Q, Qld. So I put the States into a separate table and forced the girls to select the right spelling.
I did a similar thing with Employee Trade and other qualifications. Then there were the jobs they did. Where they did them and who the Client was. Each site could have various Clients. A site was usually a Coal Mine.

I ended up with a database that did not have any repeating data, anywhere. It just seemed the logical way to do things.
I did not realise at that time that I was normalising my Database. Was it 1st, 2nd or 3rd normal form? I have no idea. But I will say that that database was normalised better than 99% of newbie stuff I see here.

You can if you wish study this and that about Normalisation. If you are really keen you can go back to the Mathematics that Normalisation is founded on.
You can do all that or you can write your database and learn as you go, ignoring the big words that the academics use.

I know which I would prefer.

(Now watch and wait for the comments)

 

[Ani]

Thanks Rain. Im pragmatic but like to do and learn at the same time. We all have different learning speeds due not only to the way our brains work but also other environmental factors. I really appreciate that there are lots of people who have an in-depth knowledge of db's on this forum, thats why I joined it. It takes years to become really good at something and when people are happy to share their knowledge I appreciate it.
On saying that I love my subject and am happy to share what I know too, the main reason for the db and related hobby is just for that reason. It isnt financially driven.
Thank you for sharing your story!
Ani

 

[Ani]

Rain....How did the exam go?

 

[ButtonMoon]

I am not unintelligent and have read as many sources of information as is freely available. Whilst I appreciate your detailed explanation of normalisation I do not have the time to ingest the finer more technical explanation, at present.
People on the forum have, most kindly, looked at my Tables and they seem to think I have done enough to continue to the next stage.
Your detailed explanation is somewhat wasted on me, perhaps you could start a thread with the other more knowledgeable experts, on the forum, whom are able to discuss it with you in a more coherent and intelligible manner?

I'm very sorry if you thought that anything I said suggested you were unintelligent. I don't know how I could have given that impression and it certainly wasn't my intention. I was simply responding to what you asked for: that you wanted to understand normal forms. I kept my answer as straightforward as I think an accurate explanation can be.

 

[RainLover]

I'm very sorry if you thought that anything I said suggested you were unintelligent. I don't know how I could have given that impression and it certainly wasn't my intention. I was simply responding to what you asked for: that you wanted to understand normal forms. I kept my answer as straightforward as I think an accurate explanation can be.

Buttons

In my opinion, you appear to know your theory. However you did use terminology that is aimed more at a person who is more experienced.

No matter how correct you are some people just want a simple explanation.

Please keep posting. This site can use people with your level of knowledge.