Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Design A Database Using an Entity-Relationship Diagram

By Ramesh Gummadi,

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.

Total article views: 21493 | Views in the last 30 days: 7
 
Related Articles
FORUM

Primary-Foreign Key

Primary-Foreign Key

FORUM

Primary and foreign keys

Can a primary key be a foreign key too?

FORUM

Relational Diagrams

Relational Diagrams

SCRIPT

Find Foreign Key Tables

an easy method to find foreign key tables from primary key table

SCRIPT

List Table Foreign Keys, Primary key and indexes

This procedure let you list [optional] by table: -Foreign keys -Primary key -Indexes

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones