Eric's Beijing Diary

When I came to Beihang University, I set about to design a course for undergraduates that would teach them the fundamentals of Structured Query Language, while at the same time helping them to transition to an English language learning environment.

In the fall semester I teach two sections with about eighty students in each section. The key to faciltating learning in this type of situation, is to evaluate frequently, and give fairly immedate feedback. I am using a text book with twenty review questions at the end of each chapter. If I have the students turn it in on paper, it is too easy for a group of students to each do a few questions, and then combine their work. But if I make it a blind test, I am requiring too high a level of memorization to be practical every week. So what I do is to tell them they are going to have a test, invite them to study the questions all week, and then take the test online without the benefit of the book. This effectively makes it a worksheet they have to memorize.

In a given semester, I give ten chapter tests. With 80 students in two sections each taking 10 tests with 20 questions each, that comes to 32,000 separate answers for my TA's to check in one semester (not counting the mid-term and the final). But that is only half the problem. Data entry for 160 students is a tedious process that lends itself to error. Students sometimes have numbers that are very similar. The last few digits might be the same, but the first few would be different. I am not good at this kind of work. And if I make one little mistake, I often have to start all over. My TA's can do this work for me, but I have no way of checking whether they have entered the grades accurately. Even if the papers were all graded by computer, the data entry process is a nightmare. Needless to say, I have to have a computerized system in order to do this effectively.

When I first came to Beijing, I was using a free online testing system that I had employed when I was in Arizona. But a couple years ago, this company announced that their testing system was no longer going to be free. They left the .html files in a special location on their website, but the database was no longer available. So when students took the test, I would get an email with their answers. But there was no longer any automatic scoring system. I had to have my TA's go through each test and compare the student's answers to the key. Fortunately, I had one TA who was a very fast and efficient young lady. She could check through a bunch of tests in a very short period. The other TA didn't like doing this. So he spent his time designing a program in C# that would read the emails, check the tests, and give the students a score. Using this program, it took me seconds to check all the tests. But the other thing he did, which saved my neck, was that he copied all the .html files for the tests, and went to work to develop a system that could function entirely independent of the Internet. Unfortunately, he had some stuff to do, so he left in the middle of the semester, and recruited another student to take his place.

Last year, just before the fall semester was going to start, I discovered that the online testing system had discarded all the .html files. Fortunately I had all the files that Titan had copied, and one of my new TA's was a good friend of his. Justin went to work right away to finish the program that Titan had started. What we have now is not really completed software. It is software in development, but it gets the job done.

Click picture for larger image.
The students go to a private intranet web site on my server and take an objective test by clicking on the radio buttons. When they submit the test, the testing system that Titan and Justin built creates a .sql file with their score. Titan was worried about possible concurrency problems (a file being locked and the system crashing if two students submit at exactly the same time) if the results were entered into a single file, so Justin set it up so that I get a separate file for each student. When I open the file, I see a statment that looks like this:

update gradesheet set CHAP_05 = 85 where student_id= 'AM37211406';

This statement, when run, enters the student's score in the database. They were apologetic about having to create separate files for each student, but I told them it was a non-issue. I simply created a master file called loadscores.sql that calls the other files. When you open it, here is what you see:

@@AM37211404
@@AM37211405
@@AM37211406
@@AM37211407
@@AM37211423
@@AM37211408
@@AM37211409

So I call that one file from the database, and that file calls the others. This process takes about 3 to 5 seconds. Actually, it's closer to ten seconds if I run both classes at the same time. I spool the output to a .doc file so that I can check the results of this process. If a student is absent, I can tell right away, because their is no file for them. So the tests are scored by the system as soon as the tests are submitted, and the data is entered with I run the loadsores script. Less than ten seconds to have all the data in the database. The only thing on paper is the final exam. I give ten chapter review tests, and the mid-term in the lab using my automated system. At the end of the semester, I use Structured Query Language to create a view that subtracts the lowest score for each student (I don't allow makeup tests, so this effectively allows each student to miss one test without penalty), and gives them a grade based on the remaining 9 tests, the midterm, and the final exam. Here is what the SQL code for my final report view looks like:

create or replace view sqlreport as
with labgrades as
(select student_id, sum(score) LabTotal, min(score) Lowest
from sqlrecords group by student_id)
select student_id, labtotal, points bonus, labtotal+points Total,
lowest, (labtotal+points)-lowest NetScore,
round(((labtotal+points)-lowest)/9) labavg, midterm, final_exam,
round(((((labtotal+points)-lowest)/9)*.5)+(midterm * .2)+(final_exam * .3)) Final_grade
from labgrades natural join bonus natural join gradesheet
order by student_id

This takes care of my end of it. But, as I mentioned previously, the most vital component of this system in rapid feedback. If students get constant feedback about their progress, they will do much better than if they are left to guess how well they are doing. What I do to facilitate this,is to create a user for each student, then create a view in each user's schema that will allow them to see their own grades at any time. I have the students register by entering their own data in the database at the beginning of each semester. Then I copy this list from the database to Excel, and use Excel to write repetitive statements to make a script that creates the users and views. This takes about 10 or 15 minutes at the beginning of the semester, and I usually do it in front of the class so that they can see how the process works. When I run this script, the users are created, given the needed permissions, and a view is created for each of them. When the log in and execute the code to look at the view, this is what they see:

SQL> select * from mygrades;

STUDENT_ID GRADE_ITEM SCORE
------------ ---------------- ----------
AM37211### Bonus Points 60
AM37211### Chapter Two 75
AM37211### Chapter Three 70
AM37211### Chapter Four 85
AM37211### Chapter Five 85
AM37211### Chapter Six 100
AM37211### Chapter Seven 95

7 rows selected.

SQL>

Every class period, I give students a chance to earn bonus points by doing hands on exercises in front of the class. They are hesitant to volunteer at first, but when they see the effect of the bonus points, they soon get bold enough to take the opportunity to improve their score.

After I had implemented this system last year, Alina observed that we there were fewer students in the room at test time than the number of tests would indicate. I told her that she would have to require the students to sign in so that only students who actually who had come and taken the test themselves would be given credit. But Justin told me he could fix the problem. He added a feature that creates an answer file for each student named by the MAC (Media Access Control) address and IP (Internet Protocol) address of each computer. If a student tried to take a test twice, the presence of the answer file from the previous test would block submission, and the student would get an error message:

Some clever students discovered that they could change the IP address and submit another test. But they were not clever enough to realize that the minute they did that, they would have red dye all over their hands. Here is what the results of that mischief looks like (the student_id's have been partially masked to protect the guilty):

50-63-1C-15-D0-B9_192.168.0.189_36211###.txt 12/17/2008 5:35 PM
50-63-1C-15-D0-B9_192.168.0.178_36211###.txt 12/17/2008 5:36 PM
50-63-1C-15-D0-B9_192.168.0.179_36211###.txt 12/17/2008 5:36 PM

You can see that all three tests were taken from the same computer (note the MAC address), but that the IP address has been changed so that the test could be submitted three times. I put the corresponding test scripts in a separate folder so that they would not upload, effectively giving all three students a 0 for that test. The student who had actually taken the test came to me and complained. I showed him the results. He insisted that this must be three separate people who couldn't find a free computer, so they had to change the IP address. I then pointed out the times to him. Not enough time for three separate people to take three separate tests. Just enough time for one person to copy the answers from his test to two others, change the IP address, and submit them separately. He finally admitted that his classmates didn't come that day, so he had taken the test for them. He conceded that they should not get a grade for the test, but he insisted that he was entitled to a grade, since he was the one who had actually taken the test. I disagreed.