Database Modeling and Design - Database Systems - page 107

Note : We could have forgone steps 1-3 and gone straight for BCNF relations by
simply observing the FDs shown in Figure 5-16 , and decomposing via Heath's theorem.
As your confidence in database design grows, you will (hopefully) be able to do this.
5.9.2 Determining Candidate Keys and then Normalizing
In many cases, the database designer may be faced with the problem where basic
knowledge of data to be stored is available, but it is not immediately clear how this
partial knowledge will translate into a set of normalized relations. For instance, you
may be able to identify an entity (or set of entities), but are not sure what the primary
key(s) to this (these) entity (entities) will be. With experience, you will be able to resolve
these challenges intuitively. However, what do you do in the absence of that invaluable
experience? The relational model provides a theoretical approach for dealing with this
problem, as explained in the following example.
Suppose that it is desirable to record the information about the performances of
students in certain courses in an educational institution environment. Assume further,
that a set of functional dependencies have been identified, but it is not sure what the final
set of normalized relations will be and how they will be keyed. Figure 5-17 illustrates a
summary of the information (assumed to be) known in the case. As usual, we start off by
assuming that the relation shown ( StudPerfDraft) is in 1NF.
Figure 5-17. Initial 1NF Relation for the Student Performance Problem
Step 1 — Determine the Candidate Key
Having assumed that StudPerfDraft is in 1NF , our next step is to determine a candidate
key of the relation. We do this by chasing the explicit and implicit dependencies. Any FD
that ends up determining all attributes (directly or indirectly) constitutes a candidate key.
The technique is referred to as computing closures.