Structured Query Language/Glossary

ACID	An acronym for the four properties atomicity, consistency, isolation and durability. Any transaction must conform to them. Atomicity means that either all or no data modification will take place. Consistency ensures that transactions transforms the database from one valid state to another valid state. Isolation requires that transactions will not affect each other, even if they run at the same time. Durability means that the modifications will keep into the database even if the system crashs immediately after the transaction. q.v.: ACID
Attribute	A set of properties (name, datatype, size, ...) used to characterize the data items of entities. A group of attributes constructs an entity-type (or table), i.e.: all values of a certain column must conform to the same attributes. Attributes are optionally complemented by constraints.
Block	Aggregation of one or more physical blocks of a mass device. Usually a block contains numerous rows of one or more tables. Sometimes one row is distributed across several blocks. q.v.: dirty block
Clause	A certain language element as part of a statement. E.g.: the WHERE clause defines search criteria.
Column	A set of values of a single table which resides on the same position within its rows.
Constraint	Similar to attributes, constraints define rules at a higher level, data items must conform to. E.g.: nullability, primary and foreign key, uniqueness, default value, user-defined criteria like `STATUS < 10`.
Cursor	A cursor is a mechanism by which the rows of a table may be acted on (e.g., returned to a host programming language) one at a time.
Database	A set of tables. Those tables contain user data and the Data Dictionary.
Database Management System (DBMS)	A set of computer programs that controls the creation, maintenance and usage of the database. q.v.: DBMS
Data Dictionary (DD)	A set of predefined tables where the DBMS stores information about all user defined objects (tables, views, constraints, ...).
Data Control Language (DCL)	A class of statements which defines the access rights to data, e.g: `GRANT ..., REVOKE, ...` .
Data Definition Language (DDL)	A class of statements which defines logical and physical design of a database, e.g.: `CREATE TABLE ...` .
Data Manipulation Language (DML)	A class of statements which retrieves and manipulates data, e.g.: `SELECT ..., INSERT ..., UPDATE ..., DELETE ..., COMMIT, ROLLBACK`.
Dirty Block	A block whose content has changed in memory, but is still not written to disc.
Entity	An identifiable object like an employee or a department. An entity is an instance of an entity-type. Usually there are many instances of a certain entity-type. Every entity is stored in one row. Entities of same entity-type are stored in rows of the same table. So entities are a logical construct and rows a physical implementation.
Entity-type	A group of attributes describing the structure of entities. As entities of same entity-type are stored in rows of the same table it can be said, that an entity-type describes a table. (Many people tend to use the term entity as a synonym for entity-type.)
Expression	A certain language element as part of a statement. It can produce either scalar values or a table.
Foreign key	A value used to reference a primary key. It can point to any primary key in the database, whether in its own table (e.g.: bill of materials) or another table. It can point to its own row.
Index	An index is a construct containing copies of original values and backreferences to their original rows. Its purpose is the provision of a fast access to the original data. To achieve this, an index contains some kind of collocation. Remark: Indexes are not part of the SQL standard. Nevertheless they are part of nearly every DBMS.
Junction table	If more than one row of table T1 refers to more than one row of table T2 (many-to-many relationship) you need an intermediate table to store this relationship. The rows of the intermediate table contain the primary keys of T1 and T2 as values. q.v.: Junction_table
Normalization	Tables should conform to special rules - namely First-, Second- and Third-Normal Form. The process of rearranging columns over tables is called normalization.
NULL	If no value is stored in the column of a row, the standard says, that the null value is stored. As this null value is a flag and not a real value we use the term null marker within this wikibook. The null marker is used to indicate the absence of any data. For example it makes a difference whether a temperature is measured and stored as 0 degrees or whether the temperature is not measured and hence not stored. One consequence of the existence of the null marker is that SQL must know not only the boolean values TRUE and FALSE but also a third one: UNKNOWN.
Predicate	A language element which specifies a non arithmetic condition. E.g: [NOT] IN, [NOT] LIKE, IS [NOT] NULL, [NOT] EXISTS, ANY, ... .
Primary key	A value or a set of values used to identify a single row uniquely.
Query	An often used statement which retrieves data from the database. It is introduced by the keyword SELECT and usually contains a predicate.
Relationship	A reference between two different or the same entity. References are not implemented as links. They base upon the values of the entities.
Relational Model	A method (and a mathematical theory) to model data as tables (relations), the relationships among each other and all operations on the data.
Row	One record in a table containing information about one single entity. A row has exactly one value for each of its columns - in accordance with First Normal Form. This value may be NULL.
Statement	A single command which is executed by the DBMS. There are 3 main classes of statements: DML, DDL and DCL.
Table (=Relation)	A set of rows of a certain entity-type, i.e. all rows of a certain table have the same structure.
Three Valued Logic (3VL)	SQL knows three boolean values: TRUE, FALSE and UNKNOWN. See: NULL. q.v.: trivalent, ternary or three-valued logic (3VL).
Transaction	A logical unit of work consisting of one or more modifications to the database. The ACID criterium must be achieved. A transaction is either saved by the `COMMIT` statement or completely canceled by the `ROLLBACK` statement.
Value	Implementation of a single data item within a certain column of a certain row. (You can think of a cell within a spreadsheet.)
View	A virtual table containing only its definition and no real data. The definition consists of a query to one or more real tables or views. Queries to the view are processed as queries to the underlying real tables.

Some of the above terms correlate to each other at the logical and implementation level.
Logical Design	Implementation
entity-type	table
entity	row
?	column
data item	value

This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.