Chapter 9:
The Client/Server Database
Environment
Modern Database Management
7th Edition
Jeffrey A. Hoffer, Mary B. Prescott,
Fred R. McFadden
© 2005 by Prentice Hall 1
Objectives
Definition of terms
List advantages of client/server architecture
Explain three application components: presentation,
processing, and storage
Suggest partitioning possibilities
Distinguish between file server, database server, 3-tier,
and n-tier approaches
Describe and discuss middleware
Explain query-by-example (QBE)
Explain database linking via ODBC and JDBC
Explain VBA and Microsoft Access
Chapter 9 © 2005 by Prentice Hall 2
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
Chapter 9 © 2005 by Prentice Hall 3
Application Logic in C/S Systems
Presentation Logic
Input – keyboard/mouse
GUI Interface
Output – monitor/printer
Processing Logic
I/O processing Procedures, functions,
Business rules programs
Data management
Storage Logic
Data storage/retrieval DBMS activities
Chapter 9 © 2005 by Prentice Hall 4
Client/Server Architectures
Client does
extensive processing
File Server Architecture
Database Server Architecture
Three-tier Architecture
Client does little
processing
Chapter 9 © 2005 by Prentice Hall 5
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.
Chapter 9 © 2005 by Prentice Hall 6
Figure 9-2: File Server Architecture
FAT CLIENT
Chapter 9 © 2005 by Prentice Hall 7
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
Chapter 9 © 2005 by Prentice Hall 8
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
Chapter 9 © 2005 by Prentice Hall 9
Advantages of
Stored Procedures
Compiled SQL statements
Reduced network traffic
Improved security
Improved data integrity
Thinner clients
Chapter 9 © 2005 by Prentice Hall 10
Figure 9-3: Two-tier database server architecture
Thinner
clients
DBMS only
on server
Chapter 9 © 2005 by Prentice Hall 11
Three-Tier Architectures
GUI interface Browser
Client (I/O processing)
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)
Chapter 9 © 2005 by Prentice Hall 12
Figure 9-4: Three-tier architecture
Thinnest clients
Business rules on
separate server
DBMS only on
DB server
Chapter 9 © 2005 by Prentice Hall 13
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
Chapter 9 © 2005 by Prentice Hall 14
Challenges of Three-tier
Architectures
High short-term costs
Tools and training
Experience
Incompatible standards
Lack of compatible end-user tools
Chapter 9 © 2005 by Prentice Hall 15
Application Partitioning
Placing portions of the application code in
different locations (client vs. server)
AFTER it is written
Advantages
Improved performance
Improved interoperability
Balanced workloads
Chapter 9 © 2005 by Prentice Hall 16
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
Chapter 9 © 2005 by Prentice Hall 17
Parallel Computer Architectures
Figure 9-6
Tightly coupled – CPUs share
common memory space
Figure 9-7
Loosely coupled – CPUs each
have their own memory space
Chapter 9 © 2005 by Prentice Hall 18
Query Processing with Parallel
Processors
Figure 9-5a:
Parallel transactions
Figure 9-5b:
Parallel query
Chapter 9 © 2005 by Prentice Hall 19
Processing Logic Distributions
Two-tier distributions
Processing logic could be
at client, server, or both
Processing logic will be at
application server or Web
server
n-tier distributions
Chapter 9 © 2005 by Prentice Hall 20
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)
The “glue” that holds client/server applications together
Chapter 9 © 2005 by Prentice Hall 21
Types of Middleware
Remote Procedure Calls (RPC)
client makes calls to procedures running on remote computers
synchronous and asynchronous
Message-Oriented Middleware (MOM)
asynchronous calls between the client via message queues
Publish/Subscribe
push technology server sends information to client when available
Object Request Broker (ORB)
object-oriented management of communications between clients and servers
SQL-oriented Data Access
middleware between applications and database servers
Chapter 9 © 2005 by Prentice Hall 22
Database Middleware
ODBC – Open Database Connectivity
Most DB vendors support this
OLE-DB
Microsoft enhancement of ODBC
JDBC – Java Database Connectivity
Special Java classes that allow Java
applications/applets to connect to databases
Chapter 9 © 2005 by Prentice Hall 23
Client/Server Security
Network environment complex security
issues
Security levels:
System-level password security
for allowing access to the system
Database-level password security
for determining access privileges to tables;
read/update/insert/delete privileges
Secure client/server communication
via encryption
Chapter 9 © 2005 by Prentice Hall 24
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
Chapter 9 © 2005 by Prentice Hall 25
Figure 9-10:
QBE view of
a multiple-
table join
query
Figure 9-12:
Equivalent
query in SQL
Chapter 9 © 2005 by Prentice Hall 26
Figure 9-9: Access usability hierarchy
API to call functions in
DLLs external to MS Access
Visual Basic for
Applications…language for
customizing the application
Stored modules of pre-
existing VBA code
Simple processes
Foundation of MS Access
Chapter 9 © 2005 by Prentice Hall 27
Using ODBC to Link External Databases
Stored on a Database Server
Open Database Connectivity (ODBC)
API that provides a common language for application programs to access
and process SQL databases independent of the particular RDBMS that is
accessed
Required parameters:
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 – built specifically for Java applications
Chapter 9 © 2005 by Prentice Hall 28
ODBC Architecture
(Figure 9-18)
Client does not need
to know anything
about the DBMS
Application Program
Interface (API) provides
common interface to all
DBMSs
Each DBMS has its own ODBC-compliant driver
Chapter 9 © 2005 by Prentice Hall 29
Visual Basic for Applications
VBA is the programming language that
accompanies Access 2000
VBA provides these features:
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
Chapter 9 © 2005 by Prentice Hall 30
