Re: SQL programming fundamentals

I've had several opportunities to privately discuss this topic with Peter, and <shameless plug>two books to express myself on the place I see for theory in practical life</shameless plug>, but I rather see theory as ambient light than a practical guide. What does the theory say about indexing, partitioning, clustering? Nothing, and yet we all know how much it matters - to say nothing of statistics. However, thinking "sets" as Harshan first mentioned is vital (seen not long ago: a 30,000 row table storing a hierarchy, that was traversed by a cursor with a condition involving a call to a function counting the number of descendants of the current row - the aim was to find all leaves. Hum, what about a self outer join to find all leaves at once?), and when, as Michael underlined, the database design is rotten to start with, having an idea of what it should be is more than helpful, unless you are adept at trying hints at random. When a table doesn't respect the first normal form, one of the first thing I check is whether pivoting it inside an inline view to make it *look* in 1st NF wouldn't help. It often does when the table isn't too big and scanning it makes sense. And I have often improved query through logic alone. I don't think that being groomed in Tutorial D is much helpful; but I would feel much better if more developers could read short books such as Date's "Database in Depth" or Pascal's "Practical Issues in Database Management", none of which is very theoretical but which define a convenient framework for thinking. Theory is like grammar; you need some, knowing too little is dangerous (single unique column a sequence-generated ID, anyone?), sometimes you have to violate it, grammar alone will not make you a great writer, and some feel it more instinctively than others. 

My EUR 0.02.

S Faroult

Hemant K Chitale wrote:
"Should one distrust any SQL code written by someone without a sound knowledge of relational theory?" and "whether the ability to think in this fashion is in any way dependent on an understanding of relational theory".
I think not. When I see good code or bad code, I do not ask the developer if he understands relational "theory". I do not care to know if his academic background is in mathematics or accountancy or any of the "non sciences". It is quitelylikely -- and I strongly believe that it IS true -- that people can understand tables and data without formal training in relational theory. 

Hemant K Chitale
http://hemantoracledba.blogspot.com


At 03:44 AM Saturday, Peter Robson wrote:

Thanks to you all for many interesting and illuminating contributions.

Two points stand out.
First the issue of 'thinking' in terms of sets when working with SQL, of achieving the mind-set jump from the simple procedural approach to the set based. It happened so long ago for me that I had in fact forgotten just how significant it is!
Which begs the question of whether the ability to think in this fashion is in any way dependent on an understanding of relational theory. Do you think it is?
Second, and something of a corollary to that latter point, is this. Should one distrust any SQL code written by someone without a sound knowledge of relational theory? The implications of an affirmative to this question are rather considerable, I would have thought... nevertheless, your own comments would be much valued! 


Peter



Hemant K Chitale

--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l

Other related posts: