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--
Either would be fine for teaching about the concepts that make a datbase what it is. The problem with most computer instruction is the various versions of the program being taught. Because many are going to be on a budget you will see everything from Access 97 to 2003 (hopefully none older than that). With sql you can have all students on one free platform (mysql) and they can take home notes that make sense.
Ohhhh, look at all the pretty shiny things.
For the love of God, stick with SQL - it's a simple case of one being slightly more difficult than the other ("I have to type stuff in rather than letting an anthropomorphic cyber-dog walk me through creating an address book"), but much more powerful. I say "slightly" more difficult because I know people can get into some pretty squirrely territory with SQL but you dont need to in order to learn basic database concepts. A very exaggerated example along these lines would be the difference between TeX and something like MS Word: in this case, of course, Word is orders of magnitude easier to use than TeX for basic things, but which would you choose if you wanted to really get to know how documents are laid out?
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.
...
(If you didn't want us to know where you work you should use another email addy.)
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.
No, it is not. The mantra is whether you should half-truths about databases and then later someone will be able to use a real database. Access is not a database it is a "database-program" one is inseparable from the other. SQL is a type of database that holds true to what it is, a database. It does not create forms, process charts, or do anything else that databases DO NOT do.
some old shit from few brief access lessons back in (local equivalent of)highschool.
it was just pretty ui playaround, even if i did remember the lessons well i wouldn't be able to use any of that information to my advantage now.
though, if you do it properly and teach some concepts behind the issues and just use the particular program you're using on the course just as a tool to get something done then you should be ok by going with whatever you choose. teach the concepts, not just what button you click.
world was created 5 seconds before this post as it is.
In our school in our "Database concepts" we were designing databases and creating bachman/bubble diagrams learning the different levels of normalization and talking about uml. When we got deeper into the course (2nd year) we added more practical aspects like writing sql statements by hand: I believe this is where access falls short. I don't believe the syntax of access is 100% standard. Sure its great to be visually able to create queries but it is probably more important to teach the actual concepts and follow standards so they can go home to the platform of their choice and put their learning to good use. Access is nice for quick jobbies but at the same time it creates a more ignorant student because they will be reliant on the gui/wizards. weak.
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.
Just make sure that you start with the general "wizard" approach, and then move on to using real SQL for things. They can build a query (or whatnot) and switch to the SQL view to check things as needed, which is a boon while learning.
Then have an in-class demonstration of benchmarks between Access and any other DB :P
Also compare/contrast data sizes - I know when I converted a 3.8mb (compacted) Access database to mySQL the total filesize dropped down to 860k - and performance skyrocketed from mysqld/apache vs access/odbc/apache.
- 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.
As mentioned above it is a good looking interface but you dont want to teach them to depend on a nice looking gui for a database.
If they wont let you use IIS then can I suggest Microweb for a quick eval to see if you want to go the MySQL route. Just download it and burn it to a CD and then you can quickly and painlessly see how MySQL works on any win32 computer.
If you also download the phpMyAdmin and drop it into the cd you can use the graphical web-based frontend and see how simple and powerful it is.
I think you will have a much better educated class as a result of using an open source database. You will help them to see and understand the structure and interelation in tables outside of a slick gui. Then once they understand this then show them how to make their administration tasks easier with a gui.
It was reported on slashdot recently that Apache has a 2/3 market share, I wonder what MySql has for a market share of internet servers.
Boredom's not a burden anyone should bear.
...Or maybe you dont understand. SQL is a language to query data. MS Access is a database application, in which you CAN execute SQL.
Apples and Oranges folks, only the orange has an apple inside.
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...
SQL is a standardized language (well, at least some common ground for every SQL implementation), knowledge of it has a great re-use value, and is not linked to a particular vendor.
Learn Access, and be tied to 1 platform. It's a bit like learning VB; yeah it's easier at first but afterwards you're caught in the Microsoft playground.
Education, I think, must avoid teaching proprietary technologies. I have an Access course, so to work from home I have to buy Microsoft Windows and Microsoft Access. As students are not the richest people in the world, what will they do if they can't pay for that software? pirate it?
OTOH, it'd be really easy to assemble a 1-CD Knoppix version with MySQL or whatever-your-SQL-favorite-flavour-is and distribute it legally in your class for a buck or less.
I think it has 3 or 4 full featured SQL databases in it somewhere.
MSAccess is the biggest piece of crap I've ever run across! You would not believe the stupid crap I have to deal with because of Access. It's SQL support is rather limited so I end up having to code VB routines to do anything useful.
SQL server is better but it's also very vulnerable to attack and virus/worm exploitable!
The real solution would be to drop in a Linux box, setup telnetd or sshd and let the students telnet into their own shell accounts. Then have them run PostgreSQL and experience a real freaking database! Then install Windows compiled versions of the psql tools so they don't have to telnet into the Linux box.
Next setup ODBC and use MSAccess to make a connection to the PostgreSQL Linux Server. Just make sure you turn on PostgreSQL debugging because MSAccess tends to optimize your already optimized SQL statements thereby causing them to fail. Once the ODBC is working you can use MSQuery tools.
All of the above will show how the real world works with databases. Using MS Access as a cheap frontend will teach quite a bit. Next setup Apache and PHP so you can build web pages that process SQL queries. Then I suppose you could show how ADO/DAO/ODBC works inside an application using VB.
For advanced course, run Java Tomcat on the server and then you can get into JDBC stuff.
Seriously, one has to ask themselves why teach MS Access and SQL Server when the rest of the world is using Sybase, Oracle, UDB, and mostly Unix systems that are all moving towards Java J2EE architectures involving XML/Soap/etc???
I mean learning MS Access and/or SQL Server will not do anyone any good whatsoever other then learning improper SQL syntax and working with non-scalable systems. (yeah you can scale but it's very very expensive with MS solutions). We have very few MS SQL production servers.
Teach MS Access if you want to teach MS Access, but don't expect to teach SQL along with it.
I can tell you that from what I've seen Access here is being presented as seemingly the only database that a user would need, along with Word, Excel, and Powerpoint as the only office apps even available. It seems like somewhat a quagmire for people to get sucked into, depending on Microsoft's crappy software.
On the educational standpoint, I think it's really hard for the students I am alongside that don't have other database experience to grasp the ideas of Access as a database. It may just be my instructor, but it would seem that they see a database as just another program that Microsoft made, and is only Access, just as Excel is spreadsheets and Word is word processing to them. I think it's a shitty habit for schools to present Microsoft upfront to students... Most of them would be crippled without it.
I may just be bitter from having to use Microsoft programs in my classes, but people aren't getting a full grasp of concepts from Access. They could be taught on top of it, but you don't NEED them. That works if you are just trying to show people how to use Access, but if you are going into real databases I don't see what Access provides as being worth the time, the money, or the effort for a school to switch. What are the benefits that they see with it? If anything, it clouds the concepts you're trying to teach in my opinion...
That's scary.
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.
Do they want faculty to tell them what email platform to use, or how to configure their switches? No? Then they should shut up and let faculty do what they hell they want, and only offer an opinion if they're asked.
As say this as an IT worker at a university, who's constantly dealing with faculty who say "Why don't we use Technology X here?".
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).
you're completely right.
fwiw, our it and mis (academic) departments both use oracle heavily and the cs department uses oracle exclusively in its database classes. as far as i'm concerned, while access has its place, that place is far far far away from anybody whose goal is to actually learn the principles of databases. if you want a well trained monkey who can do basic database stuff, send them to any ms office class. if you want someone who actually understands what's going on and has a set of skills (specifically, the "core" ddl and dml parts of sql that implementations agree on) then you teach using a real database.
you've either failed to communicate or you've demonstrated that neither you nor your it people have a clue.
sql = structured query language, i.e. a capability that rdbms may implement.
access = m$ attempt at a rdbms 'lite' which implements part of sql
sql is not synonymous with dbms, EVER.
i feel better now. that said, i'd stay the hell away from access if your goal is to teach a *database* class. if students want to learn access, send them to a course about office. if they want to learn about databases, skip the fluffy nonesense and do things properly, i.e. in the environment in which they'll be working. this means a real rdbms (such as oracle, mysql when >4.1 is released, postgress, etc.) anything less is like proposing to train people to drive 18 wheelers using ford pickup trucks.
Ignoring the "we are contemplating from switching from SQL statement" (what is SQL? I am going to assume you mean SQL server, and are just so familiar with it that you just call it SQL now... if not, you shouldnt be doing any DB work at all, do everyone a favor and stick to excel before you create behemoth database schemas that will give people nightmares for years to come). But anyway...
Access is nice from a learning perspective in that its easy to install, seperates you from what can be some nasty DB administration issues, is on your machine, and gives you a nice gui. The gui aspect is nice, because its much nicer to view the tables directly without having to tediously write select * statements all the time- its much more conducive to those "aha!" moments you get when trying to learn concepts. Also, direct data manipulation is nice if you just want to play with something (lets see if I can get this data to not be in the join by changing this value... type experiments). Those are much more tedious in other real DBMS's, though it could be argued that they could further help you learn concepts by practicing update queries and such. In addition, access makes it real easy to make tables.
On the other hand... Access's SQL engine (JET) sucks. I mean its terrible. It supports only a paltry amount of "real" db functions for data manipulation (substring being one notable example), and it doesnt even come close to supporting standard SQL, not even a plain outer join. Evaluating progress might be easier on a single server, but providing access to a single server can be a pain, and getting students to install DBMS's on their machines can also be a pain. DBMS's are not really that user friendly. But... it will give them better experience as to how real programming is done.
Ive seen classes done with access, and I must say I feel its a better route. Its just easier on the students. It lacks in alot of respects, but for a beginning student learning Concepts, its ok. Students learning things shouldnt be worrying about string manipulations or date conversions from old made up formats when learning DB concepts. All in all, access is a good choice.
I think Access is great for most people to get some understanding of what a database is and does. Start them out on Access. The average person will grasp way more from playing with Access than playing with SQL. After that explain why Access sucks and have them port whatever they created to some other platform. This reflects what I and many others do - prototype on something like Access and then build the final product on something a bit more robust. I know Access has many limitations and we could list them all day but the interface is great for prototyping and for teaching students basic database concepts. Teaching only Access would be a huge mistake, however.
When I started my computer science degree, one of the first-year units was about databases, and they used Access as the basis for teaching and assignments. We had to construct all the queries using that visual tool where you drag lines between the columns of the different tables to represent relations.
This sucked. I was confused and had no idea what I was trying to achieve with these queries, and found I was spending most of my time fighting a very lame GUI.
A year later I had to use SQL for some other work I was doing: I found a tutorial page that showed me the basics, and I was up and running in maybe 15 minutes - everything suddenly made sense, and I had vague memories of concepts from the university databases course that were actually clicking into place now that I saw how the queries I'd been drawing in the GUI were really constructed. It was so much simpler and less confusing to see a single-line SQL query than a big complex diagram with lines snaking between representations of tables.
Seriously, one has to ask themselves why teach MS Access and SQL Server when the rest of the world is using Sybase, Oracle, UDB,
The nice thing about MS-SQL for learning is that it comes out-of-the-box with a nice, developer friendly configuration that handles most management tasks automatically, and it's got a dead-simple GUI to work with.
Give someone Postgres or Oracle and they get to have fun learning DBA concepts before learning any SQL concepts.
Access actually has significantly better JOIN support than MySQL. Access also supports subselects and MySQL does not.
Conclusion: Good thing MySQL is around or Access would be at the bottom of the DB heap.
SQL was designed to allow Managers to run their own reports. It's supposed to be a very accessible tool that allows one to learn as they go.
While relational theory is required for design tasks, someone can get their stupid webboard up and running without reading a textbook by CJ Date. There's significantly more accessible texts out there which will get him grounded in the basics.
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.
In my opinion it is better to teach SQL server. It's a lot easier for someone to learn Access after learning SQL Server than it is for someone to learn SQL Server after knowing Access. SQL Server reinforces true database concepts better than Access. But you also have to consider what the people are most likely to experience in the field. One advantage of SQL Server is you could set up a single server for everyone to work on, each with their own accounts and databases, and the instructor can look at the stuff for grading. The instructor can also look at progress for larger databases. Something that can't be done as easily with Access.
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.
I'll also echo other posters who have said that teaching RDB theory is essential. It's shocking how little most people in the IT industry really understand about how to properly design a database.
Teaching SQL will teach a student skills which are portable. Teaching Access will teach a non-portable skills; they will be like the VB programming trying to use a "real" language.
If not MSSQL, you should look at MySQL. If you need to teach people high level concepts, such as SQL, DML, etc, then using a database that is ANSI compliant is the only way to go. Access database fundementals are not transferrable to Oracle, DB2 or any other "industrial strength" RDBMS... my .02 from st. lou
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.
The only reason I ask is that Access's GUI nature allows you to visualize database design. You can set up keys pretty easily by dragging and dropping from one key to another, and modifying the relationship just by right-clicking on it.
Yes, it's hokey. It's also good for people who can't see the relationship between tables just by looking at the INNER JOIN...AT... portion of your CREATE TABLE statement. What's more, you don't even really need a CREATE TABLE statement because you can build all those tables by GUI too. Many people need to have visual representations of things just to understand where everything goes, how it relates, what they can do with it, etc.
I'm not saying that they shouldn't learn more hardcore SQL at some point, but at the beginning, when people are getting broken into it, it's not a terrible design tool, especially for prototyping.
That said, when I was teaching this stuff, I hated trying to show people how to use the Query builder. It's much simpler just to use straight SQL -- but we're talking about only a part of database theory, which can be compensated for by switching from Design to SQL view. Still, there are some things that Access does awkwardly which you'll have to compensate for when trying to expose them to better databases.
Anyway, plusses and minuses. It's possible that the department is realizing that they're going to have it on all the computers anyway and that they might as well take advantage of it. Laziness, sure, but while CS is part determining the right tool for the job, it's also part learning how to make do with what you have.
--------
Bleah! Heh heh heh... BLEAH BLEAH!!! Ha ha ha ha...
Wow. UID of 221 and posting at -1 by default.
I can't really figure out what's so "intuitive" about the Access Query builder. I've used SQL for 10 years, and I can't make any sense of how to use that thing. I guess it's designed by and for dumb people.
Also, the SQL it generates is Really Fucked Up -- it's about 10x as verbose as necessary and not formatted in a manner that can easily be read.
In short, I doubt you are going to learn much SQL using the query builder. Figure out the concepts first, and then it's a very simple language.
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 think that the question is invalid. There is the dubious assumption that you must pick a tool, to teach "database concepts". IMHO incorrect, subtly and forgivably so perhaps, but still false...
To give the maligned IT group the benefit of some doubt they are probably trying to standardize on something they have available cheaply, that looks (to them) as if its freindly to newbie students.
Well, indeed it is freindly (like drug dealer freindly), unfortunately, freindliness is not a criteria for tackling the serious business of learning to design (non-trivial) databases. The first easy fix is for free, after that the new IDE junkie, spirals into nasty addiction, hooked into another mind-candy toolset.
However, if we assume that all databases in university courses are de-facto trivial, then an IDE is fine... because by the time the poor students figure out they have been duped, and hooked on a street vendors junk, its far too late to complain...
Tough love : If you really want to teach database concepts you use an ancient method called teaching, which uses an experienced "teacher".
Experienced as in "has actually done the task, in the real world, for a real system, for pay" not, "has read the course notes, 15 minutes before the class".
As many of us have not had the privilege of meeting a real teacher lately, because universitys do not hire them anymore, let me remind you that this is a sophisticated human assistant, who can communicate and teach database concepts. This includes design principles, theory, case studies, normal forms, denormalization (and when to use it), design symbology, drawing correct diagrams, discovery and definition of entitys in requirements, defining attributes and relations. The place of database design, in the larger practice of systems analysis and design. Some pragmatic history, such as when to use other non-relational types of databases (OO, Hierarchic, Hybrids). The mapping between database schemes and common program structures, and objects, linked-lists, queues and other similar constructs. Etc...
Implementation level languages and syntax such as DDL, DML, XML, etc, then become quite minor exercises, that a student can hack about, or not, as necessary.
Specialized, closed source, vendor IDE's (such as MS-Access) that blend some of these elements together with other development areas, do have a place, but IMHO, are not a relevant starting point. Unless of course, its really just edumacational hocus pocus, to sell lite and fluffy "design" courses.
Heresy?, sure...
There is no god; get over it already! Never exchange a walk on part in the war, for a lead role in a cage.
Instead of teaching Access vs MySQL, why not teach plain SQL and add notes on pecularities of MySQL, Access, db2, oracle, etc?
If students prefer windows, they can use access. If they like unix, they can use mysql.
Do you even lift?
These aren't the 'roids you're looking for.
and I'm far from a database expert (i.e. I've had ZERO formal training about databases).
The query returns all records from the table t1 where the value of column1 matches that of column1 only record[0] in the table t2.
[0]This example doesn't limit the subquery with a WHERE condition, or LIMIT, so it won't work in a sane way if t2 has more than one record in it. Nobody sane would do that in the real world.
Something you mentioned that seems off to me is that you're looking at this from the "School of Communications" point of view. First off, why is the Communications school even teaching a database class? Isn't this usally a CompSci department thing?
Considering that you're teaching non-major students about databases, learning Access instead of proper SQL gets put in a slightly different context. We're dealing with non-programmers, so Access is probably a better solution for their skillset and scope of interest in the problem.
my sig's at the bottom of the page.
How many Access databases do you know ? You can know only one, since there is only one. It's from MicroSoft and it only runs on Windows. It's a dead end.
:-)
On the other hand, without spending more than three seconds of thinking I can come up with more than half a dozen SQL databases, on all kind of operating systems and in all kind of flavours (from the very simple ones to the overpowered ones).
When you look into the real world out there you're stumbling over SQL database everywhere. It's what's used out there and requested knowledge by employers. Just look at your favourite computer magazine and look at the job ads: count how many time you read SQL and how many times you read Access.
That should be a fairly good argument to show your boss: just really take a computer magazine with job ads to your boss, and show him the ads which require SQL knowledge and which require Access so he can actually see with his own eyes. Should also work with online job services
I work at the School of Communications for a major state University.
I work in the CS department at a smallish, private university with a liberal arts focus, and I'm responsible for the Oracle systems used in DB/DBA courses. Your question seems strange to me. Some questions/comments:
1) Why on Earth is the School of Communications teaching database concepts?
2) Why does University IT care if you use SQL or Access?
3) Perhaps University IT cares because it has to run an SQL database server for you. It wants to unload that liability. The problem is that doing so actually has academic consequences: it forces students, teachers, and TA's to spend time on the various installation and configuration details of MS Access. _I_ can't say if that's good or bad, but it must be part of your decision. You should only replace server-side SQL coding with MS Access databases if you believe that Access offers pedagogical benefits.
4) Perhaps University IT cares because they think SQL sucks; because the IT director likes GUI's; because the IT director owns Microsoft stock; etc. In any of these circumstances, you can politely nod when they share their opinions -- and then forget the conversation ever happened. If University IT gets bitchy about it, then you can tell them to piss off and let you do your job.
5) The first and most important issue is deciding what you want the students to learn. Theory-vs-practice, Access-vs-SQL, GUI-vs-code, University-vs-School, etc should fall into place after that is decided. Nobody on the Internet or in University IT can help you till this is decided.
Learn computer science, not computer languages, and you'll never worry about being obsolete. Going the access route puts students straight into a little Microsoft hole that would be hard to get out of. Need some scripting - going to have to be VBA/VBscript. SQL is (mostly) a universal standard and so can be used from OS to OS. Scripting? Try perl, php, c, java (add your choice here). At the end of the day those students are at the University to get jobs, and there aren't to many out there (from the recent graduate working in a call centre) Granted Microsoft is everywhere, but why limit yourself?
Morale seems good, considering, although high spirits are just no substitute for eight hundred rounds a minute
The benefit of something like Access is that it has basic visual tools for diagramming and modelling while still supporting a robust SQL syntax. The downside is obvious: it is just another means to tie people to proprietary and otherwise non-free software systems.
Perhaps you can explain to your uppers that something like SQLite will do the job just as easily, for less money and more portably. Also, free tools already exist for SQLite that mimic several of visual features of Access.
FWIW, this is how we were taught the fundamentals of databases in university.
1) learn the history of data management starting with file management
2) learn how data structures can be encoded
3) learn techniques for accessing encoded data
4) learn efficient means to encode data structures in files
5) learn additional methods for management of said data
6) implement our own database manager
That was a 2nd year course from about 10 or so years ago. The only db software used in that course, we had to develop ourselves. I learnt a lot in that course.
Sure, you can start right at the modelling stage but then it doesn't matter as much which tools you use. Entity diagrams, normalization, indexing, sql -- these are all general. What does it matter what tools you use? For me that question all comes down to this: particularly in educational settings, the software used should be free--in every way.
The reason there are not pages comparing is because all access does is make the frontend pretty so you don't have to understand the backend concepts. access is also not really scalable without a backend database (oh.. how about mysql?). SQL can be used to help them understand the base concepts, and let them use access to do quick development/propogation of the database... then make them use PERL to work with the database!!!!!! MUAHAHAHAHAHA
Not forgetting the Licencing costs of X copies of Access
Not only will dBaseIII teach the fundamentals of database design it will teach resource management and promote a genuine enthusiasm for REAL database servers!!!
Meddle thou not in the affairs of Dragons, for thou art crunchy and with most anything.
Actually, a friend of mine sometimes talks about the database course he took at Uni.
They did use MS Access in one assignmnent -- to demonstrate why it sucked.
Access is OK to teach SQL. However, the multi-user environment, transactions, triggers, and the like are mostly availible in SQL Server. And, if you do any massive updates, don't even think of Access. It just isn't made for that.
To teach a class, it depends what. If just to teach how to query data, Access is cheaper, quicker, and easier. If you want to learn how to use a database, get SQL Server.
Have you read my journal today?
Those of us who have used Access have learned to accept that Access edits our SQL for us. In fact, it's a nice "feature" if you like to bang something out in a hurry - Access will automagically reformat the query so it looks nicer.
The real issue I have with Access is not the uncommanded editing, but the data corruption. If a database connection is lost during synchronization (phone line gets dropped, etc...), Access will corrupt the database. What's worse is that Access doesn't detect the condition and attempt to repair the database; from the user's perspective, everything is fine. Which becomes a real problem when others synchronize with a corrupted database, because the corruption will spread to any Access database which synchronizes with the corrupted one. What happens is that by the time someone notices their orders have disappeared from the database (Yes, Access WILL DELETE records during a synch), it's already too late. Instead of merely restoring the master copy, you have to restore ALL copies of the database, and no one can synchronize until you do. It's like having a virus.
Access is fine as long as it is being used as a front end for other DB's, or as a small, stand-alone database. But I've taken to advising my customers not to use Access in a multiuser environment, and to especially avoid synchronizing with another Access DB.
It is fine for teaching database concepts, but I wouldn't want to use it for any "mission critical" applications, and it's certainly not enterprise class software. As long as one makes the distinction that Access is a learning tool, it should work fine.
The society for a thought-free internet welcomes you.
One of the problems with school is that it doesn't last long enough to get much real-world experience. Adding Access will give students a vastly accelerated timeframe for experiencing horrible things. For example, if they were to use PostgreSQL, they'd probably never once experience the thrill of data corruption during their learning experience. Introduce Access, and they'll be exposed to a great deal of it.
As a bonus, perhaps they'll savor the real-world experience of spending $100+ to have Microsoft tech support read the manual to them over the telephone - the same *&%$ manual that they already read and got no useful info from.
Brilliant! Brilliant!
And just teach them with Excel. A spreadsheet provides a good visualization of a table, and since you can do multiple sheets, you could have multiple tables. Problem solved.
If you're even considering Access, this class is obviously not meant to be applicable to real databases anyway, so why make the students do all the extra work. If they ever need to use Access for a real job, they will just treat it like a complicated Excel spreadsheet anyway.
If anyone complains the curriculum is too simplistic, tell them "Research has shown that overall performance is better with significant de-normalization in the database". If they don't understand that, they'll be satisfied by the answer. If they do understand it, they now understand what the class is really about.
For teaching about basic database concepts, there is nothing wrong with access. It's cheap and relatively robust for what it is.
Conformity is the jailer of freedom and enemy of growth. -JFK
or something similar like Oracle, DB2 or any that is Transact-SQL compliant. I know that every vendor probably has their own take on the TSQL standard, but I can't even use MSAcess because the syntax is FUBAR. I'm not a great DBA (or DBMA), but I'd take SQL Server over Access any day of the week just because of the language. However, I do agree that you should try to use the best tool for the job. I just happen think that Access is a glorified spreadsheet.
"It's a dog eat dog world out there, and I'm wearing Milk-Bone underwear."- Norm (from Cheers)
If you learn Access you learn Access.
If you learn SQL you can use just about anything.
Why would your IT dept have anyting to do with setting academic policy anyway?
The CS class focused on theory, as a CS class should. The hows, whys and performance characteristics of joins, unions, indexes, table metadata, full table scans, normalized tables, etc. The environment for assignments happened to be embedded SQL in C (preprocessed) on VMS and DEC Rdb (which is now Oracle for VMS, I think). This was SQL at it's most traditional, but I think would would have been a lot better off using a C API (OCI or ODBC) instead of a C preprocessor. Today, maybe Java and JDBC would be a better choice. The OS didn't matter, and the Database didn't really either. I still prefer to write ANSI SQL as text files and just run it in the client of my choice. ER diagrams would be a really good thing to teach, that I didn't see until later. And some info on object relational mapping might be nice.
The internship involved an Access database for financial data and client reporting. It was on the largish side of what can (should?) be done with Access, so that was a bit of a struggle. I don't think Access is the best place to learn SQL, because it's SQL generation tools are like generating HTML with Word and it's SQL dialect and types are one of the more non-standard ones out there. You don't want to teach bad habbits. I think the Relationship window is invaluble, because it hints at proper (normalized) database design, which is missing from far too many programmers skill set, and can be one of the more abstract things in database theory.
I can see Access having a place in a CS/IT database class, but it shouldn't be the focus or the primary environment for assignments.
Let's see:
What are the characteristics of a database:
-Atomic Transactions - Access doesn't have this
-Multiple session capability - Acces doesn't have it, unless you are willing to put up with locking issues
I used Access for the simplest applications only, where each app installation uses it's own local Acces file to store data. For any real, multi-user database work, I used SQL Server, and now Oracle 9i.
Canadian Pony
I work for a fairly large HMO in Boston, won't name any names to protect the guilty here, but I have to say that both SQL DBs and Access DBs are used in production or as front ends.
For the love of god and all the is good, teach SQL, not Access. Part of my job is to convert all the Access DBs into front ends and the designs that I see every day are nightmare-ish at best. General design concepts and best-particies are not followed at all, and forget about transactional controls or any sort of secuirty.
I'd ask the IT Department the following question:
Pretend that you are a bank and you have to move $10 Million dollars from account A to account B. You run the following steps.
Subtract money from account A.
Add the money to account B.
Now what if the power goes out inbetween the steps?
Now what if the DB is Access?
III.IIVIVIXIIVIVIIIVVIIIIXVIIIXIIIIIIIIVIIIIVVIII
"Grab Firebird, Postgresql, maybe Mysql (kinda lacks some of the advanced sql features) ... and keep Access around for the graphics ideas (just to show students how a database can be exposed to users in a GUI, how tables are laid out, etc.)"
You don't need Access for that.
As some people have already pointed out, Access does mangle SQL, and I don't believe it is fully SQL-92 compliant. Teaching the student with this is teaching them to suckle at ms' teat.
I think you should use Access to compare and contrast. Compare Oracle to Access. Have the students explain why Access is not an ACID db. Let them write sql in notepad. When it comes time for them to get a job writing VBA for access, they'll be more than qualified to use query builder.
Technology Consulting & Free Downloads
Here's the problem. Access rocks for learning the concepts: joining tables, primary keys, etc. Wonderful. But this quickly turns into a comfort zone for the users. They start developing in Access, and their databases grow and grow, and before long they have a 2GB Access database that is trying to support 17 concurrent users.
I guess for some work environments you have to treat Access like candy. Moderate its use, and always explain to them it is a learning or prototyping tool *only*, and not the final solution.
That's the problem w/ some MS tools I find. They're so damn easy sometimes that the people who develop with them don't understand things like estimated growth or concurrency. They just go "hey, look at me! I'm tracking inventory!" If these users don't get weened from Access early in the game they will soon be spending their days repairing and compacting their databases every 4 hours and fielding unending "why can't I connect to the database" calls.
It doesn't have views, it distorts what a database actually is and it fails on the most important learning there is, security.
Access (and it's kin Foxpro) has caused more problems where I work than I can count. Because of the availablity of these "tools" even casual users think they are developers. Then after spending months developing an Access database they find they cannot share it with other users. So they continously bother the IT department with questions such as "how can I share my Access database over the web?"
The query wizard also spits out the worst "SQL" I have ever seen, and I call it SQL lightly. I get questions all the time on, why doesn't my query work. I look at the code and it's 3 pages of outer joins.
It's time to BAN Access and I refuse to support it.
Whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa. Lois, this isn't my Batman glass. - Peter
I've worked with all the major RDBMS(s). Yet, I've never really worked with Access. However, let me throw out a few possible deficiencies. I think MS Access falls short (especially from an educational standpoint) in several categories.
Transactions and the ACID test
Extensive log files (for debugging)
Replication
Query Tuning (Performance Tuning)/reading query plans
Indexing (yeah, I believe Access has this)
Table Statistics
native libraries/APIs that talk to the database, aside from ODBC
Embedded SQL (packages)/SQLJ/Pro*C etc
stored procedures/triggers
backup and recovery strategies (online backups)
How does MS Access handle table locking?
Tons of front-end/built in app server stuff (WebDB/Site Server etc)
Look, I'm absolutely clueless about Access. However, I doubt MS Access supports most of this stuff. This is the stuff that separates a programmer from a database developer/DBA. Frankly, I think MySQL is a great database to learn on. Also check out MaxDB. Heck, I'm sure DB2 probably has some sweet educational licenses. If you're a big department, I'm sure IBM would love to make inroads there.
Before I get flamed about MySQL. I think it's great that you can download and play with almost every aspect of the system. Ever try to muck with Oracle's or DB2's files. You can, but it's much more complicated. You can easily learn about every aspect of MySQL in about a month or two. Then translate that knowledge to a real RDBMS.
Please feel free to correct any of my incorrect statements. Could you tell me what school thinks Access is a good educational database so I can avoid them like the plague? Oh yeah, and what's the IP address of your department's server?
The last line was a joke (that was a parody)!
What do you mean my sig is repetitive? What do you mean my sig is repetitive? What do you mean....
Access has it's pros and cons. As stated in numerous other posts, it's not standard SQL.
Then again, which complex programs use ONLY standard sql? The vast majority of stuff I've seen uses stored procedures or special functions or special syntaxes (like outer join syntaxes) that are database specific. The difference is that Access is tuned to the small, easy, and simple projects, while others are (necessarily) tuned for performance, scalability, or reliability.
One very big plus with Access is that it's trivial to pass around the finished product. It's a single file. Double click and you're good to go. This, to me, would be the deal sealer for a beginners DB course. (Unless of course, you've got a nice VPN setup so that students can work from home while connected to your nicely managed server)
By the way, you can use Access as a "proper" front end to most any database - just be sure to use "passthrough" queries. Those queries do NOT get tampered with by Access and you can do exactly everything that your db engine provides (but for better or for worse, you do not get the visual query builder).
Yeah, overall I'm an Access fan - like everything else in life, you've just got to be smart about how and where you use it.
I've had a lot of experience with MySQL (not sure if it's the same but whatever), and a little with Access. I personally like SQL better
I came. I saw. I got the T-shirt
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.
Microsoft Database Engine is a free limited-connection version of SQL server. It is more scalable than Access, and you can use Enterprise Manager with it. One feature missing from Access is stored procedures (I think), which are essential for proper data layer design. Also, MSDE and SQL server are real server apps, as opposed to Access which is ultimately file-based.
Having used both of them I would say no. Access is a nice front-end and can be used for small databases. But, in order to do anything with it you need to spend a great deal of time learning how to do access-specific things such as forms and so on, all of which exist so that the underlying relational database cen be kept hidden.
If your goal is to teach the students how to make pretty guis but not understand the basic system, and to lock them into a proprietary tool from Microsoft then fine, use Access.
But, If your goal is to teach the students how to use databases and to develop real, portable IT skills that can take them from one job to the next in the workplace environment then give them the real thing; SQL.
The bottom line is, every tool that I have seen built "in" Access (Even in microsoft-only environments) has been riddled with special tweaks and typically had a short lifespan. That's just not worth a class.
What are your goals with your class?
If you are teaching database concepts, access is ok.
If you are trying to have your students walk out with industrial strength, practical knowledge, you should be teaching on Oracle or DB2 using the CLI. Incidentally, Oracle and IBM have academic pricing.
SQL server is a decent comprimise, but is really a "small time" database, except for the extreme cases where the tool was chosen and the DB outgrew it's original intended usage, or a microsoft partner has shoehorned it into midrange or large scale role, where it doesn't really belong, for a Microsoft PR campaign. It is also possible that lack of Oracle and DB2 expertise, combined with a good salesman can make this happen.
Here is the reason: Oracle and DB2 run on more platforms and have better scalability features, so are more popular in high end applications (and mid-low end in some cases). You aren't stuck with running them on Windows systems, which have their own little set of problems.
You won't find anyone running SQL server on a mainframe (natively) or a 32 CPU sun cluster. This is what the kids need to know how to do these days if you want them to be prepared for today's DBA market.
my 2 cents...
l8,
AC
It all depends on what the course being taught is. If you are teaching computer science, I concur with the "read the writings of C.J.Date before touching a computer" post. If the course is "office automation tools" or somesuch(what is the school of communication, anyway ?), Access is certainly suitable - it's very accesible for non-techies.
For what it's worth, I think it's a mistake for a University to teach people who are likely to end up in a programming career using Access - any prospective employer will look at a CV containing Access and think "macro monkey", not "code guru". Check the job-sites - programming jobs usually require a server-scale RDBMS; secretarial and office support jobs require Access. When I hire for programming jobs, I usually assume knowledge of any of DB2, Sybase, Informix, Oracle, Postgres, MySQL, MS SQL Server will relatively easily transfer to our platform, but that someone who uses Access will be too bound to the query builder interface to get up to speed quickly.
It's all very well in practice, but it will never work in theory.
Comment removed based on user account deletion