Wednesday 6 August 2014

Relational Model | ER to Relational model conversion

ER TO RELATIONAL MODEL CONVERSION

INTRODUCTION- In the E-R model, data is represented using entities, and relationships are defined between these entities. However, with the relational model, the entities and their relationships follow strict guidelines. Usually, an E-R model is first developed, and then it is transformed into a relational model.

RULES TO CONVERT ER TO RELATIONAL MODEL CONVERSION-

Build a table for each entity set
Build a table for each relationship set if necessary (more on this later)
Make a column in the table for each attribute in the entity set
Indivisibility Rule and Ordering Rule
Primary Key

EXAMPLE- An E-R diagram is used to represent the E-R model. It contains all known entities for the given scenario, their attributes, identifiers, and the relationships that exist among the entities.Unique identifiers are indicated with an asterisk (*), and non-unique identifiers with a plus (+).

CONVERSION-

STEP 1-Clearly identify the primary key and attributes for each entity defined in the E-R model, and ensure that it is in accordance with the rules of the relational model.Each entity, with its clearly identified primary key (indicated by PK), and attributes satisfying the previously discussed rules, becomes a table in the relational model.

STEP 2- Group together tables (formerly entities) and their relationships that have a cardinality of one relationships with 0:1 or 1:1 for their opt:card. (Optionality says what can and must happen in a relationship, and cardinality indicates the number of entity occurrences in a relationship)That is, absorb relationships where the cardinality is one into the corresponding tables.

• Maintain the initial structure for the absorbing table – do not change its primary key or any of its attributes.

• The primary key of the other table in the relationship becomes a foreign key in the absorbing table. This is indicated by an FK in the absorbing table.

• If the cardinality between the absorbed relationship and the other table in the relationship was N (0:N or 1:N), then the modified table becomes the many part of a one-to-many relationship in the new relational model.

• Otherwise, if the cardinality between the absorbed relationship and the other table in the relationship was 1 (0:1 or 1:1), then the new relationship becomes a one-to-one relationship in the new relational model (not shown).

STEP 3-The remaining relationships whose cardinalities are N (1:N or 0:N) on both sides become new tables in the new relational model.

• The primary keys from the two tables involved in the relationship become a composite primary key in the new table, and the new table usually has a name that is a combined form of the two original table names.

•The newly created table becomes the many part of the relationship between both tables, and thus creates a many-to-many relationship between the two pre-existing tables. In some instances, the newly created may have its own attributes, but this is rare.