Slashdot Mirror
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?
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--
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.
...
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.
This space intentionally left blank.
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.
meh.
Actually, SQL (Structured Query Language) is not a database, it's a language - aka, a tool for accessing databases. Microsoft SQL Server *is* a database application however, and I think their name confuses people, like the poster of this article.
- 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.US Democracy:The best person for the job (among These pre-selected choices...)
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.
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...
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.
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.
--
"we live in a post-ideological world..." - Billy Bragg.
And neither is MySQL's syntax. The SQL for example has nested queries. MySQL does not have that.
In the end it's all about relational calculus. You can teach that with both tools at hand.
The article seems to suggest that Access is very bad at being serving a database (i.e., it is not suitable for real database work) - and that is certainly the truth, yet you will hardly need a real database for teaching relational calculus. Either Access and MySQL will do.
&& aemula C. ab stirpe interiit
I've just spent the last week or so installing and attempting to uninstall Oracle 9i and 9iAS over and over again. I have *never* seen a piece of software that is as user hostile and fragile as this. Comparing SQL with Access doesn't make much sense to me but bare this in mind whoever you go with. My colleagues at work have had much greater success with the 2 main open source DBs (MySQL and PostgreSQL).
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.
Ecce Europa - Web Design for Business
For the visuals. Queries can be made by dragging and dropping columns from tables. Table creation without learning SQL syntax is simple as well. For that alone, I would recommend Access. It makes it easy to teach the concepts before being mired in syntax cruft.
That said, I would recommend using Access as a frontend to a real database via ODBC. Then you have the ease of use and baby steps at the beginning as well as the power, seamless transition to better functionality, and (perhaps most important) the hint that Access should rarely be used on its own for all but the most trivial of projects.
- I don't need to go outside, my CRT tan'll do me just fine.
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: 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: 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.
Pavlov wouldn't be so famous if he'd used a can opener instead of a bell.
Please explain this.
... 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.
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
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.
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.
Not really. Access has an odd version of non-standard SQL. I write code to interact with mySQL, MSSQL, and Oracle databases at work all the time. Recently, I had to write some code to interact with Access for police laptops as interacting with a central server was too bandwidth intensive for their in-cruiser RF computer network. Access was VERY picky and rejected more than a dozen commands that would have worked fine in any of the three major SQL DBMS products.
In short, Access shouldn't be used to teach SQL. Anything more than basic SELECT operations on integer fields will start requiring the use of non-standard extensions and work-arounds.
Access is great for teaching the basics of relational databases - but Enterprise Manager, which ships with MSSQL, is superior in this respect also.
How many roads must a man walk down? 42.
You might consider taking a step back and actually looking at the class content rather than debating which system to use for it right off the bat. Based on what little I've seen, this course is to teach databases (perhaps use, perhaps design) and underlying concepts. Is this a beginners' course? If so, you should probably strive to stay away from any given platform. I'd suggest taking a look at sqlcourse and maybe conceptualizing a class outline around that. The course itself only briefly grazes various SQL platforms.
I only offer this suggestion, having actually taught database, SQL, and platform-specific SQL classes for a former employer. The software we wrote supported two SQL back-ends: Sybase and MS SQL Server. That alone was enough to put most of the support reps I was instructing into a frenzy. When I started backing away from platforms themselves, and just teaching basic T-SQL along with diagrams of tables on a white board, it went a lot more smoothly. Really, it doesn't matter what platform you write for if you understand the query language enough to write fairly universal queries. To do anything that requires significantly esoteric querying probably requires you to have a very deep insight into the specifics of the platform you use.
It's really pretty amazing the amount of information you can teach without having to really use any SQL--but again I suggest looking at sqlcourse because it has you writing standardized T-SQL queries, but you don't ever see what platform you're using (if you're using one at all).
If this is really a class to teach basic dba functions like writing triggers and tweaking your platforms for performance, then it really comes down to the sad truth that to teach these skills you really need to teach them for a specific platform. Every company has their own esoteric set of tools, commands, and standards that in most cases have some striking similarity to T-SQL, but may do something different (look at how Pervasive.SQL, MySQL, MS SQL Server, and Sybase handle the idea of committing or rolling back a transaction).
I guess in summary it boils down to what concepts you're trying to teach. You may find that teaching database theory doesn't even require real databases. Ask a student, given two small tables, to describe what would be returned if table A and table B were left outer joined. They'll either get it or they won't, and I can't say any given platform will demonstrate it any better than a pencil and paper.
> 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.
Cut that out, or I will ship you to Norilsk in a box.
I certainly agree that the number one "feature", or that is, requirement, of a database is Protect the data at all costs! On that score alone, I'll concede that your point has a higher priority than mine.
But to me, a parallel requirement of a development environment is Protect the code at all costs. If a developer writes the SQL by tap-tap-tapping it out on his own, the tool should leave it the way the developer wrote it.
I don't accept as valid that the tool can go into MY code that I wrote with my own keyboard, days or weeks AFTER I test and deploy to production, and change that code in any way shape or form. Just like with data corruption, if the tool isn't protecting what I've put into it, then I can't KNOW that my system will continue to perform in the manner which I designed and tested it.
Pavlov wouldn't be so famous if he'd used a can opener instead of a bell.
I got part-way some of the higher-rated comments and haven't seen many people talk about a very important point:
Who's the audience and what's your goal?
At my university (medium-sized, well-known Canadian) first-year students have three places to 'start' in CS. New to computers, new to programming and some experience programming.
I've worked with the new to computers crowd, so there is my bias/experience. Database and theory were covered for two weeks in lectures and practical knowledge through three weeks of labs. We used Access - if we didn't, we would have needed triple the time to cover the basic (and maybe a little extra).
More first-year students take this course than there are CS student in the CS program. Their questions are "What is a primary key and why is it there?" NOT "why doesn't my outer join work?".
MySQL is not appropriate for this group. Given an entire course of databases, sure, but now you're targetting the CS major/minor crowd. How many arts/science students would take an entire course on DBs compared to a well-rounded, multiple application course?
Give the minors/majors a real DB. They HAVE to know this stuff at a rudimentary level or their CS degree isn't worth the paper it's printed on. Give the other students a once over with Access, tell them small companies and mom-and-pop stores use it for VERY small installations and point them to the DB course if they want to know more.