Transcript Outline at a Glance

Database Management Systems
(DBMS)
www.infoposter.co.tz
Computing and Information Technology Dept.
The Institute of Finance Management
DeSiaMore
Powered by DeSiaMore
1
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
Powered by DeSiaMore
2
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
Powered by DeSiaMore
3
Outline
–Fundamentals of Database
–File Processing
–Advantages & Disadvantages of
File Process Approach
–Data Processing
–Data Management
–Data Independence
–Data Administration Roles
DeSiaMore
Powered by DeSiaMore
4
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%
Powered by DeSiaMore
5
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
Powered by DeSiaMore
6
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
Powered by DeSiaMore
Figure
1 Illustrates the concepts
of Fields, Records, and Files7
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
Powered by DeSiaMore
8
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
Powered by DeSiaMore
9
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
Powered
by DeSiaMore
Figure
2 illustrates the concepts
of Fields,
Records, Files and Database 10
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
Powered by DeSiaMore
11
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
Powered by DeSiaMore
12
File Processing
• Data, Information, Knowledge
DATA

INFORMATION

KNOWLEDGE
INFERENCES
FORECASTS
DECISIONS
DeSiaMore
Powered by DeSiaMore
13
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
Powered by DeSiaMore
14
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
Powered by DeSiaMore
15
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
Powered by DeSiaMore
16
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
Powered by DeSiaMore
17
File Processing……….
Input
(Data)
Process
(Models)
Output
(Information)
Data
Captured
Database
Results
Action
Decision
Figure 3, Information Cycle
DeSiaMore
Powered by DeSiaMore
18
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
Powered by DeSiaMore
19
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 manipulate it.
DeSiaMore
Powered by DeSiaMore
20
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
Powered by DeSiaMore
21
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
Powered by DeSiaMore
22
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
Powered by DeSiaMore
23
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
Powered by DeSiaMore
24
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 dayto-day activities like money withdrawals,
deposits and transfers.
DeSiaMore
Powered by DeSiaMore
25
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
Powered by DeSiaMore
26
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
Powered by DeSiaMore
27
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
Powered by DeSiaMore
28
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
Powered by DeSiaMore
29
File Processing……….
• File organisation…..
– Two types of file organisation are
• Sequential file organisation and
• Direct file organisation
– Sequential file organisation
• 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
DeSiaMore
Powered by DeSiaMore
30
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
Powered by DeSiaMore
31
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
Powered by DeSiaMore
32
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
Powered by DeSiaMore
33
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
Powered by DeSiaMore
34