Relational
Database Management Systems
SQL database server
architectures:
- Process-per-client
architectures:
- eg. DB2, Informix, Oracle6
- provide maximum bullet-proofing by giving each
database client its own address space
- protects users from each other & protects
database manager from users
- processes can easily be assigned to different
processors
- consumes more memory & CPU resources
- requires a TP Monitor to resolve bottlenecks due
to process context switches & interprocess
communications overhead
- performs poorly when a large number of users
connect to a database
- Multi-threaded
architectures:
- eg. Sybase, MS SQL Server
- provides best performance by running all the user
connections, applications & the database in
the same address space coordinated by its own
internal scheduler (rather than relying on OS's
tasking & address protection schemes)
- conserves memory & CPU cycles by not
requiring frequent context switches
- tends to be more portable across platforms as
doesn't rely on as many OS services
- HOWEVER:
- a misbehaved application may bring the
whole system down & all its tasks
- user programs that consist of long
duration tasks (eg. long queries) can hog
all the server resources
- the pre-emptive scheduling tends to be
inferior to the native OS's scheduler
- Hybrid architectures:
- eg. Oracle 7,8
- consists of 3 components:
- multithreaded network listeners
- dispatcher tasks
- reusable shared server worker processes
that pick the work off the queue
- provides a protected environment for running user
tasks without assigning a permanent process to
each user
- can suffer from queue latencies (esp. Oracle7 but
this seems to have been fixed in Oracle8)
SQL
API's:
- Embedded SQL (ESQL):
- an ISO SQL-92 defined standard for embedding SQL
statements "as is" within ordinary
programming languages and must be created at
design time and then the SQL source is ran
through a precompiler to generate a source code
file that the language compiler understands
- target database must be known at design time
- examples:
- C compiled: requires start with EXEC SQL
and ends with a colon
- Java (SQLJ): SQLJ
precompilers tend to generate embedded
JDBC calls
- SQL Call-Level
Interfaces (CLIs):
- allows creating & running of SQL scripts at
runtime
- examples:
- Procedural CLI's
based on SAG:
- SAG CLI:
- SQL Access Group
consortium in 1988
developed an "open
standard" for SQL
- X/Open
SAG CLI:
- derived from SAG when it
was given to X/Open in
1994
- became the international
standard in 1996 becoming
ISO 9075-3SQL/CLI
- the forthcoming SQL3 CLI
will be based on this
with some extensions
- MS
ODBC CLI:
- derived from SAG, 1st
released as v1 in 1992,
then v2 in 1994
- proprietary SAG-based CLI's:
- most database vendors
create their own native
CLI's with extensions
which aren't standards
- object-oriented
CLI's:
- CLI's that can be accessed via
object interfaces (classes)
instead of procedural API's
- support distributed data access
via off-the-shelf ORBs
- examples:
- JDBC:
- MS
OLE DB & ADO:
- supported in Win98,
NT5, IE4, IIS 4.0
- will replace ODBC
eventually, but in
meantime supports ODBC
- OLE DB:
- defines a set of
interfaces that mediate
between data providers
& data consumers
- providers return an
abstraction called a
rowset which is a table
that can also contain
embedded tables in its
columns (cw ODBC which
returns onlt 2D tables)
- ADO:
- a higher level
programming model for OLE
DB, replacing DAO &
RDO
- provides a Remote
Data Service (RDS)
component that supports
client-side caching with
updates & data-aware
controls
- object model consists
of:
- connection objects
that represent the
connection to a data
source
- command objects that
represent a query
- recordset object that
represent the results of
the query
RDBMS
usage modes:
- Online Transaction
Processing (OLTP):
- mission-critical applications that require 1-3
sec response time 100% of the time
- typically, clients interact with a transaction
server instead of a database server
- eg. point-of-sale systems
- Decision support systems:
- used to analyse data & create reports
- not time critical & can tolerate slower
response times
- users can construct elaborate queries, answer
"what if" questions, search for
correlations in data, plot the data & export
to spreadsheet or word processor
- Enterprise information
systems (EIS):
- also called Online Analytical Processing (OLAP)/
Mulitidimensional Analysis (MDA) tools a
- uses data warehousing to replicate and clean raw
data into an informational database which is then
accessed by executives, etc