Functions of database management systems

Functions of database management systems

• 1.
  Functions of Database ManagementSystems • • • • • • • • 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
• 2.
  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
• 3.
  SHARED UPDATE • i.e.Two or more users making updates to database at the same time – Single vs. Multiuser 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.
• 4.
  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.
• 5.
  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
• 6.
  – 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.
• 7.
  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.
• 8.
  SECURITY • Restriction ofaccess to authorised users only. 1. Passwords 2. 3. 4. Encryption Views Authorisation Levels • read only • edit • delete • create
• 9.
  Data Integrity • DBMSprovides a mechanism to enforce specific rules. – Examples: *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.
• 10.
  Data Independence • DBMSmust 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.
• 11.
  Database Schema • descriptionof 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)
• 12.
  Connectivity • organisations arerarely 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
• 13.
  Database Utilities • • • • • Compact datafiles 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)