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:What's the right tool?

[OneFix]

Actually, I don't know many ppl that use Access in an office setting. Most use something like Oracle, MySQL, or DB2...

If they are enough of a user to NEED Access, they are more likely to use a Web interface to a REAL SQL backend...

What's more likely is that this school was given free licenses, free books, new computers, or just plain old money to use Access...

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

Re:Neither!

[Frequanaut]

Good lord, just when I really start to wonder about the cluelessness of the people on slashdot I can always find a perl like your comment.

I had to scroll to the bottom of the page before anyone even noted the fact that SQL is a language while Access is a database and front end. (with SQL support).

Nevermind all the non relational databases.

One Vote for Access

[cookiepus]

I've in the past built an application that uses Access as a db, using an ODBC::JDBC bridge. My choice for using Access as a db was the correct one given the nature of the application (don't argue design decisions here. Yes, i COULD have told them to convert to Linux and use mySQL. I could also implement it in the way that would be the least pain for the cusomer. I did the later)

Here's what Access has going for it:

1. It's easy, and it's visual. You can start creating tables and entering data w/o knowing anything about how it is stored or retrieved. The beginning class obviously is more concerned with "WHY A DATABASE" not with "HOW." This way the students get to using it right away, with Access being available in all computer labs and on a lot of home pc's.

2. You can use Access to demonstrate what a front-end is. You can easily design Forms in Access through which you can view, enter, and edit your data. When I took a DB class, we designed our front-ends in Java. Those who didn't know Java had a steep learning curve in those projects! For a beginners class, don't confuse them with other stuff. Access makes it simple.

3. Similarly, in Access you can create reports very easily. This shows you ways in which you can GET your data out, w/o having you learn too much technology. You can start joining your tables for example, w/o learning the syntax for the join statement.

These 3 reasons show you why Access is a good choice for teaching people what databases are and what they do.

Now, say you're done with that stuff and you still have time in your course. Guess what, Access has SQL view, in which you can enter your SQL queries using regular syntax. What's more, Access translates gui-made queries into SQL for you. This way you can show people how the stuff they've been using REALLY works. This way you first teach the "WHAT" and only later the "HOW."

Now that you've shown them the WHAT and the HOW, you can explain the WHY. Given the people's current understanding, you can start explaining about ACID and fuzzy dumps and query optimization and all that.

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.

Most important

[dtfinch]

Is that students learn about good database design and the full querying power offered by relational databases.

Access is an excellent prototyping tool, which saves you a lot of the typing and repetition involved in developing database apps. But for any just about any useful project they're faced with in real life, SQL knowledge will be a must. If they learn Access, and the price was right (academic discounts), that's all good and fun but the focus of the class should not be to get them hooked on a proprietary, non-scalable database app if you want them to get a good education. Don't let their final project be without coding.

Re:Database concepts

[jonadab]

> I believe this is where access falls short. I don't believe the syntax of
> access is 100% standard.

Last I knew, no available database has 100% standard syntax. They all differ
from the standard in a number of areas.

The problem with Access is that it's becomming obscure. It's not included
with most versions of MS Office anymore, for one reason or another (probably
because MS wanted to drive sales of their _other_ database offering, SQL
Server), and so consequently few desktops have it, so nobody knows it. SQL
is used heavily on servers and in backend stuff, and so pretty much every
database administrator knows one dialect or another of SQL.

If you're teaching people who are going to work mostly with desktop and office
stuff, Access might be appropriate, though frankly the database in MS Works is
more widely distributed on the whole, simpler, better known, and adequate for
basic office-type needs. If OTOH you're teaching people who are planning to
work in IT and administer networks and servers, they're going to have to know
SQL in one form or another. This will be true even if they work in an all
Microsoft-only environment -- for serious databases MS strongly pushes their
SQL Server product. If they work in a heterogenous environment, they'll use
Oracle (if the database is mission-critical and the place is rolling in cash)
or MySQL or PostgreSQL (for smaller installations) or _potentially_ MS SQL
Server -- but the chances of seeing Access in a heterogenous environment are
roughly zero. In any non-MS homogenous environment (e.g., everything comes
from one specific Unix vendor) the chances of using Access for anything
serious are *exactly* zero.

The question then is which SQL implementation to have them know. I would
suggest picking one of the four covered by SQL in a Nutshell, and make sure
they're aware that there are differences in the other implementations.

Which one to pick? Where are they looking to work? All-Microsoft shops?
Teach 'em MS SQL Server. Big enterprises? Oracle. Small businesses with
a heterogenous environment? MySQL probably, or perhaps PostgreSQL. If you
don't know, just pick one. Knowing one dialect of SQL will make it easy for
them to pick up another, so it's not wasted. There are differencesin the
details, but the *concepts* are to a large extent the same, at least for the
basic stuff you're going to teach in a course.