Re: Transitive dependencies

Mark Wright wrote:
>
> 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

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