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
*********************
Go to Solved ER exercises page