Links
|
Features of Relational Databases
CS 284 (CSA), Spring 2020
Relations and tables
_____ permissions
Properties of columns
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.
Row selection
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. _____ _____ _____ _____ _____ _____ _____
Operations on tables
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. _____ _____ _____ _____ _____
Transactions and logging
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. _____ _____ _____
Optimizations
Indices
|