Chapter_2

Database system concepts and architecture

⚫ Learning Objectives:
– Discuss the three categories of Data Models.
– Define the concepts of database schema & instances, and Database
State.
– Modeling Data in the Organization:
⚫ Business Rules.
⚫ Entity-Relationship Model (E-R Model)
⚫ (E-R Model) Constructs:
– Entities.
– Attributes (Classifications of attributes).
– Relationships (Degree, Cardinality , & cardinality constraints)
⚫ Entity Relationship Diagram (ERD) Design steps.
⚫ Relational Database Model (RDM) design steps.
⚫ Normalization process.
2.2 Modeling Data in the Organization
Data modeling is the most important part of the systems
development process for t he following reasons:
The characteristics of data captured during data modeling are
crucial in the design of databases, programs, and other system
components.
The facts and rules captured during the process of data
modeling are essential in assuring data integrity in an
information system.

Data modeling may still seem like a costly and unnecessary

activity that simply delays getting to "the real work”. However :
It facilitates interaction/communication between designer,
application programmer, and end user,
thus reducing any misunderstandings by providing a
simplified (visual) understanding of data (data model)
with supporting documentation (metadata).
 It can foster understanding of the organization (rules/policies) for
which the data model is being developed;
consistency and completeness of rules can be verified; otherwise,
it is possible to create systems that are incorrect or inconsistent
and unable to accommodate changes in user requirements (such
as processing certain transactions or producing specific reports).
Documenting rules and policies of an organization that govern data
is exactly what data modeling is all about.

2.2.1 Business Rules. 6

Business rules, the foundation of data models, are:

derived from policies, procedures, events, functions, and other
business objects, and
they state constraints on the organization.
Business rules represent the language and fundamental structure of an
organization.
They formalize the understanding of (1) the organization by organization
owners, managers, and leaders with that of (2) information systems
architects.
Business rules are important in data modeling because they govern how
data are handled and stored.
Examples of basic business rules are data names and definitions.
This chapter explains guidelines you can follow for the clear naming and
definition of data objects in a business.
In terms of conceptual data modeling, you must provide names and
definitions for the main data objects:
1. entity types (e.g., Customer),
2. attributes (e.g., Customer Name), and
3. relationships (e.g., Customer Places Orders).

Other business rules may state constraints on these data objects.

These constraints can be captured in a data model, such as an E-R diagram,
and associated documentation.
They govern the people, places, events, processes, networks, and
objectives of the organization.
For example, a policy that “every student in the university must have a faculty
advisor” forces data (in database) about each student to be associated with data
about some student advisor.
Your job as a database Specialist is to:
1. Identify and understand those rules that govern data.
2. Represent those rules so that they can be unambiguously understood by
information systems developers and users.
3. Implement those rules in database technology.
The following are examples of business rules of a certain company:
• The company is divided into some departments; each department has a
number and a single name that does not recur.
• For each department , There is a special employee to run. It is important
to know the start- date to run this department.
• The department may have more than one location.
• We must keep the employee data that include his number (non-
recurred), his name, gender, address, telephone and his monthly salary.
• The employee has to work in just one department and it is important to
know the number of the employees in each department.
• The employee should work in a single project at least which has just
one non-recurred number.
• It is very important to know the number of the weekly hours that each
employee spends in each project.
 2.2.2 Entity-Relationship Model
(E-R Model)
An Entity-Relationship Model is a data model for high level
description of conceptual data model and it provides a graphical
notation for representing such data model.
It is one of the common styles to set an outlay for the Relational
Database that depends on the following:
• Dividing the system to Entities such as (Employee Entity –
Department Entity in a company's system).
• Each Entity contains certain Attributes describing and defining it
(such as the Employee’s name, address) etc.
• Defining the relationships among these entities and describing
them .
• Represent this through a drawing, which is called:
(Entity Relationship Diagram) ERD
 2.2.3 Entity-Relationship Model
Constructs
The basic constructs of the entity relationship model are entities,
relationships, and attributes.
Before discussing each of these constructs, we should know what
are their notations in the E-R Diagram.

A special entity
Entity that is also a
symbols relationship

Relationship Attribute
symbols symbols
1. Entities
An Entity is a person, place, object, event, or concept in the user environment
about which the organization wishes to maintain data.
Some examples of each of these types of entities are:
Person : ➔ EMPLOYEE, STUDENT, PATIENT
Place : ➔ STORE, WAREHOUSE, STATE
Object : ➔ MACHINE, BUILDING, AUTOMOBILE
Event : ➔ SALE, REGISTRATION, RENEWAL
Concept : ➔ ACCOUNT, COURSE, WORK CENTER

What Should an Entity Be?

SHOULD BE:
• An object that will have many instances in the database.
• An object that will be composed of multiple attributes.
• An object that we are trying to model.
SHOULD NOT BE:
• A user of the database system.
• An output of the database system (e.g. a report).
 There is an important distinction between entity type and entity
instance:
Entity type: is a collection of entities that share common properties
or characteristics.
Each entity type in an E-R Model is given a name.
Because the name represents a collection (or set) of items, it is
always singular.
In an E-R diagram, the entity name is placed inside the box
representing the entity type.
Entity Instance: is a single occurrence of an entity type.
While an entity type is described just once (using metadata) in
a database, many instances of that entity type may be
represented by data stored in the database.
For example, there is one EMPLOYEE entity type in most
organizations, but there may be hundreds (or even thousands)
of instances of this entity type stored in the database.
Figure 2-3 Entity type EMPLOYEE with two instances
 2. Attributes
Each entity type has a set of attributes associated with it.
An attribute is a property or characteristic of an entity type that is of
interest to the organization. It has a noun name.
Some types of relationships may also have attributes.
Following are some typical entity types and their associated attributes:

Classifications of attributes
1. Required versus Optional Attribute.
2. Simple versus Composite Attribute
3. Single-Valued versus Multivalued Attribute
4. Stored versus Derived Attributes
5. Identifier Attributes
 (1) Required versus Optional Attribute.
Required attribute : An attribute that must have a value for every entity
(or relationship) instance with which it is associated.
Optional attribute : An attribute that may not have a value for every entity
(or relationship) instance with which it is associated.

Figure 2-4 Entity type STUDENT with required and optional attributes
 (2) Simple versus Composite Attribute.
Composite attribute : An attribute that has meaningful component parts
(attributes). E.g. Employee Address, Employee Name.
Simple (atomic) attribute : An attribute that cannot be broken down into
smaller components that are meaningful to the organization.

An attribute
broken into
Figure 2-5 A composite attribute component parts
 (3) Single-Valued versus Multivalued Attribute.
Single-Valued attribute : An attribute that has only one value for a given
entity (or relationship) instance...
Multivalued attribute : An attribute that may take on more than one value
for a given entity (or relationship) instance..

Figure 2-6 An Entity with a multivalued attribute

Multivalued:
an employee can have
more than one skill
 (4) Stored versus Derived Attribute.
Stored attribute : a normal attribute (already exist) which has a value as an
attribute of an entity type.
Derived attribute : An attribute whose value can be calculated from related
attribute values.

Figure 2-7
An Entity with a multivalued
and derived attribute

Derived: Multivalued:
From date employed and an employee can have
current date more than one skill
 (5) Identifier Attribute.
Identifier attribute : An attribute (or combination of attributes) whose value
distinguishes instances of an entity type. It can be:
A simple key identifier : or
A composite identifier :an identifier that consists of a composite attribute.

Figure 2-8
Simple key attribute
The key is
underlined

Some entities may have more than one candidate identifier; which is an
attribute that could be an identifier and satisfies the requirements for being an
identifier. So, the designer must choose one of them as the identifier.
 Figure 2-9
The key is composed
Composite key attribute of two subparts.

Criteria of Selecting Identifiers

Choose an identifier that will not change its value over the life of each instance
of the entity type. E.g. Department _Name
Choose an identifier such that for each instance of the entity, the attribute is
guaranteed to have valid values and not be null (or unknown).
If the identifier is a composite attribute, such as Flight ID, make sure that
all parts of the identifier will have valid values.
Avoid the use of so-called intelligent identifiers, whose structure indicates
classifications or locations, that may change.
Substitute simple keys for long composite keys.
End of Lecture_4