DATABASE

EVOLUTION &

ENVIRONMENT

Chandra S. Amaravadi

PREVIOUSLY, IN INTRODUCTION

Database importance

Basic data organization concepts

Database concept

Development cycle

Example application

In our introduction we discussed the following:

IN THIS DISCUSSION Evolution

The database approach

Database architecture

DBMS architecture

DBMS environment

DBMS EVOLUTION

Introduction of business computers in 1950’s, ‘60’s

Used in TP applications (file processing)

Led to problems of file processing (see next)

Academic formulations of DBMS concepts

Standardization of DBMS concepts (by CODASYL 1971)

Hierarchical and network databases

EVOLUTION OF DBMS

DBMS evolved out of file processing environment:

FILE PROCESSING APPROACH

File processing evolved from transaction environment of the 1950’s. It involves application managing its own files.

Application1Application1

File1File1

Application2Application2

File2File2

EVOLUTION OF DBMS..

Following are the problems caused by file processing:

Uncontrolled redundancy

Program data dependence

Program maintenance

Poor data quality

Inability to get reports

Application backlog

EVOLUTION OF DBMS..PROBLEMS CAUSED BY FILE PROCESSING

Uncontrolled redundancy – Same data is duplicated in multiple files

Program data dependence – Application programs dependent on

structure of the data

Program maintenance – Too much effort spent in changing programs

to accommodate changes in file structures.

Poor data quality – Redundancy can often lead to inconsistent

updates of the data, leading in turn to problems of data quality.

Inability to get reports – Since each file was tied to a particular

program, it was hard to get reports involving multiple files

Application backlog – Managers’ requests for report led application

programmers to write new applications to fulfill the request. They

were typically behind in fulfilling the requests, hence

the backlog.

ApplicationsApplications

File handling File handling

routinesroutines

FilesFiles

ApplicationsApplications

DBMSDBMS

FilesFiles

Application +Application +

Data managementData management

FilesFiles

FROM FILE PROCESSING TO DBMS..

EVOLUTION OF DBMS.. (FYI)Developments in DBMS include:

Codd’s PaperIn 1970

Ingres in 1971 Oracle in 1977

Britton Lee Founded in 1979

Sybase in 1986

Teradata corp.

SAP

EVOLUTION OF DBMS.. (FYI)

THE DBMS/DB APPROACH

THE DBMS/DB APPROACH

cust.cust.

emp.emp.

Enterprise

Entity classes

Data model

Cust.

Emp.

Data base

THE DBMS/DB APPROACH

Integrated conceptualization of the data (??)

Organized design of database

Centralized management of data

System controlled access

Checks on data quality

Retrieve data easily (reports, queries)

Application1Application1

File1File1

Application2Application2

File2File2

GETTING INFORMATION

FROM A DATABASE

GETTING INFORMATIONIN FILE PROCESSING

CUSTOMER ACCTS. LOANS

CROSS REFERENCE

REPORT

ACCT. DATA LOAN DATA

GETTING INFORMATIONIN DATABASE APPROACH

Reports

Queries [SQL, QBE]

Views [forms that display]

Data from the database can be obtained through:

GETTING INFORMATIONIN DATABASE APPROACH

ACCT

LOAN

How can we get Acct &Loan info. inone report?

ACCT # NAME DT. OPENED BALANCE

8895 Smith 4/16/11 $35,000

8896 Farley 4/22/07 $300

8897 Gomez 1/10/09 $2000

LID AMOUT INT. RATE BALANCE

9978 $6,000 6.0% $2,440

9979 $5,000 7.1% $5,000

9992 $1,000 8.5% $400

GETTING INFORMATION FROM MULTIPLE TABLES

Two rules for multi-table SQL

1. In the Select part, precede each attribute name by name of table e.g. Select Acct.name, Loan.LID

2. In the Where part, equate values of common key from both tables e.g. ..where Acct.Acct# = Loan.Acct#

Write a query to list Name, balance, loan amt and balance for all customers.

When data is retrieved from multiple tables, thefollowing are the rules for writing queries:

DATABASE & DBMS

ARCHITECTURE

THREE-SCHEMA ARCHITECTURE

A prescription for how data should be stored

(in a macro sense)

Provides benefits of logical and physical

independence

Lacking in file processing approach

*Standards Planning and Requirements Committee

An architecture for databases introduced by ANSI/SPARC*

Conceptual/

Base table (schema)

THE THREE FORMS OF DATA

External (view)

Internal/

Hardware level

(file organization)

111000110

11101001

11101001

THE THREE FORMS OF DATA..

External/ --- The view of data as seen by a user/

application program (views). Conceptual/ --- The view as seen by a database

designer (base table)

Internal --- The view of data as it is stored

internallyThese three levels provide logical and physical data independence, the

ability to change the structure of the data and the ability to change the

internal storage structure, independently of the application program.

THREE-SCHEMA ARCHITECTURE..

BaseBaseTableTable

BaseBaseTableTable

BaseBaseTableTable

View1View1 View2View2External

Conceptual/

Logical

Physical/

Internal

Data is organized at three levels to provide logical/physical ______ ________________.

THREE-SCHEMA ARCHITECTURE..

Basic concept is that of a view

A view is the way data is presented

It is a subset of the data

The data resides in base tables

A base table contains information about an Eclass

Applns. access data via views

Views are created in SQL or by forms/reports

Create table

Alter table

Create index

drop index

DBMS Facilities

ConceptualConceptualConceptualConceptual

InternalInternalInternalInternal

ExternalExternalExternalExternal

Models

Schemas

File

Organizations

Views

THE THREE TYPES OF MODELS

Create view

Drop view

___________ , ___________ and ____ __________ are examples of modelscorresponding to the three levels of the three schema architecture.

D B M S KernelD B M S Kernel

D B M S KernelD B M S Kernel

MAJOR COMPONENTS OF DBMS

Export/ImportExport/Import

Data Defn.Data Defn. SQLSQL

Prog. Language Interface

Prog. Language Interface

DataDiction-

ary

DataDiction-

ary

Screen/ReportGen.

Screen/ReportGen.

Appln.Gen.

Appln.Gen.

DBMS COMPONENTS..Data definition – the facility through which schema is defined. (how new tables are created).SQL interface – the facility through which SQL commands are typed in.Programming language interface – the facility which processes SQL commands embedded in application program. Also known

as the host language interface. Data dictionary – the facility that records details about the schema, reports, data entry forms etc.Screen & reports- the facility through which data entry screens and reports are created.Appln. Generation- the facility through which applications are created.Export/Import -- the facility through which files can be imported/exported in different DBMS formats.DBMS Kernel -- the actual programs which interact with the O/S and carry out data I/O. ODBC -- Open Database Connectivity – middleware to take SQL commands & return data.

THE DIFFERENT CLASSES OF USERS IN A DBMS

UsersDevelopersAdministrators

DBMS

DataAdministration

DBMSDBMSClientClient

DBMSDBMSClientClient

LegacyLegacyApplicationsApplications

LegacyLegacyApplicationsApplications

EnterpriseEnterpriseApplicationsApplications

EnterpriseEnterpriseApplicationsApplications

DBMS/SQLServer

AN INTEGRATED DATABASE ENVIRONMENT

Developers End Users

Dir.Dir.ServerServer

Database

Directory server- a component that stores user names & PW (aka active directory or LDAP).

DBMS client- a front end to provide access to DBMS functionality.

Enterprise applications- various information systems of the organization.

Legacy applications – older, mainframe-based applications.

DBMS Server - a DBMS that can run queries (does not have user interface, only program interface).

Database- collection of information

COMPONENTS OF AN INTEGRATED DATABASE ENVIRONMENT

DIFFERENT TYPES OF ENTERPRISE DATABASE ENVIRONMENTS

Single tier, single user, direct DBMS access

SERVER

CLIENT Two tier, multi-user, client server

SERVER

CLIENT

Middleware

three tier, multi-user, client server with middleware

SINGLE

USER

DataWarehouse

OLAP

Decisions

Data Mining

InternalDatabase

InternalDatabase

ExternalDatabase

DATA WAREHOUSES, OLAP & DATA MINING

Data warehousing refers to the use of high speed/high capacity servers to store historical data and to make this available to decision makers.

OLAP is the process of analyzing historical data on a PC using mult-dimensional databases (i.e. non-relational databases) using aggregate data operations.

Data mining refers to identification of patterns from data.

Business Intelligence

What organization standardized DBMS concepts?

Who was the chief architect of relational systems?

What data-related functions were performed in the

file processing approach?

What are some of the basic features of a DBMS?

What is the smallest unit of data in a database?

Define the terms: schema, view, database, three-schema

architecture.

How is redundancy minimized in the database approach?

What are advantages of the 3-schema architecture?

What is the difference between 2-tier & 3-tier environments?

DISCUSSION