The Database Environment

&
Development Process

.
 Contents (Summary)
• Basic Concepts and Definitions
• Database Designing and Modelling
• Client-Server Architecture, Data Dictionaries
• SQL
– DDL,DML,Views ,Constraints
• Oracle SQL
• Distributed Database
• Object oriented database
• Database Administration
 Method of Delivery
• Text Books
• Reference Book/s
• Lecture Slides
• Lab work (SQL)
• Helping notes or Reference website
(email)
 Key to success
• Punctuality
• Hard work
• Practice
• Devotion
• Dedication
 Text Book
(1) Modern Database Management By:
Fred McFadden, Jeffrey Hoffer, Mary Prescott 11th Edition, Addison Wesley

(2) Database Systems Concepts By:

Silberschatz., Korth., Sudarshan 4th Edition
(3) Database Systems By:
Thomas Connolly, Carolyn Begg 4th Edition
(4) An Introduction to Database Systems By:
C.J.Date
What is Data ?
 Definition of Data
•Data consists of Facts, text, graphics,
images, sound and video segments that
have meaning to user’s environment.

•Data can exist in a variety of forms:

- As numbers or text on pieces of paper
- As bits and bytes stored in electronic
memory
- As facts stored in a person's mind.
 Data Type
Data is what we measure
Data type refers to the classification of a
particular type of information

• integer
• floating point
• character, string
• date
• student record
Data in Context

Large volume of facts, difficult

to interpret or make decisions
based on
 INFORMATION

• Data that has been

processed in such a way that
it can increase the
knowledge of person who
uses it
Useful information that managers can use for
decision making and interpretation
 Data vs Information
• Data into information
–Summarise data
–Data In context (table)
–Decision support
• We use Database term without
distinguish data or information
 Metadata
1.Data that describe the properties or
characteristics of end-user data and
the context of those data(where data is
stored, ownership and usage).
2.metadata describe the properties of data
but are separate from that data.
 Definition of Database
• A database is a collection of related data that is
shared among many users.
• It is carefully designed and built for a specific
purpose — for example, to help a company
manage its sales, customers, or products.
• The database is filled (populated) with data that
is meaningful for the organization, and it is set up
so that certain users (like employees, managers,
or systems) can access it to run the applications
or tasks they need, such as generating reports,
tracking inventory, or processing orders.
 Alternative Terminology
D a ta P ro c e ssi n g T e rm s

F o rm a l T e rm s A l te rn a ti v e 1 A l te rn a ti v e 2

R e l a ti o n T a b le F ile

T u p le Row R e c o rd

A ttri b u te C o lu m n F ie ld

• Formal terms are mostly used for technical users.

• Data processing terms are more natural for non
technical users.
 TRADITIONAL FILE
PROCESSING
SYSTEMS(Computerized File
Based Processing System)
 Computerized File-based
Processing

File handling routines &

File definition

Sales files
Sales

File handling routines &

File definition

Contracts files
Contracts
 File-based Systems

• A collection of application programs

that perform services for the end
users (e.g. reports).
• Each program defines and manages
its own data.
Three file processing systems

Duplicate
Data
 Disadvantages of File Processing
Systems
Separation and Isolation of data
• Each application maintains its own set of data.
• Users of one application may be unaware of potentially
useful data held by other applications.
Data redundancy and Inconsistency
• Duplication of data is held by different files.
• Limited Data Sharing
– Files are usually created for one specific program, so
it's hard for other programs or users to access or share
that data easily.
• No Centralized Control of Data
– There’s no central authority to manage or secure data,
which can lead to problems like unauthorized access or
data inconsistency.
• Lengthy Development Times
– Developers spend a lot of time writing code to manage
data storage, retrieval, and updates because there's no
built-in support like in modern databases.
• Programmers Must Design Their Own File Formats
– Every program has to define its own way of storing
data in files, making it harder to maintain and
understand.
• Excessive Program Maintenance
– Because of custom file formats and no shared
structure, it takes a lot of effort to maintain or update
programs.
• 80% of Information Systems Budget on Maintenance
– A large part of the IT budget (around 80%) goes into
keeping these systems running and fixing issues, leaving
little room for innovation.
 Problems with Data Dependency
• Each Application Programmer Must Maintain Their Own Data
– Every programmer has to handle and manage their own set of data
separately, which causes repetition and extra work.
• Each Application Program Needs to Include Code for the Metadata
of Each File
– Metadata (like file structure or field names) has to be written into every
program, increasing complexity and the chances of errors.
• Each Application Program Must Have Its Own Processing Routines
– Developers must write separate code for common operations like
reading, adding, updating, or deleting data, even if the actions are similar.
• Lack of Coordination and Central Control
– There’s no central system to manage or monitor data, so different
programs can create conflicts or duplicate data without realizing it.
• Non-Standard File Formats
– Each application might use its own unique file format, making it hard to
share or reuse data across systems.
 Problems with Data
Redundancy
• Waste of space to have duplicate data
• Causes more maintenance headaches
• The biggest Problem:
– When data changes in one file, could
cause inconsistencies
– Compromises data integrity
SOLUTION:

The Database
Approach
 Database Processing

Sales
Application

Sales
DBMS

Database
Contracts
Application

Contracts
 The DATABASE Approach
• Central repository of shared data
• Data is managed by a controlling
agent
• Stored in a standardized,
convenient form

Requires a Database Management System (DBMS)

 Database Management
System
Application
#1

Application
#2

DBMS Database
containing
centralized
shared data

Application
#3 DBMS manages data
resources like an operating
system manages hardware
resources
 Database Management System
(DBMS)

A collection of programs that enables users to define, create and

maintain and control access to the database.
General-purpose software system that facilitates the processes of
defining, constructing and manipulating databases for various
applications.

Defining - constraints.(Setting up rules and limits for the

database (like what data is allowed and how it's organized).
Constructing - storing of data specifying data types,
structures(Storing data in a structured way, including deciding the
type of data and how it’s stored.)
Manipulation - querying, updating and reporting.(Interacting with the
data (e.g., searching, updating, or generating reports).)
Popular DBMS In The Market

Sybase SQL Anywhere

Informix Dynamic Server

Borland Interbase
Advantages of Database Approach
• Program-Data Independence: The DBMS keeps metadata
(data about the data) so that programs don't need to worry
about how the data is stored or formatted. This allows
changes to the data without affecting the programs.
• Benefit: It makes developing and maintaining applications
faster and easier.
• Minimal Data Redundancy: The DBMS reduces duplication
of data, meaning the same information isn’t stored in multiple
places.
• Benefit: This leads to more consistent and accurate data,
reducing errors and ensuring integrity.
 Advantages of Database Approach
• Improved Data Sharing
– Different users get different views of the data
• Enforcement of Standards
– All data access is done in the same way
• Improved Data Quality
– Constraints, data validation rules
• Better Data Accessibility/
• Responsiveness
– Use of standard data query language (SQL)
 Continued…
• Security, Backup/Recovery, Concurrency
• Disaster recovery is easier
• Reduced program maintenance
• Improved decision support
 Costs and Risks of the
Database Approach
• Up-front costs:
– Installation Management Cost and Complexity
– Conversion Costs(The cost of moving from an old
system to the new DBMS.)
– Cost of DBMS
– Additional hardware costs.
Ongoing Costs:
– Requires New, Specialized Personnel
– Need for Explicit Backup and Recovery
– Higher impact of a failure
• Organizational Conflict:
– Old habits die hard
Components of the database
environment
 Components of the
Database Environment
 Data modeling and design tools – CASE tool automated
tools used to design databases and application programs
 Repository–centralized storehouse of metadata
 Database Management System (DBMS) –software for
managing the database
 Database–storehouse of the data
 Application Programs–software using the data
 User Interface–text, graphical displays, menus, etc. for
user
 Data/Database Administrators–personnel responsible for
maintaining the database
 System Developers–personnel responsible for designing
databases and software
 End Users–people who use the applications and
databases
 DBMS Environment
Bridge
Data

Software Procedures
Hardware People
Machine Human
 Database System
Environment
Users/Programs

Application Programs/Queries

Software to Process
Queries/Programs

Software to Access Stored Data

Stored Database Stored

Definition Database
 The Range of
Database Applications
• Personal Database – standalone desktop
database
• Workgroup Database – local area network
(<25 users)
• Department Database – local area
network (25-100 users)
• Enterprise Database – wide-area network
(hundreds or thousands of users)
Personal
Computer
Database
Workgroup database with local area network
An
enterprise
data
warehouse
 The Database Development
Process
SDLC
• System Development Life Cycle

• Detailed, well-planned development process

• Time-consuming, but comprehensive

• Long development cycle

Planning

Analysis

Logical Design

Physical Design

Implementation

Maintenance
 Systems Development Life Cycle
(see also Figure 1-7) (cont.)
Planning
Planning Purpose–preliminary understanding
Deliverable–request for study
Analysis

Logical Design

Physical Design

Database activity– Implementation

enterprise modeling and
early conceptual data
Maintenance
modeling
 Systems Development Life Cycle
(see also Figure 1-7) (cont.)
Purpose–thorough requirements analysis and
Planning structuring
Deliverable–functional system specifications
Analysis
Analysis

Logical Design

Physical Design

Database activity–thorough Implementation

and integrated conceptual
data modeling
Maintenance
 Systems Development Life Cycle
(see also Figure 1-7) (cont.)
Purpose–information requirements elicitation
Planning and structure
Deliverable–detailed design specifications
Analysis

Logical Design
Logical Design

Physical Design

Database activity– Implementation

logical database design
(transactions, forms,
Maintenance
displays, views, data
integrity and security)
 Systems Development Life Cycle
(see also Figure 1-7) (cont.)
Purpose–develop technology and
Planning organizational specifications

Analysis
Deliverable–program/data
structures, technology purchases,
organization redesigns
Logical Design

Physical Design
Physical Design

Database activity– Implementation

physical database design (define
database to DBMS, physical
Maintenance
data organization, database
processing programs)
 Systems Development Life Cycle
(see also Figure 1-7) (cont.)
Purpose–programming, testing,
Planning training, installation, documenting

Analysis Deliverable–operational programs,

documentation, training materials
Logical Design

Physical Design

Database activity–
database implementation, Implementation
Implementation
including coded programs,
documentation, Maintenance
installation and conversion
 Systems Development Life Cycle
(see also Figure 1-7) (cont.)
Planning Purpose–monitor, repair, enhance

Deliverable–periodic audits
Analysis

Logical Design

Physical Design

Database activity–
database maintenance, Implementation
performance analysis
and tuning, error Maintenance
Maintenance
corrections
 Summary
• Course Contents
• Data
• Information
• Data Vs Information
• Database Terms
• DBMS
• Advantages
• Disadvantages of DBMS
• Database Terms
• Comparison of Computerized File Based Processing
System & Database System