Characteristics of a good database

A good relational design stores each fact in an appropriate place and connects related facts through keys. A primary key identifies a row uniquely; a foreign key references an existing related row. Referential integrity prevents a result from pointing to a learner who does not exist. Validation rules such as a mark range protect meaning in addition to the relationship.

If each result row repeats the learner’s name and class, changing a name requires several updates. Forgetting one produces an update anomaly. You may be unable to record a learner before a first result, an insertion anomaly. Deleting the last result may accidentally remove all knowledge of the learner, a deletion anomaly. Separate Learners from Results and connect them by LearnerID. First normal form removes repeating groups and uses atomic values for the intended operations. Second normal form removes partial dependencies on a composite key; third normal form removes transitive non-key dependencies. Normalisation depends on the actual functional dependencies, not just splitting a large table arbitrarily.

• Explain primary and foreign keys.
• Identify insertion, update and deletion anomalies.
• Separate entities using dependencies.

Worked example:

Learners(LearnerID, Name, ClassID), Classes(ClassID, ClassName), Results(ResultID, LearnerID, Mark) stores class names once and permits a learner with no result. Many Results rows can reference one Learner. A unique constraint may additionally prevent duplicate results for the same learner and assessment.

Practice: Where should a class teacher’s name be stored if each class has one teacher, and why?

Answer: Store the class-to-teacher relationship with Classes, preferably using TeacherID to a Teachers table. It depends on the class rather than an individual result. This avoids repeating and inconsistently updating the teacher for every result.

Recap: Use keys, dependencies and constraints to store facts consistently and prevent anomalies.