2

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.

1

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.

  • 1
    "Derived attributes" means nothing. CKs are a consequence of the FDs, so your claim that Sales's CK is "clear" without addressing them first is unsound. Using common sense to identify FDs, Sales is not in 3NF. "Why" depends on what defintion of NFs you use but does involve {direct_sales, indirect_sales} -> total_sales. Using common sense User & Transaction are in 3NF because whether a table is in a NF has nothing to do with other tables; only superkeys determine balance & amount. You are just going to confuse yourself unless you memorize definitions & only use defined terms in proper ways. – philipxy Jan 21 '17 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.