relational database - Definitions, Contents, Relational operations, Normalization

A form of organizing data in a computer in which each entity type is stored separately as a table, and the relationships between entities are stored as another table. In the database for the present encyclopedia, for example, the individual entries are stored in one file, and the relationships between entries (such as the indexing of the words in the entries, which controls the alphabetical order) are stored separately.

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 that data is 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.

Definitions

A relational database is a database that conforms to the relational model. A relational database could also be defined as a set of relations or a database built in an RDBMS.

An RDBMS is sometimes incorrectly called a relational database. But, strictly speaking, Oracle, Microsoft SQL Server,PostgreSQL, and MySQL are not relational databases. Under popular usage of the term, these software packages are called "Relational Database Management Systems" (RDBMS), and as such they can be used to create relational databases. There is some disagreement as to whether or not they can be considered "relational", because they do not fully conform to the relational model.

Contents

Strictly speaking, a relational database is merely 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

A relation is defined as a set of tuples that all have the same attributes. In a relational database, all of the data stored in a column should be in the same domain (i.e.

Constraints

Constraints are a way of providing restrictions on the kinds of data that can be stored in the relations.

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

A data domain (or usually just domain), is the set of possible values for a given attribute.

Keys

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. If information about family members were stored in a database, a key could be placed over the family member's name. 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. A key over more than one attribute is called a compound key. Theoretically, a key can even be over zero attributes.

Most relations have at least one key defined on it. 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. 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 revlar 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

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. This is enforced in the database by a foreign key. Each department has a department name, and a surrogate key for the department called "DepartmentID". The employee relation would have "DepartmentID" as an attribute, with a foreign key reference to the aforementioned surrogate key of the department relation.

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

A transition constraint is a way of enforcing that the data doesn't 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. In a relational database, all data is stored and accessed via relations. The data that is actually stored in the database are stored as relations. Other relvars do not have their data stored in them, but are a result of applying relational operations, to other relvars.

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

A stored procedure is executable code that is associated with the database.

Stored procedures are not always considered part of a relational database, partially because they are not essential to the functioning of the database.

Indices

An index is a way of providing quicker access to the data in a relational database.

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

Queries made against the relational database, and the derived relvars in the database are expressed in a relational calculus or a relational algebra.

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.