Entity relationship diagram to relational schema - Exercise 1

Solved Exercise - Reducing Entity Relationship Diagram into Tables, Convert ER diagram to tables, relational schemas, ER model to relational model

Entity Relationship Diagram Exercise 1

Question:

Convert/reduce the ER Diagram given in figure 1 below;

Figure 1 - ER diagram with One-to-One 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-One 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 sets – Entity 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 set – The 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-one relationship. That means, one entity (record/row) of Scientist can be related to at most one entity (record/row) of Invention entity.

To reduce / convert the relationship Invents into relational schema, we can include the primary key of either relation schemas into the other. For example, in our case we can derive the final set of relation schemas as follows;

Include IID of Invention as the foreign key of Scientist

Scientist (SID, SName, RArea, Country, IID)

Invention (IID, IName, Year)

(OR)

Include SID of Scientist as the foreign key of Invention

Scientist (SID, SName, RArea, Country)

Invention (IID, IName, Year, SID)

Both are valid relational schemas.

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

Go to ER model - Solved Exercises page