Building a Simple Datesheet

Datesheet is a common scheduling problem faced by most educational institutions. I am going to tell you in detail what a datesheet is and how to build one.
What is a datesheet?
In an educational institution, we have students who are registered in courses.
Now, we want to have an examination after some period.
In the examination, each student will give a paper for the subject s/he is registered.
We have some limited number of days during which all the students of a given course will be giving papers at the same time. And we have more than one courses, and we want to take the papers of all courses.
If a student is registered in more than one course, then s/he can’t give the paper for all the courses on the same time, but s/he can give one paper in one day, and then the second paper on the second day, and so on.We want to make an arrangement of courses in some given days so that all the students give the papers for all registered courses, but with the following conditions:
  1. A student can’t give more than one papers at the same day/time.
  2. All the students in a course will give the paper for the course at the same time.
  3. Students of two courses can give their papers at the same time only if there is no student registered in both courses. This means, both courses have no common students.

There can be other conditions, depending on the institution and type of examination. But the above conditions are the basic conditions. One extra requirement which can arise is: How to arrange all the papers in minimum possible days, while satisfying the above conditions? First lets focus on simple datesheet making (arranging of papers).

How to build a datesheet?

Now, we want to make such an arrangement so that the above conditions are met.

  1. Make a list of courses, while each course will contain a list of students registered in it.
  2. Lets have a list of days, while each day will contain the list of courses whose papers will be arranged on that day.
  3. Let current-day is the first day.
  4. Get one course and add it to the current-day.
  5. Get an other course, call it current-course.
  6. If no student in the current-course has a course in the current-day-courses, then add the current-course to the current day. This will be a little hard to grasp and implement but this is the main step. This step will ensure that the 1st condition is satisfied.
  7. Else skip the course.
  8. Go to step 5.
  9. If there is no course left with no student in the current-day courses (in other words: all courses left have students with at least one course registered in the courses in the current day), then add another day to the days list and call it the current day, and then go to step 4.
  10. If all there is no course left (all courses are added to days in the days-list), then save the days along with the courses in each day and call it the datesheet, and terminate.

This is how to build a simple datesheet.


Indexing in Database: Must Do It

I work in CUSIT (City University of Science and IT) as software engineer. I and my colleague do software development, maintenance, and database development and administration. Two days before, accountant called me and complained about an error in a report generation. When I looked, it was timeout error. I checked the underlying query and apparently everything was OK. I ran the query in sqlserver explorer and it took one minute and fifteen seconds. I ran it again and it was speeded up by one second. Query optimization!

The query returned only 18 records. It had several tables inner joined. I rearranged the query and started to execute it incrementally, by joining one table at a time. And without a single table, the query executed in less than a second. The problem was in the last remaining table.

I looked in the table definition.

What? It has no primary key! It has more than 2000 records but no indexing! All but one fields are used for comparison in the join but no indexing!

I added an auto-increment ID field as primary key and indexed all the fields except the one which only had true/false value and was not used in direct comparison.

I ran the query again and now its running time was less than a second. Wow, at least 75 times performance!

I searched for some other tables and found a few others which needed primary keys and indexing. My colleague said that there were no indexing and primary keys in some other tables as well. It was a legacy from our older boss, which was considered a must. He made several mistakes which we (I and my colleague Farrukh Abbas)  corrected after his leave.