|
|
|
|
||
        |
|
 |
|||||||||||||||||||||||||||||
 |
 |
|||||||||||||||||||||||||||||
|
Database Guru by Randy Overbeck This column is the first in a series of columns that will help to explain the design process I went through in creating my Online Testing System for the Office of Distance Learning at Rochester Institute of Technology. I have been developing the system on a part-time basis for the last 2.5 years, while I worked my B.S. degree in Microelectronic Engineering. I graduated in May and I started work with Apple's Hardware division in July. The system is designed to offer Quizzing and Testing administration and execution with only a forms capable browser. Faculty and students see a different interface based on their needs. The software used in the system are FileMaker Pro (v4.1) databases, Lasso 3.0 Plugin for Webstar, and Webstar 4.2. The server is a G3/300 with 192MBs of Ram and it serves this solution plus a few other much smaller volume databases. Currently the system is heavy use by aproximately 6 Distance Learning Faculty and in casual use for about a dozen more. If you have any questions or comments please direct them to me, Randy Overbeck at randy@macosjournal.com.
Partitioning and Relationships A very important consideration in any large-scale database design is the need to subdivide the design into smaller segments that are much more manageable. Although my views on the best approach for organizing my project has changed over the last 2.5 years, I think it is still important to explain some of my decisions so that you can learn from my mistakes. The first step was evaluating the problem that the office faced. Basically they we're looking for a solution to replace two of their most time-consuming activities; surveying their students and testing them. In the past, paper surveys were mailed to students and then returned by an included postage paid reply envelope. When they arrived, the results were entered by hand into a FileMaker Pro database. Two major problems with this approach were the input process and the very low response rate. The second issue was remote testing and quizzing. Normally in a distance learning environment testing is performed with a system of proctors and a special mailing with just the test. This method also has two major problems. The first problem is the turn-around time, which is a minimum of a week. The second is that because of the time and effort involved (usually FedEx shipments and 1.5 week turnaround from Test mailing to grade/feedback to students), this process is impractical for quizzes. With these two problems outlined I begin work on designing an Online Testing System. Previously I designed a surveying system that was dependent on the content of the survey (it was simpler to do this initially.). This proved to be very problematic when the Distance Learning Staff wanted to change the questions. So I went back to the drawing board and put together a design which was independent of the content it was displaying. This was a key element that has allowed me to continue to extend the original design for over 2.5 years. My first step was to partition the System into five Databases. The five partitions were a question Database to store all of the questions the Faculty added, a Test Database to store the question data that was in one of the tests, a Student Results Database to store the student's answers, a Logging Database to keep track of the number of times a test was taken as well as date and time, and a final database to store user data like courses enrolled, account status, plus the usual user registration data.
This partitioning system leads directly to the relationships that form the foundation of the system of databases. One-To-One Relationships (One log record for each test that the student has attempted) along with One-To-Many Relationships (One Test containing many questions) are used in the system to minimize duplication of data as well as to allow for multiple points of entry based on the user and the situation. One example of this is when a student goes to look for his/her grade on a test or quiz they only see the date and the score they received, but when the faculty looks at the same data in the database they are given a breakdown that includes all of the questions, the correct answer, the student answer and the students score on each individual question along with a score for the entire test. The important point to emphasize is that databases allow for multiple points of entry that give only the appropriate data for a particular situation to a particular user. I have produced a diagram, included below that illustrates the major relationships that are in place in my Online Testing System.
The first thing to note is that there really aren't that many relationships. Even with 5 databases the general flow of information is easy to understand. Of course I have left off some relationships, but I will explain those later in the column and in subsequent columns. Let me first start with the simplest database and explain some of its features.
Faculty Question Database The Faculty Question Database has the ability to store five different types of questions, four of which are graded by the system. The five types are One Answer (Multiple Choice), Multiple Answers, True/False, Short Answer, and Essay. The Essay questions are the ones that aren't graded by the system and the Short Answer questions must be exactly the answer given by the faculty (For example, "George Washington" would not be considered the same as "Washington" if the question was asking about the first president of the United States.) Even with these limitations (most of which I imposed to make my development easier) there is a large variety of questions. Example Page with one of each of the question types. I also built in several features at the request of a few faculty members. These were actually easy to implement because I planned for them from the onset of the project. One of these features was the addition of Groups which enable faculty to organize their questions into subsections. This proved critical for one faculty who has over 2800 questions in 4 classes. I also added question hints or feedback so that if a question is missed the system can help the student to find the right answer. Two other additions that aren't used very often are fields for graphic links and outside page links (by outside, I mean outside the scope of my testing system). Example page to create a question with each of these possibilities Now to actually track a question for its inclusion in Tests and Quizzes the system uses a Unique Number. This is a simple number field that forms the basis for the one-to-many relationship with the Faculty Tests Database in order to construct the Tests.
This method allows me to easily share one question among many tests. One significant benefit is they all point back to the source so if a question gets corrected it will be fixed in all the tests & quizzes that contain it. It wasn't always that way though. I thought that a method that has fields that are Auto Entered (which means they are filled automatically based on the unique number, but are only filled the first time that value is inserted) would be much faster than a calculation. Even on a reasonably fast server (G3/300 with 192MB of RAM) Test creation of a 25 question test using this only "fill in the first time" method took more than 50 seconds, and the worst part about this was the system was locked into this task for that period of time. Unfortunately a major limitation of FileMaker Pro is that it isn't Multi-threaded. If properly designed this isn't an issue even with sites that get 20000-30000 hits per hour, but above that this can be a real source of performance loss. Of course this Test creation portion was a worst case scenario for me. I finally forced myself to reconsider the option of calculating these every time they are needed (called a Calculation field in FileMaker Pro). This actually proved to be over twice as fast, so the moral of the story is don't assume that your first hunch on the best method to use will always be right.
In my next column I will elaborate on the other databases in the system and begin to explain the role that Lasso plays in the solution... If you have any suggestions or comments please email me Randy Overbeck at randy@macosjournal.com.
|
||||||||||||||||||||||||||||||
|
 |
|||||||||||||||||||||||||||||
|
