Database design and normalisation techniques study guide

A method for solving normalisation questions

Begin by listing the attributes in the original relation and identifying the key. Do not assume that the key is a single attribute: several attributes may be needed together to identify a record. Then write down the dependencies in words. Ask which attribute or combination identifies each other attribute.

Next, check whether any non-key attribute depends on only part of a composite key. If it does, that fact belongs in a separate relation identified by the smaller determinant. Keep the attributes that depend on the complete composite key in the relation representing the combination. This reasoning separates facts about individual entities from facts about a relationship between entities.

Finally, check for a dependency between non-key attributes. If one non-key attribute determines another non-key attribute, separate the determining attribute and the fact it determines into another relation. The original relation retains the determinant so that the relations can still be connected. A relation is in 3NF when its non-key attributes depend on the key, the whole key and nothing but the key.

Exam application

For each proposed decomposition, justify the change rather than merely drawing new tables. State the key of every resulting relation and explain why each non-key attribute depends on that key. For example, in Enrolment(StudentID, CourseID, Grade), the composite key identifies one student's enrolment on one course, so Grade belongs with that complete key. In Student(StudentID, StudentName), StudentID identifies the student's name. In Course(CourseID, CourseName), CourseID identifies the course name.

The reason for normalising is not simply to create more relations. It is to place each fact where its determining key belongs and reduce repeated storage of the same fact. This makes the design less likely to require inconsistent changes when a fact is updated. A good answer therefore links the structural rule for 3NF to the practical reason for normalisation.

Self-check

1. Can you identify the key, including a composite key, before decomposing a relation?
2. Can you state which attributes depend on the whole key and which depend on only part of it?
3. Can you find a dependency between two non-key attributes?
4. Can you name the key of each new relation?
5. Can you explain why every remaining non-key attribute depends on the key, the whole key and nothing but the key?

If any answer is uncertain, rewrite the dependencies explicitly before attempting the decomposition. This makes the reasoning visible and helps distinguish a 3NF property from a general statement that the database has been improved.