Database Management

Dr. Md. Rakibul Hoque

University of Dhaka
 Normalization
 Normalization: Database Normalization is a technique of
organizing the data in the database. Normalization is a
systematic approach of decomposing tables to eliminate
data redundancy and undesirable characteristics like
Insertion, Update and Deletion Anamolies. It is a multi-step
process that puts data into tabular form by removing
duplicated data from the relation tables.
 Database normalization, or simply normalization, is the
process of organizing the columns (attributes) and tables
(relations) of a relational database to reduce data
redundancy and improve data integrity.
 Normalization
 Normalization is a process of organizing the data in database
to avoid data redundancy, insertion anomaly, update
anomaly & deletion anomaly.
 Through normalization process, the collection of data in a
single table is replaced, by the same data being distributed
over multiple tables with a specific relationship being setup
between the tables.
 Streamlining complex groupings of data to minimize redundant
data elements and awkward many-to-many relationships.
 Process of creating small stable data structure from complex
groups of data.
 Normalization

An Unnormalized Relation for Order

An unnormalized relation contains repeating groups. For

example, there can be many parts and suppliers for each
order. There is only a one-to-one correspondence between
Order_Number and Order_Date.
 Normalization

Normalized Tables Created from Order

After normalization, the original relation ORDER has been

broken down into four smaller relations. The relation ORDER
is left with only two attributes and the relation LINE_ITEM
has a combined, or concatenated, key consisting of
Order_Number and Part_Number.
 Data Warehouses
 A data warehouse is a large store of data
accumulated from a wide range of sources
within a company and used to guide
management decisions.
 A data warehouse is a collection of data drawn
from other databases used by the business.
 It is a database that stores current and
historical data of potential interest to decision
makers throughout the company.
 Data Warehouses

 Supports reporting and query tools

 Stores current and historical data
 Consolidates data for management
analysis and decision making
 Improved and easy accessibility to
information
 Ability to model and remodel the data
 DATABASE TRENDS

Components of a Data Warehouse

 Data Mart
 The data mart is a subset of the data warehouse and is
usually oriented to a specific business line or team.
Whereas data warehouses have an enterprise-wide depth,
the information in data marts pertains to a single
department.
 A data mart represents the specific data from a data
warehouse which a user needs.
 It is a subset of data warehouse in which a summarized or
highly focused portion of the organization’s data is placed
in a separate database for a specified function or group of
users.
 Data Mining

 Data mining (sometimes called data or knowledge

discovery) is the process of analyzing data from different
perspectives and summarizing it into useful information
that can be used to increase revenue, cuts costs, or both.
It is a process used by companies to turn raw data into
useful information.
 Data mining is the analysis of data for relationships that
have not previously been discovered. For example, the
sales records for a particular brand of tennis. It is the
technique of searching for patterns in the data.
Multidimensional Databases

 A multidimensional database presents

the data to the user in several
dimensions. A three dimensional
database might present the
information by
 Sales Region
 Season
 Product Line
Multidimensional Databases
 Database Users
Users are differentiated by the way they
expect to interact with the system. Four
different types:
1. Naive users – are unsophisticated users
who interact with the system by invoking one
of the permanent application programs that
have been written previously.
E.g. people accessing database over the web,
bank tellers, clerical staff
 Database Users
 2. Application programmers – are computer
professionals who write application programs.
Application programmers can choose from many
tools to develop user interface.
 3. Sophisticated users – interact with the system
without writing programs. Instead, they form their
requests in a database query language. Analysts
who submits queries to explore data in the
database.
 Database Users

 Engineers, scientists, analysts who

implement applications to meet their
requirements.
 e.g., analyst looking at sales data (OLAP
– Online analytical processing), data
mining – finds certain kinds of patterns in
data.
 Database Users

 4. Specialized users – are sophisticated users

who write specialized database applications that
do not fit into the traditional data processing
framework.

 e.g., computer-aided design systems, knowledge-

base and expert systems and environment-
modeling systems – uses complex data types.
 Database Users

 Stand alone users – maintain personal

databases using ready made packages.

 Casual End Users – occasionally

access, may need different information
each time. Use query language to specify
requests.
 Database designers

Database designers, who are similar to software

developers, specialize in managing, creating, and
analyzing data collected in databases. Database
designers interact with all potential users and develop
views of the database that meet the data and
processing requirements of these groups. The final
database must support the requirements of all user
groups. It is the responsibility of the database designers
to communicate with all prospective users to understand
their requirements and come up with a design that
meets these requirements.
 System Analysts and
Programmers

 Determine the end user requirements

(especially naive end users) and develop
specifications for canned transactions that
meet the requirements.
 Application programmers implement the
specifications as programs, then test and
debug the programs.
 Database Administrator
(DBA)
 Database Administrators: DBA is responsible for
authorizing access to the database, for coordinating
and monitoring its use, and acquiring software and
hardware resources as needed.
 Coordinates all the activities of the database system;
the database administrator has a good understanding of
the enterprise’s information resources and needs. DBA
has central control of both data and the programs that
access that data.
 Database Administrator
(DBA)
 The functions of Database administrator
(DBA) include:
_ Schema definition
_Storage structure and access method
definition
_Schema and physical-organization
modification
_Granting of authorization for data access
Database Administrator
(DBA)

_Specifying integrity constraints

_Acting as liaison with users
_Monitoring performance and
responding to changes in requirements
_Routine maintenance that includes:
 Database Administrator
(DBA)

i) Periodically backing up the database to

prevent loss of data in case of disasters
ii) Ensuring that enough disk space is
available for normal operations and
upgrading disk space as required
iii)Monitoring jobs running on the
database and ensuring better
performance
 Transaction Management

 A transaction is a collection of
operations that performs a single logical
function in a database application
_ e.g., deposit, withdrawal, transfer
between accounts

A – Atomicity, C – Consistency, I –
Isolation, D - Durability
 Transaction Management

 Transaction-management component
ensures that the database remains in a
consistent (correct) state despite system
failures (e.g., power failures and
operating system crashes) and
transaction failures
_ e.g., system crash cannot wipe out
“committed” transactions
 Transaction Management

 Concurrency-control manager
controls the interaction among the
concurrent transactions, to ensure the
consistency of the database
_ e.g., two users accessing the same
bank account cannot “corrupt” the
system or withdraw more than allowed
 Database System Structure

 A database system is portioned into

modules that deal with each of the
responsibilities of the overall system.
The functional components of a
database system can be broadly
divided into the storage manager and
the query processor components.
 Database System Structure
 The storage manager is important because
databases typically require a large amount of
storage space. Also, data are moved
between disk storage and main memory as
needed. Since the movement of data to and
from disk is slow relative to the speed of the
CPU, it is imperative that the database
system structure the data so as to minimize
the need to move data between disk and
main memory.
Database System Structure

 The query processor is important

because it helps the database system
simplify and facilitate access to data. It
helps quick processing of updates and
queries.
 Storage Manager

 A storage manager is a program

module that provides the interface
between the low-level data stored in
the database and the application
programs and queries submitted to
the system.
 Storage Manager

 The storage manager is responsible to

the following tasks:
_interaction with the file manager (OS)
_efficient storage, retrieval and
updating of data
 Storage Manager

 The storage manager components

include:
_ authorization/integrity manager
_ transaction manager
_ file manager
_ buffer manager
 Storage Manager

 The storage manager stores:

_ data files (relations)

_ data dictionary (sometimes called
catalog)
_ indices
Thank
You