Minimization of ER Diagram

MySQLMySQLi Database

Problem statement

ER diagram is pictorial representation of shows various tables and relations amongst them. ER diagram we can reduce the number of database.

One to one cardinality

Let us consider below diagram with one to one cardinality −

Above ER diagram represents 3 entities −

  • Employee entity has 2 attributes namely emp_name. emp_id is the primary key
  • Company entity has 2 attributes namely cmp_name. cmp_id is the primary key
  • Primary key of Work entity can be emp_id or cmp_id
  • We cannot combine 3 tables into single one can either merge Work into Employee or Company. minimum 2 tables are required in one to one cardinality scenario.

One to many cardinality

Let us consider below diagram with one to many cardinality −

In this ER diagram employee can work for one company but company can have many such employees. Above ER diagram represents 3 entities −

  • Employee entity has 2 attributes namely − emp_id and emp_name. emp_id is the primary key
  • Company entity has 2 attributes namely − emp_id and cmp_name. cmp_id is the primary key
  • We cannot make cmp_id as primary key as multiple employee can work for same cmp_id. However, we can combine Employee and Work table. Hence minimum 2 tables are required in one to many cardinality scenario.

Many to many cardinality

Let us consider below diagram with one to many cardinality −

In this ER diagram employee can work for multiple companies a company can have many such employees. Above ER diagram represents 3 entities −

  • Employee entity has 2 attributes namely − emp_id and emp_name. emp_id is the primary key
  • Company entity has 2 attributes namely − emp_id and cmp_name. cmp_id is the primary key
  • Unlike previous example cannot merge Work table with either Employee or Company. If we try to merge it then it will create redundant data.Hence minimum 2 tables are required in many to many cardinality scenario
raja
Published on 22-Oct-2019 15:40:26
Advertisements