Functional Dependency Definition – Does it Permit Derivative Values?

database-designdependenciesnormalization

Sorry if the question in the title seemed vague. The question is rather broad and this is the best I could summarize.

I have read about functional dependency, 1nf, 2nf and 3nf. From what I understand, if A -> B then B is functionally dependent on A. In other words, if there is only a unique A for each B then B is functionally dependent on A. (Please correct me if I'm wrong)

Now consider the following table. 

CREATE TABLE sales (
   product_id int,
   product_name varchar,
   year int,
   indirect_sales int,
   direct_sales int,
   total_sales int
);

The candidate key for each row is clearly (product_id, year). This table seems to be 2nf but I suspect this table is still not 3nf. The reason is that we can determine total_sales from direct_sales and indirect_sales because direct_sales + indirect_sales = total_sales. Could we call this a functional dependency then? Because for each direct and indirect sales there's a unique total sales. You can also see that this could potentially cause updating errors. If you update direct_sales you must also update the total_sales simultaneously.

So how would you deal with this in the context of normalization? Normally you would create a separate table with attributes but creating a table with direct, indirect and total sales seems artificial. Also how would you deal with this in a real situation in which 3nf was strictly required.

I have provided a simplified example so you can grasp the essence of the problem but the real problem that I'm battling is with these tables. 

CREATE TABLE user (
   user_id int,
   user_name varchar,
   user_login varchar,
   user_password varchar,
   balance int
);

CREATE TABLE transaction (
   transaction_id int,
   user_id int REFERENCES user.user_id,
   amount int
);

This is a banking database that stores user information and transaction information for each user. I suspect this won't be considered 3nf because balance is always the sum of user transaction amounts. This will cause updation anomaly; if we update a transaction the balance must reflect it.

Can you help me make sense of this? Thanks.

Best Answer

I found an excellent blog post by Ken Downs that clarifies everything:

Database Skills: Third Normal Form and Calculated Values

Basically, the tables above aren't 3NF on account of the derived attributes; 3NF doesn't permit derived attributes. And, yes, it follows from it that derived attributes are functionally dependent on their source attributes.

Related Question