Database
systems
Databases:
- file-based client-based
databases:
- store records sequentially in order (explicit
physical ordering) => faster browsing
- ie. record # 106 will always be in the
same place unless physically moved eg. by
sorting, and will always follow record #
105 & precede record # 107
- eg. Paradox, Access
- indications to use this type of tables:
- primarily used with < 10 concurrent
users
- data & data structures must be easily
modified by end-users
- client machines are comparable in power
to server machines
- plenty of network bandwidth if used on
network as data manipulations generally
done on client machine
- speed & convenience are more
important than integrity
- little network & SQL expertise
available
- only one application will routinely
access the data
- applications will be responsible for
maintaining data integrity
- small to moderate amounts of data (<
100Mb)
- browsing ability important
- Paradox:
- database:
- unlike Access and SQL servers,
the Paradox "database"
is a Window's folder containing
tables, the Borland Database
Engine can be used to create an
alias to a folder
- file format:
- clustered-key, VSAM-block,
fixed-length record
- max. size depends on data block
size assigned:
- 2Kb:128Mb; 4Kb:
256Mb;16Kb:1Gb; 32Kb:2Gb
- max. record size (excl. BLOBs)
depends on data block size:
- field types:
- alphanumeric: 255
- number: 15 signif. digits, -10307
to 10308
- short: 16bits -32,767 to 32,767
- LongInt: -2,147,483,648 to
2,147,483,647 (32bit signed)
- dates: BC to 31/12/9999
- money: as for number but are
formatted with money symbol
- bytes: 255
- BCD (binary coded decimal): 32
significant digits after decimal
point
- autoincrement: longInt in a
read-only (non-editable) field,
deleting a record does not change
this value!!
- graphic: stores .bmp files in .mb
- validity checks:
- default, min, max, mandatory
- picture patterns: ! convert to
uppercase; etc;
- table lookups
- referential integrity with tables
in same directory only - cascade
or prohibit updates
- can select
"strict" which
prevents Paradox for Dos
opening table as doesn't
recognise RI
- security:
- password protection creates
encrypted tables but then these
cannot be zip compressed more
than 10-15%, and BDE operations
run ~10-15% slower
- table language drivers:
- determine how stored characters
are displayed in alphanumeric
& memo fields, and determine
the rule used in converting lower
case to uppercase as well as
sorting of expanded alphabets
- eg. DOS code page, ANSI, Paradox
ASCII, International
- BDE sets the current driver to
the last opened table as only one
driver can be open per session
- table levels:
- levels 4,5 & 7 (there is no
6) are fully compatible with each
other & can be used
concurrently
- 4 (1st version of BDE; Paradox 4
for DOS):
- improved network locking
model
- new field types: memo,
formatted memo, BLOB,
Graphic, binary, OLE,
date, money
- new index types:
multifield & case
insensitive
- referential integrity
(not in Paradox for Dos)
- BDE treats all tables
created under earlier
versions of Paradox as
Level 4
- 5:
- added block sizes 8Kb,
16Kb, 32Kb
- new field types: longInt,
autoincrement, BCD, Time,
Timestamp, Logical, Bytes
- 7,8:
- added descending
secondary indices
- locking:
- record level locking NOT page
level
- modes:
- pessimistic (default?):
- record is locked
whilst a user is in edit
mode
- exclusive
- can programmatically control
locking
- the BDE creates a pdox*.lck file
in the shared network folder
- if a user's computer
crashes during a lock,
this may become out of
date and lock records
until it is deleted
manually!!
- Access:
- backward compatibility:
- once an older database is
upgraded to Access97 it cannot be
downgraded or read by earlier
versions - binary conversion
performed in upgrade
- limitations:
- database:
- a single table which
includes forms, reports,
queries and data tables,
although one can split
into 2 databases with
forms, etc in one which
"link" to a
second database which
stores the data making it
potentially easier to
update one without having
to update the other,
however, doing so may
slow the system and have
secondary implications.
- size 1Gb but can be
unlimited via linked
tables in other files
- objects per database:
32768
- modules (incl. forms,
reports): 1024
- characters in object
name: 64
- characters in password:
14
- characters in username:
20
- concurrent users: 255
- table:
- characters in tablename
or fieldname: 64
- fields: 255
- open tables: 1024 (incl.
those opened internally
by Access)
- size: 1Gb
- characters in text field:
255
- characters in memo field:
65K via Access GUI; 1Gb
via programmatically;
- indexes: 32
- fields in an index: 10
- characters in table or
field description: 255
- characters in a record
(excl. in Memo & OLE
fields): 2000
- characters in field
property setting: 255
- SQL:
- uses a proprietary SQL
form which is a subset of
ANSI 92 with some
extensions
- main differences from
ANSI 92:
- wildcards: ? not _; *
not %;
- cannot use:
- DDL for other than
Access databases
- db security
statements (eg. COMMIT, GRANT,
LOCK)
- DISTINCT
aggregate references
- additional features:
- TRANSFORM
allows creation of
crosstab queries &
uses PIVOT
- PARAMETERS
declares parameter
variables
- can use pass-through SQL
- forms/reports:
- max. levels of nesting: 3
- max. page size: 22"
x 22"
- locking:
- page locking => results in
neighbouring records being locked
which can even prevent appending
records if the last record is
locked.
- can choose from:
- optimistic locking (the
default) - locks page
when posting
- pessimistic locking -
locks page when begins to
edit it & until
posted.
- exclusive locking - used
to lock whole table for
certain procedures
- the Jet engine creates a
temporary lock table *.ldb when
locks are created
- when using Delphi or VB with
native drivers or ODBC,
application is NOT informed when
another user has changed the
record, thus need to check that
record has not changed and if it
has ask user if still wants to
post his changes or to discard
them. This is done automatically
within the Access front end.
- Access versions:
- Access 1.0: no referential
integrity support
- Access 2.0: Jet 2.0 and 2.5 via
Service Pack 1
- Access95: Jet 3.0
- Access97: Jet 3.5
- Access2000: Jet 4.0 or MSDE (compatible with MS
SQL Server 7.0)
- set-based, server-based
RDMS databases:
- eg. Sybase, Oracle, Interbase
- tables aren't stored in individual files
- records are not ordered => browsing is very
slow
- to positively identify a record at least one
field or combination of fields must contain
unique values:
- a table with a unique primary index is
called an R-table
- need to use a GENERATOR to create unique
numbers in a multi-user environment
- data is thus set-based which has the advantage
of:
- closure:
- when an operation is performed on
a set, it always produces another
set
- BUT user should ensure
SQLs are structured so
that they produce
R-tables
- transaction processing:
- manipulation of a set of data is
all or none..thus if a part of
the operation (eg. on one record
fails), the whole operation is
aborted => is system crashes
after money has been moved out of
one record before it is inputted
by the operation into another,
the whole procedure is aborted
rather than money going
"missing"!!
- thus a set manipulation can be
"rolled back" in system
failure or explicitly by calling
the Rollback method
- tables may contain stored procedures thus
allowing manipulation of data at the server
machine
- these may be triggered procedures that
allow data validation and are performed
within a set operation, so any changes
created by the triggers can be rollbacked
too.
- triggers can be used for:
- logging changes to a
history table
- enforcing referential
integrity & cascading
deletes/updates
- data validation
- making a multi-table view
updateable
-
Database engines or servers:
- client-based:
- Borland Database
Engine:
- does not fully support Interbase's
capabilities:
- TDatabase can only be used
against a single Interbase
database & only one
transaction may exist at a time
for each BDE alias whereas
Interbase supports multiple
simultaneous transactions per
connection
- versions:
- 2.5:
- Level 4 Paradox tables
- 16bit ODBC
- 3.5:
- SQL handled via
conversion to QBEs
- Level 5 Paradox tables
- 4.5:
- 32bit ODBC
- ANSI SQL '92 compatible
local SQL Engine
- Level 7 Paradox tables
- native driver to Jet
engine
- 5:
- Microsoft Jet
engine:
- SQL support:
- mixture of SQL-92, ANSI-SQL and
proprietary SQL
- proprietary SQL support (not for
pass-through or ODBC operations):
- crosstab support:
TRANSFORM, PIVOT
- no unicode support as yet
- partial replication via filters
introduced in v3.5
- no atomic transaction support & no transaction
logging
- max. 2Gb data
- max. 255 users
- upsizing to SQL Server may require modifications
- v4.0 (Access 2000) enhancements:
- full UNICODE support
- row-level locking
- enhanced support for ANSI92 SQL - GRANT/REVOKE;
DECIMAL data type; Declarative Ref. Integ.
- Jet/SQL Server bidirectional replication
- enhanced replication conflict resolution
- native OLE DB provider
-
- server-based:
- Interbase:
- locking:
- optimistic concurrency scheme and
record versioning:
- when a user begins to
change a record, the
engine saves a copy of
the original record, when
record is being posted,
the engine compares the
current record with the
original, and if it is
different (because some
other user has changed it
in the meantime), the
users edits will be
rejected..ie. 1st post in
is the one that is used
rather than 1st user to
access the record.
- thus readers do not
prevent writers &
writers do not compromise
report generation
- referential integrity:
- supports declarative RI through
FOREIGN KEY declarations
- does not support either cascaded
updates or deletes via
declaritive RI
- implementation of these
must be via triggers:
- BEFORE INSERT on
detail tables to enforce
RI
- BEFORE DELETE on
master tables to do
cascade deletes
- BEFORE UPDATE on
master tables to cascade
updates
- event alerters:
- via Delphi IBEventAlerter
- allows the server to talk back to
the front end application, this
may be:
- synchronous wait -
application stops all
processing &
continually polls the
database to see if event
has occurred - consumes
server & client
resources
- asynchronous wait -
application informs
server it is interested
in an event but does not
stop processing, so when
the event occurs, the
Event Manager calls the
function on behalf of the
application
- data types:
- char(n): 32767
- date: 1/1/100 - 11/12/5941
(64bits)
- decimal(precision, scale):
precision 1-15; scale (no.
decimal places) 1-15;
- double precision: 64bits 1.7 x
-10307 to 1.7 x 10308
- float: 32bits 3.4 x -1038
to 3.4 x 1038
- integer: -2,147,483,648 to
2,147,483,647 (32bit signed)
- numeric(precision,scale): as for
decimal
- smallint: -32,767 to 32,767
(16bit signed)
- varchar(n): 1-32765
- blob: 0-64Kb
- security:
- relies on operating system
security as tables are not
encrypted!
- => need to run
database on dedicated
machine esp. if NetWare
or NT is the OS and a
separate machine for
Interbase itself where
users log on to
- Interbase security:
- a server-security
database file (ISC4.GDB)
is installed in INTRBASE
directory which lists
users and their
privileges
- Microsoft Database Engine (MSDE):
- introduced with MS Access 2000 and compatible with MS SQL
Server 7.0 (comparison
with Jet 4.0)
- unlike Jet engine, MSDE has:
- server-side queries so the whole data table does not
need to be sent across the net to the client for
processing
- dynamic locking which automatically chooses optimal
level of lock
- transaction support with logging
- triggers & stored procedures
- referential integrity through triggers
- does not have engine level cascading deletes &
updates
- merge replication for briefcase model
- dynamic self-management with automatic recovery,
although Jet is almost zero maintenance
- security integrated with Windows NT
- allows easy migration to SQL Server 7.0 but less
compatible with Access 97 databases than the Jet engine
- like Jet 4.0:
- max. data is 2Gb per mdb file
- Unicode
- enterprise applications require scalability, security and
can be implemented with MSDE or SQL Server but not with Jet
- MS SQL Server:
- initially started as Sybase Server but then split from
Sybase & has since has had an independent development
- v7.0:
- data transformation services for easier import/export of
data
- OLAP services even allowing Excel users to access
Gigabytes or Terabytes of data
- English Query as well as SQL
- parallel queries
-
Front end applications:
- Delphi:
- Delphi 4:
- BDE 5
- SQL Links provides support for: Oracle,
MS SQL Server, DB2, Sybase SQL server
& Interbase
- Visual
Basic:
- Powerbuilder:
- Access:
- Access97:
- uses the Jet engine & VBA
- hyperlink support - as native datatype
& opens linked HTMLs
- output to Internet Database Connector
(IDC) via MS Internet Information Server
& Personal Web Server
- dynamic or static publishing to web
- import datatables from HTMLs
- table normalisation wizard
- performance analyzer wizard
- Paradox:
- 3.5:
- 4.5:
- 5:
- 7:
- 32bit for Win95
- Graphic fields: can read BMP, PCX, TIF,
GIF, EPS
- Edits any Paradox table level <= 7
without need to convert!
- 8: