Question on Data Modelling Lab 1st -> 2nd NF

Course Q&A Data Engineering Data Modeling, Transformation, and Serving
1

Is there a reason we don’t create an OrderItem table when converting from 1st NF to 2nd NF?

As per the definition of the 2nd NF, wouldn’t it be justified?

  • We can derive OrderDate from orderNumber (‘A subset of non-key columns depend on some columns in the composite key’)
  • We can derive productCode from orderlinenumber (‘A subset of non-key columns depend on some columns in the composite key’)

Wouldn’t this be a partial dependency, which violates 2NF? The solution only splits between orders & Customers.

2

Great question!

To address your two points:

  1. In the 2NF diagram you see below, the orders table uses the ordernumber as the primary key, so it’s not a composite key. So you’re right in thinking that the orderDate can be derived from the ordernumber, but since ordernumber is not part of a composite key, it doesn’t violate the partial dependency rule
    image
    image1367×3840 314 KB
  2. We can’t actually derive productCode from just orderlinenumber. Below is the expected output of the orderdetails table in 2NF. If we only knew that the orderlinenumber was 1 without know the ordernumber, then we won’t know if the productCode is S18_1749 (ordernumber 10100) or S18_2325 (ordernumber 10101)
    image

I hope this helps. Let me know if you have any other questions about this.

1 Like
3

That explains it, productCode depends on the full compositeKey, instead of only the subset. - thank you for the thorough response.

1 Like