Definitions
A relational database is a database that conforms to the relational model, and could also be defined as a set of relations or a database built in an RDBMS.
A relational database management system (RDBMS) is a system that manages data using the relational model. Frequently, the term "RDBMS" is inaccurately used as a generic label for the relational database concept. Most current RDBMSs (for example: Oracle, Microsoft SQL Server, Ingres, MySQL, PostgreSQL) deviate significantly from the relational model and are more accurately called SQL database management products. See current usage for an explanation of the requirements for a DBMS to fully support the relational model.
Contents
Strictly, a relational database is a collection of relations (frequently called tables). Other items are frequently considered part of the database, as they help to organize and structure the data, in addition to forcing the database to conform to a set of requirements.
Relations or tables
Main articles: Relation (mathematics) and Table (database)
A relation is defined as a set of tuples that all have the same attributes. This is usually represented by a table, which is data organized in rows and columns. In a relational database, all of the data stored in a column should be in the same domain (i.e. data type). In the relational model, the tuples should not have any ordering. This means both that there should be no order to the tuples, and that the tuples should not impose an order of the attributes. Put differently, neither the rows nor the columns should have an order to them.
While this is the desired result, it is not universally achieved. The SQL standard requires columns to have a defined order. All data stored in a computer has to have an order, as the memory of a computer is linear. Also, when the data is returned, there must be an order in which the data is returned (because all transfer protocols are linear, and coincidentally enough, humans read in a linear fashion). The point here is that this order must never make a logical difference in the system. Frequently orders are imposed which impact performance, but they should never change the result of a query on the database. In practice, several of the DBMSs that are considered "relational" impose an order that makes a logical difference.
Constraints
Main article: Constraint
Constraints are a way of providing restrictions on the kinds of data that can be stored in the relations. These are usually defined (formally) in the form of expressions that result in a boolean value, indicating whether or not the constraint holds. Constraints are a way of implementing business rules into the database.
Under the strictest sense, constraints are not considered part of the relational database, but because of the integral role which they play in organizing data, they are usually considered part of the database.
Data domain
Main article: data domain
A data domain (or usually just domain), is the set of possible values for a given attribute. Because it does constrain the values the data can hold, it could be considered a constraint, but because attributes must specify a domain, it could just be considered part of the relation's definition. Mathematically, a domain can be expressed as "all values for this attribute must be an element of the specified set."
Keys
Main article: Superkey
A tuple usually represents some object and its associated data, whether that object is a physical object or a concept. A key is a kind of constraint which requires that the object, or critical information about the object, isn't duplicated. For example, a family might like to have a constraint such that no two people in the immediate family have the same name. If information about family members were stored in a database, a key could be placed over the family member's name. In a University, they have no such luxury. Each student is typically assigned a Student ID, which are used as keys for individual students stored in the school database. Keys can have more than one column, for example, a nation may impose a restriction that a province can't have two cities by the same name. So, when cities are stored in a relation, there would be a key defined over province and city name. This would allow for two different provinces to have a town called Springfield (because their province would be different), but not two cities with the same name in the same province. A key over more than one attribute is called a compound key. Theoretically, a key can even be over zero attributes. This would enforce that there cannot be more than one tuple in the relation.
Most relations have at least one key defined on it. Because a relation is defined in the relational model as being a set, it can't have duplicate rows. Some DBMSs don't enforce this. If a DBMS does enforce this, it means that there is always at least one key on each relation, namely the key involving all of the attributes of the relation.
A key could be defined formally by requiring that the cardinality of the relation should be equal to the cardinality of the relation projected over the columns of the key.
A key, in this context, refers to any set of attributes which uniquely span the relation. In particular, this is called a superkey. A candidate key is a minimal superkey, meaning that, none of the attributes in the key could be removed from the key, and still have that attribute set be a key. Many DBMSs have a concept of a primary key. The primary key (usually a candidate key) is the key most often used to identify a tuple. In some RDBMSs, the primary key of a base relvar is the storage key (sometimes clustered key), meaning that that is how the data is stored physically. If the value of the primary key is actual interesting data with logical ties to the data (like a name) for the tuple, it is called a natural key. If the key is generated and doesn't have any logical connection to the rest of the data in the tuple, it is called a surrogate key. Other candidate keys that were not chosen as the primary key are called alternate keys.
Foreign keys
Main article: Foreign key
A foreign key is not a key by the previous definition. Rather, a foreign key is a reference to a key in another table. Meaning that the referencing tuple has, as part of its attributes, the values of a key in the referenced tuple that corresponds to the relationship.
A foreign key could be described formally as "For all tuples in the referencing relation projected over the referencing attributes, there must exist a tuple in the referenced relation projected over those same attributes such that the values in each of the referencing attributes match the corresponding values in the referenced attributes".
Transition constraints
Main article: Transition constraint
A transition constraint is a way of enforcing that the data doesn't enter an impossible state because of a previous state. For example, it shouldn't be possible for a person to change from being "married" to being "single, never married". The only valid states after "married" might be "divorced", "widowed", or "deceased".
Other constraints
Other constraints of various different kinds can be created to enforce various kinds of business rules. They can be as simple as "the number of cars an individual owns must be non-negative" or complex patterns like "If the work that an employee performs is 'Hazardous Materials Transport' then that employee's age must be at least 18 years, and the employee's certifications must include 'Hazmat endorsement', and company insurance for that employee must include life insurance."
Relvars
Main articles: Relvar and View (database)
A relvar is a "relation variable". In a relational database, all data are stored and accessed via relations. The data that are actually stored in the database are stored as relations. These relations are sometimes called "base relvars". This is equivalent to a "table". Other relvars do not have their data stored in them, but are a result of applying relational operations, to other relvars. These relvars are sometimes called "derived relvars", meaning that their information is derived from other sources. These are equivalent to "views" or "queries". Derived relvars are convenient in that though they may grab information from several relvars, it is presented externally as a single relvar for a simpler perspective. Also, it can be used as an abstraction layer.
Derived relvars are not always considered part of a relational database, partially because they are not essential to the functioning of the database.
Stored procedures
Main article: Stored procedure
A stored procedure is executable code that is associated with the database. Stored procedures usually store how to perform common operations, like inserting a tuple into a relation, or gathering statistical information about usage patterns. Frequently they are used as an application programming interface (API) for security or simplicity. These are usually written as Imperative programming code extending the Data Definition Language and/or the Data Manipulation Language for the DBMS.
Stored procedures are not always considered part of a relational database, partially because they are not essential to the functioning of the database.
Indices
Main article: Index (database)
An index is a way of providing quicker access to the data in a relational database. Indices can be created on any combination of attributes on a relation. Then when tuples in a relation need to be looked up, similar to how a book's index works, the index can be accessed. Rather than having to check all of the tuples, the index tells the DBMS where the tuple is. Indices are usually implemented via B+ trees.
Indices are usually not considered part of the database, as they are considered an implementation detail, though indices are usually maintained by the same group that maintains the other parts of the database.
Relational operations
Main article: Relational algebra
Queries made against the relational database, and the derived relvars in the database are expressed in a relational calculus or a relational algebra. Some relvars merely restrict the tuples that are returned, rename the attributes, or remove some of the attributes (often: project) from the result set. These three operations are called unary operations. Other operations that can be performed involve combining more than one relvar together. Examples of these are set union, set intersection, cartesian product (often called a "cross product"), and various different kinds of joins. These are all binary operations.
Normalization
Main article: Database normalization
Normalization is a process of altering the structure of the database to make the database conform to one or more best practices, to assist in performance and ease of data manipulation. The most common forms of normalization applied to databases are called the normal forms.