INTRODUCTION TO DATABASE
1 SYSTEM
Doreen Mango
Phone:+254-722-171-281
Email: doreen.mango@cuk.ac.ke
Recommended Books
A First Course in Database Systems (Ullman)
Fundamentals of Database Systems (Elmasri)
Database Systems (Atzeni et al). Heavy on Theory
Foundations of Databases (Abiteboul, Hull and Vianu). Heavy on theory
Database Management Systems (Ramakrishnan)
Any books on SQL
Using SQL (Colburn)
SQL for dummies
SQL in 21 Days
Module Outline (theory)
Topic 1: Introduction
Topic 2: Database environment
Topic 3: Entity relationship model(ERD)
Topic 4: Normalization
Topic 5: Database security
Module Outline (practical)
Week 6: Introduction to SQL
File based approach
GENERAL PERSONNEL PAYROLL
LEDGER
File File File
one file per
PRODUCTION INVENTORY
PLANNING application
no data sharing
File File
ORDER
INVOICING DESPATCH
ENTRY
File File File
File based approach
GENERAL PERSONNEL PAYROLL
LEDGER
File File File
cross-application
PRODUCTION INVENTORY
PLANNING transfers difficult to
manage
File File
ORDER
INVOICING DESPATCH
ENTRY
File File File
File based approach
GENERAL PERSONNEL PAYROLL
LEDGER
File File File
same data , e.g. name &
PRODUCTION address, stored in many INVENTORY
PLANNING places
File File
ORDER
INVOICING DESPATCH
ENTRY
File File File
File based approach
GENERAL PERSONNEL PAYROLL
LEDGER
File File File
PRODUCTION The solution? INVENTORY
PLANNING
Put all of the data in one
place.
File File
ORDER
INVOICING DESPATCH
ENTRY
File File File
File based approach
GENERAL PERSONNEL PAYROLL
LEDGER
PRODUCTION FileFileFile INVENTORY
PLANNING File File
FileFileFile
ORDER
INVOICING DESPATCH
ENTRY
Draw backs of file
Drawbacks of based approach
file systems
Program-Data Dependence
All programs maintain metadata for each file they use
Data Redundancy (Duplication of data)
Different systems/programs have separate copies of the same
data
Multiple file formats, duplication of information in different
files
Requires space, effort and result in loss of data & metadata
integrity
Limited Data Sharing
No centralized control of data
Each application has its own private files & users has little
chance to share data outside their own applications
Draw backs of file based approach
Lengthy Development Times
For each new application programmers must design their own
file formats & descriptions from scratch
Excessive Program Maintenance
80% of information systems budget
Difficulty in accessing data
Need to write a new program to carry out each new task
Integrity problems
Integrity constraints
Hard to add new constraints or change existing ones
Draw backs of file based approach
GENERAL PERSONNEL PAYROLL
LEDGER
PRODUCTION
PLANNING DATABASE INVENTORY
ORDER
INVOICING DESPATCH
ENTRY
A Database Management System (DBMS)
Elements of a Database
Name Product Ordered Price
Record 1 Fred Smith Canned Peas 4 dozen £2.00
Record 2 Mary Jones Broccoli 0.5Kg £1.30
Record 3 Tom Spencer Canned Peas 3 dozen £1.50
Record 4 Jill Jackson Oranges 7 £3.50
Field Field Field Field
File – a collection of related data entities,
e.g. a collection of orders.
Records - Individual data entities,
e.g. the order from Mary Jones.
Fields - Specific data items are stored in,
e.g. the customer’s name, product
Database Industry
DBMS companies are among the largest software
companies in the world. Oracle Corporation is the
second largest software company worldwide.
IBM (with DB2) and Microsoft (SQL Server,
Microsoft Access) are also important players.
Microsoft reportedly sells 1 million Access licenses
per month
Challenged by object oriented DBMS.
Some Major DBMSs
Access
Oracle
Ingres
MSSQL
MySQL
Informix
Sybase
DB2
Database
DatabaseEnvironment
Database Environment
Environment
DBMS
DBMS Functionality
Functionality
Defining a particular database in terms of its data types,
structures, and constraints
Constructing or Loading the initial database contents on a
secondary storage medium
Manipulating the database:
Retrieval: Querying, generating reports
Modification: Insertions, deletions and updates to its
content
Accessing the database through Web applications
Processing and Sharing by a set of concurrent users and
application programs – yet, keeping all data valid and
consistent
Main
Main Characteristics
Characteristics of
of the
the Database
DatabaseApproach
Approach
Self-describing nature of a database system:.
program-data independence.
Data abstraction
Support multiple view of the data
Sharing of data and multi-user transaction processing
Advantages
Advantages of
of Database
DatabaseApproach
Approach
Controlling redundancy in data storage and in
development and maintenance efforts.
Sharing of data among multiple users.
Restricting unauthorized access to data.
Providing Storage Structures (e.g. indexes) for efficient
Query Processing
Providing backup and recovery services.
Providing multiple interfaces to different classes of
users.
Representing complex relationships among data.
Enforcing integrity constraints on the database.
Advantages
Advantages of
of Database
DatabaseApproach
Approach
Potential for enforcing standards:
This is very crucial for the success of database
applications in large organizations. Standards refer to
data item names, display formats, screens, report
structures, meta-data (description of data), Web page
layouts, etc.
Reduced application development time:
Incremental time to add each new application is reduced.
Slide 1- 20
Database
Database Users
Users
Divided into two
Those who actually use and control the database
content, and those who design, develop and
maintain database apps (called “Actors on the
Scene”)
Those who design and develop the DBMS software
and related tools, and the computer systems
operators (called “Workers Behind the Scene”).
Actors
Actors on
on Scene
Scene
Database administrators:
Responsible for authorizing access to the database, for
coordinating and monitoring its use, acquiring software
and hardware resources, controlling its use and
monitoring efficiency of operations.
Database Designers:
Responsible to define the content, the structure, the
constraints, and functions or transactions against the
database. They must communicate with the end-users
and understand their needs.
Actors
Actors on
on Scene
Scene
End-users: They use the data for queries, reports and
some of them update the database content. End-users can
be categorized into:
Casual: access database occasionally when needed
Naïve or Parametric: they make up a large section of
the end-user population.
They use previously well-defined functions in the
form of “canned transactions” against the database.
Examples are bank-tellers or reservation clerks who
do this activity for an entire shift of operations.
Database Application
DB apps Programs
DB apps
A program which interacts with the database by issuing an
appropriate request usually an SQL statement to the DBMS.
Can be conventional batch applications/ online applications.
Can be written in some programming language or in some
higher- level fourth- generation language.
Components of the DBMS
Components
Components of Environment
of the
the DBMS
DBMS
There are five major components in the DBMS environment
i.e. Hardware
hardware
software
Data
Procedures
People (human).
hardware
hardware components
components
Can be a pc/mainframe or n/w of computers.
Choice depends on the needs of a particular and the
DBMS used.
Some DBMSs run only on particular hardware or
operating systems, while others run on a wide variety
of hardware and operating systems.
A DBMS requires a minimum amount of main memory
and disk space to run, but this minimum configuration
may not necessarily give acceptable performance.
Software
Software
Software components
components
DBMS, apps prog, OS and n/w sw if any.
Apps re written in a using 3GL languages such as C, C +
+, java, VB e.tc and 4GL languages such as SQL,
embedded in a third-generation language.
Target DBMS may have its own fourth-generation tools
that allow rapid development of applications.
Data
Data
Most important component of the DBMS environment
from the end users’ point of view since it acts as a
bridge between the machine components and the
human components.
A database contains the operational data and the
metadata.
The data also incorporates the system catalog.
Procedure
Procedures
Procedure
Instructions and rules that govern and design and use of
the database.
Made by user and the staff that manage the database
require
These may consist of instructions on how to:
Log on to the DBMS;
Use a particular DBMS facility or application program;
Start and stop the DBMS;
Make backup copies of the database;
Handle hardware or software failures.
Change the structure of a table, reorganize the
database across multiple disks, improve performance,
Procedure
Procedure
End of chapter one
Next chapter,Database environment
Thank you for your attention !!!
