Draw Er Diagram and Convert It Into a Relational Schema

Converting ER Diagrams to Tables-

Afterwards designing an ER Diagram,

• ER diagram is converted into the tables in relational model.
• This is considering relational models can exist easily implemented past RDBMS like MySQL , Oracle etc.

Following rules are used for converting an ER diagram into the tables-

Dominion-01: For Strong Entity Set With Only Simple Attributes-

A strong entity set with only simple attributes will require simply ane table in relational model.

• Attributes of the table will be the attributes of the entity set.
• The primary fundamental of the table volition be the key attribute of the entity set.

Instance-

Schema : Student ( Roll_no , Proper name , Sex )

Too Read- Entity Sets in DBMS

Rule-02: For Strong Entity Set With Composite Attributes-

• A strong entity set with any number of blended attributes volition require just one tabular array in relational model.
• While conversion, uncomplicated attributes of the blended attributes are taken into account and not the composite attribute itself.

Case-

Roll_no	First_name	Last_name	House_no	Street	Metropolis

Schema : Educatee ( Roll_no , First_name , Last_name , House_no , Street , Urban center )

Besides Read- Types of Attributes in DBMS

Rule-03: For Strong Entity Set With Multi Valued Attributes-

A strong entity fix with whatsoever number of multi valued attributes will require two tables in relational model.

• One table will contain all the simple attributes with the main central.
• Other table will comprise the primary key and all the multi valued attributes.

Example-

Rule-04: Translating Human relationship Set into a Tabular array-

A human relationship gear up will require 1 tabular array in the relational model.

Attributes of the tabular array are-

• Chief key attributes of the participating entity sets
• Its own descriptive attributes if whatsoever.

Prepare of non-descriptive attributes will exist the primary central.

Example-

Schema : Works in ( Emp_no , Dept_id , since )

NOTE-

If we consider the overall ER diagram, three tables will be required in relational model-

• I table for the entity fix "Employee"
• One tabular array for the entity set "Section"
• 1 table for the relationship set "Works in"

Dominion-05: For Binary Relationships With Cardinality Ratios-

The following 4 cases are possible-

Case-01: Binary human relationship with cardinality ratio m:n

Case-02: Binary relationship with cardinality ratio 1:north

Case-03: Binary human relationship with cardinality ratio m:1

Case-04: Binary human relationship with cardinality ratio 1:1

Also read- Cardinality Ratios in DBMS

Case-01: For Binary Relationship With Cardinality Ratio thousand:n

Here, three tables will be required-

1. A ( a1 , a2 )
2. R ( a1 , b1 )
3. B ( b1 , b2 )

Case-02: For Binary Relationship With Cardinality Ratio 1:n

Here, two tables volition be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

NOTE- Hither, combined table will exist fatigued for the entity set B and relationship set R.

Example-03: For Binary Relationship With Cardinality Ratio m:1

Hither, two tables will be required-

1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )

NOTE- Here, combined tabular array will be drawn for the entity fix A and human relationship set R.

Case-04: For Binary Human relationship With Cardinality Ratio 1:1

Here, two tables volition be required. Either combine 'R' with 'A' or 'B'

Mode-01:

1. AR ( a1 , a2 , b1 )
2. B ( b1 , b2 )

Mode-02:

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

Pollex Rules to Remember

While determining the minimum number of tables required for binary relationships with given cardinality ratios, following thumb rules must be kept in mind-

• For binary human relationship with cardinality ration m : due north , separate and individual tables will be fatigued for each entity set and human relationship.
• For binary relationship with cardinality ratio either m : 1 or 1 : due north , e'er remember "many side will consume the relationship" i.e. a combined table will be fatigued for many side entity set and relationship set.
• For binary relationship with cardinality ratio 1 : one , 2 tables will exist required. Yous tin can combine the relationship set with any one of the entity sets.

Rule-06: For Binary Relationship With Both Cardinality Constraints and Participation Constraints-

• Cardinality constraints volition be implemented as discussed in Dominion-05.
• Because of the total participation constraint, foreign central acquires Non NULL constraint i.e. at present foreign central can not exist nix.

Case-01: For Binary Relationship With Cardinality Constraint and Full Participation Constraint From One Side-

Because cardinality ratio = 1 : n , so we will combine the entity set B and relationship set R.

Then, 2 tables will be required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

Because of total participation, foreign key a1 has acquired NOT Zip constraint, so it can't exist null now.

Case-02: For Binary Relationship With Cardinality Constraint and Full Participation Constraint From Both Sides-

If there is a central constraint from both the sides of an entity set with total participation, then that binary relationship is represented using only single tabular array.

Here, But one table is required.

• ARB ( a1 , a2 , b1 , b2 )

Dominion-07: For Binary Relationship With Weak Entity Set-

Weak entity set always appears in association with identifying relationship with total participation constraint.

Here, two tables will exist required-

1. A ( a1 , a2 )
2. BR ( a1 , b1 , b2 )

Side by side Commodity- Practice Bug On Converting ER Diagrams to Tables

Get more notes and other study textile of Database Direction Organisation (DBMS).

Picket video lectures by visiting our YouTube channel LearnVidFun.

powellthation.blogspot.com

Source: https://www.gatevidyalay.com/tag/convert-er-diagram-to-relational-schema/

Share this post