InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 23:08:42 GMT20http://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxYes I found the Wiki, and other articles, but the whole topic of normalisation seems couched in academic terms. I was trying to google for simple definitions of things like elemetary attribute key or prime attribute key - definitions that don't refer to other similar terms, sending my round in circles of cyclic definitions. I've since read your 3 normalisation articles, and heard mention that you may be planning a 4th. Perhaps you could have a bash at some good practical definitions of the terms used in normalisation - as a kind of 'demistification' level zero article. I'm sure there is something simple I'm not getting in normalisation, however here are my 'simple' definitions (which may be wrong)Attribute: This is any piece of data. In 1NF equates to a columnAttribute Key: a piece of data that on its own, or when combined with other attributes could uniquely identify a row.Candidate Key: any column, or combination of columns in 1NF that would uniquely identify a RowPrimary Attribute Key: A single column candidate key Elementary Attribute Key: ~ still unsure about this one.Thu, 08 Dec 2011 06:06:58 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]Tom Brown (12/8/2011)[/b][hr]I think that volcanic air is getting to you Tom, This question is way over most of our heads - a really hard question. No amount of googling seemed able to turn up any clues. And... well, once you see the answer its no easier to work it out. I got the not null part. And its probably not the primary key, (otherwise why call it an elementary key). The middle one long complex answer I chose this because it was long and complex - don't pretent to understand it even now, and random choice for the other one. [/quote]I probably overestimated how much relational theory people would be familiar with, and put the question into a form that was harder than it could have been if presented in a different form. Multiple choice questions where you have to select several answers are inherently more difficult than multiple choice questions where you select only one answer (choosing 3 out of five means choosing one combination out of 10 possible combinations, choosing 1 out of five is easier than choosing 1 out of 10).I'm surprised that googling came up with nothing, though - it finds the Halpin,Morgan and Morgan book, Joe Celko's book, the Wikipedia normalisation article, Zaniolo's paper, and Scot Becker's rather silly (he parades his ignorance of why EKNF is important) paper on normalisation (Google gives a broken link to that one, but it's there in the cache - at least it is today) all on the first page of results when I try it - but maybe Google knows I'm interested in normalisation.Thu, 08 Dec 2011 05:20:58 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxI think that volcanic air is getting to you Tom, This question is way over most of our heads - a really hard question. No amount of googling seemed able to turn up any clues. And... well, once you see the answer its no easier to work it out. I got the not null part. And its probably not the primary key, (otherwise why call it an elementary key). The middle one long complex answer I chose this because it was long and complex - don't pretent to understand it even now, and random choice for the other one. Thu, 08 Dec 2011 02:44:11 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]paul s-306273 (10/12/2011)[/b][hr]I notice that we are now up to approimately 25% correct.How on earth did that happen?[/quote]I think John Arnott already answered that question in [url=http://www.sqlservercentral.com/Forums/FindPost1186881.aspx]this post[/url].Wed, 12 Oct 2011 13:18:13 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxI notice that we are now up to approimately 25% correct.How on earth did that happen?Wed, 12 Oct 2011 05:43:54 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]mtassin (10/7/2011)[/b][hr][quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]mtassin (10/5/2011)[/b][hr]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.[/quote]I find it amusing to see a claim that a seminal paper by the man who id now director of the UCLA Web Information System Lanboratory and holder of UCLA's N. E. Friedmann Chair in Knowledge Science, an associate editor of VLDB Journal, who has been program chair or general chair of VLDB (more than once), SIGMOD CMD, NACLo, NID, EDBT, and chaired HotSWUp in April this year should be dismissed as not necessarily mainstream. [/quote]Look, you've been drinking his Kool-aid. And you agree with him.But I also find no mention of EKNF on Wikipedia[url]http://en.wikipedia.org/wiki/Database_normalization[/url]I'm not saying it's an invalid concept, what I'm saying is that expecting people to know a branch of Normalization that isn't part of the maintstream, and then getting worried when only 2% of us know what the heck you're talking about, and you talking about it like it's the best thing since sliced bread is just silly.[/quote]Frankly I find these repeated kool-aid cracks repulsive. I usually try to be less bloody offensive than you seem to regard as the norm for serious technical debate - but of course maybe you don't think this topic is worthy of serious debate.Ok, maybe I understand where you are coming from. Your concept of what may be useful and relevant is what's in Date's textbook and consequently (since most people think Date walks on water) what's in Wikipedia (without regard to the talk pages, which of course ought to be an essential part of the discussion although you haven't looked there). I can easily change your view the wikipedia view by changing the page you reference (the inclusion of EKNF on the page you reference was suggested some time ago and has been supported by other comments on the talk page , with no adverse comments at all, so such a change would be perfectly reasonable). I can't change Date's text book, and he won't because he regards the representation principle as nonsense, in distinct conflict with, for example, Ron[ald] Fagin - whom you maybe regard as mainstream as he is responsible for the standard definitions of 4NF and 5NF - as made eminently clear by Date's very public attack on Fagin's DKNF suggestion - although I agree that RF got that one wrong, and CJD was right to challenge it, the style of the attack and some of its content (suggesting that attempting to achieve uany useful degree of representation was a waste of time) was appalling. You appear to be relying on an appeal to an "eminent authority" to suggest that anything you have not been taught by that "eminent authority" is irrelevant in our field of work. If that's the way you think, I have to accept that that's the way you are and give up on changing your mind - I just have to hope that that silliness doesn't influence other people who may read this discussion.Fri, 07 Oct 2011 19:25:36 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]mtassin (10/5/2011)[/b][hr]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.[/quote]I find it amusing to see a claim that a seminal paper by the man who id now director of the UCLA Web Information System Lanboratory and holder of UCLA's N. E. Friedmann Chair in Knowledge Science, an associate editor of VLDB Journal, who has been program chair or general chair of VLDB (more than once), SIGMOD CMD, NACLo, NID, EDBT, and chaired HotSWUp in April this year should be dismissed as not necessarily mainstream. [/quote]Look, you've been drinking his Kool-aid. And you agree with him.But I also find no mention of EKNF on Wikipedia[url]http://en.wikipedia.org/wiki/Database_normalization[/url]I'm not saying it's an invalid concept, what I'm saying is that expecting people to know a branch of Normalization that isn't part of the maintstream, and then getting worried when only 2% of us know what the heck you're talking about, and you talking about it like it's the best thing since sliced bread is just silly.Fri, 07 Oct 2011 08:01:26 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/7/2011)[/b][hr]Now that you mention it, that certainly looks like a possible explanation. Much more likely than a spontaneous improvement.[/quote]Yes, that and the fact that Steve added to the question the number of boxes that you have to tick.JohnFri, 07 Oct 2011 06:20:20 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]john.arnott (10/6/2011)[/b][hr][quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]I find it quite frightening that so few people know enough about normalisation to get this one right.[/quote]I feel much less frightened now: 22% out of 700 is a lot better than 2% out of 200.[/quote]I must be an inveterate cynic. I don't find it at all surprising that the percentage of sucess has risen so dramatically after the next day's newsletter included the correct answers. (never was frightened, though)[/quote]Now that you mention it, that certainly looks like a possible explanation. Much more likely than a spontaneous improvement.Fri, 07 Oct 2011 02:21:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]I find it quite frightening that so few people know enough about normalisation to get this one right.[/quote]I feel much less frightened now: 22% out of 700 is a lot better than 2% out of 200.[/quote]I must be an inveterate cynic. I don't find it at all surprising that the percentage of sucess has risen so dramatically after the next day's newsletter included the correct answers. (never was frightened, though)Thu, 06 Oct 2011 18:07:36 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]I find it quite frightening that so few people know enough about normalisation to get this one right.[/quote]I feel much less frightened now: 22% out of 700 is a lot better than 2% out of 200.Thu, 06 Oct 2011 17:51:54 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]Ernie Schlangen (10/6/2011)[/b][hr]I didn't look at the link you posted since you had already stated it was in something other than English. Even if I had, I know even less Scottish Gaelic (i.e. none and had to lookup gd to find out what language it referred to) than I do French (almost none)! All I was really trying to say was that the results I saw came from the www.google.com page in English and that my comment was based solely on this, which is not necessarily the same results that you were looking at (language aside).[/quote]OK, that's fair enough, you guessed the language from my current SQLSC nickname - but surely you know the layout of Google's front page well enough that it doesn't really matter what language "advanced search" and "I guess I'm lucky" (or whatever the English versions are) and so on are in, particularly since typing eknf and hitting enter is all you would need to do. Anyway, it seems unlikely that a search at encrypted.google.com should produce results different from the same search at www.google.com, and even less likely that my choice of google interface language should mean I get more results than someone who chooses English (I am pretty sure that there are no results in Scots Gaelic for eknf), and it was the claim that such a strange discrepancy did indeed exist that I was objecting to.Thu, 06 Oct 2011 17:33:59 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]john.arnott (10/6/2011)[/b][hr][quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]Ernie Schlangen (10/6/2011)[/b][hr] I make no comment on using the encrypted Google web page you cite in French, because I don't speak French and am apparently not so security conscious as to avoid the vanilla Google page.[/quote]In French??? Where on earth did that come from?Did I type "fr" instead of "gd"? Actually, I'm pretty sure I didn't because I think I just copied (copy and paste, just mouse clicks, no typing) my home page address![/quote]Too bad we can't all be as intelligent, learned and multilingual as we'd like. Considering that your current nom de post and signature lines certainly look to be in French, I'd guess that's where on earth from whence Ernie's confusion arose.[/quote]That is indeed the case. I didn't look at the link you posted since you had already stated it was in something other than English. Even if I had, I know even less Scottish Gaelic (i.e. none and had to lookup gd to find out what language it referred to) than I do French (almost none)! All I was really trying to say was that the results I saw came from the www.google.com page in English and that my comment was based solely on this, which is not necessarily the same results that you were looking at (language aside).Thu, 06 Oct 2011 15:37:02 GMTErnie Schlangenhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxIt looks like the encrypted Google page was in Gaelic.Thu, 06 Oct 2011 15:28:19 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]Ernie Schlangen (10/6/2011)[/b][hr] I make no comment on using the encrypted Google web page you cite in French, because I don't speak French and am apparently not so security conscious as to avoid the vanilla Google page.[/quote]In French??? Where on earth did that come from?Did I type "fr" instead of "gd"? Actually, I'm pretty sure I didn't because I think I just copied (copy and paste, just mouse clicks, no typing) my home page address![/quote]Too bad we can't all be as intelligent, learned and multilingual as we'd like. Considering that your current nom de post and signature lines certainly look to be in French, I'd guess that's where on earth from whence Ernie's confusion arose.Thu, 06 Oct 2011 15:01:25 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]Ernie Schlangen (10/6/2011)[/b][hr] I make no comment on using the encrypted Google web page you cite in French, because I don't speak French and am apparently not so security conscious as to avoid the vanilla Google page.[/quote]In French??? Where on earth did that come from?Did I type "fr" instead of "gd"? Actually, I'm pretty sure I didn't because I think I just copied (copy and paste, just mouse clicks, no typing) my home page address!Thu, 06 Oct 2011 13:44:19 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]mtassin (10/5/2011)[/b][hr]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.[/quote]I find it amusing to see a claim that a seminal paper by the man who id now director of the UCLA Web Information System Lanboratory and holder of UCLA's N. E. Friedmann Chair in Knowledge Science, an associate editor of VLDB Journal, who has been program chair or general chair of VLDB (more than once), SIGMOD CMD, NACLo, NID, EDBT, and chaired HotSWUp in April this year should be dismissed as not necessarily mainstream. Who cares what the commercial mainstream is, when it's people like Zaniolo who aid us in understanding where we are at? If Codd had been dismissed as "not mainstream" (and he certainly wasn't mainstream until at least 1980) we would have no relational database systems at all today.Of course it depends partly on what you think normalisation is about. There are two schools:One school believes that normalisation should be an attempt to prevent bugs - to ensure that the schema, through its constraints, enforces as far as possible the business rules that apply to the data, so that careless programming can't cause the data to reach a state in conflict with the business rules. That's my school.Another school believes that normalisation is about decomposing relations as far as is possible without losing information in the data (losing constraints that represent business rules is perfectly acceptable, since according to this school that information doesn't count as information). That's not my school. It is Chris Date's school, as is made claim by his comments on Fagin's 6NF (aka DKNF) (with which I happen to agree) when he wanted to call his own new form "6NF" (he succeeded, we all call Date's normal form 6NF now) so your referencing his textbook as if he were an eminent authority with no axe to grind pulls no weight at all; should I reference Carlo Zaniolo's equally weighty textbook? I think not, such references are pointless.A third way of looking at it is to believe that whatever is in popular textbooks, regardless of the scientific literature, is all of normalisation and representation theory. I can't justify attaching the title "school" to followers of that approach, so I can't ask if it's your school.Thu, 06 Oct 2011 13:39:04 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxThanks for the question Tom.Thu, 06 Oct 2011 08:29:56 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]mtassin (10/5/2011)[/b][hr]Another note.. go to www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.[/quote]I was pretty sure that was nonsense, but I decided to check anyway. I went to google (https://encrypted.google.com/webhp?hl=gd - I care about security so don't use the insecure www.google.com and of course want to work in my own language where possible, and google obligingly allows me to) and tried typing in eknf. Instant response (small fraction of a second) with about 45000 hits. That seems to me to confirm that what I thought was nonsense is indeed nonsense.[/quote]Ok so Google has decided that if I type in EKNF that I mean EKSF that should say something there as well.Thu, 06 Oct 2011 07:50:04 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxCan I blame it on the spelling mistakes? ;-)Thu, 06 Oct 2011 07:00:30 GMTdhugohttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/6/2011)[/b][hr][quote][b]mtassin (10/5/2011)[/b][hr]Another note.. go to www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.[/quote]I was pretty sure that was nonsense, but I decided to check anyway. I went to google (https://encrypted.google.com/webhp?hl=gd - I care about security so don't use the insecure www.google.com and of course want to work in my own language where possible, and google obligingly allows me to) and tried typing in eknf. Instant response (small fraction of a second) with about 45000 hits. That seems to me to confirm that what I thought was nonsense is indeed nonsense.[/quote]It is not nonsense. When I look them up on google.com in English, which is what was posted, I get some hits for EKNF but almost all of them are acronym lists with no actual information available. (A notable exception is Mr. Celko's book which is now probably going to be added to my stack of books I never seem to have time to read.) However, I get tons of actual information on BCNF when googling it. I make no comment on using the encrypted Google web page you cite in French, because I don't speak French and am apparently not so security conscious as to avoid the vanilla Google page.Thu, 06 Oct 2011 06:35:58 GMTErnie Schlangenhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxTough question, I really had to think through this one.Thu, 06 Oct 2011 05:40:03 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]mtassin (10/5/2011)[/b][hr]Another note.. go to www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.[/quote]I was pretty sure that was nonsense, but I decided to check anyway. I went to google (https://encrypted.google.com/webhp?hl=gd - I care about security so don't use the insecure www.google.com and of course want to work in my own language where possible, and google obligingly allows me to) and tried typing in eknf. Instant response (small fraction of a second) with about 45000 hits. That seems to me to confirm that what I thought was nonsense is indeed nonsense.Thu, 06 Oct 2011 04:24:52 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]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.[/quote]TomI think using the word "column" instead of "attribute" would be a start.I've had a look at Sgr Zaniolo's paper and my eyes glazed over when I tried to understand the definitions of "functional dependency" and "elementary key". The fact that there is no other term for the latter suggests that we're deep into the realm of arcane theory. Not that there's anything wrong with that - I'm just surprised at your surprise that more people don't understand it fully enough to be able to answer the question.I certainly look forward to your article - especially if it goes any way towards demystifying all this theory!JohnThu, 06 Oct 2011 03:46:47 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxGood questions Tom - only got 2 out of 3 - still learnt (again) todayThu, 06 Oct 2011 00:48:07 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxDifficult one. Thanks for the question.Thu, 06 Oct 2011 00:41:35 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxWell, 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. :-)Wed, 05 Oct 2011 14:22:20 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]mtassin (10/5/2011)[/b][hr] 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 www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.[/quote]Thank You Mark. :cool:Double thanks for drinking the Koolaid, and telling us what the flavors are. :hehe:Wed, 05 Oct 2011 13:19:38 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxThis 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.Wed, 05 Oct 2011 13:06:35 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]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.[/quote]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[url]http://www.amazon.com/Database-Processing-12th-David-Kroenke/dp/0132145375/ref=sr_1_3?ie=UTF8&qid=1317839521&sr=8-3[/url] Granted that was the 8th, 9th, and 10th editions instead of the 12thNor is it in here[url]http://www.amazon.com/Introduction-Database-Systems-8th/dp/0321197844/ref=sr_1_1?s=books&ie=UTF8&qid=1317839685&sr=1-1[/url] 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 www.google.com and type in EKNF and BCNF. I know which one you'll get an immediate hit on.Wed, 05 Oct 2011 12:35:42 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]Chris Harshman (10/5/2011)[/b][hr][quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]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.[/quote]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.[/quote]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.Wed, 05 Oct 2011 11:00:28 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]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.[/quote]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.Wed, 05 Oct 2011 10:35:35 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxdefinitely, i learned something today!!!thanks, Tom!!! this kind of question is very importance to not forget the concepts of databases!Wed, 05 Oct 2011 10:29:02 GMTrfr.ferrarihttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxDOH! 2 out of 3 so needed the question to re-educate/fill in the gaps.Good question Tom...Wed, 05 Oct 2011 10:21:44 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]Rob Schripsema (10/5/2011)[/b][hr]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?[/quote]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).Wed, 05 Oct 2011 10:20:29 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspxNice 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. :cool:Wed, 05 Oct 2011 10:01:47 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]John Mitchell-245523 (10/5/2011)[/b][hr][quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]I find it quite frightening that so few people know enough about normalisation to get this one right.[/quote]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[/quote]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.Wed, 05 Oct 2011 09:53:16 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]hodgy (10/5/2011)[/b][hr]Ah, I was researching elemenTARTY keys, probably why I got it wrong ;-)[/quote]I saw that too and looked for one of the choices to be "A must equal a multiple of 3.14159265358979323846264338327950288419716939937510".:-)Wed, 05 Oct 2011 09:50:39 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]paul s-306273 (10/5/2011)[/b][hr]Not quite sure of the relevance of this question.(Or is that just sour grapes - 2correct answers, 118 incorrect answers so far!)[/quote]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.Wed, 05 Oct 2011 09:44:57 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1185646-2681-1.aspx[quote][b]John Mitchell-245523 (10/5/2011)[/b][hr][quote][b]L' Eomot Inversé (10/5/2011)[/b][hr]I find it quite frightening that so few people know enough about normalisation to get this one right.[/quote]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[/quote]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.Wed, 05 Oct 2011 09:43:15 GMTSean Lange