This data clearly demonstrates its semantics - one student may take many courses and each course may have many students.

You must deliberately set aside enough space to store multiple course names. Setting aside only 5 characters forces you to store only one course name at a time.

Imagine that you wish to determine the total enrolment in all computing courses, i.e. courses that start with 'CP':

          
SELECT COUNT(studentId) FROM ...
  WHERE courses LIKE '%CP%'

        

This returns a value of 2 rather than a correct value of 4 because each tuple is counted only once. A proper counting requires a much more complex SELECT statement.

• Although each attribute contains an atomic value, each attribute represents the same ‘fact’.
• This is also very difficult to query or update.

The count from the previous example is now:

        
SELECT COUNT(studentId) FROM ...
  WHERE course1 LIKE 'CP%' OR course2 LIKE 'CP%' OR course3 LIKE 'CP%'

and this still does not give you the right answer. Furthermore, updating means finding the first free attribute of the three - which is non-trivial to put it mildly - and what happens when a student takes four courses?

This seems to have introduced some redundancy, but it now means that our enrolment count statement works properly since each tuple is counted once and only once.

        
SELECT COUNT(studentId) FROM ...
  WHERE course LIKE 'CP%'

We'll see how to reduce redundancy with other normal forms.

Note that the semantics of the data are preserved: one student may still take many courses and one course may have many students. If the data semantics are violated by a normalization procedure then we have done something wrong.

This relation is ripe for generating anomalies. Whenever a student is added to a course the student's name as well as studentId must be added to the relation. If it is added incorrectly then we have one studentId with multiple names attached to it. Similar things can happen with the combination of instructor and course. When a tuple is deleted from the relation, if the last student for that course is removed then the course is entirely removed from the database unless we make a point of storing NULL data for the removed student. This is awkward and ugly. Common sense, and normalization techniques, tell us there must be a better way.

Note: NULL values for the grade before the final grade is assigned are perfectly valid.

We have defined {studentId,course} as the primary key for this table, and indeed as a key it uniquely identifies each tuple. However, the tuples above violate 2nd Normal Form. The non-key attributes surname and forename are only partially dependent on the primary key since the attribute course can be removed from the primary key, and the dependency still holds. i.e. surname and forename are fully functionally dependent only on studentId. Similarly, the non-key attribute instructor can be identified by the partial key {course}. Only the grade attribute is fully functionally dependent on the entire primary key, i.e. {studentId,course}→grade.

The relation’s functional dependencies are:

{studentId,course}→grade
studentId→surname,forename
course→instructor

surname, forename, and instructor are functionally dependent upon only partial keys and thus 2NF is violated.

These functional dependencies arise out of the semantics of the data in the relation. We know that students have names as well as numbers and that although a name may be repeated (David Brown is fairly popular, for example), student IDs are never repeated. If we do not understand the semantics of the data we cannot determine the function dependencies.

Note here the huge potential for reducing data redundancy. Now each combination of course and instructor is given once rather than once for every student in the course. Similarly a student's name appears only once.

The opportunity for data corruption is also greatly reduced. Now a student's name appears in one place only. An instructor's name can still appear against many courses, but that could be fixed by giving each instructor an ID number and storing their name separately, as with students.

The UML schema:

The following violates 3NF because at least one of the non-prime attributes is dependent upon another non-prime attribute:

(Note that WLU uses a 12 point GPA system.)

The functional dependencies for grade and gpa are:
grade→gpa
gpa→grade

Both grade and gpa are non-prime attributes because they are not part of any candidate keys - they do not contribute to the uniqueness of a tuple. Thus 3NF is violated because a non-prime attribute (either grade and gpa) is dependent upon a non-prime attribute (either grade and gpa).

Not all violations of 3NF involve functional dependencies that are \(X→Y\) and \(Y→X\) as we have in this example. It is sufficient if only \(X→Y\) and both \(X\) and \(Y\) are non-prime attributes.

Because 3NF is violated this table is subject to update anomalies. What happens if a student receives a grade of A+ but a GPA of 0? Which is the correct mark? (If you are a student the answer is obvious, of course.)

The relation’s functional dependencies are:

{course,studentId}→grade
{course,studentId}→gpa
grade→gpa
gpa→grade

Thus these transitive dependencies are also true:

{course,studentId}→grade→gpa
{course,studentId}→gpa→grade

Having discovered what the functional dependencies are and the fact that we have at least one transitive dependency forces us to fix the table. We can use these functional dependencies to guide our decomposition. Note that we may have to make a choice as to how to decompose a table. Sometimes this choice is arbitrary.

The original relation can be decomposed into:

The UML schema:

This decomposition removes the update anomaly problem. Now grades appear in one place only and a simple join is enough to connect them back to a student's course.

An equally valid decomposition involves a table where grade is the primary key and gpa is the dependent attribute. The choice in this case is arbitrary.

The following is in 3NF but not in BCNF:

Note that in the semantics for this data each student may have only one tutor, but each tutor may have many students.

This table is subject to insertion anomalies as both the Tutor ID and SIN must be entered whenever a tutor-student pair is entered. Here again, common sense tells us that this table is not well designed, even if we don't immediately recognize that it violates BCNF.

• Candidate keys are: {studentId,tutorId} and {studentId,tutorSin}
• tutorId→tutorSin and tutorSin→tutorId, but because both tutorId and tutorSin are prime attributes (i.e. they are both potentially part of a primary key as in the first point) these FDs do not violate 3NF (which is concerned with non-key attributes).
• Neither tutorId nor tutorSin alone are superkeys, and thus BCNF is violated.

Both tutorId and tutorSin are prime attributes because they are part of candidate keys. The use of both attributes in this table violates BCNF. The fix is to use only one of these two attributes. As noted before, BCNF is a stronger version of 3NF because it restricts both prime and non-prime attributes, while 3NF only restricts non-prime attributes.

The original relation is decomposed into:

The UML schema:

Why this decomposition over the other possible decomposition, where student IDs are matched against tutor SINs? Here outside considerations help in our decision. SINs are treated specially and should be provided extra protection and privacy. The best way to do this is to minimize their use. Given a choice, then, between the two decompositions, the one that minimizes the use of SINs is the best.

Again, update and insertion anomalies have been removed.

For example, assume that a student may take more than one course and have more than one major. Thus student has two multi-valued dependencies studentId↠course and studentId↠major. 4NF is violated by the following table:

Note that BCNF is not violated because there are no functional dependencies in this table. In fact the primary key for this table is made up of all three attributes, and thus there are no non-prime attributes.

This table contains two independent multi-valued dependencies. course and major are each multi-determined by studentId, but are independent of each other. In such a case every possible combination of studentId, course, and major must be included in the table to make sure that no data is left out. (Imagine if a student was taking five courses and had two majors - matching each major to all courses is the only way to insure consistency of data). This table is just asking for data anomalies.

and

As in the original table, the primary keys for each of these tables consists of all the attributes in the tables.