DBMS

Introduction
1.1 History of Database Systems

Storing and manipulating of data have been a major application focus in the earliest days
ofcomputers.

Integrated Data Store: The First general-purpose Database Management System (DBMS) was
designed by Charles Bachman at General Electric in the early 1960s.

CODASYL: Conference on Data Systems Languages.

Data representation framework

 The network data model

 The hierarchical data model

 The relational data model

ANSI: American National Standards Institute.

ISO: International Standards Organization

ERP: enterprise resource planning packages

MRP: management resource planning packages

Programs owning data come with many programming bottlenecks and the most outstanding
ones to are:-

i.Unhealthy dependence between data and programs

ii.Repetition of data elements

iii.Opportunities for inconsistencies

iv.Unorganized scattering ofrelated data across many files

v.Distributed ownership of data

vi.Decentralized security of data

vii.Unregulated interactions between programs using the same data

viii.Inadequate multiuser access

ix.Adhoc approach to error recovery

Note: The main purpose of database systems is to manage the data.

Database: is a collection of logically related data.

Data: means raw facts or raw data that can be recorded and that have implicit meaning. It is the
bedrock of knowledge.

A database can be of any size and of varying complexity.

A database of even greater size and complexity is maintained by the Federal Inland Revenue
Service (FIRS) to keep track of the tax forms filed by Nigerian taxpayers.

implicit properties of DB:

i.A database represents some aspect of the real world, sometimes called the miniworld

or the Universe of Discourse (UoD). Changes to the miniworld are reflected in the

database.

ii.A database is a logically coherent collection of data with some inherent meaning. A

random assortment of data cannot correctly be referred to as a database.

iii.A database is designed, built, and populated with data for a specific purpose. It has an

intended group of users and some preconceived applications in which these users are

interested.

Database Management Systems (DBMS): are software or program products that help in
defining, creating, maintaining and controlling access to a database. The DBMS is hence a
general purpose software system that facilitates the processes of defining, constructing, and
manipulating databases for various applications.

Note: A DBMS is a complex software system that consists of many components or modules,
including modules for implementing the catalog, query language, interface processors, data
access, concurrency control, recovery, and security.

Defining a database involves:

 specifying the data types

 structures

 constraints for the data to be stored in the database.

Constructing the database is the process of storing the data itself on some storage medium
that is controlled by the DBMS.
Manipulating a database includes such functions as querying the database to retrieve specific
data, updating the database to reflect changes in the miniworld, and generating reports from the
data.

Information: When data are processed, interpreted, organized, structured orpresented so as to

make them meaningful or useful.

Note: To reveal meaning, information requires context.

Data processing: can be as simple as organizing data to reveal patterns or as complex as

making forecasts or drawing inferences using statistical modeling.

The key to good decision making

In this “information age

 production of accurate

 relevant

 timely information.

Types of database

1. Number of users:

I. A single-user database: it supports only one user at a time. In other words, if user A
is using the database, users B and C must wait until user A is done.

a. Desktop database: A single-user database that runs on a personal computer.

II. A multiuser database: supports multiple users at the same time.

a. Workgroup database: When the multiuser database supports a relatively

small number of users (usually fewer than 50) or a specific department
within an organization.

b. Enterprise database: When the database is used by the entire organization

and supports many users (more than 50, usually hundreds) across many
departments.

2. Location:

I. Centralized database: a database that supports data located at a single site.

II. Distributed (decentralized) database: A database that supports data distributed across
several different sites.

3. A cloud database: is a database that is created and maintained using cloud data services,
such as Microsoft Azure or Amazon’s AWS.

infrastructure:

 hardware

 operating systems

 network technologies, etc.

4. Type of data stored in them: use in research environment.

I. General-purpose databases: contain a wide variety of data used in multiple disciplinesfor

example,a census database that contains general demographic data.

Examples:

 census database

 LexisNexis database

 ProQuest database

II. Discipline-specific databases: contain data focused on specific subject areas. The data in
this type of database are used mainly for academic or research purposes within a small
set of disciplines.
Examples:

 Medical database

 CompuStat database

 CRSP database

 Geographic information system (GIS) database.

5. How they will be used and on the time sensitivity of the information gathered from them:
The most popular way of classifying databases today.

I. Operational database: A database that is designed primarily to support a company’s day-

to-day operations.

Other names:

 Online transaction processing (OLTP)

 Transactional database

 Production database.

Examples:

 Product or service sales

 payments

 supply purchases reflect critical day

today operations.

II. Analytical database: focuses primarily on storing historical data and business metrics
used exclusively for tactical or strategic decision making. Such analysis typically requires
extensive “data massaging” (data manipulation) to produce information on which to base
pricing decisions, sales forecasts, market strategies, and so on.

Analytical databases allow the end user to perform advanced data analysis of business
data using sophisticated tools.

Two main Components of Analytical database

a. The data warehouse: is a specialized database that stores data in a format

optimized for decision support.
It contains historical obtain from:

 Operational databases

 External sources

b. Online analytical processing (OLAP): is a set of tools that work together to provide
an advanced data analysis environment for retrieving, processing, and modeling
data from the data warehouse.

Business intelligence: database application discipline.

It describes a comprehensive approach to capture and process business data with the purpose
of generating information to support business decision making.

6. Degree to which the data are structured.

I. Unstructured data: are data that exist in their original (raw) state—that is, in the format in
which they were collected. Therefore, unstructured data exist in a format that does not
lend itself to the processing that yields information.

II. Structured data: are the result of formatting unstructured data to facilitate storage, use,
and the generation of information.

III. Semi-structured data: have already been processed to some extent. Actually, most data
you encounter are best classified as semi-structured.

Conclusion: The database types mentioned thus far focus on the storage and management
of highly structured data. However, corporations are not limited to the use of structured data.
Unstructured and semi-structured data storage and management needs are being addressed
through a new generation of databases known as XML databases.

7. An XML database: supports the storage and management of semi-structured XML data.

Extensible Markup Language (XML) is a special language used to represent and manipulate
data elements in a textual format.

Social media refers to web and mobile technologies that enable “anywhere, anytime, always
on” human interactions.

8. NoSQL databases: are designed to handle the unprecedented volume of data, variety of data
types and structures, and velocity ofdata operations that are characteristic of these new
business requirements.

Note: The term NoSQL (Not only SQL) is generally used to describe a new generation of
database management systems that is not based on the traditional relational database model.
Other names:

 New generation database

 Specialized database

File processing approach

File processing approach: is a traditional approach to information system design focuses on the
data processing needs of a particular department or area within an organization.

It includes:

 Manual systems

 Computer based file systems

Examples of file processing approach:

 Accounts Receivable

 Payroll

 Inventory Control.

The file processingis being developed with third generation programming language where each
application or program is designed with its own set of data files. Traditional files often existing
environments where there is no overall plan to guide the growth of applications.

File Processing Approach has the following drawbacks:-

i.Data redundancy: refers to the duplication of data. Data redundancy often leads to higher
storage costs and poor access time.

ii.Data redundancy leads to data inconsistency: change of one state of a data while the other
duplicate is neglected. There is disagreement on the stored values

iii.Data Isolation: because data are scattered in various files, and files may be in different
formats, writing new application programs to retrieve the appropriate data is difficult.

iv.Dependency on application programs: Changing files would lead to change in application

programs.

v.Atomicity issues: Atomicity of a transaction refers to “All or nothing”, which means either all
the operations in a transaction executes or none. E.g transactions.It is difficult to achieve
atomicity in file processing systems.

vi.Data Security: data should be secured from unauthorized access.Such kind of security
constraints are difficult to apply in file processing systems.

1.8 Database Approach

The database approach: used database management system to provide facilities for querying,
data security and integrity, and allows simultaneous access to data by a number of different
users.

The database approach emphasizes the integration and sharing of data across the organization.

There are several advantages of Database management system over file system, which
include:

i.Minimal Data Redundancy: With the database approach, data files are integrated into a single,
logical structure. We are not suggesting all redundancy is controlled. It is designed into the
system to improve performances (or provide some other benefits), and the system is (or should
be) aware of redundancy.

ii.Consistency of Data: By controlling data redundancy, we greatly reduce the opportunities for
inconsistency. For example, if each address is stored only once, we cannot have disagreement
on the stored values. When controlled redundancy is permitted in the database, the database
system itself should enforce consistency by updating each occurrence of a data item when a
change occurs.

iii.Integration of Data: In a database, data are organized into a single, logical structure, with
logical relationships defined between associated data entities. This makes it easy for users to
relate one item of data to another.

iv.Sharing of Data: Most database systems today permit multiple users to share a database
concurrently, although certain restrictions are imposed such that each user would be able to
view a subset of the conceptual database model.

v.Ease of Application Development: it greatly reduces the cost and time for developing new
business applications as programmer is relieved from the burden of designing, building, and
maintaining master files.

Note: In a database system, data are independent of the application programs that use them.
Within limits, either data or the application programs that use the data can be changed without
necessitating a change.

1.9Costs of the Database Approach

i.New, Specialized Personnel: Frequently, organizations that adopt the database approach or
purchase a database management system (DBMS) need to hire train individuals to maintain the
new database software, develop and enforce new programming standards, design databases to
achieve the highest possible performance, and manage the staff of new people.

ii.Need for Explicit Backup: Minimal data redundancy, with all its associated benefits, may also
fail to provide backup copies of data. Such backup or independently produced copies are helpful
in restoring damaged files and in providing validity checks on crucial data.

iii.Interference with Shared Data: The concurrent access to shared data via several application
programs can lead to some problems:

a. First, when two concurrent users both want to change the same or related data,
inaccurate results can occur if access to the data is not properly synchronized.

b. Second, when data are used exclusively for updating, different users can obtain control of
different segments of the database and lock up any use of the data (so called deadlock).

iv.Organizational Conflict: A shared database requires a consensus on data definitions and

ownership as well as responsibilities for accurate data maintenance.

Experience has shown that conflicts on how to define data, data length and coding, rights to
update shared data, and associated issues are frequent and difficult managerial issues to
resolve.

Actors on the scene

For a small personal database, one person typically defines, constructs, and manipulates the
database. However, many persons are involved in the design, use, and maintenance of a large
database with a few hundred users.

Actors on the scene: The people whose jobs involve the day-to-day use of a large database.

Categories of actors on the scene:

i.Database Administrators:In any organization where many persons use the same

resources, there is a need for a chief administrator to oversee and manage these

resources.

Resources of database environment:

 The primary resource: is the database itself.

 The secondary resource: is the DBMS and related software.

Database administrator (DBA) is responsible for:

 Administering these resources.

 authorizing access to the database, for coordinating and monitoring its use, and for
acquiring software and hardware resources as needed.

 The DBA is accountable for problems such as breach of security or poor system
response time.

ii.Database Designers: Database designers are responsible for identifying the data to be stored
in the database and for choosing appropriate structures to represent and store this data. These
tasks are mostly undertaken before the database is actually implemented and populated with
data.

It is the responsibility of database designers to:

 communicate with all prospective database users, in order to understand their

requirements, and to come up with a design that meets these requirements. The final
database design must be capable of supporting the requirements of all user groups.

iii.End Users: End users are the people whose jobs require access to the database for

querying, updating, and generating reports; the database primarily exists for their use.

iv.System Analysts and Application Programmers (Software Engineers):

 System analysts: determine the requirements of end users, especially naive and
parametric end users, and develop specifications for canned transactions that meet
these requirements.

 Application programmers: implement these specifications as programs; then they test,

debug, document, and maintain these canned transactions.

Workers behind the scene

Workers behind the scene: those who work to maintain the database system environment, but
who are not actively interested in the database itself.

Note: In addition to those who design, use, and administer a database, others are associated
with the design, development, and operation of the DBMS software and system environment.

Categories of workers behind the scene:

i.DBMS system designers and implementers: are persons who design and implement the
DBMS modules and interfaces as a software package.

The DBMS must interface with other system software, such as the operating system and
compilers for various programming languages.

ii.Tool developers: include persons who design and implement tools, the software packages
that facilitate database system design and use, and help improve performance.

Note: Tools are optional packages that are often purchased separately. They include packages
for database design, performance monitoring, natural language or graphical interfaces,
prototyping, simulation, and test data generation.

iii.Operators and maintenance personnel: are the system administration personnel who are
responsible for the actual running and maintenance of the hardware and software environment
for the database system.

1.12 Components of the Database Environment

The database environment eight component which comparises:

1. System developers: who are resposible for the development of the database system.

2. Data administors: who are resposible for general administration and access control of
the database systems.

3. End users: who are just using fishinised database system for their operational activties
of their respective organisations or their personal business use.

4. The user interface component: is used to interface database users including system
developer, data administors and end users with DBMS, database, application programs
and even database repository.

5. Repository: Centralized knowledge base containing all data definitions, screen and
report formats and definitions of other organizations and system components.

1.13 When Not to Use a Database Management System

The overhead costs of using a DBMS are due to the following:

i.High initial investment in hardware, software, and training.

ii.Generality that a DBMS provides for defining and processing data.

iii.Overhead for providing security, concurrency control, recovery, and integrity functions.

it may be more desirable to use regular files under the following circumstances:

i.The database and applications are simple, well defined, and not expected to change.

ii.There are stringent real-time requirements for some programs that may not be met

because of DBMS overhead.

iii.Multiple-user access to data is not required.

2. DATABASE DEVELOPMENT PROCESS AND ARCHITECTURE

2.1 Overview of database terminologies

The primary function of a database: is to provide timely and reliable information that supports
the daily operations of an organization.

Problem space of the database: the business functions that the database is designed to
address.

A data model: is a conceptual description of the database. It does not include any reference to
the physical layout, or structure, of the database itself.

Database schema or simply the schema: is the physical layout of the database, which
describes how data are organized and stored in the database.

A schema: is in simple terms, the translation of a conceptual data model into a physical
representation that can be implemented using a computer.

Note: Practically speaking, the schema is still a conceptual rather than a physical construct
because it is simply a description of the database, including its tables and the relationsamong
them.

A database engine (database server):is a collection of computer programs that manipulate the
data contained in a database.

Examples:

 Microsoft Jet Database Engine and SQL Server.

 Oracle Database Server.

 IBM DB2 Universal Server.

It serves as the interface between the data in the database, as described by the schema and
application software tools that process the data.

Note: In database implementation projects, the development of the data model and the
database schema is part of a design process. The actual construction of the database starts
when the database administrator implements the schema by instructing the database engine to
prepare the physical space where the database will reside on the computer’s hard drive.

Database contains:

1. Data files.

2. Data dictionary: that describes the contents of the database.

3. A set of database integrity rules: that must be enforced in order to protect the data
maintained in the database.

4. Defined views: which are logical extractions of specific aspects of the physical database.

5. Stored procedures: which are blocks of Structured Query Language (SQL) code for
defining, managing and querying data in the database.
The term “database” does not include:

1. the user interface (UI) / (database front-end): by which users’ access and interact with
the database.

2. Application programs: that are used to process and analyze data in the database.

3. Software development tools: used to create these application programs.

4. Communications software tools (known as middleware): that support data transmission

and database processing over local area, wide area, or global communications networks.

Database associated components:

1. The database engine

2. User interfaces

3. application programs

4. Middleware

To describe database and it's associated components the terms:

 Database management system (DBMS)

 Database system: in today’s computing parlance, it is more popular than database

management system.

 Information system is commonly used.

Some authors, notably Date (2004) and Elmasri and Navathe (2003), expand the definition of
database systems to include people as an integral component.

The human component of a database system normally includes:

1. The database administrator: who is responsible for the implementation and

maintenance of the database system.

2. End users: who use the system for business decision making and operations.

3. Software developers: who design and construct software tools for the application of the
database system.

2.2 Database Development Process

Database application development: is the process of obtaining real-world requirements,

analyzing requirements, designing the data and functions of the system, and then implementing
the operations in the system.
The waterfall Software Development Life Cycle has been adopted as the basis for a model of
database development that incorporates three assumptions:-

i. We can separate the development of a database: that is, specification and creation of a
schema to define data in a database –from the user processes that make use of the database.

ii. We can use the three schema architecture as a basis for distinguishing the activities
associated with a schema.

iii. We can represent the constraints to enforce the semantics of the data once within a
database, rather than within every user process that uses the data.

The following are the stages of the database development process:-

1. Planning: The goal of the planning phase is to align information technology with the business
strategies of an organization.

At the end of planning phase: an enterprise model is developed.

Enterprise model consists of the following:-

I. Breaking the functions of an organization down into progressively lower levels of details.

II. Identifying the entity types and the relationship between them (a draft overall E-R).

2. Analysis: The purpose of this phase is to develop detailed specifications for the information
systems required to support the organization.

Analysis covers the following:

I. Study of the current business situation.

II. Determination of the new system requirements.

At the end of this phase: aconceptual data model (a detailed E-R data model) will be built.

This conceptual data model includes:

I. The relevant entities

II. Relationships

III. Attributes

IV. The business rules

V. Constraints that define how the data are used.

3. Design: The purpose of this stage is to transform the conceptual data model to an
implementation model that a particular DBMS can process with performance that is acceptable
to all users throughout the organization.

This database design includes:

 Logical database design.

 Physical database design.

4. Logical database design: is the process of mapping conceptual data model to structures that
are specific to the target DBMS.

For example: if the target environment is a relational DBMS, then the conceptual data models (E-
R data model) are mapped to normalized relations.

5. Physical database design: is the process of mapping the database structures from logical
design into physical storage structures such as files and tables. Indexes, access methodsare
also specified.

Objectives of physical design: are to provide adequate performances for user applications
interms of response times, throughout rates etc.

6. Implementation: Implementation involves a series of steps leading to operational information

systems.

It includes:

 Creating database definitions.

 Creating program code.

 Testing the systems.

 Developing operational procedures.

 Documentation

 Training

 Loading the databases.