When converting an E-R diagram to a relational schema, how is a multivalued…

Concept

The ER-to-relational mapping algorithm converts each ER construct into relations and columns using type-specific rules: simple attributes become columns of the owning relation; composite attributes are flattened into one column per component sub-attribute; derived attributes are usually dropped (recomputed on demand); and a multivalued attribute -- one that can legitimately hold zero, one, or many values for a single entity instance -- cannot be stored as a single column, because a relational table cell must hold exactly one atomic value (this is the essence of First Normal Form). The mapping rule instead creates a brand-new relation for that attribute, containing a foreign key referencing the primary key of the owning entity's relation, plus the attribute value itself; the primary key of this new relation is the combination {foreign key, attribute value} (or an added surrogate key), so the owning entity can be linked to any number of rows -- one per recorded value.

Application

Take an Employee entity with a multivalued attribute PhoneNumbers. Applying the rule: Employee's simple/composite attributes populate the Employee relation as usual, but PhoneNumbers cannot become a single Employee column, since an employee may have zero, one, or several numbers. So a new relation EmployeePhone(EmpID, PhoneNumber) is created, where EmpID is a foreign key referencing Employee's primary key and {EmpID, PhoneNumber} is the primary key of EmployeePhone. Each phone number the employee has becomes its own row in EmployeePhone, all pointing back to the same EmpID -- exactly the "separate relation with a foreign key" pattern.

Contrast with the other options

• As a composite attribute -- a composite attribute only regroups the fixed sub-parts of one value (for example, Address into Street, City, and PIN) into columns of the same table; it does not create room for a variable number of value instances.

• As a nullable column in the same table -- a nullable column still allocates exactly one storage slot per row, so it can hold at most one recorded value (or none); it cannot hold two, three, or an unbounded number of values for the same employee without adding more columns.

• As an attribute with CHECK constraints -- a CHECK constraint only restricts which values a single column may take; it does not change the table structure to allow more than one value per entity instance.

• As a separate relation with a foreign key -- matches the derivation above: each value becomes its own row in a child relation keyed back to the parent via a foreign key, which is exactly what the mapping algorithm prescribes for a multivalued attribute.