Module 2

Database
Management
Systems
MODULE – 2
ENTITY RELATIONSHIP MODEL
Introduction
 Conceptual modeling is a very important phase in designing a successful database application.
Generally, the term database application refers to a particular database and the associated programs that
implement the database queries and updates.
For example, a BANK database application that keeps track of customer accounts would include programs
that implement database updates corresponding to customer deposits and withdrawals.
User-friendly graphical user interfaces (GUIs) utilizing forms and menus are used by the end users of the
bank application. In addition, BANK customers can use mobile devices using mobile apps.
 The popular conceptual database model includes E-R and the diagrammatic notation associated with the
ER model, known as ER diagrams.
Conceptual Database using high level conceptual
data models for Database Design.
Conceptual Database using high level
conceptual data models for Database Design.
 Figure 3.1 shows a simplified overview of the database design process. The first step shown is
requirements collection and analysis. During this step, the database designers interview prospective
database users to understand and document their data requirements.
In parallel with specifying the data requirements, it is useful to specify the known functional
requirements of the application. These consist of the user defined operations (or transactions) that will
be applied to the database, including both retrievals and updates.
Once the requirements have been collected and analyzed, the next step is to create a conceptual
schema for the database, using a high-level conceptual data model.
This step is called conceptual design. The conceptual schema includes detailed descriptions of the
entity, types, relationships, and constraints.
Because these concepts do not include implementation details, they are usually easier to understand
and can be used to communicate with nontechnical users.
Conceptual Database using high level
conceptual data models for Database Design.
The next step in database design is the actual implementation of the database, using a commercial
DBMS.
Most current commercial DBMSs use an implementation data model—such as the relational (SQL)
model—so the conceptual schema is transformed from the high-level data model into the
implementation data model. This step is called logical design or data model mapping.
The last step is the physical design phase, during which the internal storage structures, file
organizations, indexes, access paths for the database files are specified.
 In parallel with these activities, application programs are designed and implemented as database
transactions corresponding to the high-level transaction specifications.
A Sample Database Application
 In this section there is a description of sample database application, called COMPANY, which serves to
illustrate the basic ER model concepts and their use in schema design.
The COMPANY database keeps track of a company’s employees, departments, and projects. The
company is organized into departments. Each department has a unique name, a unique number, and a
particular employee who manages the department and there should be a track of the start date when
that employee began managing the department.
A department may have several locations. A department controls a number of projects, each of which
has a unique name, a unique number, and a single location.
The database will store each employee’s name, Social Security number, address, salary, gender and
birth date. An employee is assigned to one department, but may work on several projects, which are not
necessarily controlled by the same department.
A Sample Database Application
 In this section we describe a sample database application, called COMPANY, which serves to illustrate
the basic ER model concepts and their use in schema design.
It is required to keep track of the current number of hours per week that an employee works on each
project, as well as the direct supervisor of each employee (who is another employee).
A department controls a number of projects, each of which has a unique name, a unique number, and a
single location. The database will store each employee’s name, Social Security number, address, salary,
gender, and birth date.
An employee is assigned to one department, but may work on several projects, which are not
necessarily controlled by the same department. It is required to keep track of the current number of
hours per week that an employee works on each project, as well as the direct supervisor of each
employee (who is another employee).
The database will keep track of the dependents of each employee for insurance purposes, including
each dependent’s first name, gender, birth date, and relationship to the employee
Entity Types, Entity Sets, Attributes, and
Keys
Entities and Attributes : The basic concept that the ER model represents is an entity, which is a thing or
object in the real world with an independent existence.
An entity may be an object with a physical existence (for example, a particular person, car, house, or
employee) or it may be an object with a conceptual existence (for instance, a company, a job, or a
university course).
 Each entity has attributes—the particular properties that describe it. For example, an EMPLOYEE entity
may be described by the employee’s name, age, address, salary, and job.
A particular entity will have a value for each of its attributes. The attribute values that describe each
entity become a major part of the data stored in the database.
Keys
 The below figure shows two entities and the values of their attributes. The EMPLOYEE entity e1 has
four attributes: Name, Address, Age, and Home_phone; their values are ‘John Smith,’ ‘2311 Kirby,
Houston, Texas 77001’, ‘55’, and ‘713-749-2630’, respectively.
The COMPANY entity c1 has three attributes: Name, Headquarters, and President; their values are
‘Sunco Oil’, ‘Houston’, and ‘John Smith’, respectively.
Keys
Attribute Types
 Several types of attributes occur in the ER model:
i) Simple versus composite
ii) Single valued versus multivalued
iii) Stored versus derived
iv) Giving NULL Values to the attribute.
v) Complex Attributes
Keys
i) Composite versus Simple (Atomic) Attributes.
 Composite attributes can be divided into smaller subparts, which represent more basic attributes with
independent meanings.
For example, the Address attribute of the EMPLOYEE entity shown can be subdivided into
Street_address, City, State, and Zip with the values ‘2311 Kirby’, ‘Houston’, ‘Texas’, and ‘77001’.
Attributes that are not divisible are called simple or atomic attributes. Composite attributes can form a
hierarchy; for example, Street_address can be further subdivided into three simple component
attributes: Number, Street, and Apartment_number.
Keys
ii) Single valued versus multivalued Attributes.
 Most attributes have a single value for a particular entity; such attributes are called single-valued. For
example, Age is a single-valued attribute of a person.
In some cases an attribute can have a set of values for the same entity—for instance, Similarly, one
person may not have any college degrees, another person may have one, and a third person may have
two or more degrees;
 therefore, different people can have different numbers of values for the College_degrees attribute.
Such attributes are called multivalued.
Keys
iii) Stored versus Derived Attributes.
 In some cases, two (or more) attribute values are related—for example, the Age and Birth_date
attributes of a person.
For a particular person entity, the value of Age can be determined from the current (today’s) date and
the value of that person’s Birth_date. The Age attribute is hence called a derived attribute and is said to
be derivable from the Birth_date attribute, which is called a stored attribute.
iv) NULL Values.
 In some cases, a particular entity may not have an applicable value for an attribute. For example, the
Apartment_number attribute of an address applies only to addresses that are in apartment buildings and
not to other types of residences, such as single-family homes. For such situations, a special value called
NULL is created.
Keys
V) Complex Attributes.
• Has multivalued & composite components in it.
• Multivalued attributes are represented within '{}'.
• Composite attributes → represented within '( )'.
Example :
Keys
Entity Types and Entity Sets
Entity Type:
• A collection of entities that have the same attributes. An entity type defines a collection (or set) of
entities that have the same attributes.
Entity Set:
• The collection of all entities of a particular entity type in the database at any point in time is called an
entity set or entity collection.
Each entity type in the database is described by its name and attributes. Figure 3.6 shows two entity
types: EMPLOYEE and COMPANY, and a list of some of the attributes for each.
Keys
Keys
Key Attributes of an Entity Type
 The attribute that is capable of identifying each entity uniquely. Such an attribute is called a key
attribute.
Ex: Roll Number of a Student, PERSON entity type, a typical key attribute is Ssn (Social Security number).
In ER diagrammatic notation, each key attribute has its name underlined inside the oval, as illustrated in
Figure 3.7(a).
The Registration attribute is an example of a composite key formed from two simple component
attributes, State and Number, neither of which is a key on its own.
Keys
Keys
Value Set of Attributes
 The set of values that can be assigned to a attribute.
Ex: If the range allowed for age attribute is between 20 and 60 , then only the values between the values
20 and 60 can only be entered. The values less than 20 or more than 60 cannot be entered in to the table.
Keys
Initial Conceptual Design of the COMPANY Database - We can now define the entity types for the
COMPANY database, based on the requirements
According to the requirements , we can identify four entity types
 1. An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and
Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and
Number are (separate) key attributes because each was specified to be unique.
2. An entity type PROJECT with attributes Name, Number, Location, and Controlling_department. Both
Name and Number are (separate) key attributes.
3. An entity type EMPLOYEE with attributes Name, Ssn, Gender, Address, Salary, Birth_date,
Department, and Supervisor. Both Name and Address may be composite attributes .
4. An entity type DEPENDENT with attributes Employee, Dependent_name, Gender, Birth_date, and
Relationship (to the employee)
Keys
Relationship Types, Relationship Sets,
Roles, and Structural Constraints
 Relationship Types, Sets, and Instances
Relationship is nothing but the association between the two entities. For example, consider a relationship
type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which associates each
employee with the department for which the employee works.
role
(Employer)
Relationship Type
Degree of two - binary
Relationship Set
 Relationship Degree
Degree of a Relationship Type - The degree of a relationship type is the number of participating entity
types. Hence, the WORKS_FOR relationship is of degree two. A relationship type of degree two is called
binary, and one of degree three is called ternary.
An example of a ternary relationship is SUPPLY, shown in Figure
Degree of three - ternary

 Role Names and Recursive Relationships
Each entity type that participates in a relationship type plays a particular role in the relationship. The role
name signifies the role that a participating entity from the entity type plays in each relationship instance,
and it helps to explain what the relationship means.
For example, in the WORKS_FOR relationship type, EMPLOYEE plays the role of employee or worker and
DEPARTMENT plays the role of employer.
Recursive Relationships - A relationship between two entities of a similar entity type is called
a recursive relationship. The entities of a particular entity type participate in the relationship within
themselves which we call it as recursive relationship.
In the figure 3.11, 1 signifies the supervisor role and 2 signifies the supervisee or subordinate role. A
particular employee can act both as a supervisor and also supervisee.
Supervisee or Subordinate Supervisor

 Structural Constraints –Structural Constraints on Binary Relationship Types
The two main types of binary relationship constraints:
 Cardinality Ratio
 Participation Constraint
 The cardinality ratio for a binary relationship specifies the maximum number of relationship instances
that an entity can participate in.
For example, in the WORKS_FOR binary relationship type, DEPARTMENT:EMPLOYEE is of cardinality
ratio 1:N, meaning that each department can be related to any number of employees (N), but an
employee can be related to at most one department .
This means that for this particular relationship type WORKS_FOR, a particular department entity can be
related to any number of employees. On the other hand, an employee can be related to a maximum of
one department.
The possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1, and M:N.
 An example of a 1:1 binary relationship is MANAGES (Figure 3.12), which relates a department entity
to the employee who manages that department.
 The relationship type WORKS_ON (Figure 3.13) is of cardinality ratio M:N, because an employee can
work on several projects and a project can have several employees.
 The relationship type works_on is of cardinality ratio 1: M because one employee can work for only
one department but one department can contain many employees.
 Participation Constraint : refers to the minimum number of relationships an entity can participate. This
constraint specifies the minimum number of relationship instances that each entity can participate in
and is sometimes called the minimum cardinality constraint.
There are two types of participation constraints :
Total Participation
 Partial Participation
1) Total Participation - If a company policy states that every employee must work for a department, then
an employee entity can exist only if it participates in at least one WORKS_FOR relationship instance
(Figure 3.9).
Thus, the participation of EMPLOYEE in WORKS_FOR is called total participation, meaning that every
entity in the total set of employee entities must be related to a department entity via WORKS_FOR. Total
participation is also called existence dependency.
Total participation from

department as all the
departments are
participating in the
relationship with
Total participation from employees
employees as all the
employees are
participating in the
relationship with the
department
2) Partial Participation - In Figure 3.12 we do not expect every employee to manage a department, so the
participation of EMPLOYEE in the MANAGES relationship type is partial, meaning that some or part of the
set of employee entities are related to some department entity via MANAGES, but not necessarily all.
Not all employees are

participating in a
relationship with the
department. This is
Partial Participation
 We will refer to the cardinality ratio and participation constraints, taken together, as the
structural constraints of a relationship type
Weak Entity Types
Entity types that do not have key attributes of their own are called weak entity types. In contrast,
regular entity types that do have a key attribute are called strong entity types.
For every weak entity there will be an identifying entity type. For example refer the E- R diagram
where the dependents entity which is the weak entity type is identified by the employee entity type. So
here the identifying entity type is employee entity type.
A weak entity type always has a total participation constraint (existence dependency) with respect to its
identifying relationship because a weak entity cannot be identified without an owner entity.
A weak entity type normally has a partial key, which is the attribute that can uniquely identify weak
entities that are related to the same owner entity
Weak Entity Types - Example
ER Diagrams, Naming Conventions, and
Design Issues
 Figure 3.2 displays the COMPANY ER database schema as an ER diagram. Regular (strong) entity types
such as EMPLOYEE, DEPARTMENT, and PROJECT are shown in rectangular boxes.
 Relationship types such as WORKS_FOR, MANAGES, CONTROLS, and WORKS_ON are shown in diamond-
shaped boxes attached to the participating entity types with straight lines.
Attributes are shown in ovals, and each attribute is attached by a straight line to its entity type or
relationship type.
Component attributes of a composite attribute are attached to the oval representing the composite
attribute, as illustrated by the Name attribute of EMPLOYEE.
 Multivalued attributes are shown in double ovals, as illustrated by the Locations attribute of
DEPARTMENT. Key attributes have their names underlined.
Derived attributes are shown in dotted ovals, as illustrated by the Number_of_employees attribute of
DEPARTMENT.
Design Issues
Weak entity types are distinguished by being placed in double rectangles and by having their identifying
relationship placed in double diamonds, as illustrated by the DEPENDENT entity type and the
DEPENDENTS_OF identifying relationship type.
The partial key of the weak entity type is underlined with a dotted line.
 The cardinality ratio of DEPARTMENT:EMPLOYEE in MANAGES is 1:1, whereas it is 1:N for
DEPARTMENT: EMPLOYEE in WORKS_FOR, and M:N for WORKS_ON.
The participation constraint is specified by a single line for partial participation and by double lines for
total participation (existence dependency).
In Figure 3.2 we show the role names for the SUPERVISION relationship type because the same
EMPLOYEE entity type plays two distinct roles in that relationship. Notice that the cardinality ratio is 1:N
from supervisor to supervisee because each employee in the role of supervisee has at most one direct
supervisor, whereas an employee in the role of supervisor can supervise zero or more employees.
Design Issues
Design Issues
Design Choices for ER Conceptual Design
Design Issues
 Alternative Notations for ER Diagrams:
In this section, we describe one alternative ER notation for specifying structural constraints on
relationships, which replaces the cardinality ratio (1:1, 1:N, M:N).
This notation involves associating a pair of integer numbers (min, max) with each participation of an
entity type E in a relationship type R.
In this method, min = 0 implies partial participation, whereas min > 0 implies total participation. Figure
3.15 displays the COMPANY database schema using the (min, max) notation.
Design Issues
Thankyou

Module 2

Uploaded by

Copyright:

Available Formats

Module 2

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Module 2

Uploaded by

Copyright:

Available Formats

Database

 The set of values that can be assigned to a attribute.

Degree of three - ternary

Supervisee or Subordinate Supervisor

Total participation from

Not all employees are

You might also like