- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
Conversion of ER-Model into Relational Model
An Entity-Relationship Model can be converted to relational model, in which each entity set and each relationship set is converted to a relational model.
Example shows the conversion of ER-Diagram into a set of relational model.
A separate relation represents each entity set and each relationship set. The attributes of the entities in the entity set become the attributes of the association, which represents the entity set. The entity identifier becomes the key to the relation, and a tuple represents each entity in the association. Similarly, the attributes of the relationships in each relationship set become the attributes of the relation, which represents the relationship set. The relationship identifiers become the keys to the association, and a tuple represents each relationship in that relation.
The entity-relationship model is converted into the following three relations, as shown in figure (b).
|PERSONS||(PER-ID, DESIGN, LAST-NAME, DOB)
From entity set PERSONS
|PROJECTS||(PROJ-ID, START-DATE, END-DATE)
From entity set PROJECTS
|WORKS-ON||(PROJ-ID, PER-ID, HRS-SPENT, STATUS)
From relationship set WORKS-ON
Conversion of ER-Model into SQL Constructs
The Entity-Relationship Model is transformed into SQL constructs using transformation rules. The following three types of tables are produced during the transformation of ER-Model into SQL constructs.
- An entity table with the same information content as the original entity: This transformation rule always occurs with the following relationships:
- Entities with recursive relationships that are many-to-many (M: N).
- Entities with binary relationships that are many-to-many (M: N), one-to-many (1: N) on the ‘1’ (parent) side, and one-to-one (1:1) on one side.
- Ternary or Higher-degree relationships.
- An entity table with the embedded foreign key of the parent entity: This is one of the most common ways CASE tools handle relationships. It prompts the user-to-define a foreign key in the ‘child’ table that matches a primary key in the ‘parent’ table. This transformation rule always occurs with the following relationships:
- Each recursive entity relationship is one-to-one (1:1) or one-to-many (1: N).
- Binary relationships that are one-to-many (1: N) for the entity on the ‘N’ (child) side, and one-to-one (1:1) relationships for one of the entities.
- A relationship table with the foreign keys of all the entities in the relationship: This is the other most common way CASE tools handle relationships in the entity-relationship model. In this case, many-to-many (M: N) relationship can only be defined in terms of a table that contains foreign keys that match the primary keys of the two associated entities. This new table may also contain attributes of the original relationship. This transformation rule always occurs with the following relationships:
- Recursive and Many-to-Many (M: N)
- Binary and Many-to-Many (M: N)
- Ternary or Higher-degree.
In the above transformations, the following rules apply to handle SQL null values:
- Nulls are allowed in an entity table for foreign keys of associated (referenced) optional entities.
- Nulls are not permitted in an entity table for foreign keys of associated (referenced) mandatory entities.
- Nulls are not allowed for any key in a relationship table because only complete row entities are meaningful in the entries.
The other sub-heading shows standard SQL statements needed to define each type of the ER-Model construct.
Conversion of Recursive Relationships into SQL Constructs
The Entity-Relationship model can be converted into SQL constructs. The figure shows the conversion of recursive relationships into SQL constructs.
Conversion of Binary Relationships into SQL Constructs
Figure illustrates the conversion of binary relationships into SQL constructs.
Conversion of Ternary Relationships into SQL Constructs
Figure illustrates the conversion of ternary relationships into SQL constructs.