ER Model
An Entity–relationship model (ER model) describes the structure of a database with the help of a diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER model is a design or blueprint of a database that can later be implemented as a database. The main components of E-R model are: entity set and relationship set.
What is an Entity Relationship Diagram (ER Diagram)?
An ER diagram shows the relationship among entity sets. An entity set is a group of similar entities and these entities can have attributes.
In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database. Lets have a look at a simple ER diagram to understand this concept.
Here are the geometric shapes and their meaning in an E-R Diagram.
We will discuss these terms in detail in the next section(Components of a ER Diagram) of this guide so don’t worry too much about these term
- Rectangle: Represents Entity sets.
- Ellipses: Attributes.
- Diamonds: Relationship Set.
- Lines: They link attributes to Entity Sets and Entity sets to Relationship Set.
- Double Ellipses: Multivalued Attributes.
- Dashed Ellipses: Derived Attributes.
- Double Rectangles: Weak Entity Sets.
- Double Lines: Total participation of an entity in a relationship sets.
Componant of ER Diagram
As shown in the above diagram, an ER diagram has three main components
- Entity
- Attribute
- Relationship
Entity
An entity is an object or component of data. An entity is represented as rectangle in an ER diagram.
For example: In the following ER diagram we have two entities Student and College and these two entities have many to one relationship as many students study in a single college. We will read more about relationships later, for now focus on entities.
Weak Entity
An entity that cannot be uniquely identified by its own attributes and relies on the relationship with other entity is called weak entity. The weak entity is represented by a double rectangle.
For example – a bank account cannot be uniquely identified without knowing the bank to which the account belongs, so bank account is a weak entity.
Attribute
An attribute describes the property of an entity. An attribute is represented as Oval in an ER diagram.
There are four types of attributes
- Key attribute
- Composite attribute
- Multivalued attribute
- Derived attribute
Key attribute
A key attribute can uniquely identify an entity from an entity set. For example, student roll number can uniquely identify a student from a set of students. Key attribute is represented by oval same as other attributes however the text of key attribute is underlined.
Composite attribute
An attribute that is a combination of other attributes is known as composite attribute. For example, In student entity, the student address is a composite attribute as an address is composed of other attributes such as pin code, state, country.
Multivalued attribute
An attribute that can hold multiple values is known as multivalued attribute. It is represented with double ovals in an ER Diagram. For example – A person can have more than one phone numbers so the phone number attribute is multivalued.
Derived attribute
A derived attribute is one whose value is dynamic and derived from another attribute. It is represented by dashed oval in an ER Diagram. For example – Person age is a derived attribute as it changes over time and can be derived from another attribute (Date of birth).
E-R diagram with multivalued and derived attributes.
Relationship
A relationship is represented by diamond shape in ER diagram, it shows the relationship among entities.
There are four types of relationships
- One to One
- One to Many
- Many to One
- Many to Many
One to One Relationship
When a single instance of an entity is associated with a single instance of another entity then it is called one to one relationship. For example, a person has only one passport and a passport is given to one person.
One to Many Relationship
When a single instance of an entity is associated with more than one instances of another entity then it is called one to many relationship. For example – a customer can place many orders but a order cannot be placed by many customers.
Many to One Relationship
When more than one instances of an entity is associated with a single instance of another entity then it is called many to one relationship. For example – many students can study in a single college but a student cannot study in many colleges at the same time.
Many to Many Relationship
When more than one instances of an entity is associated with more than one instances of another entity then it is called many to many relationship. For example, a can be assigned to many projects and a project can be assigned to many students.
Total Participation of an Entity set
A Total participation of an entity set represents that each entity in entity set must have at least one relationship in a relationship set. For example: In the below diagram each college must have at-least one associated Student.
Extended feature of ER Diagram
The basic E-R concept can model most of database features.
But same aspect of database can be extended features.
There are 3 extended features
- Specialization.
- Generalization
- Aggregation.
Specialization
It is the abstracting process of introducing new characteristics of objects to create one
or more new classes of objects.
In simple words, specialization is a result of taking a subset of higher level entity set to form a lower level entity set, [Fig. 2.34).
Entities are expanded in Specialization.
This line implies that Doctor can also be anything else apart from permanent doctor and Consulting doctor.
Generalization
It is abstracting process of viewing set of objects as a single general class.
It concentrates on the general characteristics of consequent set while ignoring there differences.
It means generalization is the result of taking union of two or more entity sets to produce a higher level entity set.
This line implies that a doctor has to be either a permanent doctor,or consulting doctor nothing else.
Therefore the entities are limited in generalization.
Difference between Generalization and specialization
Generalization
It is bottom-top design process.
It is union of two or more lower level entity sets to produce higher level entity set.
In this, lower level entity sets are also described by attributes and relationship of higher level entity sets.
It is used to emphasize similarities among lower level entity types
In this, every higher level entity must
Specialization
It is top-down design process.
It is subset of higher level entity set to form lower level entity set.
Lower level entity sets may have different attributes and may participate in relationship that do not apply higher level entity set.
It is used to emphasize distinction between higher level and lower level entity set.
It allows for the possibility Of more low also be a lower level entity.
Aggregation
It is the process of combining information on objects so that the higher level objects can be abstracted.
In any entity relationship diagram, we cannot form a relationship among the entities and their relationship.
Aggregation is the process which allows to do so.
Case studies
Let's consider "General Hospital" where the operations are as follows:
(a) In the hospital, many doctors are working. Their personal information is maintained because they get fixed salary per month.
(b) The patients are admitted to the hospital into the room. They are treated by various doctors.
(c) Sometimes, patients require certain pathological tests which are actually carried out into the labs.
Star is an agency for flat booking & it has number of builders and agents who are jointly working.A customer can get a flat for residential or commercial purpose.
If a customer is apporoched through an agent the agency and builders are giving some commision to agent.Agent shows various sites and flats within various location.Study the case and draw ER Diagram.
In a nursery plants are sold to the customers.These plants are flowering and non flowering only.Nutrients are given to the plant with some quqntity.Nutrients contains Pestisides,Watering and Manure.
A Movie studio wishes to institute a database to manage their files of movies actor and directors.the following facts are relevant.
Each actor has appeared in many movies.
Each director has directed many movies.
Each movie has one director or ona or more actors.
Each actor and directory may have several addresses.
Construct an ER for car insurance company that has set of customers.
Each customer owns one or more cars.
Each are associated with more cars.
Each can be associated with zeo to any number of recorded accidents.
In airport airline reservation is done.Airoplanes are owned by airline which have flight system.Passenger visits employee
and ask for reservation.Employee checks flight system and provide reservation to the passenger.
Draw ER Diagram.
The management of Life Hospital has decided to computerize their operations.The following information is provided by management.These are resident,fulltime and consulting doctors,with various specialization.Consulting doctors visit hospital at a fixed time everyday or some days of week which varies from doctor to doctor .Patients are admitted to hospital and their main cause of admission is recorded.For accident cases additional information such as police booklet number,name of police and accident description is recorded.A patient is admitted to a room which has certain category having fixed change per day.
The given case study is of Life Hospital which has decided to computerize their operations
Consider a trucking company which is responsible for picking up shipments for warehouse of a retail chain and deliver the shipments to the individual store loaction.
A truck may carry several shipments in a single trip and deliver it to multiple stores. Draw an ER diagram for above case.
Comments
Post a Comment
Please give us feedback through comments