Hands on: Database normalisation

What happens if, for CTW405, we swap the value in StudentID02 with that in StudentID3. The information the table can give us remains unchanged: students 3, 8 and 7 are enrolled for CTW405.

Now look again at screen 1 and try a similar swap in the row labelled P1. If we exchange the value in the Q02 column with that in column Q03, we have altered the answers given by person P1. P1 answered ‘d’ for Question 02 and ‘a’ for Question 03, not the other way about, so the table no longer tells the same story.

We can sum this up by saying that while the StudentID01 column could contain the ID of any student, the Q01 column can only contain the answer to the first question. So the difference is that the question columns are not repeats because each one is pointing to a known, distinguishable object.

The design of the CourseAttend table therefore contains repeating columns and the table does not conform to first normal form. Despite the presence in screen 1 of multiple Qxx columns that appear repetitive, they are not repeating columns and so the table meets the requirements of first normal form.

It also meets the requirements of 3NF form, which is shorthand for saying that it meets the requirements of first, second and third normal forms.

Simple solutions
In a recent answer to a reader question, I described an Access query to find all the jobs that have been finished in the past fortnight. I used this criterion in the date column to find the relevant entries:
BETWEEN DateAdd(“d”,-14,Date()) AND Date()

Bill Brown emailed to suggest an alternative solution which seems, as he says, much simpler:
>=Date()-14
The SQL is:
SELECT JobID, EndDate
FROM Job
WHERE EndDate>=Date()-14
ORDER BY EndDate;