SQL Vs. Access for Learning Database Concepts?

Jonathan Hamilton asks: "I work at the School of Communications for a major state University. The IT Department for the University (the same people that won't let us have a firewall, and use IIS and Exchange) is trying to talk my boss into switching from using SQL for teaching database concepts to MS Access. My coworkers and I think they are nuts. I have googled for pages comparing the two and can't come up with anything. I know some of the reasons why it is a bad idea, but I can't find any references. Help!" The mantra here is: the best tool for the best job. Is Access a suitable tool for teaching database concepts to students? If not, what would you use instead, and why?

Comments

What's the right tool?

[cybermace5]

I think you should find out if these students are more likely to use MS Access in an office setting, or developing SQL database applications in some programming language. Once you discover what the students will be using their database skills for, the answer will become obvious.

Re:Some Data

[Nicodemus]

This is just anecdotal, so take it with whatever grain of salt you want to. I've extensively used a few different database servers in the last 5 years or so, and am an Oracle certified DBA for 8i. Mainly MySQL, PostgreSQL, db2, Oracle and Access. When it comes to performance, Access can be fast. Not sure about the validity of it, but I remember hearing somewhere that Access uses ISAM tables, just like MySQL used to use in the 3.x days before MyISAM and InnoDB. I've done some stuff in Access that was fast, and stome stuff that was painfully slow.

A project I worked on once initially had a requirement of developing a prototype in Access. This was doing some hard crunching on a lot of data (so much so that we kept hitting the 2 gig limit of Access). It eventually got to the point where we were doing a certain operation that was pretty simple really. Read a bunch of data from one table, and insert results from the calculations in a couple other tables. This was originally developed completely in Access using VBA. It was slow, to say the least. I had profiling code in it so that I could see the current progress, and the projected completion time. The first time we ran it, it projected that it would be finished in a couple months. We spent a week tweaking like mad until we got that down to just under 4 weeks.

So then we decided it was time to scrap Access as a DB, and moved all the data to a db2 database. Our initial hope was to continue using the VBA code, though... so we hooked up the VBA code to the db2 database through ODBC. Even after another week of tweaking (including figuring out how to sorta do prepares with the kludgy API) the performance was even more abysmal than when it was in Access. Somewhere around 6 weeks if I remember right.

At this time I convinced the project manager that I could get the performance up if I ported it to Perl. I finally got the go ahead, and spent a few hours porting the code over (remember, this was a pretty simple function, it just had some ugly calculations). My first result with Perl was about 1 week. But then I realized that I had forgotten to prepare my inserts outside of the main loop. Fixed that and the thing ran in 6 hours. I swear... I'm not exaggerating or anything.

My lesson from the experience, was that Access can't handle large amounts of data (besides the fact that it has a built-in 2 gig hard limit), and that preparing before your loop is a HUGE optimization. I had used prepares like that before, but on such limited samples that it didn't make that big of a difference.

Like I said, anecdotal, but it definitely tought me some lessons.

Nicodemus

NOT ACCESS!!!!

[JetScootr]

1. Query builder. No one can learn advanced database topics from the Access Query builder. I started trying to learn database after having been a professional programmer for about 10 years (Fortran, C, Asm). When I did, I started with Access. I used the query builder to build SQL, and then tried to learn how the SQL worked. It was a mess. Nothing made sense, syntactically. It seemed that no matter how I tweaked it, the rules for what is legal and what isn't never became apparent. What's worse, even if the syntax was acceptable, sometimes I would get the wrong data back and not know why.
Access wasn't failing or misbehaving in this - I just couldn't figure out how to make it work using Access's Query builder and just "tweaking" the SQL. I learned Unix, C shell, Bourne shell, C, and Assembler all by grabbing the documentation and a keyboard, and digging in. I know how to figure out a new tool.

But my difficulty in learning SQL from Access made the REAL knowledge I needed even harder to get to: How to normalize data, how to analyze a process and figure out what's really going on, etc.
Building the SQL is the last step in the process, and the easiest. That's what SQL is for - so that the tool doesn't get in the way of purely processing truly abstract data. Real SQL does this beautifully.
I took an Oracle class and learned more in one week than I had with Access in one year. Microsoft's query builder and Access documentation hadn't really helped me.
UML straightened me out - by teaching first the concepts, then the structure of the SQL language, then the syntax. By that time, the syntax was easy and even made sense. Access really held me back.
2. Errors in Access's handling of SQL. Here's an example for you. You can set up test tables and prove this to yourself. Create two tables, each with about four fields. (This occurs in both Access 95 and 2000)
Join them on TWO fields, like this:

Select * From Alpha INNER JOIN Beta
ON ((( (ALPHA.KEYONE=BETA.KEYWUN)
AND (ALPHA.KEYTWO=BETA.KEYTOO)
)))
WHERE (ALPHA.ATTRIBANY="WHATIWANT");

Be sure to add the extra parans on the join clause - I'll explain why. Access will accept this statement. It will actually work - set up some test data and try it. But save and close the query.
Reopen the query.
You'll notice that join statement loses the outermost set of parans every time you do this. If you compact the database, and Access has to move this query, it will lose another pair.
Eventually, it will become this:

Select * From Alpha INNER JOIN Beta
ON (ALPHA.KEYONE=BETA.KEYWUN)
AND (ALPHA.KEYTWO=BETA.KEYTOO)
WHERE (ALPHA.ATTRIBANY="WHATIWANT");

Once the "AND" in the join clause is "exposed" without enclosing parans, Access will reject the query, saying "Unsupported join syntax". This will occur to perfectly functional queries that have long been in use, because ACCESS EDITS YOUR SQL WITHOUT YOU KNOWING IT!!!! Once the SQL becomes "invalid", Access won't let you open the query anymore, even in design mode, so you can fix it. There's nothing you can do with it but delete it at that point.
3. Many more reasons.... But I'm coming in late to this article. If you want to hear them, reply to this post with another post. I don't get modded as a troll, when I'm actually showing facts. I also don't want to spend huge amounts of time if I'm too late and it's not going to be read by anyone.