I'm a bit confused on the concept of weak entities and normalization and couldn't find any answers online. I know that generally weak entities have a partial key to uniquely identify its own records but the primary key is usually a composite key made up of this partial key and the foreign key (that refers to the weak entity's parent's primary key). Before I thought we could just have the foreign key be the sole primary key for the weak entity but apparently this is not such a good idea.

I am confused however on normalization, specifically 2NF where we have to make sure that no non-prime attribute is solely dependent on a subset of the primary key.

I'm missing something crucial in my understanding here. So wouldn't a weak entity be in violation of 2nf? Because a non-prime attribute could be dependent solely on the foreign key?

  • You use the term "normalization" but your post doesn't reflect what it means. If you are trying to determine whether a particular schema is in 2NF, quote its definition & ask about where you are first stuck. Follow a published textbook--dozens are free online. PS PKs don't matter, CKs do. FKs are irrelevant. Weakness is irrelevant. Just quote & apply (correct) definitions. PS What are you actually starting with & what are you actually trying to produce?
    – philipxy
    May 3 '20 at 2:18

So wouldn't a weak entity be in violation of 2nf? Because a non-prime attribute could be dependent solely on the foreign key?

No. Consider a typical example:

SalesOrder(OrderId,OrderDate) PK (OrderID)
SalesOrderItem(OrderId,OrderItemNum,ProductId,Quantity,Price) PK (OrderId,OrderItemNum)

No OrderItem non-key attribute is functionally dependent on OrderItemNum. OrderItemNum could be repeated for multiple orders. In practice it's often not, but for the purposes of normalization it's just the trailing column of the primary key.

  • sorry if I'm not fully understanding-- so if I have an example like "BugInfo(BugID, BugName,BugHeight)" whose PK = BugID and the weak entity was "BugDescription(BugID, BugDescriptionID, BugNum_Legs, Bug_Longevity)" where the primary key for the weak entity would be "(BugID, BugDescriptionID)". It looks like the "BugNum_Legs" and "Bug_Longevity", the non-prime attributes, are dependent on just the foreign key, BugID (so it doesn't have to be dependent on both BugID and BugDescriptionID). But you're saying it's still not a violation of 2NF? Apr 22 '20 at 18:07
  • In that case two different BugDescription rows could have the same BugID. Each would have a different NumLegs, so NumLegs is not functionally dependent on BugID. Apr 22 '20 at 18:13
  • Got it--I think I'm understanding a bit better. I see your point clearly when it's a 1:N relationship between the two entities. But if it were 1:1 then each row's bug description info (number of legs, longevity) would be related to the BugID. But even now it's still not violating 2NF? Apr 22 '20 at 18:17
  • For 1:1 the tables have exactly the same key columns. Ie the FK is also a key. So BugID would be a key. BugDescriptionID could also be a key, but it would be a seperate key. Apr 22 '20 at 18:22
  • 1
    Thank you David for your time helping me drill down from the original premise to understanding where that premise doesn't hold true (and indicates a faulty db design like 1:1). The comments helped clarify a lot for me. I agree that it would be easier to just have a large single table that has all this information. Apr 22 '20 at 18:35

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