Transcript Introduction to Client/Server Computing

Agenda 04/25/2006
 Finish discussing physical database design – methods to
enhance performance.
Identify size and access methods of data.
Choose appropriate hardware.
Create indices.
De-normalize the design as necessary.
Create design and procedures for archiving data.
 Discuss use of triggers and stored procedures in distributed
computing architectures.
1
Objectives for Triggers/Stored Procedures Section
 Understand the layers of an application.
Concepts of client/server computing.
Options available for constructing a complete application.
Role of a DBMS in application development.
 Know the potential capabilities of triggers and stored
procedures.
Understand the general definition of triggers and stored
procedures.
Understand what they accomplish in the layers of an
application
2
Distributed Application Components
 Business-oriented IS applications (payroll, order entry,
customer tracking, inventory control, etc.) contain four general
components (text breaks into three components):
Presentation logic: user interface.
I/O processing logic: data validation.
Business processing logic: business rules and calculations.
Data storage logic: constraints such as primary keys, referential
integrity, and actual data retrieval.
3
While almost all applications contain
those four general components, for any
given application those components
need not be part of the same program,
resident on the same computer, written
in the same language, nor written by
the same group of programmers.
Questions in application component development
 Decisions to make:
What language should a component be written in?
What hardware resource should a component reside upon?
 Information needed to make the decision:
How often will the component change?
Language changes.
Platform changes.
Business changes.
Who is responsible for maintaining the component?
How long is the application supposed to last?
5
Client/server computing
 Definition: Client/server describes the relationship
between two computer programs in which one program,
the client, makes a service request from another program,
the server, which fulfills the request.
 Although the client/server idea can be used by programs
within a single computer, it is a more important idea in a
network. In a network, the client/server model provides a
convenient way to interconnect programs that are
distributed efficiently across different locations.
 The client/server model distributes processes between
clients and servers.
6
File server architecture
PC-Based Client
PC-Based
Server
Server Contains:
Client Contains:
• Presentation logic.
• I/O processing logic.
• Business processing logic.
• Data storage logic.
File Server Architecture
 3 application logic components reside on client. Server has minimal
data storage logic only.
 Structure:
Database resides on file server. Server acts as centralized
storage for database.
Client has a copy of DBMS and all application programming logic.
Client requests database (or specific tables from the database)
which is copied from the server to the fat client. Produces
substantial network traffic as data is transferred between file
server and client.
8
2-Tier client/server architecture
PC
Server
PC
Client Contains:
Server Contains:
• Presentation logic.
• Most business processing
logic.
• I/O processing logic
• Some business
processing logic.
• Data storage logic.
2-tier client/server architecture
 Structure:
Server is responsible for database storage, access and
processing. (Data storage and data management logic through
the DBMS, which is usually located only on the server.)
Server does some business processing logic.
Client takes care of presentation logic and I/O processing logic.
 Server uses stored procedures for business processing logic.
10
What is a stored procedure?
 Stored procedure: A module of code that implements business logic. A
stored procedure consists of a collection of programmatic statements.
Stored procedures are used for almost any business processing logic.
Stored procedures are database objects. They are stored as part of the
database.
 Written in a proprietary language such as Oracle’s PL/SQL or Microsoft’s
Transact-SQL.
 Stored procedures are executed when they are needed.
 Examples of stored procedure uses from an order entry application:
Check credit rating.
Create backorder list.
Check product availability.
11
Example of a PL/SQL
CREATE OR REPLACE PROCEDURE ship_ords IS
Stored Procedure
CURSOR orders_tbl_cursor IS
SELECT * FROM
orders_tbl;
This procedure copies all
ord_record orders_tbl_cursor%ROWTYPE;
orders that have been
CURSOR shipment_view_cursor
completely shipped to the
(v_ord_num VARCHAR2) IS
SELECT sum_ship FROM
shipments
old_orders_tbl. It then
WHERE v_ord_num = shipments.ord_num;
deletes those orders from
qty_shipped
number(7);
the orders_tbl
BEGIN
FOR ord_record IN orders_tbl_cursor
LOOP
OPEN shipment_view_cursor (ord_record.ord_num);
FETCH shipment_view_cursor INTO qty_shipped;
IF shipment_view_cursor%FOUND THEN
IF qty_shipped >= ord_record.qty THEN
INSERT INTO old_orders_tbl VALUES
(ord_record.ord_num, ord_record.cust_id,ord_record.prod_id,
ord_record.ship_type,ord_record.qty, ord_record.ord_date,
ord_record.empno, ord_record.price,sysdate, sysdate);
DELETE FROM orders_tbl WHERE ord_num = ord_record.ord_num;
END IF;
END IF;
CLOSE shipment_view_cursor;
END LOOP;
END;
Why do developers use stored procedures?
 Stored procedures execute relatively quickly.
Database object.
Compiled language.
 Stored procedures can help centralize common functions and
processes allowing greater reusability of program components.
 Stored procedures can increase flexibility by taking the onus of
processing away from presentation logic and I/O processing
programs.
 Stored procedures have the drawback of being written in a
proprietary language – there is no ANSI-standard language for
stored procedures.
13
N-Tier client/server architecture.
PC
PC
Client Contains:
• Presentation logic.
• Some I/O
processing logic
Servers
Servers Contain:
• Most I/O processing.
• Business processing
logic.
• Data storage logic.
N-Tier client/server architecture
 Has three layers: client and two (or more) servers. The servers may not be
physically separate computers.
 Structure:
Client has presentation logic and some I/O processing logic (but very
minimal I/O processing logic).
If the client has only presentation logic, then the architecture is referred to
as an application server structure. One server usually has data storage and
data management logic. (much like the old-time mainframe architecture.)
Another server usually has business logic. Business logic may be in
stored procedures or may use another programming language.
Most I/O processing logic is handled by DBMS (through triggers) or
through the application server.
15
What is a trigger?
 Trigger: A set of SQL statements that are “fired” (meaning “executed”)
when an insert, update or delete occurs.
 Triggers are used most frequently for:
Data validation.
Data backup.
 A trigger is stored as a database object.
A trigger is related to a table and an event.
A trigger is “fired” when the event occurs on the table.
 A trigger is stored on the server, not on the client.
 Triggers are written in a proprietary language such as Oracle’s PL/SQL or
Microsoft’s Transact-SQL.
16
Example of a PL/SQL Trigger
CREATE or REPLACE TRIGGER emp_insert
BEFORE INSERT ON emp
This trigger is associated with the
FOR EACH ROW
emp table.
DECLARE
CURSOR get_mgr_cursor IS
This trigger will be “fired” when an
SELECT sal + nvl(comm,0) mgr_sal
attempt is made to insert a row into
FROM emp
the emp table.
WHERE emp.empno = :new.mgr;
get_mgr_rec get_mgr_cursor%ROWTYPE;
This trigger checks to see whether the
manager_not_found EXCEPTION;
new employee added to the table has a
BEGIN
salary + commission that is greater
OPEN get_mgr_cursor;
FETCH get_mgr_cursor INTO get_mgr_rec; than the salary + commission of the
manager for that employee.
IF get_mgr_cursor%NOTFOUND
THEN
RAISE manager_not_found;
ELSIF :new.sal + nvl(:new.comm,0) > get_mgr_rec.mgr_sal
THEN
raise_application_error
(-20205, 'cannot make more money than boss');
END IF;
EXCEPTION
WHEN manager_not_found
THEN
raise_application_error
(-20001, 'manager not in emp table');
END emp_insert;
Why do people use triggers?
 Allows more complex data validation than standard database
constraints.
 Allows data validation rules to be stored as part of the database
server, removing this functionality from the “presentation”
programs.
Can have different presentation programmatic formats, such as
standard application vs. web-based format.
Different formats may result in the use of completely different
languages such as VB vs. Java.
Would have to maintain both programmatic formats if I/O
processing is done in those programs.
 Centralizes I/O processing in the database.
18
What are the issues involved in deciding where a given
application logic component will reside?
 Performance.
 Flexibility of data access.
Network.
 Transparency.
Data access.
 Costs.
 Availability of
middleware.
 Data Integrity.
 Security.
 Scalability.
Initial cost.
Ongoing maintenance
cost.
Incremental upgrade
cost.