Advanced SQL
LuchoLaf
most of it. Now I want to learn advanced topics like pivoting, views,
etc. Is there any good resource or books about advanced SQL? Most
resources I found in the web are too basic.
Thanks,
Luis
Evan Keel
"LuchoLaf" <luch...@gmail.com> wrote in message
news:1189080047.7...@57g2000hsv.googlegroups.com...
SQL Cookbook by Anthony Molinaro: http://tinyurl.com/2pgvem
Evan
David Cressey
section called, "Turning a Table on its side". This demonstrates the basics
of pivoting.
I hardly think of views as an "advanced topic". But you're entitled to your
opinion.
There are two webistes I can recommend:
http://www.utexas.edu/its-archive/windows/database/datamodeling/dm/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.
Evan Keel
"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:pZSdnfih_oR...@giganews.com...
> "David Cressey" <cres...@verizon.net> wrote in message
> news:HBTDi.14699$tB2.1005@trndny05...
> >
> > There are two webistes I can recommend:
> >
> >
http://www.utexas.edu/its-archive/windows/database/datamodeling/dm/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.
> >
> >
> >
>
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/database/datamodeling/rm/overview.html
> http://www.utexas.edu/its/windows/database/datamodeling/rm/rm1.html
> http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html
> http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html
>
> --
> David Portas
>
>
David Cressey
> 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"
>
I'm not entirely comfortable with it myself, and I had found some of the
same things you quoted. I still recommend it. I'm looking for something
that's better. when I find it, I'll recommend that.
Jon Heggland
>> even though it's a commendable effort overall.
>>
>> 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. 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.
--
Jon
Jan Hidders
> >news:pZSdnfih_oR...@giganews.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.
> 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
David Cressey
"Jan Hidders" <hid...@gmail.com> wrote in message
news:1189173585....@19g2000hsx.googlegroups.com...
> On 7 sep, 15:43, Jon Heggland <jon.heggl...@idi.ntnu.no> wrote:
> > Quoth Evan Keel:
> >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote in
message
> > >news:pZSdnfih_oR...@giganews.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?
Brian Selzer
"Jan Hidders" <hid...@gmail.com> wrote in message
news:1189173585....@19g2000hsx.googlegroups.com...
>> Quoth Evan Keel:
>>
>> > "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote in
>> > message
>> >news:pZSdnfih_oR...@giganews.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
> "Jan Hidders" <hidd...@gmail.com> wrote in message
Indeed, I stand corrected.
-- Jan Hidders
Jan Hidders
> "Jan Hidders" <hidd...@gmail.com> wrote in message
>
> news:1189173585....@19g2000hsx.googlegroups.com...
>
>
>
> > On 7 sep, 15:43, Jon Heggland <jon.heggl...@idi.ntnu.no> wrote:
> > > Quoth Evan Keel:
>
> > > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote in
> message
> > > >news:pZSdnfih_oR...@giganews.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?
Yes. But as Brian correctly pointed out, what I said in the above was
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
David Portas
news:Np%Di.5257$JD....@newssvr21.news.prodigy.net...
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.
--
David Portas
David Cressey
repeatedly as if the primary key were the only key under consideration.
Indeed, when I first learned normalization, I learned it this wrong way.
Not from this website, but from another source.
> --
> David Portas
>
>