October 5, 2011 at 9:43 am
John Mitchell-245523 (10/5/2011)
L' Eomot Inversé (10/5/2011)
I find it quite frightening that so few people know enough about normalisation to get this one right.I don't. This question is couched in abstract language. "Elementary key", "prime attribute"and "fixed" aren't part of the everyday SQL Server vernacular. Don't get me wrong - I'm not complaining, since I found it an educational experience. I just think more people would have got it right if the terminology had been a bit more familiar.
John
I agree John. The vernacular was outside the scope of normal conversation far enough that I struggled to understand what the answers meant.
Great question Tom!!! Certainly an educational question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2011 at 9:44 am
paul s-306273 (10/5/2011)
Not quite sure of the relevance of this question.(Or is that just sour grapes - 2correct answers, 118 incorrect answers so far!)
Well, it's the second question of a pair about normalisation theory; the first will appear in about a week's time. (Steve's ways are sometimes hard to follow - but I'm not complaining, I saw that they would appear in the wrong order but didn't get around to asking him to change it, so it's my fault.)
My idea was to put a couple of questions that would go well with the fourth article on my series about normalisation. This is somewhat screwed up by the fact that I wrote an appallingly bad draft of the fourth article (so bad that Steve hasn't yet commented on it, probably because he would be embarrassed to tell me how bad it is) and haven't yet sorted it out, so the questions are appearing before the article insetad of after it.
It is really worrying that people don't unsterstand the representation principle (the base of all early work on normalisation - everything up to and including third normal form) and how normalisation adheres to it as far as elementary key normal form but abandoned it in Boyce-Codd normal form (and all higher normal forms) so that for many situations it is better to have a schema that has some tables not in BCNF or any higher normal form although most of the tables should conform to higher normal forms, because this lack of understanding of basic normalisation principles encourages schema designs that accomodate coding bugs with no error messages that with proper schema design would violate declared constraints.
Tom
October 5, 2011 at 9:50 am
hodgy (10/5/2011)
Ah, I was researching elemenTARTY keys, probably why I got it wrong 😉
I saw that too and looked for one of the choices to be "A must equal a multiple of 3.14159265358979323846264338327950288419716939937510".:-)
October 5, 2011 at 9:53 am
John Mitchell-245523 (10/5/2011)
L' Eomot Inversé (10/5/2011)
I find it quite frightening that so few people know enough about normalisation to get this one right.I don't. This question is couched in abstract language. "Elementary key", "prime attribute"and "fixed" aren't part of the everyday SQL Server vernacular. Don't get me wrong - I'm not complaining, since I found it an educational experience. I just think more people would have got it right if the terminology had been a bit more familiar.
John
I wish I knew some more familiar terminology. I suppose for "prime attribute" one can use "key attribute" (although the SQL influence on terminology means that a key attribute may allow nulls that case is covered bu other options) but "fixed" is just normal English (or American) and I'm not aware of any other term at all for "elementary key".
If you can suggest any better terms, I'll be glad to hear them - and will maybe use them in an article soon.
Tom
October 5, 2011 at 10:01 am
Nice question on Normalization. Would have been easier to answer if I knew that T was part of a database at BCNF or EKNF.
Thought I selected the right answers A,C,D but saw the "you are wrong" response.
I guess I need more coffee and less insomnia. 😎
October 5, 2011 at 10:20 am
Rob Schripsema (10/5/2011)
Who can explain why the fifth option, "A on its own is a candidate key of T" is NOT true? What am I missing here?
Easy counter example: consider a table T with three attributes A, B, C, all integers, none of them allowing NULLs, with the business rule A+B+C=0.
Than the candidate keys are (A,B), (B,C), and (C,A), and none of (A), (B), or (C) is a candidate key. Since in any given state of the table there can only be one value of C for a given pair of values (A,B), each of a and B is an elementary key attribute. Neither is a candidate key on its own (and since the whoe table is symmertical in the three attributes, C also is an elementary key but (C) is not a candidate key).
Tom
October 5, 2011 at 10:21 am
DOH! 2 out of 3 so needed the question to re-educate/fill in the gaps.
Good question Tom...
October 5, 2011 at 10:29 am
definitely, i learned something today!!!
thanks, Tom!!! this kind of question is very importance to not forget the concepts of databases!
October 5, 2011 at 10:35 am
L' Eomot Inversé (10/5/2011)
I wish I knew some more familiar terminology. I suppose for "prime attribute" one can use "key attribute" (although the SQL influence on terminology means that a key attribute may allow nulls that case is covered bu other options) but "fixed" is just normal English (or American) and I'm not aware of any other term at all for "elementary key".If you can suggest any better terms, I'll be glad to hear them - and will maybe use them in an article soon.
I guess I don't get why you're so upset with the results of the question. It was a good question, one I would have more likely gotten all 3 parts right in college rather than this far removed from the theory. But as others have mentioned, people don't think in these theoretical terms, and it isn't necessary to remember the theoretical terminology to understand the concepts behind it. In practice, I can tell pretty quickly if a design is third normal form or not.
October 5, 2011 at 11:00 am
Chris Harshman (10/5/2011)
L' Eomot Inversé (10/5/2011)
I wish I knew some more familiar terminology. I suppose for "prime attribute" one can use "key attribute" (although the SQL influence on terminology means that a key attribute may allow nulls that case is covered bu other options) but "fixed" is just normal English (or American) and I'm not aware of any other term at all for "elementary key".If you can suggest any better terms, I'll be glad to hear them - and will maybe use them in an article soon.
I guess I don't get why you're so upset with the results of the question. It was a good question, one I would have more likely gotten all 3 parts right in college rather than this far removed from the theory. But as others have mentioned, people don't think in these theoretical terms, and it isn't necessary to remember the theoretical terminology to understand the concepts behind it. In practice, I can tell pretty quickly if a design is third normal form or not.
I'm not particularly upset, just a bit worried that people don't appear to remember this stuff. And if there is a better known terminology that the one I used I really do want to hear about it - it will help me explain normalisation theory better.
Incidentally, there is no need at all to understand elementary keys to understand (and recognise) third normal form; understanding the differences between 3NF, EKNF, and BCNF is a very different matter, of course.
Tom
October 5, 2011 at 12:35 pm
L' Eomot Inversé (10/5/2011)
I'm not particularly upset, just a bit worried that people don't appear to remember this stuff. And if there is a better known terminology that the one I used I really do want to hear about it - it will help me explain normalisation theory better.Incidentally, there is no need at all to understand elementary keys to understand (and recognise) third normal form; understanding the differences between 3NF, EKNF, and BCNF is a very different matter, of course.
I think a big part of this is the flavor of Normalization Kool-aid we're using.
For instance, this paper that's referenced isn't necessarily Normalization Form mainstream.
I've got my college text book I taught DB Theory with from 10 years ago (the paper you reference was written in 1982) and Elementary Keys and this mid-point between BCNF and 3NF aren't in it.
So saying people need to know this variant of Normalization in order to work with SQL is flippant in my opinion. I drank the Cherry Koolaid, and you drank the Grape.
EKNF didn't exist in this book when I used it to teach DB theory
http://www.amazon.com/Database-Processing-12th-David-Kroenke/dp/0132145375/ref=sr_1_3?ie=UTF8&qid=1317839521&sr=8-3 Granted that was the 8th, 9th, and 10th editions instead of the 12th
Nor is it in here
Except as a footnote for additional reading. So EKNF at least when I went to college and when I taught it, wasn't considered relevant or a Normal form.
Another note.. go to http://www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.
October 5, 2011 at 1:06 pm
This question definately made me think. I got it wrong but nearly right. (2 out of 3). Being that I have never actually had any formal database courses, a lot of the theory based questions get me. I would love to read up on more of the theoretical aspects of normalization if I had the time to do so. But alas, it always gets bumped down the priority list.
October 5, 2011 at 1:19 pm
mtassin (10/5/2011)
So saying people need to know this variant of Normalization in order to work with SQL is flippant in my opinion. I drank the Cherry Koolaid, and you drank the Grape.Another note.. go to http://www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.
Thank You Mark. 😎
Double thanks for drinking the Koolaid, and telling us what the flavors are. :hehe:
October 5, 2011 at 2:22 pm
Well, I certainly got it wrong, but that's because of simple unfamiliarity but I tried to turn it into my familiar world. I figured elementary key meant candidate key without doing any research. Apparently I only drank the Fruit Punch.
I've typically learned to avoid these types of discussions except at a very basic level mostly because... I don't care that much. I care about what works, not necessarily the theories that lead to very minor differences or major ones that are ivory tower theoretical until the engines catch up.
So... nice question Tom, I learned something today about different versions of Normal Form. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2011 at 12:41 am
Difficult one. Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply