Section 2.15. Finding the Complement of a Missing Set

2.15 Finding the Complement of a Missing Set

2.15.1 Problem

The query in the previous recipe has one significant drawback: it does not report missing term papers for students who have not yet completed at least one term paper. That's because the driving table is the Students table. If a student hasn't handed in at least one term paper for a course, then there won't be any records in the Students table for that student/course combination. How then, do you report missing term papers for such students?

2.15.2 Solution

The key to reporting missing term papers for students who have not yet turned in any term papers is to find a reliable way to identify those students. There are two possibilities:

• Define an empty row in the Students table to identify each student.

• Create a master table that contains one row for each student.

From a database-design perspective, the second solution is probably the best, because you end up with a typical many-to-one relationship between two tables. The first solution is something of a kludge , because it uses the Students table as both a master table and as a detail table.

2.15.2.1 Solution 1: Define empty rows in the Students table

Our first solution calls for empty records to be inserted into the Students table for each student/course combination. For example, the following record would be inserted to show that David was enrolled in the ACCN101 course:

 INSERT INTO Students(CourseId, StudentName, Score, TermPaper)  VALUES('ACCN101','David',0,0) 

Notice that the Score and term paper number have both been set to 0. With these records in place, the query presented in the previous recipe can be used to display the list of missing term papers. This list will now include cases where a student has missed all term papers in a given course. The zero-records that we've inserted won't show in the final result because our query excludes the Pivot table row for zero.

2.15.2.2 Solution 2: Create a student master table

A cleaner option than creating special zero-records in the Students table is to create a completely separate student master table. This table would then track each students's enrollment in the various courses offered by the university. The following shows one possible implementation:

 CREATE TABLE StudentMaster(    CourseId CHAR(20),    StudentName CHAR(40) ) INSERT INTO StudentMaster VALUES('ACCN101','Andrew') INSERT INTO StudentMaster VALUES('MGMT120','Andrew') INSERT INTO StudentMaster VALUES('ACCN101','Bert') INSERT INTO StudentMaster VALUES('ACCN101','Cindy') INSERT INTO StudentMaster VALUES('MGMT120','Cindy') INSERT INTO StudentMaster VALUES('ACCN101','David') 

With the StudentMaster table in place and populated as shown, you can generate a report of missing term papers using the following query:

 SELECT  s.StudentName, s.CourseId, f.i TermPaper FROM StudentMaster s, Pivot f WHERE f.i BETWEEN 1 AND 4 GROUP BY s.StudentName, s.CourseId, f.i HAVING NOT EXISTS(    SELECT * FROM Students     WHERE CourseId=s.CourseId AND        StudentName=s.StudentName AND        TermPaper=f.i) ORDER BY s.StudentName 

The report lists the missing term papers for each student:

 StudentName   CourseId             TermPaper    ------------- -------------------- -----------  Andrew        ACCN101              1 Andrew        MGMT120              4 Bert          ACCN101              4 Cindy         ACCN101              3 Cindy         ACCN101              4 Cindy         MGMT120              4 David         ACCN101              1 David         ACCN101              2 David         ACCN101              3 David         ACCN101              4 

2.15.3 Discussion

If you think about it, both solutions in this recipe require the creation of a master table for students and courses. In the first solution, that master table is layered onto the existing Students table, while, in the second solution, the master table is a completely separate table named StudentMaster.

The query shown for Solution 2 uses the StudentMaster table as the basis for preparing a list of groups that need to be considered . Each group represents one student and course combination. The master table is joined to the Pivot table, and the result is a complete list of all required term papers for all student/course combinations. The HAVING clause checks the Students table to see what papers have been submitted and reduces the result of the main query to only the term papers.