Advanced Database Management System - Tutorials and Notes: Reduce the ER diagram to relation table solved exercise

Tuesday, 18 July 2017

Reduce the ER diagram to relation table solved exercise

How to reduce an ER diagram to tables? Reduction of ERD to relation schema, Convert Entity Relationship diagram to set of tables, ERD to relation schema examples, mapping ER diagram to relational tables


Reduce (convert) the following ER diagram to relational schema




ER components
Given component
Result
Strong Entity Set
Rule: Strong entity set can be directly converted into table.

(a) STUDENT
(b) SUBJECT
(c) CLASS
(a) STUDENT (Student_ID, Student_Name, DOB, Address)
 (b) SUBJECT (Subject_ID, Subject_Name, Teacher)
(C) CLASS (Class_ID, Class_Name)
Derived attribute
Rule: No need to create a column in the table for derived attribute.
Age in STUDENT table
No changes
Composite attribute
Rule: Replace the composite attribute with its component attributes.
Address in STUDENT table
STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)
1-1, 1-n, and n-1 Relationships
Rule: Include the primary key of one side entity set as the foreign key of other side entity set.

Attends (1-1 from STUDENT to CLASS)
Studies (1-n from STUDENT to SUBJECT)
CLASS (Class_ID, Class_Name, Student_ID)
SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)
Descriptive attribute
Rule: An attribute that is part of a relationship is descriptive. Include the descriptive attributes to 1 side as shown above.
DateOfJoin, Hours# of Attends relationship.
CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)
Weak entity set
Rule: Weak entity set is totally participated (existence dependent) on the strong entity set. Include the primary key of strong entity set into the weak entity set as foreign key.
(d) SECTION
SECTION (Section_ID, Section_Name, Class_ID)
Weak relationship
Rule: No need to create as a table. If created, then the table is redundant.
Has
No changes

Final set of relation schemas: (Primary keys are underlined)

STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)

CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)
Student_ID is the foreign key refers STUDENT table

SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)
Student_ID is the foreign key refers STUDENT table

SECTION (Section_ID, Class_ID, Section_Name)
Class_ID is the foreign key refers CLASS table

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











1 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