A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how those data are arranged). Common usage of the term "Relational database management system" technically refers to the software used to create a relational database, but sometimes mistakenly refers to a relational database. This article is about computing. ... The relational model for database management is a database model based on predicate logic and set theory. ... For other uses, see Data (disambiguation). ... A Logical schema is a data model of a specific problem domain that has more detail than a conceptual schema, but does not include the design considerations and physical storage parameters found in a physical schema. ... A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. ...

The term relational database was originally defined and coined by E.F. Codd.^[1] E.F. Codds work was the base of Christopher Langtons work in the field of artificial life. ...

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. In mathematics, the concept of a relation is a generalization of 2-place relations, such as the relation of equality, denoted by the sign = in a statement like 5 + 7 = 12, or the relation of order, denoted by the sign < in a statement like 5 < 12. Relations that involve two... In relational databases, SQL databases, and flat file databases, a table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. ...

Terminology

Relational database terminology.

Relational database theory uses a different set of mathematical-based terms, which are equivalent, or roughly equivalent, to SQL database terminology. The table below summarizes some of the most important relational database terms and their SQL database equivalents. SQL (IPA: or ) is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management. ...

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 organized into rows and columns. In a relational database, all the data stored in a column should be in the same domain. In practice this means that values stored in a single column must all be of the same data type and conform to the same constraints. In mathematics, the concept of a relation is a generalization of 2-place relations, such as the relation of equality, denoted by the sign = in a statement like 5 + 7 = 12, or the relation of order, denoted by the sign < in a statement like 5 < 12. Relations that involve two... In relational databases, SQL databases, and flat file databases, a table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. ... In mathematics, a tuple is a finite sequence (also known as an ordered list) of objects, each of a specified type. ... An attribute is the following: Generally, an attribute is an abstraction characteristic of an entity In database management, an attribute is a property inherent in an entity or associated with that entity for database purposes. ... In relational databases, SQL databases, and flat file databases, a table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. ... In the context of a relational database, a row, also called a record or tuple, represents a single, implicitly structured data item in a table. ... In the context of a relational database, a column of a table is a set of data values of a particular simple type, one for each row of the table. ... In mathematics, the domain of a function is the set of all input values to the function. ... In programming languages a data type defines a set of values and the allowable operations on those values[1]. For example, in the Java programming language, the int type represents the set of 32-bit integers ranging in value from -2,147,483,648 to 2,147,483,647, and...

The relational model specifies that the tuples of a relation should have no specific order and that the tuples, in turn, should impose no order on the attributes. In the relational model specific tuples are retrieved by using the projection operator over the relation. The relational selection operation is equivalent to the SQL SELECT query, possibly with a WHERE clause to limit results. In the relational model attributes must be referenced explicitly by name in all operations, while the SQL standard allows both unnamed columns in result sets and the shorthand asterisk (*) notation in queries.

The SQL standard requires columns to have a defined order. All data stored in a computer must have an order, as the memory of a computer is linear. Also, when the data are returned, there must be an order in which the data are returned because transfer protocols are also linear. Note, however, that in SQL the order of columns and rows returned in a result set is never guaranteed unless explicitly specified by the user. SQL (IPA: or ) is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management. ...

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. An abbreviation for relation variable in relational databases. ... In database theory, a view is a virtual or logical table composed of the result set of a query. ... In database theory, a view is a virtual or logical table composed of the result set of a query. ... An abstraction layer is a way of hiding the implementation details of a particular set of functionality. ...

Derived relvars are not always considered part of a relational database, partially because they are not essential to the functioning 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." For other uses, see Data domain (disambiguation). ...

In relational database implementations every data type has an associated data domain. The character data value 'ABC', for instance, is not part of the data domain for an integer attribute. The integer value '123', however, is part of the data domain for an integer attribute.

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. A constraint is a limitation of possibilities. ... For use in mathematics, see Boolean algebra (structure). ... Business rules describe the operations, definitions and constraints that apply to an organization in achieving its goals. ...

Under the strictest sense, constraints are not considered part of the relational database, but because of the integral role that they play in organizing data, they are usually considered part of the database.

In practice, constraints provide users the ability to further restrict and refine the data domain for a given attribute. For instance, constraints can be used to restrict the data domain of an integer attribute to the values '1' to '10'.

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 that requires that the object, or critical information about the object, is not 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. A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. ... A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. ... In database design, a compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes. ...

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. In the context of relational databases, a foreign key is a referential constraint between two tables[1]. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. ... In the context of relational databases, a foreign key is a referential constraint between two tables[1]. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. ... For referencing in Wikipedia, see Wikipedia:Citing sources. ...

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 don'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". A transition constraint is a way of enforcing that the data doesnt enter an impossible state because of a previous state. ...

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 'Hazard endorsement', and company insurance for that employee must include life insurance."

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. A stored procedure is a subroutine available to applications accessing a relational database system. ... In computer programming, the word code refers to instructions to a computer in a programming language. ... API and Api redirect here. ... In computer science, imperative programming, as opposed to declarative programming, is a programming paradigm that describes computation in terms of a program state and statements that change the program state. ... A Data Definition Language (DDL) is a computer language for defining data. ... Data Manipulation Language (DML) is a family of computer languages used by computer programs or database users to retrieve, insert, delete and update data in a database. ...

Stored procedures are not always considered part of a relational database, partly 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. A database index is a data structure that improves the speed of operations in a table. ... A simple B+ tree example linking the keys 1-7 to data values d1-d7. ...

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. In his original relational algebra, Dr. Codd introduced eight relational operators in two groups of four operators each. The first four operators were based on the traditional mathematical set operations: Relational algebra, an offshoot of first-order logic, is a set of relations closed under operators. ... The relational calculus refers to the two calculi, the tuple calculus and the domain calculus, that are part of the relational model for databases and that provide a declarative way to specify database queries. ... Relational algebra, an offshoot of first-order logic, is a set of relations closed under operators. ... Set theory is the mathematical theory of sets, which represent collections of abstract objects. ...

• The union operator combines the tuples of two relations and removes all duplicate tuples from the result. The relational union operator is equivalent to the SQL UNION operator.
• The intersection operator produces the set of tuples that two relations share in common. Intersection is implemented in SQL in the form of the INTERSECT operator.
• The difference operator acts on two relations and produces the set of tuples from the first relation that do not exist in the second relation. Difference is implemented in SQL in the form of the EXCEPT or MINUS operator.
• The cartesian product of two relations is a join that is not restricted by any criteria, resulting in every tuple of the first relation being matched with every tuple of the second relation. The cartesian product is implemented in SQL as the CROSS JOIN join operator.

The remaining operators proposed by Dr. Codd involve special operations specific to relational In set theory and other branches of mathematics, the union of a collection of sets is the set that contains everything that belongs to any of the sets, but nothing else. ... In mathematics, the intersection of two sets A and B is the set that contains all elements of A that also belong to B (or equivalently, all elements of B that also belong to A), but no other elements. ... In set theory and other branches of mathematics, two kinds of complements are defined, the relative complement and the absolute complement. ... In mathematics, the Cartesian product is a direct product of sets. ...

• The selection, or restriction, operation retrieves tuples from a relation, limiting the results to only those that meet a specific criteria, i.e. a subset of terms of set theory. The SQL equivalent of selection is the SELECT query statement with a WHERE clause.
• The projection operation is essentially a selection operation in which duplicate tuples are removed from the result. The SQL GROUP BY clause, or the DISTINCT keyword implemented by some SQL dialects, can be used to remove duplicates from a result set.
• The join operation defined for relational databases is often referred to as a natural join. In this type of join, two relations are connected by their common attributes. SQL's approximation of a natural join is the INNER JOIN join operator.
• The relational division operation is slightly more complex operation, which involves essentially using the tuples of one relation (the dividend) to partition a second relation (the divisor). The relational division operator is effectively the opposite of the cartesian product operator (hence the name).

Other operators have been introduced or proposed since Dr. Codd's introduction of the original eight including relational comparison operators and extensions that offer support for nesting and hierarchical data, among others. Superset redirects here. ...

Normalization

Main article: Database normalization

Normalization was first proposed by Dr. Codd as an integral part of the relational model. It encompasses a set of best practices designed to eliminate the duplication of data, which in turn prevents data manipulation anomalies and loss of data integrity. The most common forms of normalization applied to databases are called the normal forms. Normalization trades reducing redundancy for increased information entropy. Database normalization, sometimes referred to as canonical synthesis, is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. ... Best Practice is a management idea which asserts that there is a technique, method, process, activity, incentive or reward that is more effective at delivering a particular outcome than any other technique, method, process, etc. ... Database normalization, sometimes referred to as canonical synthesis, is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. ... Claude Shannon In information theory, the Shannon entropy or information entropy is a measure of the uncertainty associated with a random variable. ...

References