2.2 The Students Example

All the examples in this chapter make use of a set of tables in a system that tracks students, courses, and grades. You are a database administrator at the local university, which keeps students' records in a relational database. The university requires that each student prepare several term papers for each course that they take. A professor grades each paper that is submitted, and the Score is stored in a table.

The table in which term paper Scores are recorded is named Students. Each row contains a course ID, the student's name (used as a unique identifier for students), and the Score earned for a term paper. Each term paper can have a maximum Score of 25, so you do not have to normalize the Score to a unique base. Term papers are identified by a sequential integer that starts over at 1 for each new course that a student takes. Here is the table structure:

CREATE TABLE Students (
   CourseId CHAR(20),
   StudentName CHAR(40),
   Score DECIMAL(4,2),
   TermPaper INTEGER
)

If you execute the ch01.ImplementingSetDifference.objects.sql script, all the tables needed for the recipes in this chapter will be created and populated with data. The data in the Students table will then look like this:

CourseId             StudentName     Score  TermPaper  
-------------------- --------------- ------ ----------- 
ACCN101              Andrew          15.60  4
ACCN101              Andrew          10.40  2
ACCN101              Andrew          11.00  3
ACCN101              Bert            13.40  1
ACCN101              Bert            11.20  2
ACCN101              Bert            13.00  3
ACCN101              Cindy           12.10  1
ACCN101              Cindy           16.20  2
MGMT120              Andrew          20.20  1
MGMT120              Andrew          21.70  2
MGMT120              Andrew          23.10  3
MGMT120              Cindy           12.10  1
MGMT120              Cindy           14.40  2
MGMT120              Cindy           16.00  3

Each student needs to write three term papers for each of two courses. Currently, Andrew has submitted three papers for the accounting and management class, Cindy has submitted two for accounting and three for management, and Bert has submitted three for accounting and none for the management class.

Other tables related to this example that you may see used in the recipes include: StudentMaster, CourseMaster, and CreditRules. The StudentMaster table tells you which courses a student has registered to take. The CourseMaster table tells you the number of term papers required for each course. The CreditRules table is used in only one recipe and serves to link together the term papers necessary to achieve a specific grade in a course. The structure of these tables will be explained later in the chapter.