 | |
#1
 
| |||
| |||
|
Re: Advanced SQL -
09-07-2007
, 12:13 AM
|
Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The relational model represents data in the form of two-dimension tables" "A relational table is a flat file ..." "Primary and foreign keys are the most basic components on which relational theory is based." "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" "The third normal form requires that all columns in a relational table are dependent only upon the primary key" |
same things you quoted. I still recommend it. I'm looking for something
that's better. when I find it, I'll recommend that.
#2
| |||
| |||
|
|
"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote in message news  ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com...Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns |
common mistake. If I remember correctly, one of my database textbooks
"proves" that any relvar with just two attributes is automatically in BCNF.
--
Jon
#3
| |||
| |||
|
|
Quoth Evan Keel: "David Portas" <REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com...Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns No. There may be dependencies where the left side is empty. |
key. Under the assumption that the declared candidate keys are indeed
candidate keys, the claim is correct.
Quote:
|
But it's a common mistake. If I remember correctly, one of my database textbooks "proves" that any relvar with just two attributes is automatically in BCNF. |
also correct.
-- Jan Hidders
#4
| |||
| |||
|
|
On 7 sep, 15:43, Jon Heggland <jon.heggl... (AT) idi (DOT) ntnu.no> wrote: Quoth Evan Keel: "David Portas" <REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com...Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns No. There may be dependencies where the left side is empty. In that case the declared candidate key is not really a candidate key. Under the assumption that the declared candidate keys are indeed candidate keys, the claim is correct. |
where the left side is empty, wouldn't the same value have to exist the
dependent column, for all of the rows?
Quote:
|
But it's a common mistake. If I remember correctly, one of my database textbooks "proves" that any relvar with just two attributes is automatically in BCNF. Under the assumption that I just mentioned, that claim is actually also correct. -- Jan Hidders |
#5
| |||
| |||
|
|
On 7 sep, 15:43, Jon Heggland <jon.heggl... (AT) idi (DOT) ntnu.no> wrote: Quoth Evan Keel: "David Portas" <REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com...Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns No. There may be dependencies where the left side is empty. In that case the declared candidate key is not really a candidate key. Under the assumption that the declared candidate keys are indeed candidate keys, the claim is correct. |
{A, B, C} | A --> B /\ {} --> C
If {} --> C holds, then A --> C also holds.
Since A --> B /\ A --> C, then A --> BC.
{{A:1, B:2, C:1}
{A:2, B:4, C:1}
{A:3, B:4, C:1}
{A:4, B:3, C:1}}
Clearly A is the only candidate key, even though the FD {} --> C holds.
Quote:
|
But it's a common mistake. If I remember correctly, one of my database textbooks "proves" that any relvar with just two attributes is automatically in BCNF. Under the assumption that I just mentioned, that claim is actually also correct. -- Jan Hidders |
#6
| |||
| |||
|
|
"Jan Hidders" <hidd... (AT) gmail (DOT) com> wrote in message news:1189173585.627344.49900 (AT) 19g2000hsx (DOT) googlegroups.com... On 7 sep, 15:43, Jon Heggland <jon.heggl... (AT) idi (DOT) ntnu.no> wrote: Quoth Evan Keel: "David Portas" <REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com...Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns No. There may be dependencies where the left side is empty. In that case the declared candidate key is not really a candidate key. Under the assumption that the declared candidate keys are indeed candidate keys, the claim is correct. That's not true, Jan, consider: {A, B, C} | A --> B /\ {} --> C If {} --> C holds, then A --> C also holds. Since A --> B /\ A --> C, then A --> BC. {{A:1, B:2, C:1} {A:2, B:4, C:1} {A:3, B:4, C:1} {A:4, B:3, C:1}} Clearly A is the only candidate key, even though the FD {} --> C holds. |
-- Jan Hidders
#7
| |||
| |||
|
|
"Jan Hidders" <hidd... (AT) gmail (DOT) com> wrote in message news:1189173585.627344.49900 (AT) 19g2000hsx (DOT) googlegroups.com... On 7 sep, 15:43, Jon Heggland <jon.heggl... (AT) idi (DOT) ntnu.no> wrote: Quoth Evan Keel: "David Portas" <REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com...Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns No. There may be dependencies where the left side is empty. In that case the declared candidate key is not really a candidate key. Under the assumption that the declared candidate keys are indeed candidate keys, the claim is correct. I'm really confused by the above. First off, if there were a dependency where the left side is empty, wouldn't the same value have to exist the dependent column, for all of the rows? |
wrong. In fact my remark about binary relations in BCNF is also not
correct. For example R(A,B) with FD {}-->B is not in BCNF. Not my day,
apparently. :-(
-- Jan Hidders
#8
| |||
| |||
|
|
"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com..."David Cressey" <cressey73 (AT) verizon (DOT) net> wrote in message news:HBTDi.14699$tB2.1005 (AT) trndny05 (DOT) .. There are two webistes I can recommend: http://www.utexas.edu/its-archive/wi.../overview.html For an overview to data modeling and database design. and http://www.databaseanswers.org/ For about 200 free database designs, covering many common problems. You will want to revise the design to suit your own requirements. Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The relational model represents data in the form of two-dimension tables" "A relational table is a flat file ..." "Primary and foreign keys are the most basic components on which relational theory is based." "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns "The third normal form requires that all columns in a relational table are dependent only upon the primary key" They have it right again. No transitive dependecies. So the problem? Oh yes, remove the candidate key in this test. http://www.utexas.edu/its/windows/da.../overview.html http://www.utexas.edu/its/windows/da...ng/rm/rm1.html http://www.utexas.edu/its/windows/da...g/dm/keys.html http://www.utexas.edu/its/windows/da...ng/rm/rm7.html -- David Portas |
2NF.
Normalization is concerned with candidate keys not just primary keys, so the
definitions are necessary conditions but not sufficient ones.
--
David Portas
#9
| |||
| |||
|
|
"Evan Keel" <evankeel (AT) sbcglobal (DOT) net> wrote in message news:Np%Di.5257$JD.656 (AT) newssvr21 (DOT) news.prodigy.net... "David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote in message news ZSdnfih_oRy5H3bRVnyigA (AT) giganews (DOT) com..."David Cressey" <cressey73 (AT) verizon (DOT) net> wrote in message news:HBTDi.14699$tB2.1005 (AT) trndny05 (DOT) .. There are two webistes I can recommend: http://www.utexas.edu/its-archive/wi.../overview.html For an overview to data modeling and database design. and http://www.databaseanswers.org/ For about 200 free database designs, covering many common problems. You will want to revise the design to suit your own requirements. Call me picky but I am not entirely comfortable with that U.Texas site, even though it's a commendable effort overall. "The relational model represents data in the form of two-dimension tables" "A relational table is a flat file ..." "Primary and foreign keys are the most basic components on which relational theory is based." "The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF" They have it right here. All non-key attributes must be dependent on the full key. Only applies to tables with keys composed of multiple columns "The third normal form requires that all columns in a relational table are dependent only upon the primary key" They have it right again. No transitive dependecies. So the problem? Oh yes, remove the candidate key in this test. http://www.utexas.edu/its/windows/da.../overview.html http://www.utexas.edu/its/windows/da...ng/rm/rm1.html http://www.utexas.edu/its/windows/da...g/dm/keys.html http://www.utexas.edu/its/windows/da...ng/rm/rm7.html -- David Portas The case already mentioned by Jon is one exception to the statement about 2NF. Normalization is concerned with candidate keys not just primary keys, so the definitions are necessary conditions but not sufficient ones. Yes. You will note, IIRC, that the website discusses normalization |
Indeed, when I first learned normalization, I learned it this wrong way.
Not from this website, but from another source.
Quote:
|
-- David Portas |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.