Database: University Example: Student

The Student table is one table from the University database.

Student Table

Student
student_idPK
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_idstudent_fnamestudent_lnamestudent_gpa
1MichaelMaus 2.5
4MinnieMaus 3.8
2DonaldDuck 2.0
3Peter Rabbit3.6

Student - SQL Definition

CREATE TABLE Student (
    student_id    INTEGER,              -- some unique id
    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)
    );

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.