Transcript DBMS-Access-From

Chapter 1
Database Access
from Client Applications
Query by Example (QBE)
No official standard
 First pass at SQL
 Interactive querying or updating
 Usability hierarchy

objects
 functions/expressions
 macros
 VBA
 API

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

Joins
Inner (normal) join
 instructors and sections they teach
 Outer join
 all classes and scheduled sections
 Self join
 students and their advisors

Nested Queries

Use first query to compute lists or
statistics necessary for second query
 which students have any status with
MIS 320?
 which students have no status with
MIS 320?
 which sections have two or more
students (with any status)?
Access 97 Query Types
Select
 SQL
 Total:  group by, aggregate functions
 Action: create, delete, update, append
 Crosstab
 Top(n)

Client Applications
Code generators
 Switchboard: main menu form tied to
special table
 or, make your own
 removing extraneous items

Reports

Banded report design
 report header
 page header
 group header [repeatable]
 detail
 group footer
 page footer
 report footer
Access as a Front End
Access as the file server, uses built-in
JET engine
 ODBC with JET, translates queries from
Access to server SQL
 SQL pass-through, query must be
written for server SQL
 ODBCdirect, an extension to JET

Embedded SQL
SQL commands in 3GL programs
 more flexible, accessible interface
 improve performance
 improve database security
 Host program  precompiler  Source
code and expanded SQL  compiler 
Object code  linker/editor 
Executable code

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
Chapter 2
Distributed Databases
Distributed Database
“A single logical database that is spread
physically across computers in multiple
locations that are connected by a data
communications network.”
 conditions that encourage use of
distributed databases:

distribution and autonomy of business units
 data sharing
 data communications costs and reliability

Distributed Database
Environments


Homogeneous
 autonomous
 non-autonomous
Heterogeneous
 systems
 full functionality
 partial multi-database
 gateways
Homogeneous Distributed
Database Environment
Data distributed across all nodes
 Same DBMS at each location
 All data managed by the distributed DBMS
 One global schema

DDBMS Objectives
Location transparency
 Local autonomy
 Synchronous or asynchronous
 Advantages:

increased reliability
 local control
 modular growth
 lower communications costs
 faster response

Options for Distributing
a Database
Data replication
 Horizontal partitioning
 Vertical partitioning
 Combinations of the above

Data Replication
Advantages
 reliability
 fast response
 less complicated integrity routines
 node decoupling
 reduced network traffic at prime time
 Disadvantages
 storage requirements
 complexity and cost of updating

More on Data Replication
Schemes
 snapshot replication
 near real-time replication
 pull replication
 Replication favored when
 process requests are read-only
 data are relatively static

Partitioning (H or V)
Advantages
 efficiency
 local optimization
 security
 ease of querying
 Disadvantages
 inconsistent access speeds
 backup vulnerability

Comparison of Strategies
Strategy
Reliability
Expandability Comm.
Overhead
Manageability
Data
Consistency
Centralized
Poor
Poor
Very high
Very good
Excellent
Repl. With
Snapshots
Good
Very good
Low to
medium
Very good
Medium
Synchronized
Replication
Excellent
Very good
Medium
Medium
Medium to
Very good
Integrated
Partitions
Very good
Very good
Low to
Medium
Difficult
Very poor
Decentralized
With Indep.
Partitions
Good
Good
Low
Very good
Low
Distributed DBMS

Functions
 distributed data dictionary
 retrieval and processing locations
 translate from one DBMS to another
 data management functions such as
security
 consistency across locations
DDBMS Architecture

Each site:
 local DBMS
 local database
 distributed DBMS
 distributed data repository
 communications controller
DDBMS Objectives




Location transparency
 as if all data located at a single node
Replication transparency
Failure transparency
 detect … reconfigure … recover
 transaction manager maintains log and
concurrency control scheme
 two-phase commit protocol
Concurrency transparency
 timestamping