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

Go along, and teach a valuable lesson to all

[ka9dgx]

The secret to dealing with rules like this is to bend with the wind, like a reed.

Let them give you MS-Access, and use it like it should be used. MS-ACCESS is a pretty frond end, and sufficient for small databases. It's also a nice teaching tool because it'll let you see the syntax of a query by example. Once those lessons are imparted, and you need to do real work, the fun begins. You can then show how easy it is to install and run a real database server such as MySQL on the backend.

You can then contrast and compare the benchmarks between a system of 20 clients sharing a database on a fileserver, and a properly configured MySql server with 20 Access clients. It should teach the proper lesson once and for all.

--Mike--

Re:Go along, and teach a valuable lesson to all

[adamy]

Yep. It works well.

Better yet, don't teach em Access at all.

Teach em SQL. Make em do it from the command line. They will thank you in the long run. Why, because then when they have to write embbedded SQL to get their app to run, they will know it.

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.

SQL Works In Access

[avalys]

Somehow no one seems to know this: you can execute SQL queries from within the Access GUI.

While editing a query, just click the View menu and go to "SQL View".

So, you can start out with the basics of databases using just Access' GUI tools, and then graduate to SQL without having to switch environments.

Both

[borgboy]

Use both, really. Having Access gives you rights to use it in conjunction with the MSDE, which is sql server with some limitations.
Access can also be a pass-thru front end to other ODBC enabled RDBMSs.

Some Data

[BrynM]

I don't know if by SQL you mean PosgreSQL, MySQL or SQL server, but I did find some data.

• Database Journal has an article comparing SQL Server 2000 and Access 2000, but the feature list shouldn't have changed much. The charts that you are interested in are at the bottom right before the conclusion.
• Here's a speed comparison between MySQL and Access.
• Here's a great comparison of many SQL technologies including Access.

You do realize that you can install MySQL on a server and use Access as the front end via ODBC, don't you. If you do that, you can teach both and show the reasons why a particular solution would fit particular needs in addition to teaching the bare DB concepts. To me, this is the most open ended solution without having to create problems with people at work.

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

What?

[Bistronaut]

Access has an SQL engine in it (not the best, but hey). I don't understand the SQL vs. Access question. You could teach someone SQL using Access.

Realistically, there are much better dialects of SQL than the Microsoft ones. No need to start them off with the bad habits Microsoft encourages. PostgreSQL has one of the most ANSI-SQL compliant parsers, and it's free. I see no good reason to pay extra to get an inferior product.

Really, Access is more of a database client creation toolkit with a poor-man's database tacked on. It does some things quite well, but it's somewhat crippled to only work well with Microsoft products.

I get the jibblies just thinking about a bunch of first-year students "learning" that relational database == MSAccess.

Some stuff to start with...

[OneFix at Work]

Access has the most overhead according to this.

Here's a link to a discussion where the poster states that Access is not good for large installations...

You might check with IBM (DB2), Oracle, MySQL, or Postgres for help as well...I'm sure they'ld be more than happy to help.

Cross platform compatability. Students with Windoze, Linux, or Macs can run most SQL servers...not so with Access...

And then there's the corporate settings...most companies are using DB2, Oracle, MySql, or something that is ANSI-SQL compatible...not M$ SQL...

You also have more utilities and help available for SQL than Access...

There's GUI tools, schema browsers, etc all available for SQL...

If your school runs its website on a *NIX server, you could up-play the compatibility angle...you know, senior projects and such...

One major advantage of SQL is that all of the companies/organizations that I mentioned are free or have a free educational version...I doubt M$ does...

Neither!

[Earlybird]

SQL and Access are not the place to start. Relational database theory starts with exactly that -- theory.

The relational model, as invented by E. F. Codd, is heavily grounded in mathematics and set theory, and exists independently of higher-level access interfaces such as SQL.

You can certainly teach people to create and use databases through Access or SQL, in the same way you can (to pick a randomly politically incorrect analogy) teach people to create bombs without telling them how chemical reactions work, but then you're not telling them the full story.

Knowing what a Cartesian product is, or what normalization and the five normal forms are, or what relational integrity is -- all that lets you design better, more flexible and extensible schemas, and interact more intelligently with your data. I know Access developers who don't have a clue about the relational model, and as a result design terrible applications.

As a starting point, I recommend the books by C. J. Date, in particular An Introduction to Database Systems, 7th Edition ; his book The Database Relational Model: A Retrospective Review and Analysis: A Historical Account and Assessment of E. F. Codd's Contribution to the Field of Database Technology also looks very interesting.

As an aside, what's surprising is how many people consider Codd's original ideas outdated. The fact is, his ideas surpass what's implemented in database systems at the moment. SQL is a weak language (and SQL99, with its silly object orientation extensions, hasn't made it any better). Database vendors routinely expose underlying implementation issues to the user. They tightly couple physical representation with logical representation, leading, for example, to many people avoiding normalization because it incurs a significant performance penalty with most databases. RDBMSs today are crap.

Re:Neither!

[bpb213]

I second this. In the into to DB class I took, we didnt even get to any languages at all without going through all the basics, normalizations, etc. Only at the VERY END of the class did we actually use databases, and for that we used oracle. (school had a large piece of sun iron running it, but the class was flexible about using mysql and other databases that understand SQL)

So take the parent posters theme to heart:
Teach the how and why first, then teach someones interpretation(implementation) of that.

First Priniples

[Ratso Baggins]

As with learning long division, only to be taught the short method, then given a calculator. You are taught how to do it from first principles so you understand why.

teaching with access is like straight to the calculator. If you dont have it your're stuck, and you don't really understand the interactions between elements then debugging from the help files can be fruitless.

If you are trying to teach SQL (the language) things like stored procedures and triggers are integral not added extras.

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.

Re:A TOOL IS YOU

[dbrutus]

Please explain this.

From the page:

6.4.2 Subquery Syntax

A subquery is a SELECT statement inside another statement. For example:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
In the above example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested in the outer query, and in fact it's possible to nest subqueries within other subqueries, to a great depth. A subquery must always be inside parentheses.

Starting with version 4.1, MySQL supports all subquery forms and operations which the SQL standard requires, as well as a few features which are MySQL-specific.