In this article, we will learn about Mapping constraints in DBMS.
Conversion is nothing but converting ER diagram to tabular form. This is done because tables can be easily implemented by RDBMS like MySQL, Oracle etc.
ER to Relational Model Conversion
Conversion is nothing but converting ER diagram to tabular form.
This is done because tables can be easily implemented by RDBMS like MySQL, Oracleetc.
Certain rules are to be followed to convert ER diagram to table.They are
Rule-01: For Strong Entity Set With Only Simple Attributes
In relational model only one table is required to represent a strong entity set with simple attributes. In this
Attributes are taken as columns of the table
Key attribute is declared as primary key of the table
Rule-02: For Strong Entity Set With Composite Attributes
In relational model only one table is required to represent a strong entity set with composite attributes. In this
Simple attributes are taken as columns of the table and
Simple attributes of the composite attributes are considered as columns but not composite attributes themselves
Key attribute is declared as primary key of the table
Rule-03: For Strong Entity Set With Multi Valued Attributes
In relational model two tables are required to represent a strong entity set with multi-valued attributes.
One table with columns as primary key and multi-valued attributes and
One table with columns as primary key and other simple attributes
Rule-04: Translating Relationship Set into a Table
Only one table is required to represent a relationship set with columns as
Key attributes of each participating entity set as primary keys
Attributes of the relationship if any.
For suppose ,if we consider a relationship set with 2 entity sets , we require totally 3 tables to represent the whole ER diagram
One table for first entity set with columns as its attributes
Other table for second entity set with columns as its attributes
Another table for relationship set with columns as primary keys of both entity sets and attributes of relationship set
Rule-05: For Binary Relationships With Cardinality Ratios
In this model four cases are possible. They are :
Case-1: Binary relationship with cardinality ratio 1:1
Case-2: Binary relationship with cardinality ratio m:1
Case-3: Binary relationship with cardinality ratio 1:n
Case-4: Binary relationship with cardinality ratio m:n
Case-1: For Binary Relationship With Cardinality Ratio 1:1
In this case two tables are required , we can combine the relationship set with either of the entity sets.
First possible way
PR ( p1 , p2 , q1 )
Q ( q1 , q2 )
Second possible way
P( p1 , p2 )
QR ( p1 , q1 , q2 )
Case-2: For Binary Relationship With Cardinality Ratio m:1
In this model two tables are requiredto represent the ER diagram. They are
PR ( p1 , p2 , q1 )
Q ( q1 , q2 )
Case-3: For Binary Relationship With Cardinality Ratio 1:n
In this model two tables are required to represent the ER diagram. They are
P( p1 , p2)
QR (p1, q1 , q2 )
Case-4: For Binary Relationship With Cardinality Ratio m:n
In this model three tables are required to represent the ER diagram. They are
P ( p1 , p2 )
R ( p1 , q1 )
Q ( q1 , q2 )
Rule-06: For Binary Relationship With Both Cardinality Constraints and Participation Constraints
In this model foreign key acquires NOT NULL costraint because of total participation constraint
Rule 5 is followed to implement cardinality constraints.
In this two cases are possible :
Case-01: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From One Side
Here we will combine the entity set Q and relationship set R as cardinality ratio is 1:n.
Then, two tables will be required-
P( p1 , p2 )
QR ( p1 , q1 , q2 )
Foreign key p1 has acquired NOT NULL constraint because of total participation constraint
Case-02: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From Both Sides
Only one table is required to represent a binary relationship when there is a key constraint from both the sides of entity set with total participation. It is
PRQ( p1 , p2, q1 , q2 )
Rule-07: For Binary Relationship With Weak Entity Set
Whenever a weak entity set appears, it will have identifying relationship set with total participation constraint.
This model requires two tables to represent the ER diagram. They are
P ( p1 , p2 )
QR ( p1 , q1 , q2 )
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Login/Signup to comment