Database Management/Database Functions
This lesson introduces the basic functions of a database management system (DBMS).
Objectives and Skills
editObjectives 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
editMultimedia
editActivities
edit- Review Wikipedia: Database transaction and one or more of the following:
- 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;
- 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;
- 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
andUPDATE
statements as above but do not includeCOMMIT
orROLLBACK
. 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.
- Research encryption options for your selected DBMS.
- Research replication options for your selected DBMS.
Lesson Summary
editData Integrity
edit- 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.[1]
- 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.[2]
- 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:[3]
- 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.[4]
- 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.[5]
- 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.[6]
- 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.[7]
- Having a single, well-controlled, and well-defined data-integrity system increases:[8]
- 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
edit- 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:[9]
- 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.[10]
- To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.[11]
- 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.[12]
- 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.[13]
Transaction Log
edit- 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.[14]
- 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.[15]
Concurrency Control
edit- Concurrency control ensures that database transactions are performed concurrently without violating the data integrity of the respective databases.[16]
- 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.[17]
- Pessimistic - Block an operation of a transaction, if it may cause violation of the rules, until the possibility of violation disappears.[18]
- 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.[19]
Locking
edit- Locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.[20]
- Depending on the DBMS, locks may be applied at the row, memory page (multiple rows), table, or database level.[21]
- Locks may be exclusive, preventing any other access, or shared, allowing read access by others.[22]
Database Encryption
edit- 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.[23]
- Database encryption may be performed at the column, row, table, database, or filesystem level.[24]
Database Security
edit- 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.[25]
- Various information security controls are appropriate to databases, including:[26]
- Access control
- Auditing
- Authentication
- Encryption
- Integrity controls
- Backups
- Application security
Database Replication
edit- 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.[27]
- 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.[28]
- In multi-master replication, updates can be submitted to any database node, and then ripple through to other servers.[29]
- 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.[30]
Key Terms
edit- ACID (atomicity, consistency, isolation, durability)
- A set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.[31]
- authentication
- Verifying the identity of a computer system user.[32]
- authorization
- The function of specifying access rights/privileges to resources.[33]
- 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.[34]
- backward recovery
- Reading the log for problem transactions and applying the before images to undo their updates.[35]
- 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.[36]
- commit
- Make a set of tentative changes permanent, marking the end of a transaction and providing durability to ACID transactions.[37]
- concurrency control
- Ensures that database transactions are performed concurrently without violating the data integrity of the respective databases.[38]
- data dictionary
- A read-only set of tables that contain all data definitions in a database.[39]
- data integrity
- The maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle.[40]
- 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.[41]
- encryption
- The process of obscuring information to make it unreadable without special knowledge, key files, or passwords.[42]
- locking
- Prevent destructive interactions when transactions access the same resource.[43]
- metadata
- The set of details on how the data in the database is stored.[44]
- 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.[45]
- rollback
- Undo a set of tentative changes, which returns the database to some previous consistent state.[46]
- transaction
- One or more data-manipulation statements and queries, each reading and/or writing information in the database.[47]
- transaction log
- A history of actions executed by a database management system used to guarantee ACID properties over crashes or hardware failures.[48]
See Also
edit- 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
edit- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Data integrity
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Transaction log
- ↑ Wikipedia: Transaction log
- ↑ Wikipedia: Concurrency control
- ↑ Wikipedia: Concurrency control
- ↑ Wikipedia: Concurrency control
- ↑ Wikipedia: Concurrency control
- ↑ Wikipedia: Record locking
- ↑ SQLShack: All about locking in SQL Server
- ↑ Wikipedia: Record locking
- ↑ Wikipedia: Database encryption
- ↑ Wikipedia: Database encryption
- ↑ Wikipedia: Database security
- ↑ Wikipedia: Database security
- ↑ Wikipedia: Database replication
- ↑ Wikipedia: Database replication
- ↑ Wikipedia: Database replication
- ↑ Wikipedia: Database replication
- ↑ Wikipedia: ACID
- ↑ Wikipedia: Authentication
- ↑ Wikipedia: Authorization
- ↑ Wikipedia: Backup
- ↑ OwlGen: What are the functions of a DBMS?
- ↑ Wikipedia: Database catalog
- ↑ Wikipedia: Commit (data management)
- ↑ Wikipedia: Concurrency control
- ↑ Wikipedia: Data Dictionary
- ↑ Wikipedia: Data Integrity
- ↑ Wikipedia: Deadlock
- ↑ Wiktionary: Encryption
- ↑ OER Commons: Database Security
- ↑ Youtubeː CIT 170 - Database Design Fundamentals - Chapter 7 DBMS Functions
- ↑ Wikipedia: Recovery
- ↑ Wikipedia: Rollback (data management)
- ↑ Wikipedia: Database Transaction
- ↑ Wikipedia: Transaction log