Databases Reference
In-Depth Information
This is nice and compact, and we can easily access grades for any student or any course.
However, we could have more than one student called Susan Smith; in the sample data,
there are two entries for Susan Smith and the Computing Mathematics course. Which
Susan Smith got an 80? A common way to differentiate duplicate data entries is to assign
a unique number to each entry. Here, we can assign a unique Student ID number to
each student:
+------------+------------+---------+---------------------------+------+
| StudentID | GivenNames | Surname | CourseName | Pctg |
+------------+------------+---------+---------------------------+------+
| 12345678 | John Paul | Bloggs | Web Database Applications | 72 |
| 12345121 | Sarah | Doe | Programming 1 | 87 |
| 12345678 | John Paul | Bloggs | Computing Mathematics | 43 |
| 12345678 | John Paul | Bloggs | Computing Mathematics | 65 |
| 12345121 | Sarah | Doe | Web Database Applications | 65 |
| 12345876 | Susan | Smith | Computing Mathematics | 75 |
| 12345876 | Susan | Smith | Programming 1 | 55 |
| 12345303 | Susan | Smith | Computing Mathematics | 80 |
+------------+------------+---------+---------------------------+------+
So, the Susan Smith who got 80 is the one with the Student ID number 12345303.
There's another problem. In our table, John Paul Bloggs has failed the Computing
Mathematics course once with 43 percent, and passed it with 65 percent in his second
attempt. In a relational database, the rows form a set, and there is no implicit ordering
between them; you might guess that the pass happened after the fail, but you can't
actually be sure. There's no guarantee that the newer grade will appear after the older
one, so we need to add information about
when
each grade was awarded, say by adding
a year and semester (
Sem
):
+------------+------------+---------+---------------------------+------+-----+------+
| StudentID | GivenNames | Surname | CourseName | Year | Sem | Pctg |
+------------+------------+---------+---------------------------+------+-----+------+
| 12345678 | John Paul | Bloggs | Web Database Applications | 2004 | 2 | 72 |
| 12345121 | Sarah | Doe | Programming 1 | 2006 | 1 | 87 |
| 12345678 | John Paul | Bloggs | Computing Mathematics | 2005 | 2 | 43 |
| 12345678 | John Paul | Bloggs | Computing Mathematics | 2006 | 1 | 65 |
| 12345121 | Sarah | Doe | Web Database Applications | 2006 | 1 | 65 |
| 12345876 | Susan | Smith | Computing Mathematics | 2005 | 1 | 75 |
| 12345876 | Susan | Smith | Programming 1 | 2005 | 2 | 55 |
| 12345303 | Susan | Smith | Computing Mathematics | 2006 | 1 | 80 |
+------------+------------+---------+---------------------------+------+-----+------+
Notice that the
Student_Grades
table has become a bit bloated: the student ID, given
names, and surname are repeated for every grade. We could split up the information
and create a
Student_Details
table:
+------------+------------+---------+
| StudentID | GivenNames | Surname |
+------------+------------+---------+
| 12345121 | Sarah | Doe |
| 12345303 | Susan | Smith |
| 12345678 | John Paul | Bloggs |
