Chapter 9: ClientServer Database Systems

1
(No Transcript)
2
Chapter 9 The Client/Server Database Environment
3
Concepts

• Client/server systems operate in a networked
  environment, splitting the processing of an
  application between a front-end client and a
  back-end processor.
• client and server may reside on same computer
• both are intelligent and programmable

4
Application Logic Components

• Presentation logic
• input
• output
• Processing logic
• I/O processing
• business rules
• data management
• Storage logic
• data storage and retrieval
• DBMS functions

5
File Server Architecture

• A file server is a device that manages file
  operations and is shared by each of the client
  PCs.
• Fat client does most processing
• Limitations
• whole file or table transferred to client
• client must have full version of DBMS
• each client DBMS must manage database integrity

6
File Server Model
FAT CLIENT
7
Database Server Architecture

• Client workstation
• user interface, presentation logic, data
  processing logic, business rules logic
• Database server
• database storage, access, and processing
• Advantages
• less traffic, more control over data
• Stored procedures first use of business logic at
  database server

8
Database Server Architecture(Two-Tier)
Thinner clients
DBMS only on server
9
Advantages of Stored Procedures

• Compiled SQL statements
• Reduced network traffic
• Improved security
• Improved data integrity
• Thinner clients

10
Three-Tier Architectures

• Application server in addition to client and
  database server
• Thin clients do less processing
• Application server contains standard programs
• Benefits
• scalability
• technological flexibility
• lower long-term costs
• better match business needs
• improved customer service
• competitive advantage
• reduced risk

11
Three-Tier Architecture
Thinnest clients
Business rules on separate server
DBMS only on DB server
12
Application Partitioning

• Where should modules be placed on the client or
  on the application server?

13
Mainframes and Parallel Computer Architectures

• Mainframes hold legacy data and systems
• good for multiuser, massive data access
• Parallel architectures
• parallel transaction (two users)
• parallel query (one user, split processing)
• symmetric multiprocessing (SMP) (share memory)
• massively parallel processor (MPP) (share
  nothing)
• Consider
• large amounts of data, complex queries, large
  number of users
• cost analyze as part of initiative that it
  supports
• can current technology do the job?

14
Middleware

• Software that allows interoperability
• asynchronous remote procedure call (RPC)
• publish/subscribe
• message oriented middleware (MOM)
• object request brokers (ORB)
• SQL-oriented data access
• synchronous RPC
• Client/server middleware
• application program interface (API)
• open database connectivity (ODBC)
• java database connectivity (JDBC)
• common object request broker architecture (CORBA)
• Distributed Component Object Model (DCOM)

15
Security

• System-level password
• who can be on the system?
• Database-level password
• who can use the data? (or a given program?)
• Client/server communication
• encrypted data transfer

16
Query by Example (QBE)

• No official standard
• First pass at SQL
• Interactive querying or updating
• Usability hierarchy
• objects
• functions/expressions
• macros
• VBA
• API

17
Building Queries using QBE

• Visual image of the table is used for writing
  queries.
• Result is a dynaset
• not a base table
• dynamic or virtual set
• may or may not be updateable
• Joins (relationships) based on relationship view
  or created in query

18
Joins

• Inner (normal) join
• instructors and sections they teach
• Outer join
• all classes and scheduled sections
• Self join
• students and their advisors

19
Access Query Types

• Select
• SQL
• Total ? group by, aggregate functions
• Action create, delete, update, append
• Crosstab
• Top(n)

20
Reports

• Banded report design
• report header
• page header
• group header repeatable
• detail
• group footer
• page footer
• report footer

21
Visual Basic for Applications (VBA)

• Benefits
• complex functionality
• error handling
• faster execution
• maintenance
• OLE automation
• more programmatic control
• easier to read
• Event-driven
• event occurs
• event detected
• response generated

22
Chapter 10 The Internet Database Environment
23
Near or Far
24
Getting the Data
25
Chapter 11 Data Warehouse

• Subject-Oriented
• Integrated
• Time-Variant
• Nonupdatable

26
Two Types of Processing

• Operational processing
• captures, stores, and manipulates data to support
  daily operations
• Informational processing
• analysis of summarized data to support decision
  making
• Data warehouses
• consolidate and integrate information from many
  different sources and arrange it in a meaningful
  format for making accurate business decisions

27
Data Warehouse Architectures

• Two-level architecture
• source files read-only data warehouse
• Three-level architecture
• source files enterprise data warehouse
  (reconciled data) data mart (derived data)

28
Data Characteristics

• Status vs. event data
• balance vs. transaction
• Transient vs. periodic data
• current balance vs. balance history

29
Reconciled Data Layer

• Used to build data warehouse
• Characteristics of reconciled data
• detailed
• historical
• normalized
• comprehensive
• quality controlled
• Capture ? Scrub ? Transform ? Load

ETL Extract, transform, and load
30
Derived Data Layer

• Data that have been selected, formatted, and
  aggregated
• Star schema
• dimension table demographic data
• fact table associative data often summarized

Customer
Cruise
Reservation
Agent
31
User Interface

• Traditional query and reporting tools
• On-line analytical processing (OLAP)
• cube slicing
• drill-down
• Data mining
• explanatory
• confirmatory
• exploratory
• Data visualization

32
Slicing the Cube
33
(No Transcript)