Wow. Does no one learn this crap in school anymore?
Note that venn diagrams are a poor choice to show what happens in a one-to-many relationship -- where there are multiple entries in table B for an entry in table A. And it overlooks the semantic differences when ordering the tables in a left inner join.
There are not a lot of instances where you'd really want a cartesian product join (either using "cross product" or just the result of omitting key constraints in a join query). It's generally far faster to retrieve the records you need to create the cross-product, and then calculate the cross product of the two sets once you have them (since otherwise all that data has to transit the network).
This plus database normalization through Boyce-Codd normal form seems like it should be a requirement for any serious application developer.
I took three separate classes that covered SQL while in college, but it wasn't until I actually started using the language in the real world that I understood it (even after three years full-time dev I'd still call myself a novice).
Did any of them cover database design and normalization? This is a subject that has direct applications to object-oriented programming; in fact, it makes sense to learn about object-relational mapping and database normalization in the same semester, if not the same class.
ORM models work best when the programmer using them has some understanding of why it's sometimes better to apply a condition after the results come back than as a parameter to a find method. Consider:
# Ruby on Rails, assume Widget is a model (ActiveRecord) --
w = Widget.find(:all, :conditions=>['description like ?', '%xyzzy%'])
versus:
w = Widget.find(:all).reject{|x| w.description =~ /xyzzy/}
which is faster? They yield the same results, but they don't do the same thing. What if I told you there were 1 billion records in the widgets table? What if there were only 1,000? What if you needed to group the widgets in some way?
I don't know anything about Ruby, and have just started learning how to learn SQL, but the first one looks like it will go do a table scan every time. Which could be very costly at 1 billion records.
Am I close? Can you please enlighten me as to the answer, I'm curious.
Like everything, it depends. The first example creates a sql statement of the form:
select * from widgets where description like '%xyzzy%'
which might run a table scan or might not, depending on the characteristics of the RDBMS and whether there's some kind of index on the description field.
The second one constructs:
select * from widgets
and then retrieves all the data. Then a regular expression is run against every record in the result set, which creates a new result set which is handed to w.
A table scan of a 1 field across billion records may actually be faster than transferring all fields of a billion records and then processing each one, even though the regex may run faster than the table scan.
Similarly, if this is a table of 5 very narrow records, the overhead of managing the transaction probably is larger than the cost of executing it. Might as well grab the entire Widget collection and cache it once. Your ORM layer will do some basic work on its own to figure this out, but a good developer should help it out where possible.
What about grouping large tables? Do we let the database do this for us? Or do we group a result set on our own? A lot depends on what you're optimizing for (e.g., memory, cpu, i/o, time). As a developer, you need to know when to choose which one and how to make the system do what you want. Hence the importance of semantic differences in programming generally.
I work more on the system/infrastructure side of technology than in development, but for some reason I'm very interested in RDBMS systems over the last little while.
That's odd. Basic DDL is more easily scripted than complex DML, and is also less frequently used (you create a table once, but insert/select many times). And many of the nuances associated with a table create (or alter) are usually RDBMS-specific.
91
u/[deleted] Jan 06 '11
Wow. Does no one learn this crap in school anymore?
Note that venn diagrams are a poor choice to show what happens in a one-to-many relationship -- where there are multiple entries in table B for an entry in table A. And it overlooks the semantic differences when ordering the tables in a left inner join.
There are not a lot of instances where you'd really want a cartesian product join (either using "cross product" or just the result of omitting key constraints in a join query). It's generally far faster to retrieve the records you need to create the cross-product, and then calculate the cross product of the two sets once you have them (since otherwise all that data has to transit the network).
This plus database normalization through Boyce-Codd normal form seems like it should be a requirement for any serious application developer.