# Does the definition of functional dependency permit derivative values?

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,
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.

• What is your 1 (specific researched non-duplicate) question? Jan 10 at 14:18