# Transformation of E-R diagram into relations with suitable examples

## Transformation 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;

 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

Customers

 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.

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;

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

 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 2,030 times, 1 visits today)

Posted By : | Comment RSS | Category : Fourth Semester
Subscribe Notes