Lecture 02 Entity-Relation Model and Relation Schemas

Mapping the entity relation model to the schema:

Step 1. Mapping of Regular Entity Types
• For each regular entity type create a relation that includes all the simple attributes of that entity.

Step 2. Mapping of Weak Entity Types

• For each weak entity type create a relation that includes all the simple attributes of that weak entity.

• The primary key of the new relation should be the partial key of the weak entity plus the primary key of its owner.

Step 3. Mapping of Binary 1:1 Relationship Types
• Include one side of the relationship as a foreign key in the other 

• Favor total participation

Step 4. Mapping of Binary 1:N Relationship Types
• Include the 1 side’s primary key as a foreign key on the N side relation

 

Step 5. Mapping of Binary M:N Relationship Types
• Create a new relation whose primary key is a combination of both entities’

primary keys.
• Also include any relationship attributes

 

 

Relation Schemas:

Entity sets and relationship sets can be expressed uniformly as relation schemas that represent the contents of the database.

A database which conforms to an E-R diagram can be represented by a collection of schemas.

For each entity set and relationship set there is a unique schema that is assigned the name of the corresponding entity set or relationship set.

Each schema has a number of columns (generally corresponding to attributes), which have unique names.

 

e.g.