Sql-server – Weak entity and one-one or many-many relationship

database-designsql server

In Entity Relationship Model, can the supporting (or identifying) relationships of the weak entities be many-many or one-one rather than many-one?

Suppose I have a customer entity (C), a video entity (V) and a weak entity reservation (R). C – R has a one-many relationship and it's a supporting relationship for R. However between V – R the same one-many relationship wouldn't make sense, since a video shouldn't appear in two different reservations.

So I wonder if a one-one relationship can be a supporting one for the weak entity? Or should it be a normal one-one instead of a supporting one?

Best Answer

If you are running a video rental store, then whether or not your children eat depends very much on a video appearing in as many reservations as possible.

In your example, RESERVATION is a weak entity which relies on both the FK to CUSTOMER and the FK to VIDEO in addition to a further attribute, perhaps date, to comprise its primary key.

It is not possible for a M:N relationship to be supporting for a weak entity (although the intersection table which resolves a M:N relationship can be a weak entity). This is because a supporting relationship needs to be to a particular entity object. A set of entity objects can't identify something in relational algebra.

It is possible for a 1:1 relationship to be supporting for a weak entity, although this would be rare "in the wild". I could imagine it coming up in the context of situations that would typically be 1:M, but where there is a business rule that restricts the child to one at a time.

Related Solutions

Many-to-many relationship when one entity is without attributes

If the cluster really has absolutely no attributes then no, you don't need a separate table for these entities. The following would do:

User            UserCluster
====            ===============
UserID (PK) --> UserID (FK, PK)
UserProp1       ClusterID (PK)
UserProp2
...
UserPropN

But it is unusual that an entity has absolutely zero properties: they usually have at least have a human friendly name for display purposes. Of course you could make the cluster's ID the same as the name, but as names can change it is usually not recommended that you do this (where possible the primary key should be a value that is immutable during the existence of an entity).

Is the entity-relationship diagram about customers and accounts correct

IMHO you just have to pay attention on the order of cardinalities, and you should note that Issue Date is not a weak key, but it's not a key at all: in fact you could have two account statements of different people issued on the same date, so you need a foreign key (Issue Date plus Number of Account)

Best Answer

Related Solutions

Many-to-many relationship when one entity is without attributes

Is the entity-relationship diagram about customers and accounts correct

Related Question