UNIT II
Entity-Relationship Model
Introduction, the building blocks of an entity relationship diagram, classification of entity sets, attribute classification, relationship degree, relationship classification, reducing ER diagram to tables, enhanced entity-relationship model (EER model), generalization and specialization, IS A relationship and attribute inheritance, multiple inheritance, constraints on specialization and generalization, aggregation and composition, entity clusters, connection types, advantages of ER modelling
Introduction
There are two techniques used for the purpose of data base designing from the system requirements .Those are:
a) Top down approach known as Entity- Relationship Modeling b) Bottom up approach known as Normalisation.
We will focus on top down approach of designing database. It is a graphical technique, which is used to convert the requirement of the system to graphical representation, so that it can become well understandable. It also provides the framework for designing of database.
The Entity Relationship(ER) model was originally proposed by peter in 1976 as a way to unify the network and relational database views. Simply stated, the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram, which is used to visually represent data objects. For the database designer, the utility of the ER model is:
a) It maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables.
b) It is simple and easy to understand with a minimum of training .Therefore, the model can be used by the database designer to communicate the design to the end user.
Entity Relationship Model
The entity relationship data model represents the overall logical structure of a database. The ER data model contains three basic notations. those are
Entity sets, relationship sets and attributes.
Classification of entity sets, attribute classification
Entity:
An Entity is a real world object. An Entity is a group of related information. Rectangles are used to represent the entity in the diagram. Name of the Entity is written inside the rectangle.
Ex: Student, Customer are the examples of person.
Entity type:
An entity type defines a collection or set of entities that have the same attributes. Each entity type is described by its name and attributes.
Entity set:
An entity set is the collection of all entities of a particular entity type in the database at any point in time.
Weak Entity Set
Strong Entity Set:
An entity set that has a Primary key is termed as Strong Entity Set.A strong entity is represented by single rectangle as shown above.
Composite Entity:
Entities participating in the many to many relationships are called composite entity.
Recursive Entity: If a relation exists between the same entities, then such entities are called as recursive entity.
******************************************************************************
Attribute Classification
Attribute:
An attribute specifies the characteristics or properties of an entity. An oval shape is used to represent the attributes. Name of the attribute is written inside the oval shape and is connected to its entity by a line.
Domain:
Attributes have a domain. A domain is the set of possible values for a given Attributes.
Eg: The domain for the gender attribute consists of only two possibilities namely male and female.
Simple Attribute
These kinds of attributes have values which cannot be divided further.
Eg. STUDENT_ID attribute which cannot be divided further. Passport Number is unique value and it cannot be divided.
Composite Attribute:
This kind of attribute can be divided further to more than one simple attribute.
Derived Attribute
Derived attributes are the one whose value can be obtained from other attributes of entities in the database.
For example, Age of a person can be obtained from date of birth and current date. Average salary, annual salary, total marks of a student etc are few examples of derived attribute.
Stored Attribute
The attribute which gives the value to get the derived attribute are called Stored Attribute.
For example , age is derived using Date of Birth. Hence Date of Birth is a stored attribute.
Single Valued Attribute
These attributes will have only one value.
For example, EMPLOYEE_ID, passport#, driving license#, SSN etc have only single value for a person.
ER diagram representation of student entity and its attributes:
Key Attribute: An underline to the attribute name is put to represent the primary key. The key attribute of the weak entity is represented by dashed underline.
Keys:
Keys are the attributes of the entity, which uniquely identifies the record of the entity. For example STUDENT_ID identifies individual students, passport#, license # etc.
there are different types of keys in the database.
Super Key is the one or more attributes of the entity, which uniquely identifies the record in the database.
Candidate Key is one or more set of keys of the entity. For a person entity, his SSN, passport#, license# etc can be a super key.
Foreign Key of the entity attribute in the entity which is the primary key of the related entity. Foreign key helps to establish the mapping between two or more entities.
******************************************************************************
Relationship degree & Relationship classification
Relationship:
The Relationship is an Association between entities.A diamond shape is used to show the relationship between the entities.
Degrees of Relationship
In a relationship two or more number of entities can participate.
The number of entities who are part of a particular relationship is called degrees of relationship.
If only two entities participate in the mapping, then degree of relation is 2 or binary.
If three entities are involved, then degree of relation is 3 or ternary.
If more than 3 entities are involved then the degree of relation is called n-degree or n-nary.
Connectivity and cardinality:
Cardinality Express the minimum and maximum number of entities occurrences associated with one occurrence of the related entity.
In the ERD the cardinality is indicated by placing the appropriate numbers beside the entities using the format(x, y).
The first value represents the minimum number of associated entities. The second value represents the maximum number of associated entities.
For Eg:
The cardinality (1,1) indicates that each class is taught by only one professor.
Existence Dependency:
An Entity is said to be existence dependency when the entity is associated with another related entity.
Based on the cardinality, there are 3 types of relationship.
Types of Relationship Mapping
1.One - to - One Relationship 2. One - to - Many Relationship 3. Many - to - Many Relationship
1. One - to - One Relationship:
In One - to - One Relationship, one entity is related with only one other entity.
One row in a table is linked with only one row in another table and vice versa. For example: A Country can have only one Capital City.
2. One - to - Many Relationship
In One - to - Many Relationship, one entity is related to many other entities.
One row in a table A is linked to many rows in a table B, but one row in a table B is linked to only one row in table A.
3. Many - to - Many Relationship
In Many - to - Many Relationship, many entities are related with the multiple other entities.
This relationship is a type of cardinality which refers the relation between two entities.
For example: Various Books in a Library are issued by many Students.
Recursive Relationship
If the same entity participates more than once in a relationship it is known as a recursive relationship.
Example of a recursive relationship in ER diagram
Participation Constraints
There are two types of Participation Constraints,
1. Total Participation 2. Partial Participation
1. Total Participation:
In Total Participation, every entity in the set is involved in some association of the relationship.
It is indicated by a double line between entity and relationship.
For example: Every Department must have a Manager.
2. Partial Participation
In Partial Participation, not all entities in the set are involved in association of the relationship.
ER Diagram representation for entity, attributes, relationship, cardinality and constraints
Reducing ER diagram to tables:
Since ER diagram gives us the good knowledge about the requirement and the mapping of the entities in it, we can easily convert them as tables and columns. i.e.; using ER diagrams one can easily created relational data model, which nothing but the logical view of the database.
The basic rules for converting the ER diagrams into tables is
1. Convert all the Entities in the diagram to tables.
2. All single valued attributes of an entity is converted to a column of the table 3. Key attribute in the ER diagram becomes the Primary key of the table. 4. Declare the foreign key column, if applicable.
5. Any multi-valued attributes are converted into new table.
6. Any composite attributes are merged into same table as different columns. 7. One can ignore derived attribute, since it can be calculated at any time.
******************************************************************************
Create a simple ER diagram for a STUDENT database
Let us create a simple ER diagram for a STUDENT database. What is the requirement of this database?
‘Student attends class. Each class is divided into one or more sections. Each class will have its own specified subjects. Students have to attend all the subjects of the class that he attends’.
Now let us identify what are the entities? STUDENT, CLASS, SECTION, SUBJECT are the entities. Attributes of these entities are not specified here. But we know what could be the entities of each of the entities. We can list them as below at this point of time.
STUDENT CLASS SECTION SUBJECT
STUDENT_ID CLASS_ID SECTION_ID SUBJECT_ID
STUDENT_NAME CLASS_NAME SECTION_NAME SUBJECT_NAME
ADDRESS DOB AGE CLASS_ID SECTION_ID
Age attribute can be derived from DOB. Hence we have to draw dashed oval.
Address is a composite attribute. We have to draw its sub attributes too. So that we will be very clear about his address details.
If we see the SECTION entity, by section id, will we be able get the section that student attends? There is no relation mentioned between Student and Section. But Section is mapped only with Class. What do we understand from this? Section is a weak entity. Hence we have to represent it properly.
Since each class is having different subjects and Students attends those subjects, we can modify the relation ‘studies’ to ‘has’ relation on the relation ‘attends’.
Now the diagram will change to reflect all above points.
What are the participation constraints here?
All the Students attend any one of the class, but class can have only certain group of students. Hence total participation of Students and partial participation of class in ‘Attends’ relation.
All the class has section and all the section has class. Hence both are total participation. All the Students study some of the subjects specific for their class and each class has only
What are the cardinalities of all the relationship?
Each Student attends only one class at a time. Hence it is a 1: 1 relation.
Each class has one or more sections. Hence it can be considered as 1: N relation.
Each student attends many subjects and each class has many subjects. Hence it is a 1:N relation.
Note: If you look at STUDENT and CLASS relationship as many Students attend one class, then it would be an M: 1 relation. It is all up to the developer, how he looks at the requirement.
Enhanced Entity-Relationship Model (EER model)
EER is a high level data model that incorporates the extensions to the original ER model.
The Extended Entity Relationship Model (EERM) sometimes referred to as the enhanced entity relationship model because adding more semantic constructs to the original entity relationship model. The ER Model constructs Entity super types, entity sub types and entity clustering.
It is a diagrammatic technique for displaying the following concepts:
i) Sub class and super class
ii)Specialisation and Generalisation
iii) Union or category
iv)Aggregation
Features of EER modeling:
a) EER creates a design more accurate to database schemas. b) It reflects the data properties and constraints more precisely. c) It includes all modeling concepts of the ER nmodel.
d) Diagrammatic technique helps for displaying the EER schema. e) It includes the concept of specialization and generalization.
f) It is used to represent the collection of objects that is union of objects of different entity types.
i) Sub class and super class
Sub class and Super class relationship leads the concept of Inheritance.
The relationship between sub class and super class is denoted with symbol
Super Class
a) Super class is an entity type that has a relationship with one or more subtypes.
b) An entity cannot exist in database merely by being member of any super class. For example: Shape super class is having sub groups as Square, Circle, Triangle.
For example: Square, Circle, Triangle are the sub class of Shape super class.
ii) Specialisation and Generalisation
Generalisation:
a) Generalization is the process of generalizing the entities which contain the properties of all the generalized entities.
b) It is a bottom approach, in which two or more lower level entities combine to form a higher level entity.
c) Generalization is the reverse process of Specialization.
d) It defines a general entity type from a set of specialized entity type.
e) It minimizes the difference between the entities by identifying the common features.
Specialisation:
a) Specialization is a process that defines a group of entities which is divided into sub groups based on their characteristic.
b) It is a top down approach, in which one higher entity can be broken down into two lower level entity.
c) It maximizes the difference between the members of an entity by identifying the unique characteristic or attributes of each member.
d) It defines one or more sub class for the super class and also forms the superclass/subclass relationship.
In the above example, Employee can be specialized as Developer or Tester, based on what role they play in an Organization.
iii) Union or category
a) Category represents a single super class or sub class relationship with more than one super class.
b) It can be a total or partial participation.
For example Car booking, Car owner can be a person, a bank (holds a possession on a Car) or a company.
Aggregation
a) Aggregation is a process that represent a relationship between a whole object and its component parts.
b) It abstracts a relationship between objects and viewing the relationship as an object.
c) It is a process when two entities are treated as a single entity.
It cannot express relationships among relationships. Consider the ternary relationships attends, between a student, course, and subjects.
Student attends the Course, and he has some subjects to study. At the same time, Course offers some subjects. Here a relation is defined on a relation. But ER diagram does not entertain such a relation. It supports mapping between entities, not between relations.
from generalization. In generalization, we merge entities of same domain into one entity. In this case we merge related entities into one entity.
Here we have merged STUDENT and COURSE into one entity STUDENT_COURSE. This new entity forms the mapping with SUBJECTS. The new entity STUDENT_COURSE, in turn has two entities STUDENT and COURSE with ‘Attends’ relationship.
******************************************************************************
Inheritance:
The property of inheritance enables an entity subtype, to inherit the attributes and relationships of the super type.
In the above example the employee entitysuper type participating in a one-to-many relationship with a dependent entity through inheritance all subtypes also participated in that relationship.
IS A relationship and attribute inheritance, multiple inheritance:
Attribute Inheritance
For example, customer and employee inherit the attributes of person. Thus, customer is described by its name, street, and city attributes, and additionally a customer_id and credit_rating attribute; employee is described by its name, street, and city attributes, and additionally employee_id and salary attributes.
A lower-level entity set also inherits participation in the relationship sets in which its higher-level entity participates.
Entity Super types, Entity Sub types:
In an Organization contains different types of employee and all the employees are not having the same attributes. If you create a one table for all employees to store their information many columns have null values.
For Example the pilot shares certain characteristics with other employee such as employee_no, emp_name, emp_address, employee_hire_date on the other employees. But pilot characteristics are not shared by other employee. The pilot characteristics are employee_license and employee_ratingwill generates nulls for employees who are not pilot.
The pilot entity store only the attributes that are unique to pilot, and the employee entity store attribute that are common to all employees.
We can conclude that pilot is a sub type of employee and employee is a super type of pilot. An entity super type is a generic type i.e, related to one or more entity subtypes, where the entity super type contains common characteristics entity subtype contains unique characteristics.
Specialization Hierarchy:
Entity super types and sub types are organized in a hierarchy which describes the higher level entity super types (parent entity) and lower level entity (child entity) sub types.
The following diagram shows specialization hierarchy by an employee super type and three entity sub types.
1. Pilot
2. Mechanic
3. Accountant.
The specialization hierarchy reflects one-to-one relationship between super entity type and sub entity type.
Subtype Discriminator:
A sub type discriminator is the attribute in the super type entity that determines to which sub type is related.
In the above example if the sub type discriminator is emp_type.
If the emp_type has a value of p the super type is related to pilot sub type.
If the emp_type has a value of A the super type is related to Account subtype.
Disjoint & Overlapping Constraints:
An entity super type can have disjoint or overlapping entity supertypes.
Disjoint subtype are sub types that contains a unique subset of the super type entity set.
The Disjoint subtypes are indicated by the letter ‘d’ inside the category shape. In disjoint the super type entity is related to only one sub entity types.
Eg: An employee who is a pilot can appear only in the pilot sub type, not in any of other sub types.
Overlapping subtypes are subtypes that contains non unique subsets of the super type entity set.
The Overlapping subtypes are indicated by the letter’ o’ inside the category shape. In the overlapping the super type entity is not relate to only one sub entity types.
Completeness constraint:
The completeness constraint can be partial or total.
Partial completeness means that not every super type entity is a member of sub type entity. A single horizontal line under the circle represents a partial constraint O.
Total completeness means that every super type is must be a member of atleast one sub type. A double horizontal line under the circle represents the total completeness constraint.
Specialization and Generalization:
We can use various approaches to develop entity super types and sub types.
Specialization is the top down process of identifying lower level entity sub type from a higher level entity super type.
Eg: The specialization is used to identify multiple entity supply (Pilot, Mechanic, Accountant) from the super entity employee.
Generalization is the bottom-up process of identifying higher level entity super types from a lower level entity sub types.
Entity Clustering:
Generally the data model will develop an initial ERD containing a few entities. As the designed approach completion the ERD will contain hundreds of entities and relationships. In those cases, we can use entity cluster to minimize the number of entities in the ERD.
An entity cluster is a virtual entity type used to represent multiple entities and the relationship in the ERD.
An entity cluster is formed by combining multiple inter related entities into a single entity object. An entity cluster is considered virtually in the sense that it is not actually an entity in the final ERD.
For Example: Consider the facility that rent for rooms for small parties. The manager of the facility keep the all the events in the following table formats.
Date Time_start Time_End Room Event_name Party-of
The above table can be represented as Event entity
EVENT(Date, Time_start, Time_End, Room, Event_name, Party-of)
In the above entity there is no simple natural keys i.e, used as a primary key in the model.
Based on the concept of primary key we suggest one of these options (Date, Time_start, Room) or (Date, Time_End, Room).
1. When Implementation of data model, the composite primary key in the event entity makes complexity and also coding.
2. The solution to the problem is to use a numeric single attribute as surrogate primary key.
EVENT(Date, Time_start, Time_End, Room, Event_name, Party-of)
Advantages of ER-Modelling:
a) Straightforward relation representation: Having designed an ER-diagram for a database application, the relational representation of the databse model becomes relatively straightforward.
b) Easy conversion for E-R to other datamodel: Conversion from E-R diagram to a network or hierarichal data model can easily be accomplished.
This is turn helps in the clear understanding of the data structure and in minimizing redundancy and other problems.
Disadvantages of E-R Modelling:
a) No industry standard for notation: There is no industry standard notation for developing an E-R diagram.
