Database Management/Database Functions

From Wikiversity
Jump to navigation Jump to search

Objectives and Skills[edit]

This lesson allows users to learn the basic functions of a database management system (DBMS).

  1. Activity on views for SQL Server[1]

Readings[edit]

  1. Read Concurrency Control Mechanisms: Wikipedia: Concurrency Control
  2. Wikipedia: Data Access
  3. Wikipedia: Data Integrity
  4. Wikipedia: Database Abstraction Layer
  5. Database Security (OER Commons): Concurrency Control
  6. Database Security (OER Commons): Locking and Locking Modes
  7. Database Security (OER Commons): Authorization
  8. Read Transactional Databases: Wikipedia: Database Transaction
  9. DBMS functions

Multimedia[edit]

  1. Youtube: Functions of DBMS
  2. Youtube: Database Management System: Basics
  3. Youtube: Introduction to Database Abstraction Layers
  4. Youtube: Getting Started With Databases
  5. Youtube:How to Create Database and tables in MySqlhttps://www.youtube.com/watch?v=pZKrznZymMg
  6. Youtube:Database Lesson #1 of 8 https://www.youtube.com/watch?v=4Z9KEBexzcM
  7. Youtube: Creating a Table in SQL Server Management Studio
  8. Youtube Generating Scripts for Database Objects in SQL Server

Activities[edit]

Lesson Summary[edit]

  • Backups have two distinct purposes.
    • The primary purpose is to recover data after its loss, be it by data deletion or corruption.[1]
    • The secondary purpose of backups is to recover data from an earlier time, according to a user-defined data retention policy, typically configured within a backup application for how long copies of data are required.[2]
  • Backups represent a simple form of disaster recovery and should be part of any disaster recovery plan, backups by themselves should not be considered a complete disaster recovery plan.[3]
  • Database authorization explains privileges to users such as create role, grant role, revoke role and drop role.[4]
  • Control of data concurrency and data consistency is vital in a multiuser database. Data concurrency allows many users to access data at the same time. Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.[5]
  • Database locking prevents multiple sessions from changing the same data at the same time.[6]
  • In a database system, a transaction might consist of one or more data-manipulation statements and queries, each reading and/or writing information in the database.[7]

Key Terms[edit]

authorization
Authorization is the function of specifying access rights/privileges to resources related to information security and computer security in general and to access control in particular.[8]
backup
A backup, or data backup, or the process of backing up, refers to 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.[9]
Backward Recovery
The DBMS accomplishes backward recovery by reading the log for the problem transactions and applying the before images to undo their updates.[10]
concurrency control
Concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible. Concurrency control ensures that database transactions are performed concurrently without violating the data integrity of the respective databases.[11]
data access
Data access crucially involves authorization to access different data repositories.[12]
data dictionary
A data dictionary is a read-only set of tables that contain all data definitions in a database. The definitions included in the database include all of the following: tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and more. The data dictionary does not contain any of the information located in the data tables, only the data about the tables. [13]
data integrity
Data integrity is the maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle,[1] and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data.[14]
database abstraction layer
A database abstraction layer is an application programming interface which unifies the communication between a computer application and databases such as SQL Server, DB2, MySQL, PostgreSQL, Oracle or SQLite.[15] In addition to this, a database abstraction layer is a simplified representation of a database in the form of a written description or a diagram. The three formal abstraction layers include: user model, logical model, and physical model. [[2]]
database security
Database security concerns the use of a broad range of information security controls to protect databases (potentially including the data, the database applications or stored functions, the database systems, the database servers and the associated network links) against compromises of their confidentiality, integrity and availability. It involves various types or categories of controls, such as technical, procedural/administrative and physical.[16]
data transformation
Data transformation is the process of converting data from one format or structure into another format or structure. It is a fundamental aspect of most data integration[1] and data management tasks such as data wrangling, data warehousing, data integration and application integration.[17]
dirty read problem
dirty read problem: Transactions read a value written by a transaction that has been later aborted. This value disappears from the database upon abort, and should not have been read by any transaction ("dirty read"). The reading transactions end with incorrect results.[18]
GRANT statement
Is used to provide a user's privileges to a specific role or roles, to be able to act on certain actions on database objects.
Locking
When transactions access the same resource, locking will prevent destructive interactions. For example two users will not be able to update the same information at the same time[19]
lost update problem
A second transaction writes a second value of a data-item (datum) on top of a first value written by a first concurrent transaction, and the first value is lost to other transactions running concurrently which need, by their precedence, to read the first value. The transactions that have read the wrong value end with incorrect results.[20]
Metadata
The set of details on how the data in the database is stored.[21]
optimistic concurrency
Optimistic concurrency control (OCC) is a concurrency control method applied to transactional systems such as relational database management systems (begin, modify, validate, commit/rollback).[22]
recovery
data recovery is 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.[23]
storage management
Storage management usually refers to the management of Computer data storage, which includes memory management.[24]
transaction
A transaction might consist of one or more data-manipulation statements and queries, each reading and/or writing information in the database.[25]
View
A snapshot of specific data in a database at a moment in time.

Review Questions[edit]

Enable JavaScript to hide answers.
Click on a question to see the answer.
  1. _________ statement gives users privileges to a specific role(s) to perform actions on database objects.
    GRANT statement gives users privileges to a specific role(s) to perform actions on database objects.

    [26]

  2. ___________________ means that many users can access data at the same time.
    Data concurrency means that many users can access data at the same time.[27]
  3. You can prevent records from being overwritten incorrectly by ________ out other users who need to edit records at the same time.
    You can prevent records from being overwritten incorrectly by ________ out other users who need to edit records at the same time.[28]
  4. ________ in a database is also known as data about data.
    Metadata in a database is also known as data about data.
  5. There are two different types of integrity for a database, _______ and _______.
    There are two different types of integrity for a database, Physical and Logical.
  6. Which data doesn't contain any information in the data tables, but rather contain information about the tables?
    Data dictionary
  7. ________ a database reverses the encryption. If your encrypted database takes longer to respond to user requests as it gets larger, you might consider _______ it to improve its responsiveness.
    Decrypting a database reverses the encryption. If your encrypted database takes longer to respond to user requests as it gets larger, you might consider decrypting it to improve its responsiveness.

See Also[edit]

DBMS Tutorial

References[edit]