UNIVERSITY OF KERBALA – COLLEGE OF CSIT

DATABASE
DESIGN
2ND CLASS
SEMESTER I 24/25

Lecture 8

ASST. LEC. WAEL H. ALI COMPUTER SCIENCE DEPARTMENT

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

Lecture 8: RELATIONAL DATABASE DESIGN BY ENTITY-

RELATIONSHIP (ER) MODEL

1. 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. Hence, a major part of the database application will require the design,
implementation, and testing of these application programs. Entity-Relationship (ER) model is a popular
high-level conceptual data model. This model and its variations are frequently used for the conceptual
design of database applications, and many database design tools employ its concepts. Also, object
modeling methodologies such as the Unified Modeling Language (UML) are becoming increasingly
popular in both database and software design. An important part of these methodologies namely, class
diagrams are similar in many ways to the ER diagrams. In class diagrams, operations on objects are
specified, in addition to specifying the database schema structure.

2. A SAMPLE DATABASE APPLICATION

The COMPANY database keeps track of a company’s employees, departments, and projects. Suppose
that after the requirements collection and analysis phase, the database designers provide the following
description of the mini-world the part of the company that will be represented in the database.
 The company is organized into departments. Each department has a unique name, a unique number,
and a particular employee who manages the department. We keep 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.

2024 – 2025 1|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

 We store each employee’s name, Social Security number,2 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. We keep track of the current number of hours per week that an
employee works on each project. We also keep track of the direct supervisor of each employee (who
is another employee).
 We want to keep track of the dependents of each employee for insurance purposes. We keep each
dependent’s first name, gender, birth date, and relationship to the employee.

Figure 1: Company Database Schema

2024 – 2025 2|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

3. ENTITIES, ATTRIBUTES AND RELATIONSHIP

The ER model describes data as entities, relationships, and attributes. we introduce the concepts of entities
and their attributes. Then, we specify the initial conceptual design of the entity types for the COMPANY
database relationships.

3.1 ENTITIES AND ATTRIBUTES

The basic object that the ER model represents is an entity, which is a thing 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, 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. 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. Several types of attributes occur in the ER model: simple
versus composite, single-valued versus multivalued, and stored versus derived.

 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 can be subdivided into Street_address, City, State, and Zip.
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.
 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, a Colors
attribute for a car. Cars with one color have a single value, whereas two-tone cars have two color
values. Such attributes are called multivalued.

2024 – 2025 3|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

Figure 2: Sample of Composed Attributes.

 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.
 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
situation, a special value called NULL is created. NULL can also be used if we do not know the
value of an attribute for a particular entity—for example, if we do not know the home phone
number of ‘John Smith’.
 Complex Attributes: composite and multivalued attributes can be nested arbitrarily. We can
represent arbitrary nesting by grouping components of a composite attribute between parentheses
() and separating the components with commas, and by displaying multivalued attributes between
braces { }. Such attributes are called complex attributes. For example, if a person can have more
than one residence and each residence can have a single address and multiple phones.

2024 – 2025 4|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

3.2 RELATIONSHIP

In Figure 1 there are several implicit relationships among the various entity types. In fact, whenever an
attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute
Manager of DEPARTMENT refers to an employee who manages the department; the attribute
Controlling_department of PROJECT refers to the department that controls the project; the attribute
Supervisor of EMPLOYEE refers to another employee (the one who supervises this employee); the
attribute Department of EMPLOYEE refers to the department for which the employee works; and so on.
In the ER model, these references should not be represented as attributes but as relationships. In the initial
design of entity types, relationships are typically captured in the form of attributes. These attributes get
converted into relationships between entity types.

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 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 department or employer.

Role names are not technically necessary in relationship types where all the participating entity types are
distinct, since each participating entity type name can be used as the role name. However, in some cases
the same entity type participates more than once in a relationship type in different roles. In such cases the
role name becomes essential for distinguishing the meaning of the role that each participating entity plays.
Such relationship types are called recursive relationships. Figure 1 shows an example. The
SUPERVISION relationship type relates an employee to a supervisor, where both employee and
supervisor entities are members of the same EMPLOYEE entity set. Hence, the EMPLOYEE entity type
participates twice in SUPERVISION: once in the role of supervisor (or boss), and once in the role of
supervisee (or subordinate).

2024 – 2025 5|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

Cardinality Ratios for Binary Relationships. 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 (that is, employs) any number of employees,9 but an
employee can be related to (work for) only one department. This means that for this particular relationship
WORKS_FOR, a particular department entity can be related to any number of employees (N indicates
there is no maximum number). 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.

Relationship types can also have attributes, similar to those of entity types. For example, to record the
number of hours per week that an employee works on a particular project, we can include an attribute
Hours for the WORKS_ON relationship type. Another example is to include the date on which a manager
started managing a department via an attribute Start_date for the MANAGES relationship type. Notice
that attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity types. For
example, the Start_date attribute for the MANAGES relationship can be an attribute of either EMPLOYEE
or DEPARTMENT, although conceptually it belongs to MANAGES. This is because MANAGES is a 1:1
relationship, so every department or employee entity participates in at most one relationship instance.

For a 1:N relationship type, a relationship attribute can be migrated only to the entity type on the N-side
of the relationship. For example, if the WORKS_FOR relationship also has an attribute Start_date that
indicates when an employee started working for a department, this attribute can be included as an attribute
of EMPLOYEE. This is because each employee works for only one department, and hence participates in
at most one relationship instance in WORKS_FOR. In both 1:1 and 1:N relationship types, the decision
where to place a relationship attribute—as a relationship type attribute or as an attribute of a participating
entity type—is determined subjectively by the schema designer.

2024 – 2025 6|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

For M:N relationship types, some attributes may be determined by the combination of participating entities
in a relationship instance, not by any single entity. Such attributes must be specified as relationship
attributes. An example is the Hours attribute of the M:N relationship WORKS_ON; the number of hours
per week an employee currently works on a project is determined by an employee-project combination
and not separately by either entity.

Participation Constraints and Existence Dependencies. The participation constraint specifies whether
the existence of an entity depends on its being related to another entity via the relationship type. 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 and partial. 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. 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. 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. In ER diagrams, total participation (or existence dependency) is displayed as a
double line connecting the participating entity type to the relationship, whereas partial participation is
represented by a single line. Notice that in this notation, we can either specify no minimum (partial
participation) or a minimum of one (total participation).

4. WEEK ENTITY TYPE

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—which include all the examples discussed so far—are
called strong entity types. Entities belonging to a weak entity type are identified by being related to
specific entities from another entity type in combination with one of their attribute values. We call this

2024 – 2025 7|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

other entity type the identifying or owner entity type, and we call the relationship type that relates a
weak entity type to its owner the identifying relationship of the weak entity type.

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. In our example, if we assume that no two dependents of
the same employee ever have the same first name, the attribute Name of DEPENDENT is the partial key.
In the worst case, a composite attribute of all the weak entity’s attributes will be the partial key. In ER
diagrams, both a weak entity type and its identifying relationship are distinguished by surrounding their
boxes and diamonds with double lines. The partial key attribute is underlined with a dashed or dotted line.

Weak entity types can sometimes be represented as complex (composite, multivalued) attributes. In the
preceding example, we could specify a multivalued attribute Dependents for EMPLOYEE, which is a
composite attribute with component attributes Name, Birth_date, Sex, and Relationship. The choice of
which representation to use is made by the database designer. One criterion that may be used is to choose
the weak entity type representation if there are many attributes. If the weak entity participates
independently in relationship types other than its identifying relationship type, then it should not be
modeled as a complex attribute.

2024 – 2025 8|Page

By : @CSFilesBot - @WajibCS
 University of Kerbala
College of CSIT – Computer Science Database I
Instructor: Asst. Lect. Wael H. Ali Lecture 8: ER Diagram

5. SUMMARY OF NOTATION FOR ER DIAGRAMS

2024 – 2025 9|Page

By : @CSFilesBot - @WajibCS