Chapter One
Introduction to Database System
Database systems are designed to manage large
data set in an organization.
The data management involves both definition and
the manipulation of the data which ranges from
simple representation of the data to considerations of
structures for the storage of information.
The data management also consider the
provision/Condition of mechanisms for the
manipulation of information.
A DBMS is a powerful tool for creating and managing
large amounts of data efficiently and allowing it to persist
over long periods of time, safely.
1
Introduction to Database System....
Thus the DB course is about:
How to organize data
Supporting multiple users
Efficient and effective data retrieval
Secured and reliable storage of data
Maintaining consistent data
Making information useful for decision making
2
Cont…
Data management passes through the
different levels of development along with
the development in technology and services.
These levels could best be described by
categorizing the levels into three levels of
development.
3
Database Management development
Levels
Even though there is an advantage and a problem
overcome at each new level, all methods of data
handling are in use to some extent. The major three
levels are;
[Link] Approach
[Link] File Based Approach
[Link] Approach
4
Database Management development
Levels .......
1. Manual Approach
In the manual approach, data storage and retrieval
follows the primitive and traditional way of information
handling where cards and paper are used for the purpose.
The data storage and retrieval will be performed using
human labour.
5
Cont
Files for as many event and objects as the
organization has are used to store information.
Each of the files containing various kinds of
information is labelled and stored in one or more
cabinets.
The cabinets could be kept in safe places for security
purpose based on the sensitivity of the information
contained in it.
Insertion and retrieval is done by searching first for the
right cabinet then for the right the file then the
information.
One could have an indexing system to facilitate access
to the data
6
Database Management development
Levels .......
Limitations of the Manual approach
Prone to error
Difficult to update, retrieve, integrate
You have the data but it is difficult to compile the
information
Limited to small size information
Cross referencing is difficult
7
Database Management development
Levels .......
2. Traditional File Based Approach
After the introduction of Computer for data processing
to the business community, the need to use the device
for data storage and processing increase.
File based systems were an early attempt to
computerize the manual filing system.
This approach is the decentralized computerized data
handling method.
A collection of application programs perform
services for the end-users.
In such systems, every application program that
provides service to end users define and manage its
own data 8
Cont…
Such systems have number of programs for each
of the different applications in the organization.
Since every application defines and manages its
own data, the system is subjected to serious data
duplication problem.
File, in traditional file based approach, is a collection
of records which contains logically related data.
9
10
Database Management development
Levels ...of the Traditional File Based approach
Limitations
As business application become more complex
demanding more flexible and reliable data handling
methods, the shortcomings of the file based system
became evident. These shortcomings include, but not
limited to:
Separation or Isolation of Data: Available
information in one application may not be known.
Data Synchronisation is done manually.
Limited data sharing- every application maintains its
own data.
Lengthy development and maintenance time
11
Cont…
Duplication or redundancy of data (money and time
cost and loss of data integrity)
Data dependency on the application- data structure
is embedded in the application; hence, a change in the
data structure needs to change the application as well.
Incompatible file formats or data structures (e.g.
― C‖ and COBOL) between different applications and
programs creating inconsistency and difficulty to process
jointly.
Fixed query processing which is defined during
application development
12
Database Management development Levels ...
The limitations for the traditional file based data handling
approach arise from two basic reasons.
1. Definition of the data is embedded in the application
program which makes it difficult to modify the
database definition easily.
2. No control over the access and manipulation of the
data beyond that imposed by the application programs.
The most significant problem experienced by the
traditional file based approach of data handling can be
formalized by what is called “update anomalies”.
We have three types of update anomalies;
13
Database Management development Levels ...
1. Modification Anomalies: a problem experienced
when one or more data value is modified on one
application program but not on others containing the
same data set.
2. Deletion Anomalies: a problem encountered where
one record set is deleted from one application but
remain untouched in other application programs.
14
Cont…
3. Insertion Anomalies: a problem experienced
when ever there is new data item to be
recorded, and the recording is not made in all
the applications.
when same data item is inserted at different
applications, there could be errors in encoding
which makes the new data item to be considered
as a totally different object.
15
Database Management development Levels …
3. Database Approach
Thus in Database Approach:
Database is just a computerized record keeping
system or a kind of electronic filing cabinet.
Database is a repository for collection of computerized
data files.
16
Cont…
Database is a shared collection of logically related data
and description of data designed to meet the information
needs of an organization.
Since it is a shared corporate resource, the database is
integrated with minimum amount of or no duplication.
Database is a collection of logically related data
where these logically related data comprises
entities, attributes, relationships, and business rules
of an organization's information.
17
Database Management development Levels ...
In database also contains a description of the data which
is known as ―Metadata‖ or ―Data Dictionary‖ or
―Systems Catalogue‖ or ―Data about Data‖ or some
times ―Data Directory‖.
Since a database contains information about the data
(metadata), it is called a self descriptive collection of
integrated records.
The purpose of a database is to store information and
to allow users to retrieve and update that information on
18
Cont…
Database is deigned once and used simultaneously by
many users.
Unlike the traditional file based approach in
database approach there is program data
independence.
That is the separation of the data definition from the
application.
Thus the application is not affected by changes made in
the data structure and file organization.
Each database application will perform the
combination of: Creating database, Reading,
Updating and Deleting data.
19
Database Management development Levels .......
Benefits of the database approach
Data can be shared: two or more users can access and
use same data instead of storing data in redundant
manner for each user.
Improved accessibility of data: by using SQL, the
users can easily access data without programming
experience.
Redundancy can be reduced: isolated data is integrated
in database to decrease the redundant data stored at
different applications.
20
Cont…
Quality data can be maintained: the different
integrity constraints in the database approach will
maintain the quality leading to better decision
making
Inconsistency can be avoided: controlled data
redundancy will avoid inconsistency of the data in the
database to some extent.
Transaction support can be provided: basic demands
of any transaction support systems are implanted in a
full scale DBMS.
21
Database Management development Levels ....
Integrity can be maintained:
Security measures can be enforced:
Improved decision support:
Standards can be enforced:
Compactness:
22
Cont…
23
Database Management development Levels ...
Limitations and risk of Database Approach
Introduction of new professional and specialized
personnel.
Complexity in designing and managing data
The cost and risk during conversion from the old to the
new system
High cost to be incurred to develop and maintain the
system
Complex backup and recovery services from the users
perspective
Reduced performance due to centralization and data
independency
High impact on the system when failure occurs to the
central system. 24
Database Management System (DBMS)
Database Management System (DBMS) is a Software
package used for providing EFFICIENT,
CONVENIENT and SAFE MULTI-USER (many
people/programs accessing same database, or even same
data, simultaneously) storage of and access to MASSIVE
amounts of PERSISTENT (data outlives programs that
operate on it) data.
A DBMS also provides a systematic method for
creating, updating, storing, retrieving data in a
database.
A DBMS also provides the service of controlling data
access, enforcing data integrity, managing concurrency
control, and recovery. 25
DBMS and Components of DBMS Environment
Each DBMS should have facilities to define the database, manipulate the
content of the database and control the database.
These facilities will help the designer, the user as well as the database
administrator to discharge their responsibility in designing, using and
managing the database. It provides the following facilities: 26
DBMS and Components of DBMS Environment....
DataDefinition Language (DDL):
Language used to define each data element required
by the organization.
Commands for setting up schema or the intension of
database
These commands are used to setup a database,
create, drop and alter table with the facility of
handling constraints
27
Cont…
DataManipulation Language (DML):
Is a core command used by end-users and
programmers to store, retrieve, and access the data in
the database
e.g. SQL
Since the required data or Query by the user will be
extracted using this type of language, it is also called
"Query Language“
28
Cont..
Data Control Language:
Database is a shared resource that demands control of
data access and usage.
The database administrator should have the facility to
control the overall operation of the system.
Data Control Languages are commands that
will help the Database Administrator to control
the database.
The commands include grant or revoke privileges
to access the database or
particular object within the database and
to store or remove database transactions
29
DBMS and Components of DBMS Environment....
Data Dictionary:
Due to the fact that a database is a self
describing system, this tool, Data Dictionary, is
used to store and organize information about the data
stored in the database.
1. Hardware: are components that one can touch and
feel.
These components are comprised of various types of
personal computers, mainframe or any server computers
to be used in multi-user system, network infrastructure,
and other peripherals required in the system.
30
DBMS and Components of DBMS
Environment....
2. Software: are collection of commands and
programs used to manipulate the hardware to
perform a function.
These include components like the DBMS
software, application programs, operating systems,
network software, language software and other
relevant software.
31
DBMS and Components of DBMS Environment....
3. Data: Data is the most important component to the user
of the database.
There are two categories of data in any
database system: that is Operational and Metadata.
Operational data is the data actually stored in
the system to be used by the user.
Metadata is the data that is used to store information
about the database itself.
The structure of the data in the database is called
the schema, which is composed of the Entities,
Properties of entities, and relationship between
entities and business constraints.
32
DBMS and Components of DBMS Environment..
4. Procedure: this is the rules and regulations on how
to design and use a database.
It includes procedures like how to log on to the DBMS,
how to use facilities, how to start and stop DBMS, how
to make backup, how to treat hardware and software
failure, how to change the structure of the database.
33
DBMS and Components of DBMS Environment....
5. People: this component is composed of the
people in the organization that are responsible or
play a role in designing, implementing, managing,
administering and using the resources in the database.
This component includes group of people with high
level of knowledge about the database and the
design technology to other with no knowledge of
the system except using the data in the database.
34
Database Development Life Cycle (DDLC)
As it is one component in most information system
development tasks, there are several steps in designing a
database system.
Here more emphasis is given to the design phases of
the system development life cycle.
The major steps in database design are;
1. Planning: that is identifying information gap in an
organization and propose a database solution to solve
the problem.
35
Cont…
1. Analysis: that concentrates more on fact finding
about the problem or the opportunity.
Feasibility analysis, requirement determination
and structuring, and selection of best design method
are also performed at this phase.
3. Design: in database development more emphasis is
given to this phase. The phase is further divided into
three sub-phases.
[Link] Design: concise description of the
data, data type, relationship between data and
constraints on the data.
There is no implementation or physical detail
consideration.
Used to elicit and structure all information requirements36
Database Development Life Cycle (DDLC)...
[Link] Design: a higher level conceptual abstraction with
selected specific data model to implement the data structure.
It is particular DBMS independent and with no other
physical considerations.
[Link] Design: physical implementation of the logical
design of the database with respect to internal storage and
file structure of the database for the selected DBMS.
4. Implementation: the testing and deployment of the
designed database for use.
5. Operation and Support: administering and
maintaining the operation of the database system
and providing support to users.
Tuning the database operations for best
performance. 37
Roles in Database Design and Use
As people are one of the components in DBMS
environment, there are group of roles played by different
stakeholders of the designing and operation of a database
system.
1. Database Administrator (DBA)
Responsible to oversee, control and manage the database
resources (the database itself, the DBMS and other related
software)
Authorizing access to the database
Coordinating and monitoring the use of the database
Responsible for determining and acquiring hardware and
software resources
Accountable for problems like poor security, poor performance of
the system in all steps of database development 38
Cont…
2. Database Designer (DBD)
Identifies the data to be stored and choose the
appropriate structures to represent and store the
data.
Should understand the user requirement and
should choose how the user views the database.
39
Roles in Database Design and Use..
Database Designer (DBD)...
A. Logical and Conceptual DBD
Identifies data (entity, attributes and relationship)
relevant to the organization
Identifies constraints on each data
Understand data and business rules in the
organization
Sees the database independent of any data
model at conceptual level and consider one
specific data model at logical design phase.
40
Cont…
B. Physical DBD
Take logical design specification as input & decide
how it should be physically realized.
Map the logical data model on the specified
DBMS with respect to tables and integrity
constraints. (DBMS dependent designing)
Select specific storage structure and access path to
the database
Design security measures required on the database
41
Roles in Database Design and Use...
3. Application Programmer and Systems Analyst
System analyst determines the user requirement and
how the user wants to view the database.
The application programmer implements these
specifications as programs; code,
Test, debug, document and maintain the application
program.
The application programmer determines the
interface on how to retrieve, insert, update and
delete data in the database.
The application could use any high level
programming language according to the
availability, the facility and the required service.
42
Roles in Database Design and Use...
4. End Users
Workers, whose job requires accessing the database
frequently for various purposes.
There are different group of users in this category.
A. Naïve Users:
Sizable proportion of users
Unaware/uninformed/ of the DBMS
Only access the database based on their access level
and demand
Use standard and pre-specified types of queries.
43
Cont…
B. Sophisticated Users
Users familiar with the structure of the Database
and facilities of the DBMS.
Have complex requirements
Have higher level queries
Are most of the time engineers, scientists,
business analysts, etc
44
Roles in Database Design and Use...
c . Casual Users
Users who access the database occasionally.
Need different information from the database
each time.
Use sophisticated database queries to satisfy
their needs.
Are most of the time middle to high level
managers
45
Cont…
These users can be again classified as ― Actors on the
Scene‖ and ―Workers Behind the Scene‖.
Actors on the Scene: Persons whose job involves daily
use of a large database
Data Administrator
Database Administrator
End users
Workers Behind the Scene‖. Persons whose job
involves design, development, operation, and
maintenance of the DBMS software and system
environment.
46
Roles in Database Design and Use...
Workers behind the scene
DBMS designers and implementers: who design and
implement different DBMS software.
Tool Developers: experts who develop software
packages that facilitates database system designing and
use. Prototype, simulation, code generator developers
could be an example.
Independent software vendors could also be categorized
in this group.
Operators and Maintenance Personnel: system
administrators who are responsible for actually
running and maintaining the hardware and software of
the database system and the information technology
facilities. 47
ANSI-SPARC (American National
Standards Institute, Standards Planning
And Requirements
Committee)Architecture
The purpose and origin of the Three-Level database
architecture
All users should be able to access same data.
A user's view is unaffected or immune to changes
made in other views.
Users should not need to know physical database storage
details. As there are naïve
users of the system, hardware level or physical details
should be a black-box for such users.
48
Cont…
DBA should be able to change database storage
structures without affecting the users' views.
Internal structure of database should be unaffected by
changes to physical aspects of storage, such as change of
hard disk
DBA should be able to change conceptual structure of
database without affecting all users
In any database system, the DBA will have the
privilege to change the structure of the database, like
adding tables, adding and deleting an attribute,
changing the specification of the objects in the database.
49
ANSI-SPARC Architecture....
All of the above and much more functionalities
are possible due to the three level ANSI-SPARC
architecture.
Three-level ANSI-SPARC Architecture of a Database 50
ANSI-SPARC Architecture and Database DesignPhases...
So
51
Cont…
1. External Level: Users' view of the database. It describes
that part of database that is relevant to a particular user.
Different users have their own customized view of the
database independent of other users.
2. Conceptual Level: Community view of the database.
Describes what data is stored in database and
relationships among the data along with the business
constraints.
3. Internal Level: Physical representation of the database
on the computer. Describes how the data is stored in the
database.
52
ANSI-SPARC Architecture and Database Design
Phases...
The following example can be taken as an illustration for
the difference between the three levels in the ANSI-
SPARC database Architecture. Where:
The first level is concerned about the group of
users and their respective data requirement
independent of the other.
The second level is describing the whole content of
the database where one piece of information will be
represented once.
53
Cont…
The third level
54
Data Independence
Logical Data Independence:
Refers to immunity of external schemas to changes in
conceptual schema.
Conceptual schema changes e.g. addition/removal of
entities should not require changes to external schema or
rewrites of application programs.
The capacity to change the conceptual schema without
having to change the external schemas and their
application programs.
55
Cont…
Physical Data Independence
The ability to modify the physical schema without changing the
logical schema
Applications depend on the logical schema
In general, the interfaces between the various levels and
components should be well defined so that changes in some
parts do not seriously influence others.
The capacity to change the internal schema without having to
change the conceptual schema
Refers to immunity of conceptual schema to changes in the
internal schema
Internal schema changes e.g. using different file
organizations, storage structures/devices should not require
change to conceptual or external schemas. 56
Data Independence....
Data Independence and the ANSI-SPARC Three-level
Architecture
57
A Classification of data models
A data model is a description of the way that data is
stored in a database.
Data model helps to understand the relationship between
entities and to create the most effective structure to
hold data.
Data Model is a collection of tools or concepts for
describing
Data
Data relationships
Data semantics
Data constraints
The main purpose of Data Model is to represent the data
in an understandable way. 58
Cont…
Categories of data models include:
1. Object-based 2. Record-based 3. Physical
Record-based Data Models
Consist of a number of fixed format records.
Each record type defines a fixed
number of fields,
Each field is typically of a fixed
length. And it is classified in to
Hierarchical Data Model
Network Data Model
Relational Data Model 59
A Classification of data models....
I. Hierarchical Model
The simplest data model
Record type is referred to as node or segment
The top node is the root node
Nodes are arranged in a hierarchical structure as sort of upside-
down tree
A parent node can have more than one child node
A child node can only have one parent node
The relationship between parent and child is one-to-many
Relation is established by creating physical link between stored
records (each is stored with a predefined access path to other
records)
To add new record type or relationship, the database must be
redefined and then stored in a new form. 60
Cont…
61
A Classification of data models....
ADVANTAGES of Hierarchical Data Model:
Hierarchical Model is simple to construct and
operate on
Corresponds to a number of natural hierarchically
organized domains - e.g., assemblies in
manufacturing, personnel organization in companies
Language is simple; uses constructs like GET, GET
UNIQUE, GET NEXT, GET NEXT WITHIN
PARENT etc.
DISADVANTAGES of Hierarchical Data Model:
Navigational and procedural nature of processing
Database is visualized as a linear arrangement of
records
Little scope for "query optimization"
62
A Classification of data models....
II. Network Model
Allows record types to have more than one parent
unlike hierarchical model
A network data models sees records as set members
Each set many to many has an owner and one or more
members
Allow relationship between entities
Like hierarchical model network model is a collection
of physically linked records.
Allow member records to have more than one owner
63
Cont…
64
A Classification of data models....
ADVANTAGES of Network Data Model:
Network Model is able to model complex
relationships and represents semantics of
add/delete on the relationships.
Can handle most situations for modelling
using record types and relationship types.
Language is navigational; uses
constructs like FIND, FIND member,
FIND owner, FIND NEXT within set,
GET etc.
Programmers can do optimal
navigation through the database.
DISADVANTAGES of Network Data Model:
Navigational and procedural nature of
processing
Database contains a complex array of pointers
that thread through a set of records.
Little scope for automated "query optimization‖ 65
A Classification of data models....
[Link] Data Model
Developed by Dr. Edgar Frank Codd in 1970 (famous
paper, 'A Relational Model for Large Shared Data
Banks')
Terminologies originates from the branch of
mathematics called set theory and predicate logic and
is based on the mathematical concept called Relation
Can define more flexible and complex relationship
Viewed as a collection of tables called ―Relations‖
equivalent to collection of record types
66
Cont…
Relation: Two dimensional table
Stores information or data in the form of tables rows
and columns
A row of the table is called tupleequivalent to record
A column of a table is called attribute equivalent to
fields
Data value is the value of the Attribute
Records are related by the data stored jointly in the
fields of records in two tables or files. The related
tables contain information that creates the relation
67
A Classification of data models....
The tables seem to be independent but are related some
how.
No physical consideration of the storage is required by
the user
Many tables are merged together to come up with
a new virtual view of the relationship
Alternative terminologies
Relation Table File
Tuple Row Record
Attribute Column Field
The rows represent records (collections of information
about separate items)
68
A Classification of data models....
In conducting searches, a relational database matches
information from a field in one table with information in a
corresponding field of another table to produce a third table
that combines requested data from both tables.
The columns represent fields (particular attributes of a
record)
Conducts searches by using data in specified
columns of one table to find additional data in another
table.
69
Any Questions?...
..
70
Thank you for your presence
71