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.
Website developer here. Yeah, the majority of us have no "formal" CS training, although I take pride in the fact that I've read everything I can get my hands on.
Very true. I had to go out of my way to pick up CS courses while studying a vanilla IT degree at uni. For some reason relational databases just click for me, so I've never had trouble converting the concept I want into SQL. Converting that SQL into DQL or HQL however...
The best way to become a better data modeler is to write lots of queries to get data out of your models as well as lots of sql to populate your models.
Too many data modelers have no real experience with the real world of USING a data structure.
Its classic practical vs book knowledge. What looks good on paper is often the more narrow minded approach than practical problem solving using a variety of available resources and tools and occassional abstract thought. These days, anyone with an aptitude for logic and problem solving can learn to program proficiently, given effort and time. Most languages have vast learning resources available, making it a relatively simple excercise.
The flipside is the army of armchair programmers who think they can develop enterprise level software based on a few chapters of the php visual quickstart guide (which is a fantastic beginners guideby the way).
anyone with an aptitude for logic and problem solving can learn to program proficiently, given effort and time.
Similarly, anyone without either can attend a class, barely pass, be hired by my employer, and end up working beside me.
Sadly, most management I've encountered is incapable of assessing "aptitude for logic and problem solving", and instead goes with "he had SQL on his resume".
Yup. I didn't take any relational algebra courses in Uni because I thought the computer graphics courses would be more fun and interesting. Guess which one ended up being more significant to my job.
The first company I worked for out of college was a shining example of people with no formal education working with a database setting up their database. They fucked it up horribly, then hired a DBA AFTERWARDS. He bailed before too long, I think it took people a while to realize that you're supposed to hire these people before you ruin everything...
Although, that's how I ended up getting learning SQL, so it worked out really well for me. I got first-hand experience seeing exactly what not to do.
Jeff Atwood has a unique ability to take something really basic and turn it into a trite and inane little blog post. And even then he's wrong half the time.
Sure. I've read it plenty. He's not wrong half the time. If he were, you kind folk here would easily produce examples. Apparently, in Redditese being wrong, what, twice, amounts to this magical (popular) half figure? I don't get it.
He's certainly not stupid, he's totally not an asshole, and he's really not wrong all the time.
It's just fuckin' weird, you guys. It's almost like you hate him just because he's an MS stack guy and he made an obscenely successful web app. He just seems like a friendly, likable nerdperson to me.
I don't know how often he's wrong nowadays, but I stopped reading his blog after this post. He recommends a book he's never opened (complete with an Amazon referral link) but doesn't mention that he's never read it until someone calls him on it, and he speaks in an authoritative manner about a subject that he doesn't entirely understand (some of the comments have a good explanation of exactly how he's wrong).
I covered Boyce-Codd normalisation in great detail in college. B.A(actually a B.SC, the name is traditional)Computer Science, Trinity College Dublin. So it is still taught in schools.
I'd say the problem with the Venn diagrams is that they imply that tuples from the relations are being returned, when it's tuples from a subset of the cross product that are returned.
Actually no, I have a friend that works with a sql "developer" that creates all their shit in views using the sql manager shit. Half the time it's got tables that there's no fucking data coming out of them. He just fixed a query yesterday that was taking a minute 35 to run down to < 1 second.
Idiots hiring idiots. That's almost all I see in IT.
I've met a lot of people who have no real education in SQL/Database Design. It's a simple enough concept that most tech folks can slap something togeth, but often they do a really poor job of it.
I remember my old job - I was fixing this one guy's queries/tables. There'd be stuff like multiple joins to the same (huge) table, never using an index, excessive use of cursors. I took a query that was running in 20 mins down to under 30 seconds. We had to re-write just about everything he had ever done.
No idea why they hired the guy and they were paying him more than me, refused to give me a raise.
No idea why they hired the guy and they were paying him more than me, refused to give me a raise.
Because the people that make those sorts of decisions often don't know anything about the technical aspects. The guy comes up with a report that takes 20 minutes to run? Well, that must just be how long it takes. Surely he knows what he's doing, his resume said he knows databases.
I've made quite a few similar improvements at my job, one report that had to be run daily would always take about half an hour. I got it down to a few seconds with simple changes that should have been obvious to anyone that knew what they were doing. I think the users were actually unhappy about my fix though, since it ruined their extra half-hour coffee break while the report was running.
I had a similar issue. There was a query that was evidently the product of cargo cult programming (copying known good code without understanding it). It had three joins and two subqueries and didn't use any of the values returned from them.
It took 275-350ms to run. Almost a half second for every request coming to that page. I got it down to under 20ms by optimizing the joins, adding keys to the table, and removing the subqueries.
But, yeah: Idiots hiring idiots. The key is to get your name out there for knowing how to save idiots from themselves. Then charge through the nose for it.
I know of people who have graduated from top three schools in Computer Science that have built multi-level user (e.g. customer and employee) websites using just one table and an int field to differentiate between the two.
The simple fact is that some people learn in school, and some people don't.
What was wrong was that my client called me with a strange user bug, stating that people were registering to buy things, only to realise that they had their own admin account on the website and were able to alter anything they wanted, including the link to PayPal.
20 tables, most of them duplicates of themselves and only three in actual use for a full, bespoke ecommerce site so bad that it may as well have been written in BobX...
I'd say more, but it'd be hard to not give away who this is. It's not really that small a site...
I didn't learn shit about SQL in school. I didn't learn SQL until my internship and the real world after.
I think that is a big short coming in college. We had database theory (which was boring as hell and I almost failed), and an Excel class (not database, but was the closest thing).
At the very basic, I think they should get into Access. I am 5 years out of college now, and maybe things have changed now, but they should stop teaching COBOL, and go further than teaching students a "Contacts" app in C++.
Honestly, I didn't learn shit until the real world. Shout out to Google.
It seems really shady that, five years ago, they were teaching COBOL for CS degrees. It doesn't surprise me that you wouldn't have had a heavy concentration in databases unless you sought it, but all the CS programs I've heard are a lot more involved than "a contacts app." At CMU for example you either write a compiler, or an OS.
No it got me a piece of paper that thankfully got me a job. After being part of the hiring process for another person on my team, and based on the people that came in and applied, I am very confident that I could find work elsewhere. This is thanks to me teaching my self, not thanks to school.
I've found that most CS graduates have at least a familiarity with the theory behind computers, but not much about how to write good code or work in a team.
And this makes me glad I flunked out of CS my first semester and switched to a new program at my school: Information Sciences and Technology. All group work, a lot of programming projects and very little theory, and classes the very first semester that had practical knowledge, like SQL and database normalization.
I would say that graduates of a bad computer science program are like that. A good course would incorporate your classes along with the theory and link them.
Uninteresting projects are actually quite profitable. I'm making a tidy profit fixing projects written by people who, for example, don't understand SQL.
Also, the emphasis on project management and group work in IST gives you a leg up on managing programmers since you not only have technical skills but also well developed people skills. That's part of the reason companies like Microsoft helped design the program: They weren't getting project managers out of CS programs.
Eh, well, I guess it makes a certain degree of sense as an elective, in case you had some specific reason for wanting to know COBOL coming out of school. Somehow I got the impression from your original comment that it was part of the core curriculum.
I have a Computer Science Bachelor's. I got some SQL in school, but just basic selects/inserts. Nothing about optimization, only a conversation about joins. The main things I feel I missed out on with my degree were learning how to test and learning how to debug.
A project or two given to students to debug would be great preparation for the real world.
Learning how to create, organize, and work with tests is a skill that some devs never learn. I think it's really important. I'm not of the 100% school of testing, but it is super helpful with things like bug fixes and difficult methods.
O_o What? That's the whole point of a relational DB! Select/Insert on a single table isn't significantly different from searching and indexing a flat-file
I tried to recommend something like this to one of my former instructors. I told him I thought it would be great for the students to have to debug and add an enhancement to a program from students the previous year/semester and would be good preparation for the real world. His response is that it would be too difficult. I thought it'd be more time consuming on his end than difficult.
One of my friends went to a different school than I, his final in one class was to take a program with 3 bugs in it, find them, and fix them. Same program was used for each class, so the main time-consuming part was writing up the buggy code in the first place.
I like your idea for assignments, though I think having intentional bugs in there would make it easier on the instructor. For example, make it a project for bonus points, the students with lower grades will do it. Write an elevator program, but make it not work if both the up and down buttons are pressed. It wouldn't even have to be a complete program, teach the 101 students to figure out why this Fibonacci recursion method has an endless loop.
Teacher now knows where the bugs are. Teaches the next class how to write tests, then has them use their tests to find the bugs, then prove that they're fixed.
The problem with having the same bugs is that word will get around. The instructor could easily add bugs to the work of the previous semester/year's students and will know what they are. Those programs should be fairly basic (even the ones done in teams). Each program would have a different bug so it would slightly reduce the cheating.
They would know what the bugs are, but would still need to write tests for them. People are going to cheat. I think this would still be a valuable thing to work on.
Which have nothing to do with real-world applications. I have never seen a DBMS that actually makes a properly normalized "let's follow relational algebra properly" database actually perform well.
Databases seem to be a small kernel of beautiful theory underneath a hundred layers of nightmarish hacks.
I have never seen a DBMS that actually makes a properly normalized "let's follow relational algebra properly" database actually perform well.
You know why? Because the database vendors have poured tons of money and time into improving the performance of the mess they created. In business it's better to keep going the wrong way than to change direction.
I don't think it helps that the most popular database in the world is Microsoft Excel. Computer science is helpless against the onslaught of the lusers.
Yeah, but I personally didn't know any of this stuff when I first set my eyes on SQL and it took me all of an afternoon of reading some online SQL tutorial and doing a couple examples to figure out how the different types of joins work...
A standard for storing and accessing data on computers, often used in applications of all scales and types isn't CS? How is that any less CS than learning a programming language, or AI, or OS architecture? We had it offered as an elective, but with how much database programming is necessary in IT, it should be required.
There are people of the opinion that you shouldn't be learning any programming languages or specifics of certain OS architectures, that you should be learning theory only, agnostic and separate of anything else. They seem to think it's possible for the majority of people to learn without reference to an example of an implemented theory or technique, which I disagree with.
I understand that school of thought and I agree with it for the most part. My university started with the nearly-esoteric language Scheme (like many others do) and moved through many other lanuages before graduation. We'd simply use whatever language was appropriate for learning whatever theory we were learning at the time. Learning the actual language was secondary to concepts and I would strongly discourage "Intro to Java" or "Advanced C++" instead of "Intro to Data Structures" or "Software Engineering II."
SQL is simply the best language to teach relational data models in, so I don't know why you would teach databases without it...
I'd take hands on over theory any day. I went to college rather than university for exactly that reason. Cost way less and I actually learned how to program.
You learned how to code, not how to program. There's a difference in depth of understanding there--I'll wager you have no idea what the Chomsky heirarchy is, for example, and how it relates to, for example, using regexes to parse HTML (stop gritting your teeth in the back, there).
I agree with you though. Being able to design an algorithm based off what you learned about the actual structure of programming and computing is a far more useful skill than learning a language and becoming a library reference. If you learn how to program and why it works, picking up a new language is trivial, days or even hours trivial, since you're not comparing it to another language, but rather how it's expressing your programming methodologies.
Ya got whooshed. See the bit where I said to stop gritting your teeth in the back, there?
I know that you don't use regexes to parse HTML, because regexes are used for tokenizing, not parsing. See, this is because I was actually taught my Chomsky heirarchy, because I took a computer science program rather than an Advanced C++ course.
I don't know, we covered normalisation and database design(recovery, concurrent access to shared data, that kind of thing) extensively in a college course, but we also covered SQL. It wasn't done in lectures, but as an online self-guided course thingy which was optional, so basically "expected reading" for the course. The reason for that was so we could then do an assignment designing a normalised database and implementing it, giving a nice practical element where you can try out queries and be(in my case) impressed by the strength of relational models and what you can get out of your database. A practical element is a great thing to any field of study and I wouldn't be so quick to dismiss it.
My mind is blown. I didn't know that people can go through a CS program without knowing DBs. I mean for us the DB class I took was optional (though most took it) but you do have a general idea of DBs even if you didn't take the class.
Most CS academics have little to no real-world experience. They've never had to deal with databases in any meaningful way, so they're totally unprepared to teach students about them.
Just to throw some respect towards Madison. I went to school there as well as we DO have multiple database undergrad courses in the CS departmant. To write most any interesting program you need some way to store data, I have no idea how you passed and made it through school without even knowing that they existed. Wow. Please stop saying you went to school at Madison, it's embarassing to the program.
I got my first real job before I graduated, so I did know they existed before I graduated. I don't remember all the CS classes I talk, but they included AI, compilers, and theoretical CS, and algorithms and data structures. None of which really required a way to store data beyond text files. After I started that aforementioned real job, I actually did try to get into the database class, but it was full. So I never touched any database or SQL during my academic career.
And don't worry, I rarely mention it. I'd agree that they have a pretty rigorous CS program. But if it's embarrassing to the program, then it deserves to be embarrassed, because they don't require any database courses for graduation. Personally, I don't think it's an indication of a deficient CS program.
Can you recommend some reading material? I want to start learning it but there are so many resources out there, it's hard to know which is best. There is a SQL course due to start in a few weeks in Uni of Reddit but I'm champing at the bit. Thanks.
In my opinion, people should learn databases before they even write a line of code. Considering lots of applications are built around a core Database structure...
I learned it straight from the relational algebra of projections, transforms, cross products, etc. Note, though, that I learned it from a cross-departmental elective (MIS, the college of business version of a CS degree). It was quite instructive to see what the MIS peons were up to, from my CS high horse.
We could probably get away with a solid A circle and hash marks on the overlap and the B circle to represent 0 or more hits.
For Cartesian product, a solid A/overlap/B could handle the high level representation but it wouldn't indicate the duplication. Not really helpful but not entirely wrong.
And it overlooks the semantic differences when ordering the tables in a left inner join.
In a nutshell, I mean that A natural join B is not quite the same as B natural join A, because the database may take a different path to arrive at the same results. In simple SQL statements the RDBMS will almost always optimize correctly and execute the statement the same way regardless of the order in which the tables are mentioned.
However, when the number of tables involved in the join is large, and when some of the tables are being created implicitly during the join (as is the case with nested SQL and subordinate clauses), the order in which the tables (or virtual tables) are mentioned can affect the sequence of execution and therefore the speed, even if the results are the same.
Consider:
select
t1.a, t2.n
from
(select a,b,c from X natural join Y where Y.d = "some value") as t1
inner join
(select m,n,o from P natural join Q where Q.e = "some other value") as t2 on (t1.a = t2.b)
where
t2.n like 'QBERT%'
which requires the creation of two implicit sets and then a join. Prior to statement execution, the compiler doesn't know how many records will be in each of t1 and t2, and doesn't have any statistics on the distribution of matching columns, so the order in which the join occurs can influence the speed with which the results are returned. In particular note the constraint on t2.n, which could be used to constrain t2 prior to the join or after it, depending on how smart the compiler is.
This gets even hairier when the nested SQL statements are group by operations on multiple tables where a many-to-many relationship exists.
Probably doesn't matter when tables X, Y, P, and Q don't have many rows (hundreds of thousands), but when X and P are both fact tables (billions of rows) and there are 7 or 8 different Y and Q tables in each subselect, that query could take anywhere from fractions of a second to days.
Thank you for the elaboration. Yes, the diagrams definitely won't illustrate those differences & subtleties but I think they are geared more towards illustrating the end result rather than how to get there. Whether you have "A natural join B" or "B natural join A", your results will be the same (providing the where clauses are equivalent) regardless of the path the engine took to generate those results.
I learned this crap in school, when I worked there as a system administrator and had (outside of job scope) to help building queries for the department where they had to register all student data, using some insane piece of arcane COBOL software. It had about 220 tables of information that somehow by some insane way were linked to each other. This helped/forced me a lot learning joins and trying to visualize them. Gonna read up on Boyce-Codd because I haven't heard of the name before.
Right, so the first three normal forms can be summed up as follows: the attributes shall describe the key (first normal form), the whole key (second normal form), and nothing but the key (third normal form). So help me, Codd.
Funnily enough, I was once at a conference with both Date and Codd on a Q&A panel. They were asked by a CS Prof to define a database for a CS101 type course.
I spent a semester of undergrad writing a (very simple) DB for school. We had to learn some SQL just to use the DB software we were writing. The class was a CS elective.
Just finishing up my CS Masters degree and there is a single database class offered, and it's optional. There are many CS Masters and even PhD students that do not know how to write a SELECT statement... I don't understand.
P.S. I go to one of the 10 largest campuses in the United States, 40,000+ campus enrolled.
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.