Transcript Introduction to Database

IS 4420
Database Fundamentals
Chapter 9:
The Client/Server Database
Environment
Leon Chen
Overview




Explain three application components:
presentation, processing, and storage
Distinguish between file server, database server,
3-tier, and n-tier approaches
Middleware and ODBC
Microsoft Access


Query-by-example (QBE)
VBA
2
clients
Server
Client / Server Architecture
3
Client/Server Systems





Networked computing model
Processes distributed between clients and
servers
Client – Workstation (usually a PC) that
requests and uses a service
Server – Computer (PC/mini/mainframe)
that provides a service
For DBMS, server is a database server
4
Application Logic in
Client/Sever Systems
Presentation Logic


Input – keyboard/mouse
Output – monitor/printer
GUI Interface
Processing Logic



I/O processing
Business rules
Data management
Procedures, functions,
programs
Storage Logic

Data storage/retrieval
DBMS activities
5
Client/Server Architectures
Client does
extensive processing

File Server Architecture

Database Server Architecture

Three-tier Architecture
Client does little
processing
6
File Server Architecture
FAT CLIENT
7
File Server Architecture



All processing is done at the PC that requested
the data
FAT CLIENT
Entire files are transferred from the server to the
client for processing
Problems:


Huge amount of data transfer on the network
Each client must contain full DBMS
•
•
Heavy resource demand on clients
Client DBMSs must recognize shared locks, integrity checks,
etc.
8
Thinner
clients
DBMS only
on server
Two-tier database server architecture
9
Two-Tier Database Server
Architectures

Client is responsible for
 I/O
processing logic
 Some business rules logic

Server performs all data storage and
access processing
 DBMS is only on server
10
Advantages of Two-Tier Approach
Clients do not have to be as powerful
 Greatly reduces data traffic on the
network
 Improved data integrity since it is all
processed centrally
 Stored procedures  some business
rules done on server

11
Advantages of
Stored Procedures
 Compiled
SQL statements
 Reduced network traffic
 Improved security
 Improved data integrity
 Thinner clients
12
Three-tier Architecture
Thinnest clients
Business rules on
application server
DBMS only on
Database server
13
Three-Tier Architectures
Client
GUI interface
(I/O processing)
Browser
Application server
Business rules
Web Server
Database server
Data storage
DBMS
Thin Client

PC just for user interface and a little application
processing. Limited or no data storage (sometimes no
hard drive)
14
Advantages of Three-Tier
Architectures







Scalability
Technological flexibility
Long-term cost reduction
Better match of systems to business needs
Improved customer service
Competitive advantage
Reduced risk
15
Challenges of Three-tier
Architectures
 High
short-term costs
 Tools and training
 Experience
 Incompatible standards
 Lack of compatible end-user tools
16
Application Partitioning


Placing portions of the application code in
different locations (client vs. server)
Advantages
Improved performance
 Improved interoperability
 Balanced workloads

17
Parallel Computer Architectures

Tightly Coupled
Symmetric Multiprocessing (SMP)
 Multiple CPUs
 Shared RAM


Loosely Coupled
Massively Parallel Processing (MPP)
 Multiple CPUs
 Each CPU has its own RAM space

18
Middleware



Software which allows an application to
interoperate with other software
No need for programmer/user to
understand internal processing
Accomplished via Application Program
Interface (API)
19
Types of Middleware

Remote Procedure Calls (RPC)



Message-Oriented Middleware (MOM)


push technology  server sends information to client when
available
Object Request Broker (ORB)


asynchronous calls between the client via message queues
Publish/Subscribe


client makes calls to procedures running on remote computers
synchronous and asynchronous
object-oriented management of communications between clients
and servers
SQL-oriented Data Access

middleware between applications and database servers
20
Database Middleware

ODBC – Open Database Connectivity


OLE-DB



Most DB vendors support this
OLE –Object Linking and Embedding
Microsoft enhancement of ODBC
JDBC – Java Database Connectivity

Special Java classes that allow Java
applications/applets to connect to databases
21
Using ODBC to Link External Databases
Stored on a Database Server

Open Database Connectivity (ODBC)


Required parameters:





API that provides a common language for application
programs to access and process SQL databases independent
of the particular RDBMS that is accessed
ODBC driver
Back-end server name
Database name
User id and password
Additional information:



Data source name (DSN)
Windows client computer name
Client application program’s executable name
Java Database Connectivity (JDBC) is similar to ODBC,
uut built specifically for Java applications
22
ODBC Architecture
Microsoft
Access
Oracle
ODBC
driver
Oracle9i
23
Client with Microsoft Access
ODBC
Unix server with Oracle9i
24
Microsoft Access Introduction –
Excerpted from Wikipedia





Microsoft Access is a relational database management
system from Microsoft, packaged with Microsoft Office
Professional which combines the relational Microsoft Jet
Database Engine with a graphical user interface.
It can use data stored in Access/Jet, SQL Server, Oracle, or
any ODBC-compliant data container.
Some professional application developers use Access for
rapid application development (RAD), especially for the
creation of prototypes and standalone applications
Skilled software developers and data architects use it to
develop powerful, complex applications.
Relatively unskilled programmers and non-programmer
"power users" can use it to build simple applications
without having to deal with features they don't understand.
25
Microsoft Access Introduction –
Excerpted from Wikipedia (cont.)


Access does not scale well if data access is via a network,
so applications that are used by more than a handful of
people tend to rely on a Client-Server based solution such
as Oracle, DB2, Microsoft SQL Server, PostgreSQL, MySQL,
or MaxDB.
However, an Access "front end" (the forms, reports,
queries and VB code) can be used against a host of
database backends, including Access itself, SQL Server,
Oracle, and any other ODBC-compliant product.
26
Microsoft Access usability hierarchy
27
Query-by-Example (QBE)






Direct-manipulation database language
Graphical approach
Available in MS Access
MS Access translates QBE to SQL and vice
versa
Useful for end-user database programming
Good for ad hoc processing and prototyping
28
QBE view of a multiple-table join query
29
View SQL Code of QBE
30
Visual Basic for Applications


VBA is the programming language that
accompanies Access
VBA provides these features (that
Access Macro might not have):








Ability to perform complex functionality
Error handling
Faster execution than macros
Easier maintenance
OLE automation
Programmatic control
Ease of reading for programmers
Event-driven – nonprocedural programming that
detects events and generates appropriate responses
31