CODEWITHSUNDEEP
sqlrelational-databaseentity-relationshiperdweak-entity
Petter
Petter

Reputation: 21

Weak entity with unique Number ID

This is a bird-watcher database example. Say if you have three entities, BirdSpecies, Location and Observer. To have an entity Observation, you need all of these three. Without them there is no observation.

My understanding is that the requirement above makes Observation a weak entity. But what if the same person can spot the same species on the same location several times? Then the entry won't be unique.

My question is therefore, can you have a primary key for Observation that is just a number, sequentially increasing for each observation, and the entity still being a weak entity?

Upvotes: 0

Views: 1658

Answers (2)

reaanb
reaanb

Reputation: 10085

Weak entities are identified by a single parent entity's primary key and another attribute. Weak entities are typically parts of a whole. Observation (without introducing a surrogate key) is a ternary relationship, not a weak entity.

To record multiple observations by the same person of the same species in the same location, I would include a date/time value in the Observation relation and primary key, or alternatively a non-prime count column to record the number of observations. Remember that relations cannot have duplicate entries, so it's not uniqueness that is at risk without a distinguishing column, but your ability to record multiple entries. SQL DBMSs, however, aren't properly relational and will allow you to shoot yourself in the foot.

Once you introduce a surrogate key, you reify the relationship into an associative entity. Entities identified by a surrogate key are always strong entities, since they're identified by their own attributes. A surrogate key allows you to record otherwise duplicate entries, which is why surrogate keys are often complemented with unique keys on other attributes.

Upvotes: 0

pradosotx
pradosotx

Reputation: 1

I think that the weakness of the new entity is conditioned by its relations, now matter which its primary key is.

To understand this, imagine that instead of having a sequentially increasing number, you have a date-time, unique to each observation. This doesn't change the fact that if you remove one of the three entities there is no observation.

Upvotes: 0

Related Questions