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:
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.
|
