I have to map an ER diagram to a relational diagram, but I struggle to map multivalued attributes of a weak entity. The problem is in this piece of my ER diagram:
COURSE_MATERIAL
is a weak entity is defined by the two multivalued attributes URLs and Books. Since COURSE_MATERIAL
has a total participation constraint, it takes as primary key the key of the strong entity COURSE
(as foreign key). In general, if a strong entity has a multivalued attributes, a new table is created which contains the key of the strong entity and the multivalued attribute (they're defined as the primary key of the new table). In this case the multivalued attributes belong to a weak entity that doesn't have any primary key on its own. How can I create the two tables for URLs
and Books
? Will them take the COURSE
key like COURSE_MATERIAL
and the multivalued attribute as usual?
Let's clarify the concept of a weak entity in ERD:
an entity that cannot be uniquely identified by its attributes alone. The existence of a weak entity is dependent upon another entity called the owner entity. The weak entity’s identifier is a combination of the identifier of the owner entity and the partial key of the weak entity.
So it doesn't mean that a weak entity has no primary key, but that its primary key is a compound primary key, a part of it being a foreign key to the owning entity. In the case of a 1:1 it doesn't even have to be compound: the weak entity could just reuse the primary key of its owner, since there is no other element related to the same entity to differentiate with:
With this in mind, the problem becomes straightforward: you can just use your standard technique for multivalued attributes, and add one (or two, one for book and one for url) table that has a even more compound primary key which is made of the compound primary key that you'll define for COURSE_MATERIAL
, and an additional attribute that allows to differentiate the multiple values. If you'd transform your ERD, it would then look like:
If showing these additional tables as additional weak entities in your diagram, as above, you'd find that it would look more complex than necessary: if COURSE_MATERIAL
only has these multi-valued attribute and these attributes are independent of each other, you could directly create two relationships of COURSE
with two weak entities URL
, and BOOK
, keeping compound primary keys less complex (only two components, instead of two and three). The simplified diagram would then look like: