Entity Relational Model

Chapter 2: Entity Relational Model:--


The entity relational model perceives the real world as consisting basic object called entities and relationship among those entities.
The E-R Model is important primarily for its role in database design. It provides useful concept that allows us to move from an informal description of what users want from their database to a more detail and precise description that can be implemented in a database management system.
Entity and entity set
The entity may be defined as a thing which an organization recognizes as being capable of an independent existence and which can be uniquely identified.
The collection of similar entities is called entity set.
An entity may be a physical object such as house, car, an event such as house sell or a car service or a concept such as a customer transaction or order.
An entity is represented by a name in a box.
Attributes
An entity is represented by a set of attributes. Attributes are descriptive properties passed by an each member of an entity set.
For each attribute there is a set of permitted values known as domain of that attribute which can be assigned to attributes.

Teacher
id name address
1 abc xyz
Key attribute

domain

Each attribute is represented by an oval and is associated with domain of legal values.
Each entity contains a unique identifier attribute known as key attribute which are usually indicated by underlining.
Associations are indicated by connecting lines.
Types of attribute
1. Simple and composite attribute
Simple attributes are those which cannot be broken down into sub-parts. E.g.: rollno, gender etc.

Composite attributes are those which can be broken down into different sub parts. E.g.: name, address etc.

2. Single valued and multivalued attribute
Single valued is those whose value will always be one. E.g.: rollno, citizenship-no is always unique for one person.

Multivalued attributes are those which can have two or more than two values. E.g.:phone-no, guardian etc. It is denoted by

3. Null attribute
Null attributes are those which can have null values. E.g.: Middle-name.

4. Derive attribute
These are the attributes which are derived from other attributes. E.g. age (if date of birth is one attribute)
Relationship Set
The association of the two or more than two entities is known as relationship set.
Note: A relationship may also have attribute called descriptive attribute.

Degree of a relationship
1. Unary relationship
Also called a recursive relationship. It is a relationship between instances of one entity type.

2. Binary relationship
It is a relationship between instances of two entity types and is the most common encountered in data modeling.Relationship
3. Ternary relationship
The relationship that involves three entities is called ternary relationship.

4. N-ary relationship
Any relationship that involves n-number of entities is called n-ary relationship.

Roles in relationship
If the relationship involves single entities playing different role such are called roles in relationship. Role is indicated in E-R diagram by labeling the line. E.g.: worker and manager both are employee but are playing different roles.

Constraints on relationship types
Relationship types have certain constraints that limit the possible combination of entities that may participate in relation.
1. Mapping cardinalities
It expresses the number of entity to which another entity can be associated through a relationship set.
Types of mapping cardinalities
a. One-to-one relationship
If an entity set A can relate at most one entity in entity set B and entity in entity set B can relate with at most one entity in entity set A then such relationship is one-to-one relationship.

b. One-to-many relationship
If an entity set A can relate with more than one entity in entity set B and entity in entity set B can relate with at most one entity in entity set A then such relationship is one-to-many relationship. E.g.: a parent can have many children but the children can’t have many parents.
c. Many-to-one relationship
If an entity set A can relate with at most one entity in entity set B and entity in entity set B can relate with more than one entity in entity set A then such relationship is many-to-one relationship.
d. Many-to-many relationship
If an entity set A can relate with more than one entity in entity set B and entity in entity set B can relate with more than one entity in entity set A then such relationship is many-to-many relationship.

b) Participation Constraints
It specifies whether the existence of entity depends on its being related to another entity via the relationship sets.
Types
1. Total participation
The participation of entity set ‘E’ in a relationship set ‘R’ is said to be total if every entity in ‘E’ participate in at least in one relationship in ‘R’.


E.g. If company policy states that every employee must work under department, then an employee can exists only if it participate in at least one relationship instances (i.e. employee can’t be existed without department).

2. Partial participation
If only some entities in ‘E’ participate in relationship ‘R’, the participation of entity set ‘E’ in relationship ‘R’ is said to partial participations. E.g. every employee will not be an manager of the department so the participation of these type of relationship is partial.

Keys
The main role of key is to specify the given entity and relationship in entity set and relationship set (i.e. specifies particular entity from the entity set).
Unique identifier in entity set is key.
Types of keys
1. Super key
It is the set of one or more attributes whose combine value uniquely identifies the entities in the entity set. E.g. {empno, ename, address} can be considered as a super key. If we assume that there are no two employees of the same name and address then the set {ename, address} is another super key.

2. Candidate key
A candidate key is a minimal super key i.e. a super key which does not have any proper subset which is also a super key. E.g. {empno} and {ename, address} are two candidate key.

3. Primary key
It is a candidate key i.e. chosen by a database designer as a principle means of uniquely identifies entities within the entity set. E.g. The candidate key {empno} can be considered to be the primary key.

4. Composite key
In some entity set a single attribute can’t be use to uniquely identify entities. In that case we have to use two or more attributes to uniquely identify entities within the entity set. When primary key contains sets o two or more than two attributes, it is called a composite primary key. E.g. {ename, address} can be a composite key, if ename and address of two employees are not same.

Weak Entity
An entity set that does not have a primary key is referred to as weak entity. The existence of weak entity set depends upon the existence of strong entity set through a one-to-many relationship.
Entity set which have its own primary key is known as strong entity.
Weak entity is represented by double line rectangle box in E-R diagram.
E.g.


The relationship form by weak entity does not have descriptive attributes so the relationship becomes weak relation and is represented by double line diamond box.

Some attributes of weak entity set can be used as key known as partial key also known as discriminator. Discriminator of weak entity set is represented by dotted line.
Primary key of weak entity is formed by combining primary key of strong on which it depends and its discriminator.
Extended ER features
1. Generalization
Generalization is the process by combining or taking union of entity set to make a higher level entity set. It is a bottom up designing process.

2. Specialization
It is a process of defining a set of sub groupings within an entity set.
This is the process of defining a set of sub classes of super classes. E.g.: secretary, engineers, technician are specialization of super class employee based on job types.
Simply generalization and specialization are inverse of each other. So specialization is a top down design process.

3. Total generalization/specialization

 Total specifies that every entity in the super class must be a member of at least one specialization.

 Shown in the ER diagram by double line.


4. Overlapping generalization/specialization
 Overlapping specifies that the same real world entity may be a member of more than one sub-class in the same specialization.


5. Disjoint generalization/specialization
 A disjoint requires that an entity belong to no more than one lower level entity set.


6. Aggregation
It is an abstraction through which relationship are treated as higher level entities.




Fig: Aggregation
Design of an entity relationship database schema
entity set derived attribute

weak entity primary key

relationship set Total participation E in R

weak relationship entity one-to-many

attribute one-to-many

multivalued-attribute one-to-one

generalization/specialization many-to-many

Fig: Symbols used in ER diagram
Designer should focus on
1. Whether to use an attribute or an entity set to represent an object.
2. Whether a real world concept is expressed more accurately by an entity set or relationship set.
3. Whether to use a ternary relationship or a pair of binary relationship.
4. Whether to use a strong or weak entity.
5. Whether using generalization is appropriate.
6. Whether using aggregation is appropriate.

Design phase
 User requirement
 Organize way of arranging data
 Analysis of user requirement
 Process of database design