Database Management/Database Functions

This lesson introduces the basic functions of a database management system (DBMS).

Objectives and Skills

Objectives and skills for this lesson include:

• Review data integrity provided by a DBMS
• Use database transactions
• Observe concurrency control and record locking
• Research database security and encryption

Readings

1. Wikipedia: Data integrity
2. Wikipedia: Database transaction
3. Wikipedia: Transaction log
4. Wikipedia: Concurrency control
5. Wikipedia: Record locking
6. Wikipedia: Database encryption
7. Wikipedia: Database security
8. Wikipedia: Database replication

Multimedia

1. YouTube: DBMS Functions

Activities

1. Review Wikipedia: Database transaction and one or more of the following:
   • MySQL: START TRANSACTION, COMMIT, and ROLLBACK Statements
   • Microsoft SQL Server: BEGIN TRANSACTION
   • PostgreSQL: BEGIN, UPDATE, SAVEPOINT, ROLLBACK, and COMMIT Transaction Statements
   • SQLite: Transaction
2. Experiment with COMMIT. For example, try the following script using the Northwind database. What is the value of ShipperName after the script is run?
   SELECT * FROM Shippers;
BEGIN TRANSACTION;
UPDATE Shippers
SET ShipperName = 'Speedy Delivery'
WHERE ShipperID = 1;
COMMIT TRANSACTION;
SELECT * FROM Shippers;
3. Experiment with ROLLBACK. For example, try the following script using the Northwind database. What is the value of ShipperName after the script is run?
   SELECT * FROM Shippers;
BEGIN TRANSACTION;
UPDATE Shippers
SET ShipperName = 'Slow Delivery'
WHERE ShipperID = 1;
ROLLBACK TRANSACTION;
SELECT * FROM Shippers;
4. Experiment with record locking.
   • In a command prompt or terminal window, start a command-line interface (mysql, sqlcmd, or sqlite3) and open/use the Northwind database.
   • Execute BEGIN TRANSACTION and UPDATE statements as above but do not include COMMIT or ROLLBACK. Leave the transaction open.
   • In a separate command prompt or terminal window, try to UPDATE the same Shipper record. What happens?
   • After testing, commit or rollback the first transaction.
5. Research encryption options for your selected DBMS.
6. Research replication options for your selected DBMS.

Lesson Summary

Data Integrity

• Data integrity is the maintenance of, and the assurance of, data accuracy and consistency over its entire life-cycle and is a critical aspect to the design, implementation, and usage of any system that stores, processes, or retrieves data.
• Implementing checks on the data as close as possible to the source of input (such as human data entry), causes less erroneous data to enter the system. Strict enforcement of data integrity rules results in lower error rates, and time saved troubleshooting and tracing erroneous data and the errors it causes to algorithms.
• Data integrity is normally enforced in a database system by a series of integrity constraints or rules. Three types of integrity constraints are an inherent part of the relational data model: entity integrity, referential integrity and domain integrity:
  • Entity integrity concerns the concept of a primary key. Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null.
  • Referential integrity concerns the concept of a foreign key. The referential integrity rule states that any foreign-key value can only be in one of two states. The usual state of affairs is that the foreign-key value refers to a primary key value of some table in the database. Occasionally, and this will depend on the rules of the data owner, a foreign-key value can be null. In this case, we are explicitly saying that either there is no relationship between the objects represented in the database or that this relationship is unknown.
  • Domain integrity specifies that all columns in a relational database must be declared upon a defined domain. The primary unit of data in the relational data model is the data item. Such data items are said to be non-decomposable or atomic. A domain is a set of values of the same type. Domains are therefore pools of values from which actual values appearing in the columns of a table are drawn.
• User-defined integrity refers to a set of rules specified by a user, which do not belong to the entity, domain and referential integrity categories.
• Having a single, well-controlled, and well-defined data-integrity system increases:
  • stability (one centralized system performs all data integrity operations)
  • performance (all data integrity operations are performed in the same tier as the consistency model)
  • re-usability (all applications benefit from a single centralized data integrity system)
  • maintainability (one centralized system for all data integrity administration).

Database Transaction

• A database transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. Transactions in a database environment have two main purposes:
  • To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
  • To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.
• A database transaction, by definition, must be atomic (it must either be complete in its entirety or have no effect whatsoever), consistent (it must conform to existing constraints in the database), isolated (it must not affect other transactions) and durable (it must get written to persistent storage). Database practitioners often refer to these properties of database transactions using the acronym ACID.
• A transaction is typically started using the command BEGIN (although the SQL standard specifies START TRANSACTION). When the system processes a COMMIT statement, the transaction ends with successful completion. A ROLLBACK statement can also end the transaction, undoing any work performed since BEGIN. If autocommit was disabled with the start of a transaction, autocommit will also be re-enabled with the end of the transaction.

Transaction Log

• A transaction log (also transaction journal, database log, binary log or audit trail) is a history of actions executed by a database management system used to guarantee ACID properties over crashes or hardware failures. Physically, a log is a file listing changes to the database, stored in a stable storage format.
• If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted transactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure atomicity and durability of transactions.

Concurrency Control

• Concurrency control ensures that database transactions are performed concurrently without violating the data integrity of the respective databases.
• The main categories of concurrency control mechanisms are:
  • Optimistic - Delay the checking of whether a transaction meets the isolation and other integrity rules until its end, without blocking any of its operations, and then abort a transaction to prevent the violation, if the desired rules are to be violated upon its commit.
  • Pessimistic - Block an operation of a transaction, if it may cause violation of the rules, until the possibility of violation disappears.
  • Semi-optimistic - Block operations in some situations, if they may cause violation of some rules, and do not block in other situations while delaying rules checking to transaction's end, as done with optimistic.

Locking

• Locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.
• Depending on the DBMS, locks may be applied at the row, memory page (multiple rows), table, or database level.
• Locks may be exclusive, preventing any other access, or shared, allowing read access by others.

Database Encryption

• Database encryption can generally be defined as a process that uses an algorithm to transform data stored in a database into "cipher text" that is incomprehensible without first being decrypted.
• Database encryption may be performed at the column, row, table, database, or filesystem level.

Database Security

• Database security concerns the use of a broad range of information security controls to protect databases against compromises of their confidentiality, integrity and availability. It involves various types or categories of controls, such as technical, procedural/administrative and physical.
• Various information security controls are appropriate to databases, including:
  • Access control
  • Auditing
  • Authentication
  • Encryption
  • Integrity controls
  • Backups
  • Application security

Database Replication

• Database replication involves sharing information so as to ensure consistency between redundant resources, such as software or hardware components, to improve reliability, fault-tolerance, or accessibility.
• Database replication can be used on many database management systems (DBMS), usually with a primary/replica relationship between the original and the copies. The master logs the updates, which then ripple through to the replicas. Each replica outputs a message stating that it has received the update successfully, thus allowing the sending of subsequent updates.
• In multi-master replication, updates can be submitted to any database node, and then ripple through to other servers.
• Database replication becomes more complex when it scales up horizontally and vertically. Horizontal scale-up has more data replicas, while vertical scale-up has data replicas located at greater physical distances.

Key Terms

ACID (atomicity, consistency, isolation, durability)

A set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.

authentication

Verifying the identity of a computer system user.

authorization

The function of specifying access rights/privileges to resources.

backup

The copying into an archive file of computer data that is already in secondary storage so that it may be used to restore the original after a data loss event.

backward recovery

Reading the log for problem transactions and applying the before images to undo their updates.

catalog

Metadata in which definitions of database objects such as base tables, views (virtual tables), synonyms, value ranges, indexes, users, and user groups are stored.

commit

Make a set of tentative changes permanent, marking the end of a transaction and providing durability to ACID transactions.

concurrency control

Ensures that database transactions are performed concurrently without violating the data integrity of the respective databases.

data dictionary

A read-only set of tables that contain all data definitions in a database.

data integrity

The maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle.

deadlock

A state in which each member of a group waits for another member, including itself, to take action, such as sending a message or more commonly releasing a lock.

encryption

The process of obscuring information to make it unreadable without special knowledge, key files, or passwords.

locking

Prevent destructive interactions when transactions access the same resource.

metadata

The set of details on how the data in the database is stored.

recovery

A process of salvaging (retrieving) inaccessible, lost, corrupted, damaged or formatted data from secondary storage, removable media or files, when the data stored in them cannot be accessed in a normal way.

rollback

Undo a set of tentative changes, which returns the database to some previous consistent state.

transaction

One or more data-manipulation statements and queries, each reading and/or writing information in the database.

transaction log

A history of actions executed by a database management system used to guarantee ACID properties over crashes or hardware failures.

See Also

• TutorialsPoint: DBMS Tutorial
• Methods and Tools: Database Locking: What it is, Why it Matters and What to do About it
• Database Security OER Commons: Concurrency Control
• Database Security OER Commons: Locking and Locking Modes
• Database Security OER Commons: Authorization
• OwlGen: What are the functions of a DBMS?

References

← Database Design

Database Management

Database Administration →