Dear All,

Firstly, I am a new poster here and would like to say hello to everyone.

I have a transitive dependency problem that I am positive would benefit from
the groups insight.

I have been tasked with remodeling a design (to improve integrity) that
supports a survey. I have simplified the design for the sake of clarity.

The gist of the survey is:
Agencies complete award data about moneys in categories awarded to
institutes.

Business Rules:
Awards consist of 9 categories (two are shown for simplicity - research,
support) that are amounts in dollars.
When the award is saved the data are validated against previous years to
check for variance errors.
Each category amount can have a maximum of one error associated with it at
any time.
A category error is defined as a type of variance from previous years data.
Each category error belongs to a separate domain. (i.e. A variance for one
category is not applicable to another category).
Each category error has a complicated description.

The award relation is currently modeled as followed (<primary key>):

award ( <institute, year>, research, research_err, support, support_err... )
Clearly not in 3NF.

I see the award relation functional dependencies as follows:

FD1 (institute, year) -> research, research_err, support, support_err
FD2 research -> research_err
FD3 support -> support_err

Giving the following transitive dependencies:

TD1 (institute, year) -> research -> research_err
TD2 (institute, year) -> support -> support_err

The real survey has 9 such categories that have changed very little since
1965! How would one normalize the award relation to remove the transitive
dependencies? I have three proposed designs:

Solution 1:
award (<institute, year>, research_amount, support_amount) (1 tuple per
award)
award_error (<institute, year, error>) FK - award, error
error(<error>, description)
All the errors are lumped into a single domain and its difficult to find out
what category each error refers to.

Solution 2:
Decompose award category attributes (research, support) into a single
category entity:

award_category (<institute, year, category> amount) (n tuples per award)
category_error(<institute, year, category, error>) FK - award_category,
error
error(<category, error>), description)

Solution 3:
Decompose each award category attribute (research, support) into separate
entities:

research(<institute, year, research>, amount, error) FK - research_error
support(<institute, year, category>, amount, error) FK - support_error
research_error(<error>, description)
support_error(<error>, description)

Remember the categories have changed twice since 1965 so adding or removing
categories is not that painful. I am leaning towards Solution 3 because its
simple and I can construct the non normalized award relation from it quite
simply (the data is used by a web based app layer and they will want to see
an award, with error information as 1 tuple rather than n tuples). I'm sure
there is a better way but my brain can't see it. Does it even need
normalizing further? I understand it's difficult to jump into a problem
space but any suggestions and comments on the proposed designs would be
gratefully received. My apologies for the verbosity of this post.

Thank you in advance,

Mark

"Mark Wright" <markwright (AT) hotpop (DOT) com> wrote


<snip>

My apologies I made a mistake in Solution 3 the relations should actually
look like this:

research(<institute, year>, amount, error) FK - research_error
support(<institute, year>, amount, error) FK - support_error
research_error(<error>, description)
support_error(<error>, description)

Thanks,
Mark

Mark Wright wrote:
I don't think FD2 and FD3 are correct. Think about it. Does every row
with the same amount in the research column have the same error in the
research_err column? I don't think so. As far as I can tell these
columns properly depend upon (institute, year) so unless you include the
error descriptions in your table it is in 3NF (and very probably in 5NF).

-- Jan Hidders

"Jan Hidders" <jan.hidders (AT) REMOVETHIS (DOT) pandora.be> wrote

You are quite right Jan the _err attributes do depend on (institute, year)
and the FD: institute, year, research -> research_err does not hold either.

Thank you for the clarification and taking the time to respond.

Regards,
Mark