Conversion of E-R diagram to Relational Database
ER Model, when
conceptualized into diagrams, gives a good overview of entity-relationship,
which is easier to understand. ER diagrams can be mapped to relational schema,
that is, it is possible to create relational schema using ER diagram. All the ER constraints may not be mapped into relational model, but an approximate schema
can be generated.
ER
diagrams mainly comprise of −
- Entity and its attributes
- Relationship, which is association
among entities.
Mapping Entity
An
entity is a real-world object with some attributes.
Mapping Process (Algorithm) :
- Create
table for each entity.
- Entity's attributes should become fields of tables with their respective data types.
- Declare primary key.
Mapped to relation scheme Student(Roll_No,Name,Class,Subject) with Roll_No as primary key.
Mapping Relationship
A
relationship is an association among entities.
Mapping Process:
- Create
table for a relationship.
- Add the
primary keys of all participating Entities as fields of table with their
respective data types.
- If
relationship has any attribute, add each attribute as field of table.
- Declare a
primary key composing all the primary keys of participating entities.
- Declare all
foreign key constraints.
Mapped to schemas Student(Roll_No,Name), Course(CID,Title) and Enrolled(Roll_No,CID,Marks,JoiningDate) with Roll_No and CID as primary key.
Mapping Weak Entity Sets
A
weak entity set is one which does not have any primary key associated with it.
Mapping Process
- Create
table for weak entity set.
- Add all its
attributes to table as field.
- Add the
primary key of identifying entity set.
- Declare all
foreign key constraints.
Mapped to schemas Student(RollNo,Name), Dependent(RollNo,Name),Where Roll_No of Student references RollNo of Dependent.
Mapping Hierarchical Entities
ER
specialization or generalization comes in the form of hierarchical entity sets.
Mapping Process
·
Create tables for all
higher-level entities.
·
Create tables for
lower-level entities.
·
Add primary keys of
higher-level entities in the table of lower-level entities.
·
In lower-level tables,
add all other attributes of lower-level entities.
·
Declare primary key of
higher-level table and the primary key for lower-level table.
·
Declare foreign key
constraints.
Mapped to Schemas:
Student(RollNo,Name,Age,Gender)
Teacher(Empid, Name,Age,Gender)
OR
Person(Pid, Name, Age,Gender)
Student(Rno,Pid)
Teacher(Empid, Pid)
No comments:
Post a Comment