Transcript DATABASE CONCEPT

DATABASE CONCEPT
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.
DeSiaMore
www.desiamore.com/ifm
1
Database Terminologies
 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
DeSiaMore
www.desiamore.com/ifm
2
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
DeSiaMore
www.desiamore.com/ifm
3
MANUAL FILE SYSTEM
USER
FILE KEEPER
FILE CABINET
DeSiaMore
www.desiamore.com/ifm
4
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
DeSiaMore
www.desiamore.com/ifm
5
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
DeSiaMore
www.desiamore.com/ifm
6
File processing systems structure
Customer file
User 1
Rental file
User 2
DeSiaMore
Customer
Processing
Application
Rental
Processing
Application
www.desiamore.com/ifm
Customer file
Rental file
7
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. Program
data independence for file formats and access
techniques
DeSiaMore
www.desiamore.com/ifm
8
iv. 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.
v. Data inflexibility
Program-data independency and data isolation
limited the flexibility of the file processing
system in providing user requirement
Due to this limitations, the concept of
Data base management system (DBMS) was born
DeSiaMore
www.desiamore.com/ifm
9
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
DeSiaMore
www.desiamore.com/ifm
10
DBMS STRUCTURE
USER 1
CPA
USER 2
RPA
USER 3
OPA
DeSiaMore
www.desiamore.com/ifm
DBMS
DATABASE
11
CHARACTERISTICS OF DMS
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
DeSiaMore
www.desiamore.com/ifm
12
EXAMPLES OF DBMS
 Ms ACCESS
 SQL SERVER
 ORACLE
 MY SQL
DeSiaMore
www.desiamore.com/ifm
13
FUNCTIONS OF DBMS
 To store data
 To organize data
 To control access to data
 To protect data
DeSiaMore
www.desiamore.com/ifm
14
USES OF DBMS
 To provide decision support
 To provide transaction processing
DeSiaMore
www.desiamore.com/ifm
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 facilities
This process the application created by
design tools.
iii. DBMS engine
Translates between the design tools
and run time facilities and data
DeSiaMore
www.desiamore.com/ifm
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 increased
DeSiaMore
www.desiamore.com/ifm
17
DISADVANTAGES OF DBMS
i. Reduction in speed of data access time
ii. Require special knowledge
iii. Possible dependency of application
programs to specific DBMS versions
DeSiaMore
www.desiamore.com/ifm
18
DATABASE
ADMINISTRTATOR
Is a one who controls and manage the data
base
Functions of DBMS
 To make decisions concerns the content of
the data base
 Plan storage strictures and access
strategies.
 Provide user support
 To define security and integrity checks
 Interpret backup and recovery strategies
DeSiaMore
www.desiamore.com/ifm
19
TYPES OF DATABASES
There are four common types of database models,
which are:
• Flat
• Hierarchical
• Network
• Relational
DeSiaMore
www.desiamore.com/ifm
20
FLAT DATABASE MODEL
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
DeSiaMore
www.desiamore.com/ifm
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
Relationship in such system are thought of in
terms of children an parents, such as a children
may have only one parent but a parent can have
multiple children
DeSiaMore
www.desiamore.com/ifm
22
Parents and children are tied together by links
called “pointers”
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 may relationship
DeSiaMore
www.desiamore.com/ifm
23
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
DeSiaMore
www.desiamore.com/ifm
24
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
stored
DeSiaMore
www.desiamore.com/ifm
25
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
Column name must be unique within the
table, however those columns in different
tables my share the same name
Rows must be unique
DeSiaMore
www.desiamore.com/ifm
26
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, it
relates the rows of the table to other rows
that appear elsewhere in another table
DeSiaMore
www.desiamore.com/ifm
27
DATABASE DEVELOPMENT
The process can be broken into 5 phases
(i)Planning
(ii)Analysis
(iii)Design
(iv)Implementation
(v)Maintenance
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
Data base design is non deterministic
DeSiaMore
www.desiamore.com/ifm
28
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 use
 What question will be answered
2. Feasibility study are conducted
3. Requirements are gathered
DeSiaMore
www.desiamore.com/ifm
29
Analysis phase
Database can be analyzed on different models:
i. Conceptual model
ii. Logical model
iii. Physical model
Conceptual model
 It provide the framework for developing a
database structure schema from top to down
 Three data base components (entities,attributes
an relationship) are described in detail.
DeSiaMore
www.desiamore.com/ifm
30
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
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
DeSiaMore
www.desiamore.com/ifm
31
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
Candidate keys
DeSiaMore
www.desiamore.com/ifm
32
Logical model
This is done after conceptual model
 Tables and fields are extracted
 Entities modeled as tables
 Attributes modeled as fields
 Each entity instance is called a record.
 PK and FK are determined
 After that Normalization process takes place
Physical model
 This defines how data will be stored and
accessed in a computer system
DeSiaMore
www.desiamore.com/ifm
33
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)
 Security mechanism
DeSiaMore
www.desiamore.com/ifm
34
Database Management Systems
(DBMS)
Abdallah Seleman
Computing and Information Technology Dept.
The Institute of Finance Management
DeSiaMore
www.desiamore.com/ifm
35
Course Co-ordinator
Abdallah Seleman
[email protected]
Block D,55
Consultation Hours
Tuesday & Wednesday
10:00 AM-12:00PM
DeSiaMore
www.desiamore.com/ifm
36
An Integrated of :• Introduction
– Fundamentals of database
– File Processing
•
•
•
•
•
•
•
Database processing
Common database software
Database Management Systems (DBMS)
Relational Database Management Systems (RDBMS)
Data Normalization
Introduction to Structured Query Language (SQL)
Implementation of a Relational Database using a RDBMS
DeSiaMore
www.desiamore.com/ifm
37
Recommended Readings
• Leon, A. and Leon,M (2002) Database Management
Systems,Dar es Salaam University Press
• Narang, Rajesh (2004) Database Management Systems,
New Delhi
• David Kroenke (2002), Database Processing:
Fundamentals, Design and Implementation, 8th Edition,
Prentice Hall, Upper Saddle River NJ
• Thomas M. Connolly and Carolyn E. Begg (2002),
Database Systems: A Practical Approach to Design,
Implementation, and Management, 3rd Edition, AddisonWesley, Harlow England.
– Note, Additional Readings shall be provided if necessary.
DeSiaMore
www.desiamore.com/ifm
38
Outline
–Fundamentals of Database
–File Processing
–Advantages & Disadvantages of
File Process Approach
–Data Processing
–Data Management
–Data Independence
–Data Administration Roles
DeSiaMore
www.desiamore.com/ifm
39
Assessment
• You will be assessed
through continuous
assessments (Coursework)
that comprise of two
compulsory Tests, quizzes
and assignments
• You are required to seat
for a final examination at
the end of the semester II
DeSiaMore
• CourseWork carries 40%
• Final Examination carries
60%
www.desiamore.com/ifm
40
Fundamentals of Database
• Computer uses databases by using a set of well defined
rules.
• Example, Assume that each card in the mailbox has five
lines of data items, namely as:– Name, Locality, City, State and Pin_Code
– These fields combined to form a record.
– Generally, A database contains the following.
•
•
•
•
•
DeSiaMore
Field
Record
File
Database
Key Field
www.desiamore.com/ifm
41
Fundamentals of Database…….
Fields
Or
Data Items
Name
Locality
City
State
Pin_Code
Record
Abdallah
9 Beveridge Street
Manchester
UK
110059
Field Name
Record #
Name
Locality
City
State
Pin_Code
1.
Abdallah
9 Beveridge Street
Manchester
UK
110059
2.
Gauravaz
12/B Mohan Garden
Mombai
MH
400003
3.
Deepak
Leeds
UK
4.
Fazal
WZ-92 Palam Vihar
121. Pankha Rd
New Delhi
ND
Record (1st)
320001
110045
Record (4th)
Field |Contents
DeSiaMore
www.desiamore.com/ifm
42
Figure
1 Illustrates the concepts
of Fields, Records, and Files
Fundamentals of Database…….
• Field
– The smallest piece of meaningful information
in a file is called a data item or Field.
– A data item is generally used for a group of
alphanumeric characters.
– Example, Name, Locality, City, State,
Pin_Code are all known as Data Items or
Fields as shown in figure 1
DeSiaMore
www.desiamore.com/ifm
43
Fundamentals of Database…….
• Record
– Collection of related fields
– Example, Figure 1 contains four records and
each record has five related fields namely as
• Name, Locality, City, State and Pin_Code
• File
– Is the Collection of all related records.
– Example, in figure 1, the file contains the list of
addresses of four
friends.
DeSiaMore
www.desiamore.com/ifm
44
Fundamentals of Database…….
Field 1
Fields
Field 2
Emp_Code
Address
Field 1
Field 2
Emp_Code
Salary
Field 1
Field 2
Emp_Code
Name
Relating Key
Fields
Records
àContain Related
Fields
Files
àContain Related
Records
Database
à Contain Related
Files
Records of 100
Employees
------
File 1
Contains 100 Records of Empl.
File 1
Records of 100
Employees
Records of 100
Employees
------
File 2
Contains 100 Records of Empl.
File 2
------
File 3
Contains 100 Records of Empl.
File 3
DeSiaMore
www.desiamore.com/ifm
45
Figure
2 illustrates the concepts
of Fields, Records, Files and Database
Fundamentals of Database…….
• Database
– Database is a collection of related files.
– A database is an organised collection of facts
– Is a Collection of information arranged and presented to
serve an assigned purpose
– Examples• A dictionary, where words are arranged alphabetically
• Telephone directory where subscriber names are listed in
alphabetic order.
• Figure 2 shows Employees Database having related files
containing records of employees
DeSiaMore
www.desiamore.com/ifm
46
Fundamentals of Database…….
• Key Field
– The Keyfield in a record is a unique data item which is
used to identify the record for the purpose of accessing
and manipulating database
– In figure 2,
• File 1 contains employee records with fields Emp_Code and
Address,
• File 2 contains employee records with fields Emp_Code and
Salary
• File 3 contains employee records with fields Emp_Code and
Name
• All the three files have one common field, namely
Emp_Code, this field is called the Keyfield – Is used for
identifying and relating records
DeSiaMore
www.desiamore.com/ifm
47
File Processing
• Data, Information, Knowledge
DATA

INFORMATION

KNOWLEDGE
INFERENCES
FORECASTS
DECISIONS
DeSiaMore
www.desiamore.com/ifm
48
File Processing……….
• Data
– Are raw facts which can be manipulated
– Data is required in the operation of any organisation and the same
or similar data may be required for various purposes
• Information
– The manipulation of data, simply information is summarization of
data in presentable form
– Data consists of facts which become information when they are
processed and convey meaning to people.
– Information is the backbone of any organisation
– Information is the critical factor that enables managers and
organisations to gain a competitive advantage. It can be considered
as the most critical resource of an organisation.
DeSiaMore
www.desiamore.com/ifm
49
File Processing……….
• Information………..
– It is the indispensable link that ties together all
the components of an organisation for better
operation and coordination and for survival in
today’s brutally competitive environment.
Definition,
Information is ‘data that have been put into a meaningful
and useful context and communicated to a recipient who
uses it to make decision’ (Burch and Grudnitski,1989)
DeSiaMore
www.desiamore.com/ifm
50
File Processing……….
• Information…….
– It involves the communication and recipient of
intelligence or knowledge
– It should predict the future with reasonable level of
accuracy
– It should help the managers make the best decision and
a prevent them from taking wrong decision
– It consists of data , images , text , documents and voice
often inextricably intertwined but always organised in a
meaningful context.
DeSiaMore
www.desiamore.com/ifm
51
File Processing……….
• Information……..
– Notice that the data that is being processed or refined
can be input, stored or both and this formulates the
cycle of information, refer figure 3.
– Example, If orders and payments are data, then
balance_due and quantity_in_hand would be the
information
• Knowledge
– Refers to as the facts , events and inference rules used
by a computer program in order to operate intelligently.
– It refers to a person’s capability and wisdom as how
much that person knows about one particular subject
DeSiaMore
www.desiamore.com/ifm
52
File Processing……….
Input
(Data)
Process
(Models)
Output
(Information)
Data
Captured
Database
Results
Action
Decision
Figure 3, Information Cycle
DeSiaMore
www.desiamore.com/ifm
53
File Processing……….
• Information Processing
– Information processing is the acquisition,
storage, organisation retrieval, display and
dissemination of information
– Quality information means that the information
that is
– Accurate
– Timely
– Relevant
DeSiaMore
www.desiamore.com/ifm
54
File Processing……….
• Files, File organisation and Management
• File
– A file is a collection of bytes stored as an
individual entity
– All data on disk is stored as a file with an
assigned file name that is unique within the
directory it resides it.
– To the computer, a file is the series of bytes
– The structure of a file is known to the software
that manipulatewww.desiamore.com/ifm
it.
DeSiaMore
55
File Processing……….
• File…..
– It contains data that is needed for information
processing
– These data is called entities, An entity is
anything about which information can be
stored, examples, physical object, a person,
concept or event and so on
– An attribute is a characteristic of interest about
an entity.
DeSiaMore
www.desiamore.com/ifm
56
File Processing……….
• File……..
– The values of the attributes describe a particular
entity.
– An Instance of the entity is represented by a set
of specific values for each of the attributes
Examples,
Entity – a car
Attributes (car) –Make, Model, Price, Eng. Capacity
Instances (car) –Mercedes-benz, S500, £12,000, 4956cc
DeSiaMore
www.desiamore.com/ifm
57
File Processing……….
• File………..
– In data storage, data items are usually grouped
together to describe an entity
• There are different types of files,
– Master files
– Transaction files
– Report files
DeSiaMore
www.desiamore.com/ifm
58
File Processing……….
• Master files
– A file of relatively permanent information
about entities
– These files are used as a source of reference
data for processing transactions and
accumulated information based on the
transaction data.
– Example, the accounts master file in a bank will
contain details like account name, balance,
address and so on
DeSiaMore
www.desiamore.com/ifm
59
File Processing……….
• Transaction Files
– A collection of records describing activities or
transactions by organisation.
– Created as a result of transactions and preparing
transaction documents
– Are used to update the details in the master file
– Example in the same bank system, the day-today activities like money withdrawals, deposits
and transfers.
DeSiaMore
www.desiamore.com/ifm
60
File Processing……….
• Report Files
– A file created by extracting data to prepare a
report
– Example, All accounts sorted by account
number containing the details like account
name, balance and so on
DeSiaMore
www.desiamore.com/ifm
61
File Processing……….
• Operations on Files
– There are mainly two kinds of file operations
• Retrieval and
• Update operations
– Retrieval operations do not change the contents
of the file thus it only locates records in the file
matching certain specific criteria
– Update operations change the contents of the
file by modifying the records, deleting (delete)
the records and inserting (Insert) new records
DeSiaMore
www.desiamore.com/ifm
62
File Processing……….
• Operations on Files……
– The following five operations are required for
the processing of records in files
•
•
•
•
•
DeSiaMore
File creation
Records location
Record creation
Record deletion
Record modification
www.desiamore.com/ifm
63
File Processing……….
• File Organisation
– A technique for physically arranging the
records of a file on secondary storage devices
– Factors necessary to be considered when
choosing the file organisation
•
•
•
•
•
DeSiaMore
Speed of data retrieval
Speed of processing data
Speed of update operations
Storage space
Security
www.desiamore.com/ifm
64
File Processing……….
• File organisation…..
– Two types of file organisation are
• Sequential file organisation and
• Direct file organisation
– Sequential file organisation
DeSiaMore
• Records are stored in some predetermined sequence
one after the other
• It contains one field referred to as Primary Key
• Primary Key usually determines their sequence or
order
www.desiamore.com/ifm
65
File Processing……….
• Sequential file organisation….
– A primary key is a field ( or set of fields) whose
contents is unique to one record and can
therefore be used to identify that record.
– Example, Student_ID, Customer_ID, Emp_ID
and so on
– Sequential file organisation is very common
because it makes effective use of the least
expensive secondary storage device –the
magnetic tape
DeSiaMore
www.desiamore.com/ifm
66
File Processing……….
• Sequential file organisation….
– Records must be processed and accessed
sequentially
– It means when using sequential access to reach
a particular record, all records preceding it most
first be processed
– Efficient when the entire file or an appreciable
portion of the file must be processed together
– Processing data using sequential access referred
to as sequential file processing
DeSiaMore
www.desiamore.com/ifm
67
File Processing……….
• Advantages of Sequential file processing
– 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
DeSiaMore
www.desiamore.com/ifm
68
File Processing……….
• Disadvantages ..
– 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.
DeSiaMore
www.desiamore.com/ifm
69
Database Management Systems
(DBMS)
Abdallah Seleman
[email protected]
Block D,55
Consultation Hours
Tuesday & Wednesday
10:00 AM-12:00PM
DeSiaMore
www.desiamore.com/ifm
70
File Processing……….
• Advantages of Sequential file processing
– 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
DeSiaMore
www.desiamore.com/ifm
71
File Processing……….
• Disadvantages ..
– 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.
DeSiaMore
www.desiamore.com/ifm
72
File Processing……….
• Direct File Organisation
– 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
DeSiaMore
www.desiamore.com/ifm
73
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
74
File Processing……….
`
Terminal
CPU
1U
Figure Direct
4, Direct
File Processing
–Records
are accessed directly
Access
Storage Device
(DASD)
DeSiaMore
www.desiamore.com/ifm
75
File Processing……….
• Direct file processing….
– There are several strategies which are used to find record..
• Relative addressing
• Hashing (randomising)
• Indexing
• Relative addressing
– 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
DeSiaMore
www.desiamore.com/ifm
76
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
– 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
DeSiaMore
www.desiamore.com/ifm
77
File Processing……….
– 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
DeSiaMore
www.desiamore.com/ifm
78
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
– 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
DeSiaMore
www.desiamore.com/ifm
79
File Processing……….
• Disadvantages of direct file processing
– 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
DeSiaMore
www.desiamore.com/ifm
80
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
81
Data Processing
• Data processing comprises the following;
– Capturing of data
– Storing of data
– Updating and retrieving of data and information
• Data Management
– 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
DeSiaMore
www.desiamore.com/ifm
82
Data Management..
• Data management involves the following,
– Data administration
– The standards of defining data
– The way in which people perceive and use data
in their day-to-day activities
DeSiaMore
www.desiamore.com/ifm
83
Data Independence
• Data Independence
– Data independence allows a database to be
structurally changed , it means data can be;
• 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
DeSiaMore
www.desiamore.com/ifm
84
Data Independence….
• There are two distinct levels of data
independence;
– Logical data independence
– Physical data independence
• Logical data independence
– 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
DeSiaMore
85
Data independence….
• Physical data independence
– 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
DeSiaMore
www.desiamore.com/ifm
86
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
The DBA
controlsmodification
the database structure
and sets up the
for
creation,
and maintenance
of definition
the
physical
as well as logical implementation of the database.
database
DeSiaMore
www.desiamore.com/ifm
87
Database Administration Roles..
• Implementing Security Features,
– 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
DeSiaMore
www.desiamore.com/ifm
88
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
– The users of a database can be classified
depending on their degree of expertise or their
mode of interactions with the DBMS.
DeSiaMore
www.desiamore.com/ifm
89
Database Administration Roles…
• DBMS Users…..
– The user can be classified as..
•
•
•
•
Naïve Users
Online Users
Application Programmers
BDAs
• Naïve Users
– 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.
DeSiaMore
www.desiamore.com/ifm
90
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
91
Database Administration Roles…
• Online Users
– 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
DeSiaMore
www.desiamore.com/ifm
92
Database Administration Roles…
• Application programmers
– Professional programmers, who are responsible
for developing application programs or user
interface
• Database Administrator
– DBA is a knowledgeable person who is
responsible for the physical design and
management of the database
DeSiaMore
www.desiamore.com/ifm
93
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
94
Data Dictionary…..
• Importance of Data Dictionary
– 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
DeSiaMore
www.desiamore.com/ifm
95
Database Processing
Data Items
Relationships
Constraints
Database
Schema
Figure 1, Components of a Database
DeSiaMore
www.desiamore.com/ifm
96
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
97
Database Processing….
– 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
DeSiaMore
www.desiamore.com/ifm
98
Database processing…..
• Relationships
– Relationships represent a correspondence between the
various data elements
• Constraints
– Are predicates that define correct database states.
• Schema
– 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
DeSiaMore
www.desiamore.com/ifm
99
Database processing…..
• Schema…
– It separates the physical aspects of data storage from the
logical aspects of the representation
– As in figure 2,
• 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
DeSiaMore
www.desiamore.com/ifm
100
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
101
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
102
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
DeSiaMoredata is attempted.
www.desiamore.com/ifm
103
Database processing…..
– 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.
DeSiaMore
www.desiamore.com/ifm
104
Database processing…..
• Characteristics of Data in a Database
– 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
DeSiaMore
www.desiamore.com/ifm
105
Types of Database Language
• There are three types of database languages
– DDL ( Data Definition Language)
– DML (Data Manipulation Language)
– DCL (Data Control Language)
DeSiaMore
www.desiamore.com/ifm
106
Types of Database Language…
• DDL
– 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
DeSiaMore
www.desiamore.com/ifm
107
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 SQL commands that are used to create database objects
The physical
structure
of each
table on disk
are• known
as Data storage
Definition
Language
or DDL
DeSiaMore
www.desiamore.com/ifm
108
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
109
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
110
Any Questions
DeSiaMore
www.desiamore.com/ifm
111
Database Management Systems
(DBMS)
• Outline
–
–
–
–
–
Introduction to DBMS
Database Architecture
Database Management System
Why DBMS
Types of DBMS
DeSiaMore
www.desiamore.com/ifm
112