DATABASE

MANAGEMENT

Lecture 1
HRM 322

By Richard Mkechera
1
 INTRODUCTION

What is Database?
Database can be defined as a
collection of information organized in
such a way that it can be accessed
easily.
Examples of database:
i.Telephone directory
ii. Tracking customer orders
iii. Maintain employees records.
2
 Database Terminology
 Data
This is the fact, or about specific entity(person,place or thing)
 Information
Is the data that have been processed and is useful to the user
 Field
Is a single item of information or data in entity e.g. employee name
 Record
Is a group of fields about an entity,example Employee’s particulars

3
 History of databases
Manual system
Before the advent of the ideas behind the modern database systems,it was
common for different firm to store information using manual system.
Structure of manual system
 Information concern all division in the firm, can be stored in dedicated room or
each division such as sales, accounts, production can be stored in there own
information separate offices
 Room or office will be furnished with shelves,that holds records for different
subjects
 Information will be stored in hard flat files, each file will carry one record
 Each file will have a specific number to identify it
 A person will use a file number to retrieve it

4
5
 If management wanted an overall view of the
firm,they would have to gather lots of
information from many different divisions
before the appropriate statistic could be
obtained .
 This process was too laborious to be
considered and thus vital information in
decision making could be lost
 Due to this problem the modern database
ideas(FPS) were born
6
FILE PROCESSING SYSTEM

 Information stored as group of records in separate

files
 File systems consisted of a few data files and many
application programs
 Each file called a soft file
 Flat file contained the processed information for
one specific function
 Use of programming languages to write
applications
7
File processing systems structure

8
Limitation of File Processing system

i. Separate and isolated data

To make decision, user might need data from
more than one file
ii. Data redundancy
Often the same information was stored in more
than one file, in addition to taking up more
space in the system, this cause loss of data
integrity
iii. Difficult in representing data from user’s view
To create useful application for user, often
data from various files must be combined. In
file processing system, it was difficult to
determine the relationship between isolated
data in order to meet user requirement.
9
iv. Data inflexibility
Program-data independency and data
isolation limited the flexibility of the
file processing system in providing
user requirement

10
DATA BASE MANAGEMENT SYSTEM(DBMS)

 Is a program that allows users to define, create, manipulate,

store, maintain retrieve and process the data in the data base
in order to produce meaningful information.
 Focus on information presentation
 Data stored as a records in various database files that can
be combined to produce meaningful information for users
 It controls all functions of capturing, processing, storing,
retrieving data and generates various forms of data output
 Manage access by multiple users and multiple programs to
common stored data.
 And hence it overcomes all limitations of FPS
11
DBMS STRUCTURE

12
CHARACTERISTICS OF DBMS

i.Computerized record keeping system

ii.Contain facilities that allow user to:
(a)add, and delete files (b)
Insert, retrieve, update, delete data
iii.Collection of databases; each can be
used for separate purposes or combined

13
EXAMPLES OF DBMS
 Ms ACCESS
 SQL SERVER
 ORACLE
 MY SQL
 DB2
 TERON
 POSTGRESS
 ENGRESS

14
FUNCTIONS OF DBMS
 To store data
 To organize data
 To control access to data gTo protect data

15
 COMPONENTS OF DBMS

The basic components of a DBMS can be divided

into three subsystem
i.Design tool
This provide features for creating the data base and
various application, forms, and reports
ii.Run-time facilitiesThis process the application
created by design tools.
iii. DBMS engine Translates between the design
tools and run time facilities and data

16
 ADVANTAGES OF DBMS

i.Centralized data reduce management

problems
ii. Data redundancy and consistency are
controllable
iii. Program-data interdependence is
diminished
iv. Flexibility of data is increase
17
DISADVANTAGES OF DBM

i. Reduction in speed of data access

time
ii. Require special knowledge
iii. Possible dependency of
application programs to specific
DBMS versions

18
DATABASE ADMINISTRTATOR

Is a one who controls and manage the

data base
Functions of DB ADMIN
i. To make decisions concerns the content of the data base
ii. Plan storage strictures and access strategies.
iii. Provide user support
iv. To define security and integrity checks
v. Interpret backup and recovery strategies

19
 Data Base Models

There are four common types of database

models, which are:
• Flat model
• Hierarchical model
• Network model
• Relational model
• Object oriented model

20
 FLAT DATABASE MODE

 The earliest and simplest database

model
 Is a way of organizing information
in a single table
 Is good only for simple database
 Possible redundancy of data
21
 HIERARCHICAL DATABASE MODEL

 As its name implies, the hierarchical

database model defines hierarchically
arrangement of data
 Is like upside down tree
 A single table acts as a root of the database
from which the other table branches out
 Parents and children are tied together by
links called “pointers

22
 More efficient than Flat database
 Has some serious problems, that you
can not add a record to a child table
until it has already been incorporated
with the parent
 Redundancy of data may occur
because it does not handle many to
many relationship

23
24
NETWORK DATABASE MODEL

 It was designed to solve problems of hierarchical data base

model
 It solves the problem of data redundancy by representing
relationship in terms of sets rather than hierarchy
 It is similar to the hierarchical model, in fact the hierarchy
model is a subset of network model
 But this model was difficult to implement, so another
simple model was developed, which is RELATIONAL
DATABASE MODEL

25
26
RELATIONAL DATABASE MODEL
 Is a collection of data items organized as a set of formally-
describes tables from which data can be accessed or
reassembled in many different ways without having to
reorganize the data base tables
 A collection of data organized in two-dimensional tables
consisting of named columns and rows
 It is easy to create
 It is easy to extend, after original database creation.
 The core of Relational data base model is the concept of
table, which is also called relation in which all data is store

27
 Each table is made up of records (horizontal
rows also known as tuples) and fields (vertical
column also known as attributes)
 Table-is a two dimensional representation of data
consisting of column and zero or more rows
 The table name must be unique
 The table name must be descriptive

28
 Null values-is a missing or unknown value in a
column of a table, null are not the same as zeros
 Primary key-is the column or group of columns
whose values uniquely identify each row of a table
 Every table must have only one PK
 Pk must always have a value
 PK must be unique
 Foreign key is a column or group of columns that
is a primary key in another table
29
30
 DATABASE DEVELOPMENT

The process can be broken into 5

phases:
(i)Planning
(ii)Analysis
(iii)Design
(iv)Implementation
(v)Maintenance

31
These phases often overlap, and
some techniques and tools may
be used in more than one stage,
especially between analysis and
design. E.g Data Dictionary

32
 PLANING PHASE

 Here the overall database structure is defined.

 It involves the following tasks
1. The purpose of database is determined
 What information will be used
 How information is to be used
 What question will be answered
2. Feasibility study are conducted
3. Requirements are gathered

33
 ANALYSIS PHASE

Database can be analyzed on different

models:
i. Conceptual model
ii. Logical model
iii. Physical mode

34
Conceptual model
 It provide the framework for
developing a database structure
schema from top to down
 Three data base components
(entities,attributes and
relationship)

35
 Entities
This defines a thing that exist and is
distinguishable from which data will
be collected, e.g person,place or
object.
These are are basic building blocks of
database

36
 Entity instance
 Is a particular occurrence of an
entity
 Entity set/class/type A group of
similar entities
 Attributes This describes the
properties of entities and relationship

37
 Relationship
A relationship is a connection between entity classes.
Example, relationship between PERSONS and
AUTOMOBILES could be an “OWNS” That is to say ,
people own automobile
 Types of relationship
We have three types of relationship
1.One-to-one
2. One-to-may
3.Many-to-many

38
 1:1

1:1- Relationship
An entity in A is
associated with
atmost one
entity in B, and
associated with
atmost one
entity in A.

39
 1: M

1: M Relationship
(One to Many)
An entity in A is
associated with any
number of entities in
B. An entity in B,
however, can be
associated with at
most one entity in A.

40
 M:1
M:1 Relationship
(Many to One) An
entity in A is
associated with at
most one entity in
B.An entity in B,
however, can be
associated with any
number of entities in
A.
41
 M:M

M:M
Relationship(M
any to Many)
An entity in A
is associated with
any number of
entities in B, and an
entity in B is
associated with any
number of entities in
A.
42
 DESIGN PHASE

This determines how best the

information system that was obtained
The following should be determined and
and represented in design phase
 Tables needed
 Fields needed for each table
 Relationships between tables
 DBMS
 User views (Input forms, output reports)
43
 Primary Keys

Primary Keys:
A primary key is the column or a group of columns that can uniquely identify any
row in the table. This key should also identify the table through the database.
Listed below are some of the important elements of a primary key:
 It must uniquely identify each record in a table.
 It must contain unique values.
 It cannot be a null field.
 It cannot be a multi-part field.
 It should contain a minimum number of fields necessary to be called
unique.
 Its value cannot be modified except in very rare cases .

44
Example

45
 Listed below are some rules for establishing primary
keys:
 Each table must have only one primary key.
 Each table within a database should have a unique
primary key.
 When a combination of attributes is used as a
unique identifies, it is known as composite
primary key.

46
 Foreign Keys

A foreign key is a copy

of a primary key in
another table.
The key connects to
another table when a
relationship is being
established. Consider
the issues table again
47
RDBMS SYSTEM

48
Description of RDBMS parts
PARTS DESCRIPTION
Disk - Meta-data – schema - table definition, view definitions,
Storage: mappings
- Data – relation instances, index structures statistics about data
- Log – record of database update operation sessential for failure
recovery

DDL and - Commands for relation scheme creation

other - Constraints setting
command - Commands for handling authorization and data access control
processor:

Query Compiles - SQL adhoc queries and update / delete commands

compiler

49
Query Selects a near optimal plan for executing a query- relation
optimizers properties and index structures are utilized

Application Preprocess to separate embedded SQL commands. Uses host

Program language compiler to compile rest of the program. Integrate the
Compiler compiled program with the libraries for SQL commands supplied
by RDBMS

RDBMS Executes Compiled queries, Compiled application programs.

Run Time Interacts with Transaction
System: Manager, Buffer Manager.

Transaction T
Manager: · Keeps track of start, end of each transaction. Enforces
concurrency control protocols

50
Buffer
Manager
Manages disk space. Implements paging mechanism

Recovery
Manager Takes control as restart after a failure. Brings the system to a
consistent state before it can be resumed

51