 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / DB Theory / September 2007Advanced SQL
Hi, I been working with SQL for a while and think I can handle with 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 > Hi, I been working with SQL for a while and think I can handle with > most of it. Now I want to learn advanced topics like pivoting, views, [quoted text clipped - 4 lines] > > Luis SQL Cookbook by Anthony Molinaro: http://tinyurl.com/2pgvem Evan > Hi, I been working with SQL for a while and think I can handle with > most of it. Now I want to learn advanced topics like pivoting, views, [quoted text clipped - 4 lines] > > Luis If you have a copy of "Oracle:the Complete Reference" you will find a 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. > There are two webistes I can recommend: > [quoted text clipped - 8 lines] > 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" "The third normal form requires that all columns in a relational table are dependent only upon the primary key" 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  SignatureDavid Portas > > 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. > > [quoted text clipped - 14 lines] > "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 Quoth Evan Keel: >> Call me picky but I am not entirely comfortable with that U.Texas site, >> even though it's a commendable effort overall. [quoted text clipped - 4 lines] > 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. SignatureJon > Quoth Evan Keel: > [quoted text clipped - 8 lines] > > 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 > > Quoth Evan Keel: > > [quoted text clipped - 12 lines] > 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? > > But it's a > > common mistake. If I remember correctly, one of my database textbooks [quoted text clipped - 4 lines] > > -- Jan Hidders > > > Quoth Evan Keel: > [quoted text clipped - 22 lines] > 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 >> Quoth Evan Keel: >> [quoted text clipped - 15 lines] > 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. >> But it's a >> common mistake. If I remember correctly, one of my database textbooks [quoted text clipped - 5 lines] > > -- Jan Hidders > >> Quoth Evan Keel: > [quoted text clipped - 29 lines] > > Clearly A is the only candidate key, even though the FD {} --> C holds. Indeed, I stand corrected. -- Jan Hidders >> > There are two webistes I can recommend: >> > [quoted text clipped - 39 lines] >> http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html >> http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html 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. SignatureDavid Portas > >> > 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. > >> > [quoted text clipped - 30 lines] > > 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 [quoted text clipped - 5 lines] > 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 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. > Call me picky but I am not entirely comfortable with that U.Texas site, even > though it's a commendable effort overall. [quoted text clipped - 7 lines] > "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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.