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).
Received on Tue Oct 07 2003 - 14:11:11 CDT
