Transformation of E-R diagram into relations with suitable examples

Download our Android App from Google Play Store and start reading Reference Notes Offline.

transforming-e-r-diagram-to-relationsTransformation of E-R diagram into relations | BSc.CSIT
Fourth Semester | Second Year | Tribhuvan University (TU)
Subject: System Analysis and Design (SAD)

Transformation of E-R diagram into relations
To compare the conceptual data model and the normalized relations developed, ER diagram must be transformed into relational notation, normalized and then merged with the existing normalized relations. Transformation involves 4 steps and they are;

1)Represent entities.

  • Each entity type in ER diagram becomes a relation.
  • Identifier of entity type becomes primary key of relation
  • Other attributes become non-primary key.

Primary key:

  • Value of key must uniquely identify every row in relation.
  • Key should be non-redundant

Example of entity representation is as follows;

entity representation

Customer_ID Name Address
1824 Ram Lalitpur
2543 Shyam Nepalgunj

2) Represent relationships

  • Representation of relationship depends on its nature (degree and cardinality)
  • In some case, we make the primary key of one relation, foreign key of another relation
  • In other case, we create separate relation to represent relationship
  • There are many types of relationships such as follows;

a) Binary 1:N ( one to many) relationship

  • 1:N is represented by adding primary key of entity on one side of relationship as a foreign key in relation that is on the many side of relationship

relationships representationCustomers

Customer-Id Name Address
1234 Digvijay Tinkune
3265 Swopnil Maharajgunj

Orders

Order-Number Order-date Promised-date Customer-ID
234 15th July 17th July 1234
322 25th April 30th April 3265

b) For 1:1 (one to one) relationship

  • Add primary key of A as foreign of B
  • Or Add primary key of B as foreign of A
  • Or both of above can be shown for one to one relationship.

one-to-one-relationship
c) M:N relationship

  • For M:N relationship between A and B, we create a separate relation C
  • Primary key of C is composite by consisting of primary key of each of two entities in the relationship
  • Any non-key attributes associated with M:N relationship are included with relation C

For example;
many-to-many-relationship

Order

Order-number Order date promised date
3245 12th Jan 13th Jan
7856 15th April 18th April

Order Line

Order-number Product ID Quantity ordered
3245 9134 2
7856 1312 1

Product

Product-Id Description Room
9134 Bookcase Study
1312 Cabinet Study

d) Unary Relationships

  • It is relationship between instances of single entity type
  • It is also called recursive relationships

For example
unary-relationships

Emp-ID Name Birthdate Manager-ID
1 Gita 1996/04/04 2
2 Remo 1997/05/05 1

3) Normalize the relations
Relations created in step 1 and 2 have unnecessary redundancies so normalization is needed to make them well structured.

4) Merge the relation
After we have created relations from separate E-R diagrams, it may have redundancies, so we should merge these relations.
Example: EMPLOYEE 1
(Emp_ID, Name, Address)
EMPLOYEE 2
(Emp_ID,Name, Jobcode)

Above relation have redundancies, so relation after merging as follows;
EMPLOYEE
(Emp_ID, Name, Address, Jobcode)

Problems during merging

  1. Synonyms
    In some cases, two or more attributes may have different name but same meaning. For example: employee_ID and employee-number
    During merge, agreement from users on single standardized name for attribute or choose a third name
  2. Homonyms
    attribute name that may have more than 1 meaning
    eg: STUDENT1(s_id, Name, Address)
    STUDENT2(s_id, Name, Phone, Address)

    Address in student1 refers to campus address and that of student 2 refers to home address. So it can be resolved by STUDENT(s_id, Name, Phone, Campus_address, home_address)
  3. Dependencies between non-keys
    When two 3NF relations are merged to form single relation, dependencies between non-key may result.
    eg: STUDENT1(s_id, major), STUDENT2(s_id, Advisor)   It is merged to
    STUDENT (s_id, major, advisor)
    Suppose each major has exactly 1 adviser, so adviser is dependent on major, so STUDENT would be in 2NF because it contains non-key dependency. It could be solved as follows;
    STUDENT(s_id, Major)
    MAJOR ADVISER(Major, Adviser)
  4. Class/Subclass:
    class/subclass relation may be hidden n user views or relations
    eg: PATIENT1(pid, name, address, date, treated) PATIENT2(pid, room)
    If there are two types of patient: inpatient and outpatient, it can’t be merged as room is only for inpatient
    so class/subclass relation is created.
    PATIENT(pid, name, address)
    INPATIENT(pid, room)
    OUTPATIENT(pid, date, treated)
(Visited 1,142 times, 1 visits today)

Posted By : Digvijay | Comment RSS | Category : Fourth Semester
Tag :

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

Wordpress DMCA
Community | Toolbar | Android App | Founder/Developer : Hari Prasad Chaudhary | CSIT Portal Manager : Digvijay Chaudhary