Transcript Functions of Database Management Systems

Functions of Database
Management Systems
•
•
•
•
•
•
•
•
Data storage retrieval and update facilities
A user-accessible catalogue or data dictionary
Support for shared update
Backup and recovery services
Security services
Integrity services
Connectivity
Utilities
Support for Logical
Transactions
• logical transaction = many separate physical
transactions (reading, updating, writing
records)
• if transaction are interrupted before entire
completion "up to date" data is sacrificed for
consistent data.
• If not, transaction is committed - ie written to
disk
• DBMS provides mechanisms that either
Commit or Rollback transactions
SHARED UPDATE
• i.e. Two or more users making updates to
database at the same time
– Single vs. Multi-user Environment (eg:
Networked DBMS)
• Problem: double update
– CUSTOMER BALANCE: 418
– Pat (recording sale: +100) and Jo (recording payment -100):
– CORRECT: Pat reads, updates and writes (commits: 518). Jo
reads (518), updates and writes (commits: 418).
– VALUE: 418.
– INCORRECT: Pat reads and updates. Jo reads and updates.
Pat writes (commit: 518). Jo writes (commit: 318).
– VALUE: 318.
SHARED UPDATE SOLUTIONS
• 1. AVOIDANCE:
– Prohibit shared update,
– Allow access for retrieval only,
– Record updates in transaction file and update
database periodically using a batch program.
• Problem: Data is temporarily out of date
• customer may not be allowed credit
because his balance had not been
credited with last payment.
SHARED UPDATE SOLUTIONS
• 2. LOCKING
– Lock table/record/field from access by other
users.
• TYPES OF LOCK
– Exclusive Lock
– Read Only Lock
– Lock Time-Out
• Other variables
– Lock Granularity
– Deadlock
– Exclusive Lock: Other users can neither
read nor update locked table/record/row.
Extreme and inflexible.
– Read Only Lock: Other users can read but
not update the locked table/record.
– Lock Time-Out: If a record is locked, a user
could have a long wait for its release. Some
DBMS's detect lengthy locks and unlock
them, undoing any updates made to any
records during the transaction.
– Lock Granularity: Refers to the level of the
lock: field, record, page/block, table.
– Deadlock: Users can have a lock on more
than one record at a time. This poses
problems when two users require each others
locked records.
RECOVERY
1.
Backups or Saves (normal backup of DB files)
2.
Journaling / Audit trail / Audit file
– Keep a log or journal of the activity which updates
the database
– recovery involves: Copying the backup over
database and running a special program to update
the backup version of the database with the
transaction in the log.
SECURITY
• Restriction of access to authorised users only.
1.
2.
3.
4.
Passwords
Encryption
Views
Authorisation Levels
• read only
• edit
• delete
• create
Data Integrity
• DBMS provides a mechanism to enforce specific
rules.
– E.g.
*Customer numbers must be numeric.
• But programmers must also develop their own
* Credit Limits must be £300, £500 or £1000 only,
* The sales rep for a given customer must exist,
* No customer may be deleted if he/she currently
has an order on file.
Data Independence
• DBMS must support the isolation of data
structure from the programs
• Users or application programs not affected
by changes to the database structure.
• Logical and Physical Data Independence
Usually achieved through Subschema or
View type mechanisms.
Database Schema
• description of the overall logical structure
of a database, expressed / programmed in
Data Definition Language (DDL)
• broken down into sub-schemas: logical
description of a user’s view or program’s
view of the data used
• DDL can be very sophisticated on a
mainframe or trivial on a PC (queries /
views)
Connectivity
• organisations are rarely single site / single
entity
• flows of data transcend the boundaries of
organisations - so do information systems
• data communication must be implemented
• databases can be used to support the
distribution of information resources
Database Utilities
•
•
•
•
•
Compact data files
Index / re-index data files
Repair database (crash)
Import/export data from and to other sources
Enforce standards (eg: integrity of relationships,
NF...)
• Associated data dictionary
• Access to remote computers (login, emulation)