Advanced Database Management System - Tutorials and Notes: Entity relationship diagram to relational schema - Exercise 4

Search Engine

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

Saturday, 30 April 2016

Entity relationship diagram to relational schema - Exercise 4

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


Entity Relationship Diagram Exercise 4


Question:
Convert/reduce the ER Diagram given in figure 1 below;
Figure 1 - ER diagram with 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
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 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 collectively invented only one thing).

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.

Final relation schemas of the given ER diagram are as follows;
Scientist (SID, SName, RArea, Country, IID)

Invention (IID, IName, Year)

Invents (SID, IID)



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









No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents