Database
developers involved in the task of designing a database have to translate real
world data into relational data, i.e. data organized in the form of tables.
First they have to understand the data, then represent it in a design view and
then translate into a RDBMS. One of the techniques that is great to use
is the E-R diagram. Most of the developers who are involved in data base systems
might already be familiar with it or atleast heard about it. I am
going to try to briefly explain the concept and give an example to understand
it.
So
What is E-R Model?
This
model was first introduced by Dr.Peter Chen in 1976 in a paper titled
"The Entity-Relationship Model- Toward a Unified View of Data". The most
useful thing in this model is, it allows us to represent data in the form
of a diagram popularly known as E-R diagram and from this diagram we can map
the data into a relational schema. First I will try to informally
define some terms used in this model.
Term | Definition | Example |
| Entity | Any real world object that has some well defined characteristics. | Employee, Professor, Department, Student, Course etc. |
| Property (or) Attribute | Any characteristic of an entity. | For example an employee has a name, ID, department etc.; A professor has name, the subject he teaches, the department he belongs to etc.; A department has a name, number of employees etc; A student has a name, id and class he belongs to; A course has a name, course number etc. |
| Relationship | Association among entities or an entity that helps to connect two or more other entities. | Registration (connects student and the course entities) Project ( connects employee and department entities) |
| Regular Entity | An entity which is independent of other entities. | Course is a regular entity. |
| Weak Entity | An entity which is dependent on some other entity. | Section is a sub type of Course. i.e a course has many sections. With out a course there is no section. |
In the
E-R model all the above listed terms are represented in a diagrammatic technique
known as the E-R diagram.
Lets draw
an E-R Diagram
Say we are given
a task of designing a database for an university system. We try to recognise
the various entities that form a university sytem and then establish relationships
among them. I represented this data in the form of an E-R diagram.
Each
entity is shown as a rectangle. For weak entities the rectangle has
a double border. In the above diagram regular entities are University, College,
Dean, Professor, Department, Student and Course. Section is a weak entity.
Properties
or attributes of an entity are shown in ellipses and are attached to their
respective entity by a single solid line. In this diagram I am showing properties
for only student entity for the sake of clarity of the diagram.
The
relationship between entities are shown as diamonds and the entities which
are a part of the relationship are connected to the diamond by a solid line
labeled either '1' or 'M' indicating whether the relationship is one-to-many,
one-to-one or many-to-many.
Lets
Map the E-R diagram to Relational Schema ( as tables)
Regular
Entity - map to base table
Weak
Entity - map to base table with the primary key of the dominant entity as foreign
key in the table.
One-to-Many
relationship - No need to introduce new table, but need a primary key on the '1'
side to be a foreign key in the 'M' side.
Many-to-Many
relationship - New base table with two foreign keys corresponding to the two
participants.
One-to-One
or Zero relationship - Usually primary key on one side is a foreign key on the
other side.
Let
me now derive tables from the above diagram from this set of rules.
All
the regular entities represented by a rectangle can be translated
into base tables.
Table - University | |
| UID (primary key) | int |
| Name | varchar (20) |
| Chancellor | varchar (20) |
There is a 1-M
relationship between University and College and 1-1 relationship between Dean
and College. So primary key in table University will be a foreign key in table
College and primary key in table Dean will be foreign key in table College.
The rest of the tables also follow the same pattern.
Table - College | |
| CID (primary key) | int |
| University (foreign key references UID in University table) | int |
| Dean (foreign key references DeanID from Dean table) | int |
| Name | varchar (20) |
Table - Dean | |
| DeanID (primary key) | int |
| Name | varchar (20) |
| Age | int |
Table - Department | |
| DID (primary key) | int |
| College ( foreign key references CID in College table) | int |
| Chair (foreign key references PID in professor table) | int |
| Name | varchar (20) |
Table - Professor | |
| PID (primary key) | int |
| Department ( foreign key references DID in Department table) | int |
| Name | varchar (20) |
Table - Course | |
| CourseID (primary key) | int |
| Department ( foreign key references DID in Department table) | int |
| Name | varchar (20) |
Table - Section | |
| SectionID (primary key) | int |
| Course ( foreign key references CourseID in Course table) | int |
| Professor (foreign key references PID in professor table) | int |
| Name | varchar(20) |
Table - Student | |
| StudentID (primary key) | int |
| Department ( foreign key references DID in Department table) | int |
| Name | varchar (20) |
| DateofEnrollment | smalldatetime |
| TelephoneNumber | varchar(20) |
There is only one many-to-many relationship in the above diagram and that is
between section and student. That means a student can register for many sections
and a section has many students. To establish this relationship we will create
a new table called Student_Registration.
Table - Student_Registration | |
| Student (foreign key references StudentID in Student table) | int |
| Section ( foreign key references SectionID in Section table) | int |
Cool! Now we
finished designing a database with the help of an E-R diagram. So folks tell
me now if this technique is useful and simple to use and start using it for
your projects.
Conclusion
This
example is simple and you can design this data base with out actually using
the E-R diagram from common sense. How ever when you are given a task of designing
a data base, first putting it in the form of a diagram makes your job easy.
When the task is of designing a big data mart or data warehouse this technique
is indispensable. I
welcome any comments or suggestions.
References
Introduction to Database Systems by C.J. Date.