A better database management system, for teaching databases

Question

I have been teaching databases, over the last few weeks, to key-stage 3 pupils (year 7 and 8).

The school I am at uses Microsoft's Access. This seems adequate in many ways. However it has many problems, that frustrate pupils and thus, get in the way of learning.

For example:

• Continuously asking “do you want to save?” (It should just save. I would not be entering this if I did not want it saved. This is not the 1980's when saving work would take a considerable chunk of your day. It could have it done before I have realised that it has asked.) I have noticed that pupils often select no, even though I spend a considerable time, telling them to save often, to save when prompted, etc. There was a study that showed that when experts see a save prompt pop up it MS-word, they click yes without even reading it. However when a novice sees one, they read it 20 times, then click no (thus loosing there work). When asked the novices said, that the computer was asking permission, so it must be something dangerous.

• Not letting us rename an object, if it is open (this will seem normal to Microsoft user, as this is how there file-system works, bit it is confusing).

• Not letting us delete something that has a error. I get called over a lot to help a pupil that is getting errors, they can see what is wrong, and want to fix it, but the computer won't let them fix it until it is fixed. e.g. They know that they need to change view to fix the error, but the computer will not let them change view until they fix it. The work around is often unintuitive. Or half adding a field to a table, it will not let you do anything until it is complete. Pupils see their error, and try to delete the field, but this is something and it is not allowed, until they finish. I have to tell them to set it to something random, then delete it. I use this as an opportunity to talk about usability design.

• There are others, but I have not seen them all, and can not remember all that I have seen.

Can you tell be of a better database management system, for teaching databases?

Answer 1

At the state college I work for we stopped teaching Access as the "intro to databases" class a while ago. I taught the class this term using MySQL and MySQL Workbench. We had it installed on our lab machines (windows) and I also provided a VirtualBox VM with a Linux desktop, the MySQL server, the Workbench program, and a few other goodies.

Since I ended up with 2 extra class periods after the content was all delivered, I spent a little time showing different ways of interacting with the mysql server (command line, phpMyAdmin, code interaction via PHP and Java) as well as the mysql server management from the Linux class I teach.

Our progression in our AS degree track -

Intro to SQL - all inserts, updates, selects, joins, transactions, views, stored procedures. Nothing on creating tables - just working with existing databases and tables.

Databases 2 - all table structure and relationships, referencing back to Intro class constantly.

second semester programming classes (C++ or Java) get into workign with a database from a programming language.

For our batchelors program, there is more use of programming and databases, with students learning MongoDB. The batchelors level programming classes all use database connectivity, although with the Android class we move from mysql or mongo to sqlite.

Answer 2

Disclaimer: This recommendation comes from my experience teaching web development (and using an ORM to interface with the database), because I've never had the opportunity to teach a database management course.

I'd recommend using an RDS like SQLite. SQLite is an RDS that stores the database in a binary file, and has a SQL command line interface. I find it fairly simple to use, and a good introduction to how relational databases work and how to interact with them.

It has GUIs, such as SQLite Studio (which I've never used, but looks useful) and is fully cross platform. It also provides a good stepping stone to more typical production databases such as PostgreSQL, MySQL, MS SQL Server, etc.

It is also open source and in the public domain.

If you choose to teach from the command line interface, that'd easily eliminate the GUI related issues you mention in the question, and would also force the students to interact with the database using SQL (which is a good and bad thing).

Answer 3

I'll start by saying that my experience is in industry, not in a classroom. I worked for a fortune 500 company that brought a new division up to the billion dollar per year mark using an MS Access database. For five years, over a decade ago, I was one of the people working in Access daily to expand and manage the product until it was retired.

While MS Access has flaws and weaknesses, it's a real powerhouse in the right hands. Tuning an MS Access database is much like tuning DB2 without the tools. We can actually blame Access for the prevalence of SQL based solution in enterprise computing - IT departments using ISAM/VSAM/other "no sql" solutions couldn't compete with a business user at their desktop for cost/benefit for projects.

If you want to get enterprisey, all of the major players (Oracle, IBM, Microsoft) have free and education licenses. Of the three, MS is my own preference.

PostgreSQL is the only open source RDBMS that I recommend, mainly because it's the only one positioned as enterprise ready. MySQL and SQLite are positioned as entry level databases for projects that don't require enterprise features.

Edit: Comments prompted this realization that what I look for in a database for enterprise work is much the same as what I would expect educators to look at, and a list of those attributes might be useful, in no particular order.

RDBMS's are mission critical components that develop almost cult followings. While I tend to favor some RDBMS's over others, I have no emotional investment in favor of any I have used over any other.

1. Ease of Support - if a system requires a lot of support, it is expensive for the enterprise and untenable for the education market. DB2 is incredibly hard to support, so it is one I rarely recommend.

2. Consistency - if the RDBMS behaves in a consistent manner under a broad range of conditions, many quirks can be tolerated and worked around.

3. ACID compliance - Does it support "all or nothing" transactions spanning multiple statements? Partial commits are hard to debug. ACID compliance means easier error handling and better focus on the problem at hand. MySQL and SQLite fail this test, but are still fine for many types of problems.

4. Locking behavior - every RDBMS has is own way of handling latching and locking, which is best under specific circumstances. In my experience, generational or non-locking architectures are easier to work with in general because locks are rare events.

Postgres, SQLServer, and Oracle are least prone to locking issues. DB2's locking behavior is a consistent source of complaint from develoeprs and support teams. MySQL locks the entire database on every write.

Real life scenario - kids volunteer as developers with open community game servers and get good experience. I don't have the patience to tell the kid over and over that he's fixated on the wrong RDBMS for that particular job because MySQL locks the entire database for every write operation, and saving game state in a MMORPG is too write intensive for that product.

5. Standards compliance - none of the RDBMS' are 100% standards compliant, but the big three and postgres are gradually narrowing in on standards compliance. They mostly extend the standard versus violate it.

6. XA support - this allows products spanning multiple databases and even non-RDBMS data sources/sinks to use multi-phase commit logic to ensure the ACID compliance spans the entire system. Whether or not this is important depends on the system you are building. The big three and Postgres all have good XA support.

6. Support system - When you have a problem, can you find help?

In my experience, DB2 communities tend to be unhelpful ("why does't you company hire a real DBA?" is a common response to questions. IBM has education and free license options, but IMHO it's a pain to manage.

Oracle communities are very helpful - Tom Kyte is/was the product architect at Oracle, and his blog "Ask Tom" is one of the best sources of help for Oracle issues. Oracle's full standard database is freely licensed for non-production use. Oracle is difficult to set up initially, but very stable after that.

SQLServer is so pervasive now and is so easy to handle compared to the other "big three" that answers are usually a "google" away, usually on Microsoft's technet site or one of the Stack Exchange communities. SQLServer is easy to set up and requires almost no management once it is configured.

The open source databases tend to be very well supported by their developers and user communities.

Overall

There is no reason not to use the "big three" given the free and educational licensing options available. Of the three, SQLServer imposes the least disruption to the development (or education) process.

Postgres is right up with the big three for reliability and features. The first upgrade path for successful projects built on MySQL is usually to convert to Postgres. Since MySQL and Postgres are the same price, why not start with Postgres if you prefer to use an open source database? Postgres is (in my experience) less quirky, allowing you to focus on the matter at hand.