Transcript Database Management Systems (DBMS)

Database Management Systems
(DBMS)
DeSiamore
www.infoposter.co.tz
DeSiaMore
www.desiamore.com/ifm
1
File Processing……….

Advantages of Sequential file processing



DeSiaMore
It uses magnetic tape, the least expensive method
of secondary storage.
It is the most efficient form of organisation when
the entire of file or most of it is processed at once
Transaction file and old master file together act as
a back-up, it can be used to create the new
master file when existing one damaged or
destroyed
www.desiamore.com/ifm
2
File Processing……….

Disadvantages ..


DeSiaMore
Time factor –the time it takes to access a
particular record may be too long for many
applications
The entire file most be accessed and a new
master file created , even if only one record
requires maintenance or updating.
www.desiamore.com/ifm
3
File Processing……….

Direct File Organisation




DeSiaMore
The data can be organised in such a way that they are
scattered throughout the disk
This form of organisation that supports direct access also
referred to as random access
The records can be accessed nearly instantaneously and in
any order
When a record accessed, a record can be read or updated
and when this process competed , then the system is free
to respond to another request
www.desiamore.com/ifm
4
File Processing……….



Direct processing requires either magnetic disk or
optical disk and cannot use magnetic tape
Direct access systems do not search the entire file
rather, they move direct or nearly directly to the
required record, to do this the system must have
some way to determine where a particular record is
stored
Example, in figure 4, data are entered directly into
the system through a terminal that is in contact with
the CPU of the central computer, the system locates
the specific record in the master file and then
updates it.
DeSiaMore
www.desiamore.com/ifm
5
File Processing……….
`
Terminal
CPU
1U
Figure Direct
4, Direct
File Processing
–Records
are accessed directly
Access
Storage Device
(DASD)
DeSiaMore
www.desiamore.com/ifm
6
File Processing……….

Direct file processing….

There are several strategies which are used to find record..




Relative addressing
Hashing (randomising)
Indexing
Relative addressing



DeSiaMore
Simplest method of finding a record
A record’s primary key is associated with a specific
physical storage location
On retrieval process, the user enters the Key and the disk
operating system associates this key with the appropriate
location on the disk
www.desiamore.com/ifm
7
File Processing……….

Relative addressing….


Relative addressing loses its appeal when the record key
cannot be made to match the physical location
Hashing also known as Randomising



DeSiaMore
Method for determining the physical location of a record.
The record key is processed mathematically and another
number is computed that represents the location where the
record will be stored
Record keys are transformed into storage addresses and
by using an arithmetic procedures called randomising or
hashing algorithm
www.desiamore.com/ifm
8
File Processing……….



DeSiaMore
The task of this process is to take a set of records keys and
find a formula to map them into set of disk storage location
Identifiers
On retrieval process, user needs to retrieve the record
once its key is entered and the hashing routine is used to
determine where the record can be found on storage disks.
Major difficulty with the hashing procedure is due to that
some addresses will never get generated whilst two or
more record keys produce identical disk address or
synonyms or Collisions
www.desiamore.com/ifm
9
File Processing……….

Indexing


It uses a primary index which associates a primary key
with the physical location in which a record is stored.
Advantages of direct file organisation




DeSiaMore
Data can be accessed directly and quickly
Primary and secondary indexes can be used to search data
in many ways
Files can still be processed sequentially using secondary
index
Centrally maintained data can be kept up-date
www.desiamore.com/ifm
10
File Processing……….

Disadvantages of direct file processing



DeSiaMore
The use of an index lowers the computer system’s
efficiency
The hardware must be expensive for these
systems because all data must be stored on disks
There will be no backup data if a file destroyed,
the files are updated directly and no transaction
files are maintained on system
www.desiamore.com/ifm
11
File Processing……….

Summary on direct file organisation

The choice of file organisation and the methods
used for direct access depend on the five
characteristics





DeSiaMore
File volatility
File activity
File query needs
File size
Data currency
www.desiamore.com/ifm
12
Data Processing

Data processing comprises the following;




Capturing of data
Storing of data
Updating and retrieving of data and information
Data Management


DeSiaMore
Data management is the arrangement of all data and
information with an organization
It also refers to the methods of physical storage and
retrieval of data on a disk or other storage devices
www.desiamore.com/ifm
13
Data Management..

Data management involves the following,



DeSiaMore
Data administration
The standards of defining data
The way in which people perceive and use data in
their day-to-day activities
www.desiamore.com/ifm
14
Data Independence

Data Independence

Data independence allows a database to be
structurally changed , it means data can be;


DeSiaMore
Added and deleted or data attributes altered with
minimum disruption to the existing system
This implies that application programs are not required
to have detailed knowledge of the records layout, it
means when a record layout is changed like fields
added, deleted, changed in size then fewer application
programs or none would be changed
www.desiamore.com/ifm
15
Data Independence….

There are two distinct levels of data
independence;



Logical data independence
Physical data independence
Logical data independence

DeSiaMore
Insulates application programs from logical
operations such as combining two records into
one or splitting an existing record into two or more
records
www.desiamore.com/ifm
16
Data independence….

Physical data independence

DeSiaMore
Indicates that the physical storage structures or
devices used for storing data could be changed,
this happens without needing a change in the
record structure or application programs
www.desiamore.com/ifm
17
Database Administration Roles

Database administration –Centrally
controlling the database


Implemented by a person or group of persons
under the supervision of a knowledgeable person
called Administrator, this person known as
Database Administrator (DBA)
DBA is responsible for supervising the creation,
modification and maintenance of the database
The DBA controls the database structure and sets up the definition for
physical as well as logical implementation of the database.
DeSiaMore
www.desiamore.com/ifm
18
Database Administration Roles..

Implementing Security Features,




DeSiaMore
DBA maintains the integrity of a database
DBA maintains that the database is not accessible by
unauthorised users,
DBA is responsible for granting permission to use the
database and stores the profile of each user
The user profile can be used by the DBA to verify that a
particular user is allowed to access and perform a given
operation on database within the limited time frame
www.desiamore.com/ifm
19
Database Administration Roles…

Measures Against Data Loss


DBA is responsible for defining procedures to
recover data from failures –human natural, or
hardware malfunctioning with minimum loss
DBMS Users

DeSiaMore
The users of a database can be classified
depending on their degree of expertise or their
mode of interactions with the DBMS.
www.desiamore.com/ifm
20
Database Administration Roles…

DBMS Users…..

The user can be classified as..





Naïve Users
Online Users
Application Programmers
BDAs
Naïve Users


DeSiaMore
Are those users who need not be aware of the presence of
the database system or any other system supporting their
usage
Example, the users of an Automatic Teller Machine fall in
this category.
www.desiamore.com/ifm
21
Database Administration Roles…

Naïve Users…


The user is instructed through each step of a transaction,
user respond by pressing a coded key or numeric value
Operations that can be performed by Naïve user are very
limited and affect on precise portion of the database
Naïve users are ‘end users of the database who work through a
menu driven application program where the type and range of
response is always indicated to the user’
( Jain et al., 2002)
DeSiaMore
www.desiamore.com/ifm
22
Database Administration Roles…

Online Users


DeSiaMore
Are those users who may communicate with the
database directly via an online terminal or
indirectly via a user interface and application
program
These users are aware of the presence of the
database system and may have acquired a
certain amount of expertise with the limited
interaction they are permitted with a database
www.desiamore.com/ifm
23
Database Administration Roles…

Application programmers


Professional programmers, who are responsible
for developing application programs or user
interface
Database Administrator

DeSiaMore
DBA is a knowledgeable person who is
responsible for the physical design and
management of the database
www.desiamore.com/ifm
24
Data Dictionary

A Data Dictionary is a database about databases, it holds the
following information about each data element in the databases;








Name
Type
Range of values
Source
Access authorization
Indicates which application programs use the data.
A data dictionary may be a stand-alone information system used
for management and documentation purposes, or it may be an
integral part of the database management system.
Data dictionary is used to actually control the database
operations, data integrity and accuracy
DeSiaMore
www.desiamore.com/ifm
25
Data Dictionary…..

Importance of Data Dictionary



DeSiaMore
It provides the name of a data element, its
description, and data structure in which may be
found
Provides great assistance in producing a report of
where a data element is used in all programs that
mention it
It is possible to search for a data name, provided
keywords that describe that name
www.desiamore.com/ifm
26
Database Processing
Data Items
Relationships
Constraints
Database
Schema
Figure 1, Components of a Database
DeSiaMore
www.desiamore.com/ifm
27
Database processing…..

What is a Database?
 A collection of data designed to be used by different people,
 Organised in such a way that a computer program can quickly
select desired piece of information
 A database consists of four elements;





Data
Relationships
Constraints
Schema
Data
 Binary computer representations of stored logical entities
 They are distinct piece of information usually formatted in a
special way.
 The term data is often used to distinguish binary (machinereadable) information from textual (human-readable) information.
DeSiaMore
www.desiamore.com/ifm
28
Database Processing….


DeSiaMore
Example, some applications make a distinction
between data files (files that contain binary data)
and text files (files that contain ASCII data)
In database management systems, data files are
the files that store the database information
whereas other –index files and data dictionaries,
stores administrative information known as
metadata
www.desiamore.com/ifm
29
Database processing…..

Relationships


Constraints


Relationships represent a correspondence between the
various data elements
Are predicates that define correct database states.
Schema


DeSiaMore
Describes the organisation of data and relationships within
the database.
Defines various views of the database for the use of the
system components of the database management system
and for the application’s security as in figure 2
www.desiamore.com/ifm
30
Database processing…..

Schema…


It separates the physical aspects of data storage from the
logical aspects of the representation
As in figure 2,



DeSiaMore
The internal schema defines how and where data are
organised in physical data storage
The conceptual schema defines the stored data structures in
terms of the database model used.
The external schema defines a view/s of the database for
particular users. In this case the database management
system provides services for accessing the database whilst
maintaining the required correctness and consistency of the
stored data
www.desiamore.com/ifm
31
Database processing…..
External Schema 1
External Schema 2
……….
External Schema N
Global Conceptual Schema
Internal Schema
Physical
Database
Figure 2, Organisation of a Database
DeSiaMore
www.desiamore.com/ifm
32
Database processing…..

Why a Database


Why should an organisation have an integrated database
to store its operational data?
Deficiencies of pre-database information processing
include (but not limit to) the following…








DeSiaMore
Data inconsistency
Lack data integrity
Data repetition or redundancy
Interdependence between programs and data files
Lack of foolproof data security mechanisms
Lack of coordination across applications using common data
Non-uniform back-up and recovery methods
Encoded data
www.desiamore.com/ifm
33
Database processing…..

The advantage of having the data in a database are;
 Redundancy can be reduced –having a centralised database
redundancy or multiple copies of the same data can be reduced
 Inconsistency can be avoided –this depends on data redundancy,
which means when the same data is duplicated and changes are
made at one site, which is not propagated to the other site, then it
gives rise to inconsistency. So if the redundancy is removed
chances of having inconsistent data is also removed
 Data can be shared –the existing application can share data in a
database
 Standards can be enforced –with the central control of the
database, the DBA can enforce standards
 Security restrictions can be applied –the DBA can define
authorisation checks to be carried out wherever access to
sensitive data is attempted.
DeSiaMore
www.desiamore.com/ifm
34
Database processing…..


DeSiaMore
Integrity can be maintained –integrity means that
the data in the database is accurate. Centralised
control of the data helps in permitting the
administrator to define integrity constraints to the
data in the database
Conflicting requirement can be balanced –
database designers can be able to create
database that is the best for the organisation by
knowing the overall requirements.
www.desiamore.com/ifm
35
Database processing…..

Characteristics of Data in a Database







DeSiaMore
Shared –a data in a database are shared among different users and
applications
Persistence –data in a database exist permanently in the sense the
data can live beyond the scope of the process that created
Validity/Integrity/ Correctness –data should be correct with respect to
the real world entity that they represent
Security –data should be protected from unauthorised access
Consistency –whenever more that one data element in a database
represents related real-world values, the values should be consistent
with respect to the relationship
Non-Redundancy –no two data items in a database should represent
the same real-world entity
Independence –the three levels in the schema (internal, conceptual
and external) should be independent of each other so that changes
in the schema at one level should not affect the other levels
www.desiamore.com/ifm
36
Types of Database Language

There are three types of database languages



DeSiaMore
DDL ( Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
www.desiamore.com/ifm
37
Types of Database Language…

DDL




DeSiaMore
Used to define data and their relationships to
other types of data
Used to formulate schema-level concepts
Mainly used to create files, databases, data
dictionaries, and tables within databases.
Defines the format or schema of the database
www.desiamore.com/ifm
38
Data Definition Language…

It allows specification of following information
about each tables





The schema of each table
The integrity constraints
The set of values associated with each attribute
The security and authorization information for each table
The physical storage structure of each table on disk
The SQL commands that are used to create database objects
are known as Data Definition Language or DDL
DeSiaMore
www.desiamore.com/ifm
39
Types of Database Language……

DML



DML is a language which deals with the processing or
manipulation of various database objects
It provides for the program interface to open and close
database, find records in files, navigate through the
records, add new records and change or delete existing
records
To formulate changes to be effected in a database instance
The SQL commands that are used to manipulate data within
database objects are called Data Manipulation Language or DML
DeSiaMore
www.desiamore.com/ifm
40
Types of Database Language……

DCL



DCL is a language which used to improve security features
and thus prevents unauthorised access to data in the
database
Security is provided by granting or revoking privileges on a
user
Privileges determines whether or not a user can execute a
given command or a command can be executed on specific
groups of data
The SQL commands that are used to control the behaviour of
database objects are called Data Control Language or DCL
DeSiaMore
www.desiamore.com/ifm
41
Any Questions
DeSiaMore
www.desiamore.com/ifm
42
Database Management Systems
(DBMS)

Outline





DeSiaMore
Introduction to DBMS
Database Architecture
Database Management System
Why DBMS
Types of DBMS
www.desiamore.com/ifm
43