2.2 The Students ExampleAll 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. |