Search code examples
databasedatabase-designrelational-databasedatabase-schema

why do we need weak entity instead of making it strong entity?


For example, I have 2 entities: book and copy with 1-n relationship as a book can have many its copies.

if copy is a strong entity,

book(PK_ISBN#, title, edition, date) copy(PK_copy#, condition, FK_ISBN#)

if copy is a weak entity,

book(PK_ISBN#, title, edition, date) copy(ISBN#, copy#, condition) Primary key (ISBN#, copy#) Foreign key ISBN# references book(PK_ISBN#)

Question: why would the copy entity be the weak entity instead of strong entity when both cases, I think, are similar.

P/S: one more question: How can we model partial or total participation constraint in SQL code.


Solution

  • As you've probably realised, in practice there is little or no difference in SQL implementations between a table representing a strong entity and one representing a weak entity. The concept exists in ER notation but has very little relevance to the relational model or SQL except as a way of understanding the semantics of the domain of discourse.

    Your examples are a little sketchy on details however. It appears that the copy attribute is unique in the first example but not in the second, which suggests the copy attribute means something different in each case.

    A total participation constraint between two tables is usually impossible to enforce in SQL because Standard SQL doesn't support multiple assignment (you can't update both tables at once). The workaround is to disable or defer constraint checking during updates, which means such constraints are of only limited value. Partial participation is essentially what a foreign key constraint achieves.