SQL Database Glossary
Our database glossary explains common database terminology and SQL database jargon.
A
Access
Microsoft Access is an entry-level database management software from Microsoft, which allows you to organize, access, and share information easily. Access is very user-friendly and easy to use for inexperienced users, while sophisticated enough for database and software developers.
ACID
ACID short for Atomicity - Consistency - Isolation - Durability and describes the four properties of an enterprise-level transaction:
- ATOMICITY: a transaction should be done or undone completely. In the event of an error or failure, all data manipulations should be undone, and all data should rollback to its previous state.
- CONSISTENCY: a transaction should transform a system from one consistent state to another consistent state.
- ISOLATION: each transaction should happen independently of other transactions occurring at the same time.
- DURABILITY: Completed transactions should remain stable/permanent, even during system failure.
ADO
Short for Microsoft ActiveX Data Objects. ADO enables your client applications to access and manage data from a range of sources through an OLE DB provider. ADO is built on top of OLE DB and its main benefits are ease of use, high speed, and low memory overhead. ADO makes the task of building complex database enabled client/server and web applications a breeze.
B
C
Column
Database tables are made of different columns (fields) corresponding to the attributes of the object described by the table.
COMMIT
The COMMIT command in SQL marks the finalization of a database transaction.
Cursor
Short for Current Set Of Records in some database languages. The cursor is a database object pointing to a currently selected set of records.
D
Data
Piece of information collected and formatted in a specific way. The term data is frequently used to describe binary (machine-readable) information.
Database
A database is a collection of information organized into related tables of data and definitions of data objects. The data within a database can be easily accessed and manipulated trough computer program.
DB2
DB2 is a relational database management system developed by IBM. DB2 runs on a variety of platforms including Sun Solaris, Linux and Windows.
dBase
dBase is a popular relational database management system produced by Ashton-Tate corporation in the early 1980s. The dBase format for storing data has become industry standard and is still in use today.
DELETE
The DELETE is a SQL command used to delete record(s) from a table in database.
E
F
Field
See Column definition
First Normal Form
See Normalization definition
Flat File
Flat file is a data file that has no structured relationships between its records.
Foreign Key
A foreign key is a key field (column) that identifies records in a table, by matching a primary key in a different table. The foreign keys are used to cross-reference tables.
Fourth Normal Form
See Normalization definition
FoxPro
Visual FoxPro is a Microsoft development environment designed for database developers.
G
H
I
Index
An index is a database feature (a list of keys or keywords), allowing searching and locating data quickly within a table. Indexes are created for frequently searched attributes (table columns) in order to optimize the database performance.
INSERT
The INSERT is a SQL command used to add a new record to a table within a database.
Isolation
See ACID definition
J
JOIN
The JOIN is a SQL command used to retrieve data from 2 or more database tables with existing relationship based upon a common attribute.
K
Key
See Primary Key and Foreign Key definitions
L
Lock
Locks are used by Database management systems to facilitate concurrency control. Locks enable different users to access different records/tables within the same database without interfering with one another. Locking mechanisms can be enforced at the record or table levels.
M
MySQL
MySQL is an open source relational database management system. MySQL can be used on various platforms including UNIX, Linux and Windows (there are OLE DB and ODBC providers as well as .NET native provider for MySQL). MySQL is widely used as a backend database for Web applications and it' viable and cheaper alternative to enterprise database systems like MS SQL Server and Oracle.
N
Normalization
Normalization is the process of organizing data to minimize redundancy and remove ambiguity. Normalization involves separating a database into tables and defining relationships between the tables. There are three main stages of normalization called normal forms. Each one of those stages increases the level of normalization. The 3 main normal forms are as follows:
- First Normal Form (1NF): Each field in a table must contain different information.
- Second Normal Form (2NF): All attributes that are not dependent upon the primary key in a database table must be eliminated.
- Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a common field, this common field information should be separated into a different table.
There are 2 more normalization forms, fourth normal form (4NF) and fifth normal form (5NF), but they are rarely used. Normalization makes databases more efficient and easier to maintain.
NULL
The NULL SQL keyword is used to represent a missing value.
O
ODBC
Short for Open DataBase Connectivity, a standard database access technology developed by Microsoft Corporation. The purpose of ODBC is to allow accessing any DBMS (DataBase Management System) from any application (as long as the application and the database are ODBC compliant), regardless of which DBMS is managing the data. ODBC achieves this by using a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to transform the application's data queries into commands that the DBMS understands. As we said earlier, both the application and the DBMS must be ODBC compliant meaning, the application must be capable of sending ODBC commands and the DBMS must be capable of responding back to them.
OLE DB
Short for Object Linking and Embedding Data Base. OLE DB is a set of COM-based interfaces that expose data from a range of sources. OLE DB interfaces give applications standardized access to data stored in various information sources like Relational Database Management Systems (MS SQL Server, Oracle, MySQL), small personal databases like MS Access, productivity tools like spreadsheets; plain text files, etc. These interfaces support the amount of DBMS functionality appropriate to the data store, allowing the data store to share its data.
Oracle
Oracle is an enterprise relational database management system. Oracle's main rival product MS SQL Server is a low cost alternative offering the same features.
P
PostgreSQL
PostgreSQL is an object-oriented open source relational database management system, which uses a subset of SQL language.
Primary Key
The primary key of a relational table holds a unique value, which identifies each record in the table. It can either be a normal field (column) that is guaranteed to be unique or it can be generated by the database system itself (GUID or Identity field in MS SQL Server for example). Primary keys may be composed of more than 1 field (column) in a table.
Q
Query
Queries are the main way to make a request for information from a database. Queries consist of questions presented to the database in a predefined format, in most cases SQL (Structured Query Language) format.
R
Record
The record is a complete set of information presented within a RDBMS. Records are composed of different fields (columns) in a table and each record is represented with a separate row in this table.
ROLLBACK
The ROLLBACK is a SQL command which cancels/undoes the proposed changes in a pending database transaction and marks the end of the transaction.
Row
See Record definition
S
Second Normal Form
See Normalization definition
SELECT
The SELECT is a SQL command, which is the primary means for retrieving data from a RDBMS.
SQL
SQL is short for Structured Query Language and is an industry standard language used for manipulation of data in a RDBMS. There are several different dialects of SQL like, ANSI SQL, T-SQL, etc.
Stored Procedure
Stored Procedure is a set of SQL statements stored within a database server and is executed as single entity. Using stored procedures has several advantages over using inline SQL statements, like improved performance and separation of the application logic layer from database layer in n-tier applications.
T
Table
A Table in RDBMS refers to data arranged in rows and columns, which defines a database entity.
Third Normal Form
See Normalization definition
Transaction
Transaction is a group of SQL database commands regarded and executed as a single atomic entity.
Trigger
Triggers are special type of stored procedures executed automatically when certain events take place. There are different types of triggers - for update, for insert and for delete. Each trigger is associated with a single database table.
U
UPDATE
The UPDATE is a SQL command used to edit/update existing records in a database table.