## TOPICS (Click to Navigate)

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## Entity Relationship Diagram Exercise 5

Question:
Convert/reduce the ER Diagram given in figure 1 below;
 Figure 1 - ER diagram with Composite attribute, Multi-valued attribute and Many to Many relationship

Solution:

Given in the figure;

Entity sets and relationship sets

 Name Entity set / Relationship set Type Scientist Entity set Strong entity set Invention Entity set Strong entity set Invents Relationship set Many-to-Many relationship

Entity set Scientist

 Attributes Attribute Type Description SID Simple and Primary key Scientist ID SNam Composite Scientist Name RArea Multi-valued Research Area Country Simple Country

Entity set Invention

 Attributes Attribute Type Description IID Simple and Primary key Invention ID IName Simple Name of the invention Year Simple Year of invention

Reduction into relational schema

Strong entity setsEntity set that has a primary key to uniquely represent each entity is Strong entity set.
Strong entity sets can be converted into relational schema by having the entity set name as the relation schema name and the attributes of that entity set as the attributes of relation schema.
Then we have,
Scientist (SID, SName, RArea, Country)
Invention (IID, IName, Year)
 1. After converting strong entity sets into relation schema Scientist (SID, SName, RArea, Country) Invention (IID, IName, Year)

Composite attributesIf an attribute can be further divided into two or more component attributes, that attribute is called composite attribute.
While converting into relation schemas, component attributes can be part of the strong entity sets’ relation schema. No need to retain the composite attribute.
In our case, SNam becomes FName, and LName as follows;
Scientist (SID, FName, LName, RArea, Country)
 2. After converting composite attributes into relation schema Scientist (SID, FName, LName, RArea, Country) Invention (IID, IName, Year)

Multi-valued attributes – Attributes that may have multiple values are referred as multi-valued attributes.
In our ER diagram, RArea is a multi-valued attribute. That means, a scientist may have one or more areas as their research areas.
To reduce a multi-valued attribute into a relation schema, we have to create a separate table for each multi-valued attribute. Also, we need to include the primary key of strong entity set (parent entity set where the multi-valued attribute belongs) as a foreign key attribute to establish link.

In our case, the strong entity set Scientist will be further divided as follows;
Scientist (SID, FName, LName, RArea, Country)
Scientist_Area (SID, RArea)
 3. After converting multi-valued attributes into relation schema Scientist (SID, FName, LName, Country) Scientist_Area (SID, RArea) Invention (IID, IName, Year)

Relationship setThe association between two or more entity sets is termed as relationship set.
A relationship may be either converted into a separate table or not. That can be decided based on the type of the relationship. Only many-to-many relationship needs to be created as a separate table.
Here, we are given a many-to-many relationship. That means,

• one entity (record/row) of Scientist is related to one or more entities (records/rows) of Invention entity set (that is, one scientist may have one or more inventions) and,
• one entity (record/row) of Invention is related to one or more entities (records/rows) of Scientist entity set. (that is, one or more scientists  may have invented one thing collectively).
To reduce the relationship Invents into relational schema, we need to create a separate table for Invents, because Invents is a many-to-many relationship set. Hence, create a table Invents with the primary keys of participating entity sets (both, Scientist and Invention) as the attributes.
Then we have,
Invents (SID, IID)
Here, SID and IID are both foreign keys and collectively forms the primary key of Invents table.

Finally, we have the following relation schemas;
 4. After converting relationship sets into relation schema Scientist (SID, FName, LName, Country) Scientist_Area (SID, RArea) Invention (IID, IName, Year) Invents (SID, IID)

*********************