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?

5
  • "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, 2018 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? Dec 23, 2018 at 7:36
  • i think this type of constraint would mean the the database is not in DKNF (Domain Key Normal Form). Dec 24, 2018 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, 2018 at 0:32

1 Answer 1

3

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.

4
  • 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? Dec 22, 2018 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, 2018 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? Dec 23, 2018 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, 2018 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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