Introduction to mapping from ER model to Relational model

 

To understand Mapping (conversion) from Entity Relationship Model to Relational Model, you should have a knowledge (an idea) about: 

·       ER model concepts

·       Relation model concepts

in Database design, mapping or Conversion of an E-R diagram into a relational model is very much required because E-R diagrams represent the conceptual level of the database design while the relational model represents the logical level of the Database design. The relational models can be easily implemented using RDBMS softwares like Oracle, MySQL, etc.

The following steps shows, how mapping ER diagram to relational model:

Step 1: Mapping  the regular/strong entities

Step 2: Mapping  the weak entities

Step 3: Mapping 1:1 Relationships

Step 4: Mapping 1:N Relationships

Step 5: Mapping N:N Relationships

Step 6: Mapping Multi-valued attribute and Composite attributes

Exammple: Consider the below ER diagram

Fig. 1 Simple Employee Entity

In the above ER diagram, Employee as an entity set and EmpNo, EmpName, and Salary as its attributes. Here we map entity set into a relation Employee and attributes of an entity set will become the columns inside the table. The key attribute will become the primary key of the table.

Fig. 2 Mapping ER diagram into Relation

Translation of a relationship into a relation:

                                                                     We map the entity set into the relation in a relational model, we can also map a relationship set into a relation. The attribute of such a relation includes key attributes of the participating relations. The attributes are will become a foreign key.

For example: In the above diagram, there are two entity sets Employee and Department. These entity sets are participating in a relationship works in. 

The relationship set is converted into relation with attributes EmpNo from Employee relation, D_id from Department relation and Since, the attribute of the relationship set itself.

Continue...





Comments

Post a Comment

Popular posts from this blog

Image
  Why Concurrency Control Is Needed:        Concurrency control  is the process of managing simultaneous execution of transactions (managing simultaneous operations on the database) such as queries, updates, inserts, deletes and so on in a multiprocessing database system without having them interfere with one another. [• Prevents interference when two or more users are accessing database simultaneously and at least one is updating data. • Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result.] When multiple transactions execute concurrently in an uncontrolled or unrestricted manner, then it might lead to several problems. Such problems are called as  concurrency problems . For example: In airline reservations database in which a record is stored for each airline flight. Each record includes the number of reserved seats on that flight as a named (uniquely identifiable) dat...
Read more
Image
  Why Recovery Is Needed:                                                     Whenever a transaction is submitted to a DBMS for execution, the system is responsible for making sure that either all the operations in the transaction are completed successfully and their effect is recorded permanently in the database, or that the transaction does not have any effect on the database or any other transactions. In the first case, the transaction is said to be committed , whereas in the second case, the transaction is aborted . The DBMS must not permit some operations of a transaction T to be applied to the database while other operations of T are not, because the whole transaction is a logical unit of database processing. If a transac...
Read more