Converting an ER-Model into Relational Schema :

The ER Model is intended as a description of real-world entities. Although it is constructed in such a way as to allow easy translation to the relational schema model. The ER diagram represents the conceptual level of database design meanwhile the relational schema is the logical level for the database design. There are various steps involved in converting an ER-model into Relational schema. Each type of entity, attribute and relationship in the diagram takes their own depiction here. Consider the ER-Diagram Below :

Mapping Entity Sets : 

An entity set is a set of entities of the same type that share the same properties, or attributes. For example, a Students set may contain all the students of a school; likewise a Teachers set may contain all the teachers of a school from all faculties. The Besic rules for converting the ER-Diagrams into tables is : 

1. Convert all the Entities in the Diagram to tables.All the entities in the rectangular box in the ER-Diagram become independent tables in the database. So the entities STUDENT, TEACHER, BOOKS, LIBRARY forms individual tables.

2. All single valued attributes of an entity is converted to a column of the table.All the attributes, whose value at any instance of time is unique, are considered as columns of that table.

3. Key attribute in the ER diagram becomes the primary key of the table. In the above diagram ROLL_NO, BOOK_ID, TEACHER_ID are the key attributes of the entities. hance we consider them as the primary keys of respective table.

4. Declare the foreign key column, if applicable. In the diagram attribute ROLL_NO in the LIBRARY entity is from STUDENT entity. Similarly attribute TEACHER_ID in the LIBRARY entity is from TEACHER entity. Hence by declaring the foreign key constraints, mapping between the tables are established.

5. Any multi-valued attributes are converted into new table. In the diagram in STUDENT entity the attribute SUBJECTS is multi-valued attribute. We need to store it separately, so that we can store any number of SUBJECTS. Hence we create a separate table SUBJECTS with ROLL_NO and SUBJECT_ID and SUBJECT_NAME as its columns. We create a composite key using ROLL_NO AND SUBJECT_ID.

6. Any composite attributes are merged into same table as different columns. In the diagram STUDENT Address is a composite attribute. It has Door_No, Street, City, State and Pin. These attributes are merged into STUDENT table as individual columns. 

7. One can ignore derived attribute, since it can be calculated at any time. In the STUDENT table, Age can be derived at any point of time by calculating the difference between DateOfBirth and current date. Hence we need not create a column for this attribute. It reduces the duplicity in the database.

These are the very basic rules of converting ER diagram into tables and columns, and assigning the mapping between the tables. Table structure at this would be as below :

Mapping Weak Entity Sets:

A weak entity set is one which does not have any primary key associated with it. Weak entity is also represented as table. All the attributes of the weak entity forms the column of the table. But the key attribute represented in the diagram cannot form the primary key of this table. We have to add a foreign key column, which would be the primary key column of its strong entity. This foreign key column along with its key attribute column forms the primary key of the table. The Besic rules of Mapping weak entity sets are :

1. Create table for weak entity set

2. Add all its attributes to table as field.

3. Add the primary key of identifying entity sets.

4. Declare all foreign key constraints.

Mapping Relationship : 

A relationship is an association among several entities. In other words A relationship defines how two or more entities are inter-related. For example STUDENT and SUBJECT entities are related as 'STUDENT X studies SUBJECT Y', here 'studies' defines the relationship between STUDENT and SUBJECT. The Besic rules of Mapping Relationships are :

1. Create table for a relationship.

 2. Add the primary keys of all participating Entities as fields of table with their respective data types.

3. If relationship has any attribute, add each attribute as field of table.

4. Declare a primary key composing all the primary keys of participating entities.

5. Declare all foreign key constraints.

 $ Mapping one-one Relationship : If the relationship is one-one, then we need not to create any separate table.

 # Mapping one-to-many Relationship : If the relationship is one-to-many, then again we need not to create any separate table.

 # Mapping many-to many Relationship : If the relationship is many-to-many, then we need a separate table. In the above diagram we have entities STUDENT, TEACHER and BOOKS, And these three entities are related to each-other by the status of BOOKs are issued or returned. So the Table Would be. 

Links : RDBMS List

No comments:

Post a Comment