Features of Relational Databases
CS 284 (CSA), Spring 2005
Each column in a RDB relation has properties including the following.
Column name.
Type of the data stored in that column. Like C++ and unlike Scheme, a column's type is ordinarily declared up-front, and values of other types may not be stored within those columns.
Note: DBMSs commonly include a null value that can be assigned as a value to any type. The null value can be used to indicate missing data, as opposed to a default (but present) data value.
Constraints, which are logical conditions that must be satisfied by values in that column. Examples:
A key constraint specifies that one or more column values must form a unique combination of values, so that no two rows of the table may have the same combination of values in those columns. When a key constraint includes only one column, this implies that each row has its own unique value in the key column.
More generally, an integrity constraint limits the range of permissible values in a column or combination of columns. For example, a column of an integer type might be further restricted to have non-negative integers or integers between -10 and 10 by integrity constraints; another integrity constraint might require that at least one of a collection of columns is non-zero.
Constraints relate to data much as programmed asserts relate to
algorithms. Recall that a C++ or java assert call
causes a program crash or exception if that assert's
argument, a boolean expression, returns false. Likewise, an attempt
to insert a new row into a database table will fail if that new row's
values do not satisfy all relevant constraints.
Access permissions. Some DBMSs may permit a DBA or database programmer to restrict or allow access to individual columns by certain users or groups of users.
A query is an operation for retrieving data from the rows of one or more tables. The data comes from rows selected by a boolean expression (with an empty boolean expression selecting all rows), and the result values desired from the query are specified as combinations of column values for rows satisfying that boolean expression.
For example, a simple query might request all rows of a particular table; more complicated queries might request a combination of column values from multiple tables satisfying elaborate logical conditions that relate columns of those tables.
Queries may include function calls to be performed on the resulting data, for example, requesting the ratio of two result columns or the sum of all values in a particular column.
A view is a table whose values are automatically determined as the result of a query.
Views are often used in logical schema to present data in a table that is convenient for a user. For example, a student records database may store comprehensive information about students including home address and phone number, current course enrollments, past grades, campus phone number, and financial aid information. A view for a student's academic advisor would probably include current course enrollments and past grades, but not financial aid information, and probably not home address. A view for a financial aid counsellor would include the financial aid information, but might only show the number of courses currently being taken, and would probably exclude specific grades.
_____
_____
_____
_____
_____
_____
_____
Note: Our terminology is derived directly from SQL, the most common language for database programming.
A create operation defines the schema for a table, i.e., the columns with their types and constraints. The result is an empty table ready to accept new rows with values satisfying that schema.
An insert operation adds a row to a table (assuming the new row's values match that table's types and constraints).
A delete operation removes one or more rows from a table. The rows to remove are determined by a boolean expression, as in the case of a query.
A drop operation deletes a table and all of its rows. Obviously, delete and drop operations must be handled with care!
An update operation modifies values within one or more rows of a single table. The desired modifications are described in terms of columns, and the rows to modify are determined by a boolean expression.
A grant operation specifies access permissions for tables, parts of tables, whole databases, etc.
_____
_____
_____
_____
Database transactions are sequences of database operations that are performed as a unit: either all operations succeed and are performed in order, or none of the operations is performed (which occurs whenever any one of the operations fails).
After starting a transaction, operations as above are specified until a transaction commit is entered. The commit indicates that the DBMS should attempt to carry out all of the operations in that transaction.
Transactions are called atomic or indivisible operations, because either all of a transaction is carried out or none of it is.
A log of operations is a record of operations performed, maintained automatically by a DBMS. Such logs may be stored on disks or other media, and commonly have the purpose of supporting recovery after a system crash.
Differences between transactions and logs: A transaction is under the control of a database programmer, with the purpose of performing multiple database operations as a unit without leaving a database in an inconsistent state. A log is controlled by a DBMS, and is performed automatically in order to recover from system crashes. Typically, only DBAs can influence or examine logs, whereas transactions are the province of programmers, just like the rest of their database code.
The notions of transactions and logs are not unique to relational databases, but are used in all data models. Transactions are important enough to real-world applications of databases that many people do not consider a system to be a DBMS unless it supports transactions.
_____
_____
_____
rab@stolaf.edu, April 25, 2005