Frequently Asked Questions

Q: What is the difference between a database application and a database management system?

A: A database application is a software program that performs a specific function that the user wants to accomplish. This could be to enter, retrieve, or process database information. This program could be written in a language such as C, or it could be written in a fourth generation language (4GL) that doesn't even seem like programming. A database management system (DBMS) is not written in SQL. It takes statements written in SQL and applies them to the database to perform the entering, retrieving or processing required. Generally this SQL is embedded within the application program that could be written in C, Basic, or some other procedural language.

Q: In Access, the "ON DELETE CASCADE" and "ON UPDATE CASCADE" clauses don't seem to work. What is going on here?

A: The reason you cannot get ON DELETE CASCADE and ON UPDATE CASCADE to work is that Access does not support those two operations, among a number of others that are in the ANSI/ISO international standard. For all its popularity and widespread use, Access is not a full-featured relational database management system. Microsoft's SQL Server is much more complete, as are DB2 and Oracle.

Q: How do I enter SQL statements into Access?

A: This question comes up often. Microsoft does not make it easy to find SQL in Access. I think they prefer that people not mess with it. The Access implementation of SQL is incomplete and in many ways incompatible with the international SQL standard. Since Access's particular syntax is incompatible with SQL:1999, as covered in standard reference works such as SQL for Dummies, there are a few places where you will have to code things slightly differently to get them to work in Access.

Here's how to get to SQL in Access:

1. Click on the Queries menu item.
2. Select Create Query in Design view.
3. Close the show table window.
4. Click on the Query menu.
5. Select SQL Specific.
6. Select Data-Definition.

This will give you an editing window into which you can enter SQL code.

Q: I can see how maximum cardinality is used when creating relationships between data tables. However, I don't see how minimal cardinality applies to database design. What am I missing?

A: You are correct in noticing that maximum cardinality is a more important characteristic of a relationship than minimum cardinality is. All minimum cardinality tells you is the minimum allowed number of rows a table must have in order for the relationship to be meaningful. For example, a basketball TEAM must have at least five PLAYERS, or it is not a basketball team. Thus the minimum cardinality on the PLAYER side is five and the minimum cardinality on the TEAM side is one.

One can argue that a person cannot be a player unless she is on a team, and thus the minimum cardinality of TEAM is mandatory. Similarly an organization cannot be a basketball team unless it has at least five players. The minimum cardinality of PLAYERS is mandatory also. One could argue in the opposite direction too. When a player quits a team, does it cease to be a team until a replacement is recruited? It cannot engage in any games, but does it cease to be a team? This is an example of the fact that each individual situation must be evaluated on its own terms. What is truth in THIS particular instance? The next time a similar situation arises, the decision might be different, due to different circumstances.

Q: What is a "User's Data Model"?

A: A userís data model consists of the things the user of a database expects to see in the database. For example if you were doing a database for a school and the users were the teachers, then they would expect to see things such as each student's name, grade for each assignment, overall grade, etc. If the user was the principal of the school then the data model might include contact information for the parents, grades for all classes, etc. One database might have many different types of users so you would need to be mindful of all the userís data models to develop applications that satisfied everyone's needs.

Q: In the Entity-Relationship model, what is the difference between an entity instance and an entity class?

A: Entity instances can be grouped together into entity classes. In a corporate database model, EMPLOYEE is an entity class and Anthony Jones is an instance of the EMPLOYEE entity class. Lawn Mower is an instance of the PRODUCT entity class.

Q: When is it appropriate to buy a "shrink-wrapped" application that meets a fairly standard database design need, as opposed to upgrading an existing custom application? I have an existing database that can be upgraded. However, it crashed - probably because we were using Microsoft Access with a networked system of 3 active users. Now we need to determine if we want to (1.)re-create and improve the existing database, (2.) design a new database using SQL, or (3.) purchase an existing application. I schedule continuing education workshops. I imagine there are several basic "event planning" applications available.

A: I seriously doubt that your system "crashed" because you have three active users. Access can handle much more traffic than three people can provide, even if they are the world's fastest. I am confident your problem was caused by something else. You should probably engage a consultant to track down the real cause. In my experience, packaged applications invariably disappoint. Every organization has its own unique needs and its own unique way of doing things. People are accustomed to doing things that way. If you do decide to go to a new system, I recommend a custom system that is made to look as much like the old system as possible, to minimize the learning curve of your people. However, probably the cheapest solution is to have an expert fix the system you already have. If she examines it carefully and tells you that it is so poorly designed that it is not worth fixing, THEN look for a new solution.