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.