Lecture 10

quicksandwalleyeInternet and Web Development

Oct 31, 2013 (4 years and 7 days ago)

80 views

Database Management

Fall 2003




Data Processing


Architecture



Chapter 12

Personal database


Local storage and processing


Advantages


Personal computers are cheap


Greater control


Friendlier interface


Disadvantages


Replication of applications and data


Difficult to share data


Security and integrity are lower


Disposable systems


Misdirection of attention and resources

Host/terminal


Remote storage and processing


Associated with mainframe computers


All shared resources are managed by the
host


Upgrades are in large chunks

Host/terminal

ADVANTAGES




Install software once



Terminals require little
maintenance



Shared resources



Multiple users

DISADVANTAGES




Maintenance and
downtime affect everyone



Application can’t be
customized



Scalable to a point



Contention for resources



CPU/Memory becomes
bottleneck

LAN architectures


A LAN connects computers within a
geographic area


Transfer speeds of up to 1,000 Mbits/sec


Permits sharing of devices


A server is a computer that provides and
controls access to a shareable resource

Client/Server
-

2nd Generation

DC

manager

DBMS

Operating system

Server

DC

manager

Operating system

DC

manager

Operating system

DC

manager

Operating system

LAN

Application

Application

Application


Two
-
tier solution


Client runs application, Server provides
resources

Client

Client

Client

Client/Server


Client is typically a PC or workstation


Server is typically a larger with multiple cpu
and gigabytes of memory and disk space

ADVANTAGES




Workload distributed



Customization of
applications



Client systems less
expensive

DISADVANTAGES




Higher Maintenance



Software distribution
more complicated



Network (LAN) becomes
bottleneck

Client/Server
-

2nd Generation

DC

manager

DBMS

Operating system

DC

manager

Application

Application server

Data server

Operating system

DC

manager

Operating system

DC

manager

Browser

Thin client

Operating system

DC

manager

Browser

Operating system

LAN


Three
-
tier solution


best of both worlds


Client runs thin client, Server provides
resources, App Server runs application

Middleware

Client/server

ADVANTAGES




Optimal distribution of
workload and resources



Single install of
application



Desktops customizable



Better access control to
DB server

DISADVANTAGES




Higher complexity



More costly high
-
end
hardware



Application must support
multiple clients


Client is typically a PC and browser


Application server is typically a web server

Thick and thin clients

Type of client

Thick

Thin

Technology

LAN

Web

Application logic

Mostly on the
client

Mostly on the
server

Network load

Medium

Low

Data storage

Server

Server

Server intelligence

Medium

High

Evolution of client/server
computing

Architecture

Description

Two
-
tier

Processing is split between client PC
and server, which also runs the DBMS.

Three
-
tier

Client PC does presentation, processing
is done by the server, and the DBMS is
on a separate server.

N
-
tier

Client PC does presentation. Processing
and DBMS can be spread across multiple
servers. This is a distributed resources
environment.

N
-
tier Architecture

Client

Client

Web

Server

Firewall

Internet

Application

Server

Database

Server

Database

Application

Server

Database

Server

Database

Distributed database


Communication charges are a key factor
in total processing cost


Transmission costs increase with distance


Local processing saves money


A database can be distributed to reduce
communication costs

Distributed database


Database is physically
distributed as semi
-
independent databases


There are communication
links between each of the
databases


Appears as one database

Distributed Database

homogeneous distributed database

A hybrid


Architecture evolves


Old structures cannot be abandoned


New technologies offer new opportunities


Ideally, the many structures are patched
together to provide a seamless view of
organizational databases


Distributed database principles apply to
this hybrid architecture

Distributed Database

Homogeneous

Envirnoment

Heterogeneous

Envirnoment

Distributed database access


Remote Request


Remote Transaction


Distributed Transaction


Distributed Request


A single request to a single remote site


SELECT * FROM atlserver.bankdb.customer


WHERE custcode = '12345';

Remote Request

Remote Transaction


Multiple data requests to a single remote
site

BEGIN WORK;

INSERT INTO atlserver.bankdb.account


(accnum, acctype)


VALUES (789, 'C');

INSERT INTO atlserver.bankdb.cust_acct


(custnum, accnum)


VALUES (123, 789);

COMMIT WORK;


Distributed Transaction


Multiple data requests to multiple sites

BEGIN WORK;

INSERT INTO osloserver.bankdb.employee


(empcode, emplname, …)


SELECT empcode, emplname, …




FROM atlserver.bankdb.employee




WHERE empcode = 123;

DELETE FROM atlserver.bankdb.employee



WHERE empcode = 123;

COMMIT WORK;



Two
-
phase commit


Database

Database

Distributed Request

BEGIN WORK;

CREATE VIEW temp


(empcode, empfname, emplname, empsalary)

AS


SELECT empcode, empfname, emplname, empsalary



FROM atlserver.bankdb.employee


UNION


SELECT empcode, empfname, emplname, empsalary



FROM osloserver.bankdb.employee;

SELECT empcode, empfname, emplname, empsalary*.15 AS bonus



FROM temp;

UPDATE atlserver.bankdb.employee


SET empusdretfund = empusdretfund + 1000;

UPDATE osloserver.bankdb.employee


SET empkrnretfund = empkrnretfund + 7500;

COMMIT WORK;


Multiple requests to multiple sites


Each request can access multiple sites

Distributed database design


Horizontal Fragmentation


Vertical Fragmentation


Hybrid Fragmentation


Replication


Horizontal fragmentation

Distributed Database

DEPT table resides on

HQ database, EMP table

is on Sales DB

Vertical fragmentation

Replication


Full replication


Tables are duplicated at each of the sites


Increased data integrity


Faster processing


More expensive


Partial replication


Indexes replicated


Faster querying


Retrieval from the remote database



Replication


Identical remote databases


Parallel Processing


Distributed processing in a local environment

Database

Database

Database

Server

Application

Server

Client

Client

Client

Database

Server

Database

Remote job entry


Local storage


Often cheaper


Maybe more secure


Remote processing


Useful when a personal computer is:


too slow


has insufficient memory


software is not available


Some local processing


Data preparation

(Chapter 12:

What to know)

Ways to distribute data storage

(
where you keep the data
)
and data
processing (
where the computer works on it
)


Remote job entry:



local storage, remote processing


Personal database:



local storage, local processing


Host/terminal:



remote storage, remote processing


Client
-
server computing:



various distributions of local & remote


storage/processing

(Chapter 12,

what to know)

Alternative client
-
server

architectures…


Two
-
tier, thick client:



client does most of the application

processing work;

server provides data management and access

functions


Three
-
tier, thin client:


application processing is re
-
assigned to an

application server; thin client (often a browser)

handles data presentation tasks; a database server

handles data management functions


N
-
tier:



extends three
-
tier, with thin client having access to

multiple application and database servers

(Chapter 12:

What to know)

Distributed database:


What is it?


data is located on different (and often

heterogeneous) systems; the
system catalog
,

which is itself distributed, maintains information

about the location and structure of all the parts


Partitioning:



data can be partitioned (fragmented)



horizontally

(distributes records locally to where

they’re needed), or
vertically

(distributes fields

to spread the processing load); also note
hybrid


Replication:



tables (“full rep”) or indexes (“partial rep”) can

be duplicated across sites;
synchronous

vs.

asynchronous

replication

(Chapter 12:

What to know)

Distributed data access:


The details of the SQL in Chapter 12 are FYI…
however, you should understand the possibilities:


Remote request:



an application issues a single request to a single

remote site




Remote transaction:



multiple data requests to a single remote site


Distributed transaction:



multiple data requests to multiple sites



each request is for data on a single server


Distributed request:



multiple data requests to multiple sites





each request may be for data at multiple sites