# 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.