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:

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:

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.

V

W

X

Y

Z