3.2.3 Mapping from ER Models to Relational Models Mapping Algorithm Example There is almost a one-to-one correspondence between the ER constructs and the relational ones. The two major distinctions are: 1. In a relational schema, relationships are represented implicitly through primary and foreign keys of participating entities. 2. In a relational schema, columns of relations cannot be multi-valued or composite. Composite attributes are replaced with their simple component ones, and multi-valued attributes are stored in a separate relation. ER Constru ct Relational Constru ct entity table 1:1 or 1:N relationship Foreign key (or a table to capture the relationship) M:N relationship "relationship" table and 2 foreign keys n-ary relationship type "relationship" table and 'n' foreign keys simple attribute column composite attribute set of simple component columns multi-valued attribute table and foreign key value set domain key attribute primary (or secondary) key 一. Mapping Algorithm We can translate an ER schema to a relational schema by following a nine-step algorithm based on the one given in Elmasri and Navathe 1994. The algorithm attempts to minimize the need for joins and NULL values when defining relations (Steps 2, 4, and 5). 1. For each strong entity E: o Create a new table. o Include as its columns, all the simple attributes and simple components of the composite attributes of E. o Identify the primary key and the alternate keys. Do not include any multi-valued attribute as a key. If the only unique field of an entity is a multi-valued attribute, introduce an artificial primary key field. 2. For each weak entity W that is associated with only one 1:1 identifying owner relationship: ...
