2

Consider two simple tables, OrderHeader and OrderDetail which contain, respectively, the data shown below:

OrderHeader

| OrderHeaderID | CustomerID | OrderTotal |
|---------------|------------|------------|
| 1             | 124        | 14.5       | 
| 2             | 525        | 35.6       |
| etc           |            |            |

OrderDetail

| OrderDetailID | OrderHeaderID | ProductID |
|---------------|---------------|-----------|
| 1             | 1             | 415       |
| 2             | 1             | 52        |
| etc           |               |           |

The OrderTotal column can be calculated by adding the price of each product from the OrderDetail table. That sounds like a functional dependency! ie:

  • SUM(OrderDetail.Product.Price) → OrderHeader.OrderTotal

...but afaik it's not a violation of 3NF.

Is this actually allowed? If yes, is it disallowed in higher normal forms?

  • "SUM(OrderDetail.Product.Price) -> OrderHeader.OrderTotal" doesn't make sense. "FD" has a definition & it's re one table & two sets. (And it does not involve SQL "aggregate function" names, which anyway are not the names of functions of one parameter.) If you want your reasoning corrected then you need to say it clearly instead of misusing notation & terms. Otherwise we can only pointlessly reproduce yor textbook & show that what you wrote doesn't make sense. (There's no limit to things one could notice & worry about. Just memorize & apply definitions, theorems, algorithms & heuristics.) – philipxy Dec 23 '18 at 2:46
  • @philipxy Thanks, I didn't realize that normalization exclusively focused on single tables. Is there a reason Codd et al were only concerned with table level dependencies? Isn't an inter-table dependency just as likely to cause an insert/update/delete anomaly? – just.another.programmer Dec 23 '18 at 7:36
  • i think this type of constraint would mean the the database is not in DKNF (Domain Key Normal Form). – ypercubeᵀᴹ Dec 24 '18 at 16:35
  • @yper-crazyhat-cubeᵀᴹ Any constraint not implied by column types (domains) & CKs violates DKNF. DKNF is not in the chain of NFs reached by lossless decomposition per FDs & JDs (which ends at 6NF). It is not of any particular help or concern. What is the point of mentioning it? – philipxy Dec 25 '18 at 0:32
2

There is an informal dependency, but it's not a functional dependency. See the wiki article for the formal definition. Here's an excerpt in simple terms:

In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x.

There is no normal form definition which precludes summarizing child record values in the parent record. However, lots of people would suggest against doing this in transactional databases, particularly where there's a possibility that a child record could be updated (or deleted, or more child records added) such that there's a risk of the attribute in the parent becoming inconsistent with the aggregate value of the children.

If your OrderHeader table had a CustomerName column, then that would violate 3NF because of the transitive functional dependency on CustomerID.

Being informal, a lot of people would say that OrderTotal is denormalized because it's susceptible to things that look like insert, update and delete anomalies and so people draw a comparison with the formal definitions of normalization and normal forms (since these are also about avoiding insert/update/delete anomalies). There are some sticklers, though, who will be adamant that avoiding summary attributes in parent records is not the same thing as normalization per se.

| improve this answer | |
  • Why isn't this a functional dependency on the child table? For any set of OrderDetail tuples with the same ProductID, I'll necessarily have the same OrderTotal. Isn't that the exact definition of a functional dependency? – just.another.programmer Dec 22 '18 at 21:52
  • @just.another.programmer Not quite. Follow the link to the wiki article for the precise definition. The definition is that for any particular value of the dependent attribute the determining attribute is always known and consistent. By definition a functional dependency that is actionable for purposes of normalization involves predicates in the same relation. Functional dependencies don't, by definition, involve multiple records. They involve columns in the same record. – Joel Brown Dec 22 '18 at 23:38
  • That seems like an arbitrary distinction. I thought the point of functional dependencies was to identify and eliminate attributes that are susceptible to insert/update/delete anomalies. Why should we only worry about that in a single table? – just.another.programmer Dec 23 '18 at 7:37
  • 2
    @just.another.programmer Normalization is about knowing when and how to break up a table to avoid possible anomalies. Database normalization is not the only principle or best practice that can be applied to database design. There can be data redundancies, bad choice of keys, circular references, inappropriate physical representations of data types and other categories of "how not to database" which are all useful in their various ways. Functional dependencies come into play in two of the normal forms (one transitive, the other partial) otherwise there are other considerations. – Joel Brown Dec 23 '18 at 20:55

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.