Database: University Example: Student

The Student table is one table from the University database.

Student Table

Student

student_id	PK
student_fname
student_lname
student_gpa

One issue with this design is that the GPA (Grade Point Average) is a computed field and therefore should not be stored, but computed each time. Because it changes infrequently, and because it is relatively expensive to compute, we're storing it in this denormalized form. If it is infrequently used, it would be better to recompute it each time.

Student - Sample Data

student_id	student_fname	student_lname	student_gpa
1	Mickey	Mouse	2.5
4	Minnie	Mouse	3.8
2	Donald	Duck	2.0
3	Peter	Rabbit	3.6

Student - SQL Definition

CREATE TABLE Student (
    student_id    INTEGER,              /* use SS# */
    student_fname VARCHAR(20) NOT NULL, /* first name */
    student_lname VARCHAR(40) NOT NULL, /* last (family) name */
    student_gpa   FLOAT,                /* grade point average */
    PRIMARY KEY (student_id)
    );

Primary key

The choice of Social Security Number (SSN) for the primary key will only work if all students have SSNs. There are also legal issues with requiring this, so it must be possible to create fake SSNs in some cases. An arbitrary key would solve these problems, but would probably prove more awkward to use in real life.

Denormalized field

The grade point average (student_gpa) is a computed field, which violates the rules of normalization. References to student_gpa could be replaced by a subquery that computed the equivalent value. Making the query that computs the gpa would not be quick, so there would be a lot of motivation to store the computed gpa as a field.

A trigger (stored SQL or PL/SQL commands that are executed with something is changed) would be defined that would recompute the student_gpa value whenever grade is entered or changed for that student.