## TOPICS (Click to Navigate)

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

## Entity Relationship Diagram Exercise 2

Question:
Convert/reduce the ER Diagram given in figure 1 below;
 Figure 1 - ER Diagram with One 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 One-to-Many relationship

Entity set Scientist

 Attributes Attribute Type Description SID Simple and Primary key Scientist ID SName Simple Scientist Name RArea Simple 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)

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 one-to-many relationship. That means,
• one entity (record/row) of Scientist can be related to many entities (records/rows) of Invention entity set (that is, a scientist may have invented one or more things) and,
• one entity (record/row) of Invention is related to exactly one entity of Scientist entity set. (that is, one invention is uniquely done by a single scientist)

To reduce the relationship Invents into relational schema, we need to include the primary key of one side relation schema (i.e., Scientist) into the many side relation (i.e., Invention) as foreign key. For example, in our case we can derive the final set of relation schemas as follows;

Include SID of Scientist as the foreign key of Invention

Scientist (SID, SName, RArea, Country)

Invention (IID, IName, Year, SID)

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