What are the most commonly used acronyms among database administrators and what are their correlated meanings?
This is for the community and those searching for meanings of commonly used terms and acronyms when working with databases, etc.
terminology
What are the most commonly used acronyms among database administrators and what are their correlated meanings?
This is for the community and those searching for meanings of commonly used terms and acronyms when working with databases, etc.
User terms surrogate key and natural key for primary key as a variant.
Surrogate Key:
Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).
Natural Key:
Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.
This is a great question and a set of great answers. I think one thing that is missing from the discussion is an answer which delves into the distinction between a database and a database management system (DBMS). I like the definition of database that Shark provided from dictionary.com. I think it really shows the need for the distinction between the database and the DBMS. The database is a "a comprehensive collection of related data organized for convenient access." The second part of that definition, which says "generally in a computer" is where the distinction lies. If it is stored in a computer, it may or may not be stored in a DBMS. It may be stored in an OS file system. It might be stored in a proprietary file system. Thus I agree with FrustratedWithFormsDesigner that a card catalog is a "database" (well maybe - is it comprehensive and related? More on that later). It just happens to be stored in a file cabinet. In today's world most "comprehensive collections of related data organized for convenient access are stored on a computer, so I disagree with Shark that it is a pity Dictionary.com added that part. I think it is absolutely correct - as a definition of "database".
So how do we define DBMS? I went back to dictionary.com and found this:
"A suite of programs which typically manage large structured sets of persistent data, offering ad hoc query facilities to many users. They are widely used in business applications. "
The definition continues on and is quite long. It describes common features provided by a DBMS, such as security, data integrity, transaction management, concurrency control, and most importantly - data independence. A DBMS provides an external view of the data abstracted from how it is physically stored.
Using this definition, I think it is clear that a DBMS must provide a data model, which is how the data is organized for presentation to the user. The three common models are hierarchical (IMS), network (IDMS), and relational (DB2, Oracle, SQL-Server, etc). There is also the OO model (OODBMS). Only the relational model today has broad applicability. THe other models are still in use but only in niche situations. The DBMS must also provide the other features mentioned. I would refer to these collectively as data management features or capabilities.
Therefore, software products which provide data management features are DBMS', whereas products that do not provide these are not DBMS'. NoSQL products are not DBMS'. That is not to say they are not useful, and not to say they don't store "databases". I like to think that DBMS', as the definition says, solve a class of problems related to business applications like accounting, payroll, billing, customer relationship management, sales, etc. NoSQL products, while not DBMS', are excellent for solving a class of problems that are unrelated to traditional business applications but now exist due to the huge amount of storage and bandwidth computing technology is capable of today. These are applications like internet search, like online auction, like twitter and like facebook. The DBMS is not a good fit to solve these problems as the DBMS contains data management features which, while an absolute necessity for a business application, are of no use for solving storage and retrieval of Craig's list ads or twitter feeds (well usually anyway - that is another discussion for another time :-)). Those problems require massive scale out and extremely fast response and the DBMS, with its feature bloat, isn't a good fit.
A data professional needs to understand all of these tools for storing data and what class of problems they are suited to solve in order to choose the right tool for the job, just like a general contractor has to know which of his or her construction tools is the right tool for the job. No tool is good or bad in and of its self. It is good if it is a good fit to solve an important problem.
I will conclude by noting two other key distinction in the definition of both database and DBMS that might be overlooked in the discussion thus far. The definition of database includes "comprehensive collection of related data." The definition of DBMS includes "manage large structured sets of persistent data". First, for data storage to rise to meet the definition of database, it must be "comprehensive" and "related". This is where the excel spreadsheet of sales, or the huge customer VSAM file or flat file, do not qualify as databases. These examples are single sets of data, not multiple sets of data that are related. None of them are comprehensive over an entire subject area. The sales spreadsheet just has sales. It doesn't relate to information about customers and products beyond perhaps the customer name and the product number. Now if that spreadsheet is a work book that contains a list of customers, a list of products, and then a list of sales that relate the customers to the products, we have a database. But if we were going to store it in a relational way we'd be better off using MS Access or some other relational DBMS. So perhaps a card catalog isn't a database after all as while comprehensive (it has a record of all the books in the library) it isn't related as it only has information about books, not complete related information about authors, publishers, etc.
Second, a DBMS excels at storing "structured" data. It is entirely based on a defined schema of discrete data elements with structured types. A NoSQL product, say a key value store which is devoid of a schema, excels at storing unstructured data. That NoSQL product therefore does not meet the definition of a DBMS. But if the problem you are trying to solve is the storage of unstructured data (something we didn't even attempt to do when DBMS' were first developed), and you don't need data management features independent of the application you will write to process that unstructured data, the NoSQL product is a perfect tool fit.
I hope this answer adds value to the other great answers posted here. I look forward to any comments and discussion points anyone else may have that will help us all broaden our understanding of databases and classes of technology that solve data related problems.
Best Answer
ACID – Atomicity, Consistency, Isolation, Durability
AIO Asynchronous I/O
BASE - Basically Available, Soft-State, Eventually Consistent...essentially a counterpart to (though not really "opposite" of) ACID, this is the core principle of most NoSQL implementations.
BI - Business Intelligence
BLOB – Binary Large OBject
CAP – Consistency, Availability, Partition tolerance... The three requirements of a distributed system (as they apply to a database) according to Eric Brewer's CAP Theorem.
CDM – Copy Data Management
CI – Clustered Index
CK – Candidate Key
CLOB – Character Large OBject
CRUD – Create, Read, Update, and Delete
CS – Cursor Stability - an isolation level supported by different database management systems.
CTE – Common Table Expression
DB – Database
DBA – Database Administrator
DBMS – Database Management System
DCL – Data Control Language
DDL – Data Definition Language
DML – Data Manipulation Language
DMV - Dynamic Management Views
DR - Disaster Recovery
DRBD - Distributed Replicated Block Device
DRDA – Distributed Relational Database Architecture
DRI - Declarative Referential Integrity
DSS - Decision Support Systems
DTD – Document Type Definition
DW or DWH – Database Warehouse
EAV – Entity-Attribute-Value (aka. the archenemy)
ERD - Entity Relationship Diagram
ETL – Extract, Transform, Load
FDW - Foreign Data Wrapper (PostgreSQL)
FK – Foreign Key
FLWOR – For, Let, Where, Order, Return - an expression form used within XQuery to query XML within a database (not sure if DB2 only)
FS – Filesystem
FTS – Fulltext Search
GBP – Group Buffer Pool
HA – High Availability
HADR – High Availability Disaster Recovery
HDD – Hard Disk Drive
ICP – Index Condition Pushdown (MySQL)
IOPS – IO Per Second
IOT – Index Organized Table (Oracle)
ISAM - Indexed Sequential Access Method
I/O – Input/Output
JDBC – Java Database Connectivity
KV – Key/Value
LAMP - Linux, Apache, MySQL and PHP
LBAC - Label Based Access Control
LOB – Large OBject
LPAR – Logical Partition
LRU – Last Recently Used (algorithm)
LUN – Logical Unit Number
MDC – Multidimensional Clustering Table
MDM – Master Data Management
MDX – Multidimensional Expressions
MED – Management of External Data
MQT – Materialized Query Table (IBM DB2)
MV – Materialized View
MVCC – Multiversion Concurrency Control
NAS - Network Attached Storage
NCI – Non-clustered Index
NF - Normal Form (ie: 1NF, first normal form)
ODBC – Open Database Connectivity
ODS - Operational Data Store
OLAP – Online Analytical Processing
OLTP – Online Transaction Processing
OODBMS – Object-Oriented Database Management System
OOM – Out Of Memory
ORM – Object-Relational Mapping
OS – Operating System
PK – Primary Key
PL/pgSQL – Procedural Language/SQL (PostgreSQL) used for writing stored procedures. Similar to PL/SQL.
PL/SQL – Procedural Language/SQL (Oracle) used for writing stored procedures. Also see SQL PL.
QPS – Queries Per Second
RAC – Real Application Clusters (Oracle)
RAID – Redundant Array of Independent Disks
RBAR – Row By Agonizing Row
RDBMS – Relational Database Management System
RBR – Row-Based Replication (MySQL)
RPO - Recovery Point Objective - how much data you can afford to lose. If your server went down, this is the point at which you'd be able to recover the data.
RR – Repeatable Read - an isolation level supported by different database management systems.
RS
– Read Stability - an isolation level supported by different database management systems.
– Replica Set - multiple physical nodes forming a logical node with redundant data. Most commonly used in the MongoDB ecosystem
RTO - Recovery Time Objective - how much time it would take you to recover the data to the RPO
SAN – Storage Area Network
SBR – Statement-Based Replication (MySQL)
SCD – Slowly Changing Dimension
SE – Storage Engine (MySQL and forks)
SEQUEL – Structured English QUEry Language, which was IBM's precursor to SQL, which is why SQL is sometimes (often?) pronounced SEQUEL and not S.Q.L.
SP – Stored Procedure
SQL – Structured Query Language
SQL PL – SQL Procedure Language used for writing stored procedures. Also see PL/SQL.
SQL/XML – an extension of the SQL language used for querying XML.
SSD – Solid State Drive
TPS* - Transactions Per Second, a measurement of database performance.
UAT - User Acceptance Testing
UDF – User Defined Function
UDT – User Defined Type
UR – Uncommitted Read - an isolation level supported by different database management systems.
URLT - Update Resume; Leave Town - For those DBAs that don't bother putting together a proper recovery strategy
XML – eXtensible Markup Language
XSD – XML Schema Definition
XSLT – XML Stylesheet Transformation