Transcript Database basics

DATABASE
EVOLUTION &
ENVIRONMENT
Chandra S. Amaravadi
PREVIOUSLY, IN INTRODUCTION
In our introduction we discussed the following:
Database importance
Basic data organization concepts
Database concept
Development cycle
Example application
IN THIS DISCUSSION
Evolution
The database approach
Database architecture
DBMS architecture
DBMS environment
DBMS EVOLUTION
EVOLUTION OF DBMS
DBMS evolved out of file processing environment:
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
FILE PROCESSING APPROACH
File processing evolved from transaction
environment of the 1950’s. It involves application
managing its own files.
Application1
File1
Application2
File2
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.
FROM FILE PROCESSING TO DBMS..
Application +
Data management
Files
Applications
Applications
File handling
routines
DBMS
Files
Files
EVOLUTION OF DBMS.. (FYI)
Developments in DBMS include:
Codd’s Paper
In 1970
Ingres in 1971
Oracle in 1977
Britton Lee
Founded in
1979
Sybase in
1986
System R
Teradata corp.
SAP
EVOLUTION OF DBMS.. (FYI)
THE DBMS/DB APPROACH
THE DBMS/DB APPROACH
Entity classes
Emp.
cust.
Cust.
emp.
Enterprise
Data model
Data base
THE DBMS/DB APPROACH
Integrated conceptualization of the data (??)
Organized design of database
Centralized management of data
Checks on data quality
System controlled access
Retrieve data easily (reports, queries)
Application1
Application2
Application
DBMS
File1
File2
Files
Application
GETTING INFORMATION
FROM A DATABASE
GETTING INFORMATION
IN FILE PROCESSING
CROSS REFERENCE
REPORT
CUSTOMER ACCTS.
LOANS
ACCT. DATA
LOAN DATA
GETTING INFORMATION
IN DATABASE APPROACH
Data from the database can be obtained through:
Reports
Queries [SQL, QBE]
Forms
“Views”
GETTING INFORMATION
IN DATABASE APPROACH
ACCT
ACCT #
NAME
DT. OPENED
BALANCE
8895
Moody
4/16/13
$35,000
8896
Leak
4/22/14
$300
8897
Boring
1/10/16
$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
LOAN
How can we
get Acct &
Loan info. in
one report?
GETTING INFORMATION FROM
MULTIPLE TABLES
Rules for multi-table Select
When data is retrieved from multiple tables, the
following are the rules for writing queries:
1. In the Select part, precede each attribute name by
name of table e.g. Select Acct.name, Loan.LID
2. In the From part, list tables separated by commas.
3. 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.
DATABASE & DBMS
ARCHITECTURE
THREE-SCHEMA ARCHITECTURE
An architecture for databases introduced by ANSI/SPARC*
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
THE THREE FORMS OF DATA
External (view)
Conceptual/
Base table (schema)
11101001
Internal/
Hardware level
(file organization)
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
internally
These 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..
External
Conceptual/
Logical
Base
Table
View1
View2
Base
Table
Base
Table
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
THE THREE TYPES OF MODELS
External
Conceptual
Internal
Views
Create view
Drop view
Schemas
Create table
Alter table
File
Organizations
Models
Create index
drop index
DBMS Facilities
___________ , ___________ and ____ __________ are examples of models
corresponding to the three levels of the three schema architecture.
MAJOR COMPONENTS OF DBMS
D B M S Kernel
Data
Defn.
SQL
Prog.
Language
Interface
Data
Dictionary
Screen/
Report
Gen.
Appln.
Gen.
D B M S Kernel
Export/Import
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
Administrators
Developers
DBMS
Users
AN INTEGRATED DATABASE ENVIRONMENT
Data
Administration
Legacy
Applications
Dir.
Server
Developers
End Users
DBMS
Client
Enterprise
Applications
DBMS/SQL
Server
Database
COMPONENTS OF AN INTEGRATED
DATABASE ENVIRONMENT
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
DIFFERENT TYPES OF ENTERPRISE
DATABASE ENVIRONMENTS
SINGLE
Single tier, single user, direct DBMS access
USER
CLIENT
Two tier, multi-user, client server
SERVER
CLIENT
three tier, multi-user, client server
with middleware
Middleware
SERVER
DATA WAREHOUSES, OLAP & DATA MINING
Internal
Database
OLAP
Internal
Database
Data
Warehouse
Decisions
Data Mining
Business
Intelligence
External
Database
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.
DISCUSSION
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?
REVIEW OF CONCEPTS
Concept
File processing
Description
Application programs managing their own files.
Internal view
The view of data as seen by a user/application
program(views).
The view as seen by a database designer (base
table).
The view of data as it is stored internally
Select
It is used to select data from a database.
From
It is used to list the tables.
Where
It is used to filter the results and apply conditions.
Table
A collection of records
Schema
The structure of a file or a database.
Data mining
Identification of patterns from data.
Data warehousing
Use of high speed servers to store historical data.
OLAP
A method of analyzing historical data
External view
Conceptual view
REVIEW OF CONCEPTS..
Concept
Description
Single tier
Database is directly accessed from a PC
Two tier
Client server architecture
Three tier
A middleware performs query translation
Directory server
A server that is used for authentication
Middleware
Software that performs middle-man functions
Legacy applications
Older mainframe applications
DBMS server
A server that can respond to queries
Enterprise applications
Newer applications of the organization