ER Diagram to Relational Schema

A database whose entities and relations have been modeled as an ER diagram can be represented as a set of relational schemas, one for each entity and relation.

• A strong entity set reduces to a schema with the same attributes

• A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set Ex. In the following diagram, section is the weak entity and course is the strong/identifying entity.

• A many-to-many relationship set is represented as a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set. Ex.

• Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side (instead of creating a new schema for the relation) Ex.Instead of creating a schema for relationship set inst_dept, add an attribute dept_name to the schema for the entity set instructor.

• For one-to-one relationship sets, either side can be chosen to act as the “many” side, i.e. the extra attribute can be added to either of the tables corresponding to the two entity sets

• Composite attributes are flattened out by creating a separate attribute for each component attribute

• A multivalued attribute M of an entity E is represented by a separate schema EM

  • Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M

  • Ex. Multivalued attribute phone_number of instructor is represented by a schema:

    inst_phone= ( ID, phone_number)

  • Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM. For example, an instructor entity with primary key 22222 and phone numbers 456-7890 and 123-4567 maps to two tuples: (22222, 456-7890) and (22222, 123-4567)

• Representing specializations as schemas:

  • Method 1: form a schema for the higher-level entity, form a schema for each lower-level entity set, then include primary key of higher-level entity set and local attributes. The drawback is that we would have to access two relations to get the required information

  • Method 2: form a schema for each entity set with all local and inherited attributes. The drawback is redundancy