Relational Schema to ER Diagram

Question

I have designed a relational model for a university database which is given below. now I am asked to make an ER Diagram of this database. I just want to know that is there any tool using which I can generate the ER diagram from the relational schema. if no, what are the steps to make an ER diagram from a relational model?

Answer 1

I don't know of any tools that can generate a proper ER diagram from a relational model. One difficulty for such tools is that not all relational models can be interpreted as ER models. While the relational model can express any finite set of facts (since it's equivalent to first-order logic), the entity-relationship model is more limited.

To convert a relational model into an ER diagram, I'd suggest the following steps:

1. A good understanding of the ER model will be valuable. Study Chen's paper The Entity-Relationship Model - Toward a Unified View of Data.

2. For each column, determine whether it represents an entity set or a value set. Entity keys are normally primary keys in one table and foreign keys in others. Values sets generally represent labels and measurements, and are in dependent columns.

   • For example, in teacher, T_ID and dept_name are entity keys, while name and password represent value sets.

3. Identify relationships. Relationships are represented by two or more entity keys in the same table, at least one of which is part of the primary key.

   • For example, in teacher, the pair (T_ID, dept_name) represents a relationship between the entity sets identified by T_ID and dept_name. We can call those entity sets teacher and department, but don't confuse them with the tables that have the same names. Another example is advisor (T_ID, S_ID).

4. Identify attributes. Attributes are mappings from entity sets or relationships to value sets. The primary key of a table will determine the entity or relationship set (atomic or composite PK), with which the dependent columns (value sets) are associated, forming attributes.

   • For example, in teacher, T_ID -> name is an attribute, and T_ID -> password is another.

5. Make a diagram. Represent each entity set with a rectangle and each relationship set with a diamond. Connect relationships to the related entity sets. Draw keys and attributes as ovals attached to the determining entity or relationship set. We don't draw keys for relationships - they are determined by the keys of the associated entity sets.

This is just a basic starting point - the process is actually more complicated, since we need to look out for weak keys, weak entity sets, identifying relationships, associative entity sets, total or partial participation, and relationship cardinality.

Again, I highly advise you to study Chen's paper for all the detail.

PS. I believe your primary key for section (and the corresponding foreign keys in teaches and takes) is incorrect. I suspect the primary key should be only sec_id, but since I have no sure knowledge of what your model actually represents (beyond my own interpretation of the table and column names), I can't say for sure.