ER - Entity Relationship Model

The two  techniques  that  which  are  used  for  designing  a  database  from  the  system  requirement  are  

1.ER Modelling  (Top  Down approach)

2.Normalization (Bottom Up approach)

Note:

Specialization (Top Down approach) is always applied on single entities

Generalization (Bottom Up approach) is always applied to the group of entities 

ER Modelling [Top down approach]:

ER-Models are originally proposed by peter in 1976 ,to unify the network and the relational database that are being used. 

ER-Model is a conceptual data model that views the real world as entities and relation ships.

Entity: An entity is an object whose information is stored in the database it has an independent  existence to collect the data.

Entities are classified as dependent/independent (or) strong/weak

Entity Set: An Entity Set is a set of entities of some type that share the same properties.

Relationship: A relationship represent an association between two (or) more entities they are classified as 

Degree of Relationship :

1.Unary Relationship

2.Binary Relationship

3.Ternary Relationship 

Relationship Constraints:

1.Cardinality Ratio.

2. Participation Constraints 

Entity: An entity is anything (a person, a place, a thing, or an event) about which data are to be collected and stored. An entity represents a particular type of object in the real world. Entity occurrence is unique and distinct.

Ex: customer, student, employee etc…

Types of Entity:

• Strong Entity
• Weak Entity

Strong Entity:

Strong  entity  are  those  entity  types  which  has  a  key  attribute. The primary key helps in identifying each entity uniquely. It is represented  by  a rectangle. In the above example, Roll_no identifies each element  of  the  table uniquely and hence, we can say that STUDENT is a strong entity type.

Weak Entity:

Weak entity type doesn't have a key attribute. Weak entity type can't be identified on its own. It depends upon some other strong entity for its distinct identity. A weak entity is represented by a double outlined rectangle.

Attributes: Attributes describe the behavior as the properties of an entity that which are associated, different types of attributes are 

1.Simple Attribute

2 Composite Attribute

3.Single value attribute

4.Multi valued Attributes

5.Derived attribute

1.Simple Attribute: It is an attribute composed of single component with an independent existence. These are atomic attributes that which cannot be further sub divided

Example: 

2.Composite Attribute: An attribute composed of multiple components each with an independent existence is called composite attribute

Example:

3.Single valued Attributes: A single value attribute is that which holds a single value for a single entity

Example 1:

Example 2:

4.Multi valued Attributes: The attribute that which holds multiple values for a single entity

Example:

5.Derived attribute :A derived attribute represents a values that is derived from a value of a related attributes  (or) set of attributes where some attributes are related for a particular entity 

Example:

Symbols used in Entity Relationship diagrams:  

Steps in designing ER diagram

Step1:Identify the entities that which are required for developing a project

Step2:Identify  the key attributes for every entity and other relevant attributes

Step3:Find the relationship  among the entities

Step4:Now draw the complete ER diagram with all attributes including the key attributes

Example 1:
ER Diagram for Hospital Management System:

Example 2:
ER-diagram for university management System Database:

Step1: Using MS-Access 

Create the data base for the below ERD diagram with four entity (patient, doctor, nurse and ward)using MS-Access database as shown 

Entity 1: 
patient

pat_id	pat_name	pat_disease
101	raju	tb
102	rani	cancer
103	kani	fever
104	vani	cough
105	dhoni	typhoid

Entity 2:

doctor

doc_id	doc_name	pat_id
201	rk	101
202	pk	102
203	ck	103
204	dk	104
205	sk	105

Entity 3:

nurse

n_id	ward_id	pat_id
301	401	101
302	402	102
303	403	103
304	404	104
305	405	105

Entity 4:
ward

ward_id	pat_id
401	101
402	102
403	103
404	104
405	105

Entity Relationship for Hospital Management System :

Step1: Using MS-Visio

Draw the ERD as shown in fig using MS Visio 

click on file menu -> new -> Software and Database -> Database Model Diagram

Now draw the ERD Diagram as shown in MS-Visio

patient entity

doctor entity

nurse entity

ward entity

Step 2:

Now we shall establish hospital management system using the above four entity relations using the relationship component as shown 

Step 3:

click on Data menu bar -> Link Data to Shapes and then select Microsoft Access Database which will be as shown 

Step 4:

Now patient entity database created in the ms access will be on to your ms visio  as shown

Step 5:

Repeat the same procedure for the remaining entities database  (doctor, nurse and ward) created in the ms access will be on to your ms visio  as shown

Step 6: 

How to run the query in MS-Visio 2007 : To run the query in MS-Visio we need to create a query in MS-Access as follows 

Click on Create menu bar -> Query Design

Step 7: Now add all the entity on to the query wizard

Step 8: Now write your query using the sql view by clicking on design menu bar -> View 

Queries:

1. select * from patient where pat_disease='tb';
2. select * from patient where pat_name like 'r%';
3. select * from patient where pat_id=105;
4. select * from patient where pat_name like '%i';
5. select * from doctor where doc_name like '%k';