Design A Database Using an Entity-Relationship Diagram

,

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

An

Introduction to Database Systems by C.J. Date.

Rate

5 (3)

Share

Share

Rate

5 (3)