Database Reference
In-Depth Information
We also learn that the
Possible Answer Value
and
Possible Answer Description
need
more than just
Survey Question Number
as their primary key. We learn from talking with
the business that the same
Possible Answer Value
can apply to more than one
Survey
Question
. We also learn that when the
Survey Question Singular Response Indicator
is
set to
No
, the same
Survey Question
can contain more than one
Possible Answer
. There-
fore there is a many-to-many relationship between
Survey Question
and
Possible Answer
,
which is resolved on the logical through a linking entity called an associative entity. We can
then connect this associative entity to
Completed Survey Response
, which addresses the
minimal primary key issue for the last two questions in our spreadsheet where the answer
was
No
.
Here is our 2NF model after making the above changes to
Survey Section
, adding the as-
sociative entity between
Possible Answer
and
Survey Question
, and connecting
Survey
Question Possible Answer
to
Completed Survey Response
:
Third Normal Form (3NF)
For third normal form, we need to remove hidden dependencies. A hidden dependency is
when a property of an entity depends upon one or more other properties in that same entity