[gonzaw]

Good tutorial.


A tiny thing though: Isn't your 1NF example an example of 4NF instead?

In that case (id >> phone) is the only dependency (with id->name being implied).
If your relation scheme is "person(id,name,phone)" it fulfills 1NF as long as you do it like this:

+----+------+--------------------+
| id | name | phone              |
+----+------+--------------------+
|  1 | Joe  | 588-5522           | 
|  2 | Anna | 589-4567           |
|  1 | Joe  | 789-85522          |
|  2 | Anna | 987-12354          |
+----+------+--------------------+

Coincidentally it's also in BCNF too />

[gonzaw]

Wait, I think that is correct...

...damn I get 1Nf and 4NF confused at times but I think (id ->> phone) is a multivalued dependency there, even if (id->name) is a functional dependency too.

[Atli]

Thanks

gonzaw said:

If your relation scheme is "person(id,name,phone)" it fulfills 1NF as long as you do it like this:

That's true, but that type of table would, in practice, violate the 2NF. I could have used something like that as the 1NF solution, but I opted instead to show a fully normalized solution; something that would actually be practical to use. You are actually quite right that it is also 4NF compliant (and 5NF as well), but like I mention somewhere in there, that tends to be true for most 3NF compliant designs. I wasn't actually aiming for that level of normalization, only the 3NF.

[gonzaw]

Oh yeah my bad, it would indeed violate 2NF since the PK would be {id,phone}

[Adqusit]

Very Well Done, for providing such a tremendous knowledge about DataBase. Well, what it was missing in it, which is actually hurdling for me is what is Candidate Key, i want it to be explained properly.

Secondly, ever non-key attribute must be fully dependent on Primary Key, not on an instance of Primary key?

My question is that in Composite key, we have 2 columns or 3 for PK. So how this rule is applied for composite key?

[Atli]

Hey, Adqusit. Thanks.

Adqusit, on 25 March 2013 - 04:40 PM, said:

Well, what it was missing in it, which is actually hurdling for me is what is Candidate Key, i want it to be explained properly.

Simply put, a candidate key is any combination of columns in a table that could act as a primary key. Any single field or a combination of fields that can uniquely identify a row should be considered a candidate key. So in a users table that includes a user ID, an email, and a Firstname + Lastname unique key combo, all three of those could potentially act as a primary key, and are therefore all candidate keys. Only one of them (the ID) will actually be the PK though.

Which of the candidate keys you select as the PK depends on some things. The PK should never accept NULLs, should be as simple as possible, and it should ideally never change. - To figure out which should actually be chosen as the PK, consider this: Which field is most reliable? And which field is easiest to reference?

Applying that to the design I just mentioned: Emails tend to change, so that make that a bad choice as a PK. The Firstname + Lastname combo are overly complex to be used as FKs, so that's ruled out as well. The ID is the obvious choice. It's why it exists in the first place, because an ID never has to change, and as a single integer it's easy to reference.

Adqusit, on 25 March 2013 - 04:40 PM, said:

Secondly, ever non-key attribute must be fully dependent on Primary Key, not on an instance of Primary key?

I'm not following you here. What is the difference between a PK and an instance of a PK?

For the 3NF, every non-prime/non-key attribute in the table must rely solely on the PK of row, and not on any other field in the row.

Adqusit, on 25 March 2013 - 04:40 PM, said:

My question is that in Composite key, we have 2 columns or 3 for PK. So how this rule is applied for composite key?

Again, I don't follow. Could you show the structure of this design? Would be far easier to understand.

[Adqusit]

Atil, you says that

Quote

So in a users table that includes a user ID, an email, and a Firstname + Lastname unique key combo, all three of those could potentially act as a primary key, and are therefore all candidate keys. Only one of them (the ID) will actually be the PK though.

Let me clarify myself, Does Candidate key means that a field/column, that can be made a PK, is called Candidate Key. Like in your example, User_ID, email, first or last name all can be used as PK, so these all are able to be made a PK? Am I right with this?



Secondly, what i was saying about 3NF is:

In third Normal form, it is there that every non key attribute must be fully dependent on primary key, and not on any instance of PK. right. If i have a composite key lets say, S.No and Prod_ID, right NOw applying the 3NF on this composite key? Does composite key is valid choice in 3NF.??

[Atli]

Adqusit, on 26 March 2013 - 09:50 AM, said:

Atil, you says that

Quote

So in a users table that includes a user ID, an email, and a Firstname + Lastname unique key combo, all three of those could potentially act as a primary key, and are therefore all candidate keys. Only one of them (the ID) will actually be the PK though.

Let me clarify myself, Does Candidate key means that a field/column, that can be made a PK, is called Candidate Key. Like in your example, User_ID, email, first or last name all can be used as PK, so these all are able to be made a PK? Am I right with this?

Yes, each potential PK is what we call a Candidate Key. Only one of those will actually be the PK, though.

The phrase "Candidate Key" just means "Field or field combos that are unique for each row". That's the same requirement for a PK

Adqusit, on 26 March 2013 - 09:50 AM, said:

In third Normal form, it is there that every non key attribute must be fully dependent on primary key, and not on any instance of PK. right. If i have a composite key lets say, S.No and Prod_ID, right NOw applying the 3NF on this composite key? Does composite key is valid choice in 3NF.??

There is nothing that says you can't use composite PKs in 3NF tables. In fact, they are very frequently used like that in N:M join tables.

The bigger concern when setting that up is the 2NF, which requires that fields should not be partially dependent on a candidate key. (Including the PK.) If a table with a composite PK is in 2NF, then the fact that it has a composite PK shouldn't be an issue for the 3NF.

[Adqusit]

Very well done. Now I'm quite clear about Candidate Key. thank you Sir.


As for as 3NF and Composite key is concern, i'm clear with them not completely but i've discussed databases (of different companies) with my seniors, even one of the most prominent international forum; they all say that don't be so sticky with these theoratical rules, because when you are practically programming with databases, then you sometimes confronted with a situation where you violate the rules, deliberately.

What you say about this?

[Atli]

Sure, that's true. There are cases where you can benefit from not making the tables completely normalized. I mention that at the end of the tutorial. Normalization is a design goal, and may not always be the most practical solution. You shouldn't ignore the normalization rules on a whim though. It is important that when you violate one of the normal forms, that you do so knowingly and that you have a good reason. Violating them for convenience or out of ignorance can be very dangerous.

As for being "theoretical" rules. That implies that the rules are unproven; that their intended impact on databases hasn't been verified. That is entirely untrue. These rules have been used through the world for a very long time, and their validity has been proven countless times.

[Adqusit]

So. how i will find out that whether my Tables are properly normalized. I mean after applying the basic theoratical rules, i will say its ok its ready now. But isn't there any way for cross checking?

[Atli]

You mean like some sort of normalization validation service? Not that I am aware of, no. If you've made sure the first three normal forms are satisfied, then the database is normalized. It's up to you to make sure you've done that properly. - Doesn't hurt to get another DBA to go over it as well. We all miss things on occasion.

[Adqusit]

Oh God. Atil you made me so relax. by these words that

Quote

We all miss things on occasion.

It, then indicates that everyone makes mistake, but with the passage of time, mistakes are being removed with experience. Am I right?

[Adqusit]

Can i ask you a question here about DataBase?

[modi123_1]

Preferably you would ask that in the 'database help' section.