E-R Diagrams

E-R Diagrams:

An ER diagram is a graphical representation of tables (entities) in a database and the relationships between them. It was developed by Peter Chen in 1976. ER diagram uses symbols to represent three different types of information. The rectangle is commonly used to represent entities. The diamonds are normally used to represent relationships and ovals are used to represent attributes.

Relationship

A logical connection between different entities is called relationship. The relationship indicates how the entities are connected or related to each other. The entities that participate in a relationship are called participants. The relationship may be between different entities or between an entity and itself. The entities are connected to each other in different ways.

A relationship is represented by a simple line that connects two entities as follows:

The name of relationship is displayed over the line that represents the type of connection between two entities.

Cardinality and Modality

Cardinality

The number of entity occurrences associated with each occurrence of the related entity is known as cardinality. It specifies how many instances of an entity related to each instance of another entity. It means that cardinality specifies the maximum number of relationships. The cardinality of relationship can be as follows:

1. One-to-one:

One-to-one cardinality means that one instance of entity A is related to only one instance of entity B.

2. One-to-Many:

One-to-many cardinality means that one instance of entity A is related to many instances of entity B.

3. Many-to-Many:

Many-to-Many cardinality means that one instance of entity A is related to many instances of entity B and one instance of entity B is related to many instances of entity A.

4. Zero or None:

Zero or none cardinality means that no instance of entity A is related to any instance of entity B.

5. Recursive:

Recursive cardinality indicates a relationship of an entity to itself.

Notations for Cardinality

Different notations for cardinality are as follows:

Modality

Modality describes the relationship as either mandatory or optional. It specifies the minimum number of relationships. The relationship is called optional when the minimum number is zero. The symbol for optional relationship is O.

The relationship is called mandatory when the minimum number is one or more. The symbol for mandatory relationship is (   ).

Entity class and Entity instance

A group of entities of the same type is called entity class. All entities in an empty type share common characteristics. For example, STUDENT entity class is a collection all students. A member of an entity class is also known as an entity instance. For example, a student Tim of STUDENT entity type is entity instance.

Entity-Relationship Model

When a system analyst sits down to design a new system, one crucial task is to identify and state what data needs to be held. From the statement of data requirements a conceptual data model is produced. This describes that how the system will group the elements of the data. There are three important terms that define the data requirements.

Entity

An entity is what in which data is to be held or stored. Examples of entities include Customer, Employee, Stock Item, and Supplier.

Attribute

An attribute is defined as the characteristic of an entity that can be a name etc. Examples of attributes associated with a Customer include Customer ID, Surname, Initials, Title, and Address.

Relationship

A relationship is defined as a link or association between entities. An example is the link between Dentist and Patient; one dentist has many patients, but each patient only one dentist.

Examples of Entity Relationship Diagram

An entity relationship diagram is a diagrammatic way of representing the relationship between the entities in a database. To show the relationship between two entities, both the relationship type and the name of the relationship need to be specifies. In the first relationship shown below, the type of relationship is one to one the name of the relationship is drives:

Sometimes it can be tricky to establish the type of the relationship. For example, several employees may use the same company car at different times. A single employee may change the company car that he used. The relationship will depend upon whether the data held refers to the current situation or whether it is historical record. The assumption has been make that the database is to be record the current car driven by an employee.

EXAMPLE:

The data requirements for a hospital in-patient system are defined as follows:

A hospital is organized into a number of wards. Each ward has a ward number and a name recorded, along with a number of beds in that ward. Each ward is staffed by nurses. Nurses have their staff number and names recorded and are assigned to a single ward.

Each patient in the hospital has a patient identification number, and his name, address and date of birth are recorded. Each patient is under the care of a single consultant and is assigned to a single ward. Each consultant is responsible for a number of patients. Consultants have their staff number, name and specialism recorded.

The four entities for the hospital in-patient system are WARD, NURSE, PATIENT, CONSULTANT and the identifier for each of these are ward number, staff number, patient identification number and staff number. The E-R diagram to show the relationship between the entities is given below: