Editing Relational model (section)

== Conceptualization ==

=== Basic concepts ===
[[File:Relational database terms.svg|thumb|A relation with 5 attributes (its degree) and 4 tuples (its cardinality) can be visualized as a table with 5 columns and 4 rows. However, unlike rows and columns in a table, a relation's attributes and tuples are unordered.]]

A ''relation'' consists of a ''heading'' and a ''body''. The heading defines a [[Set (mathematics)|set]] of ''attributes'', each with a ''name'' and ''data type'' (sometimes called a ''domain''). The number of attributes in this set is the relation's ''degree'' or ''[[arity]]''. The body is a set of ''tuples''. A tuple is a collection of ''n'' ''values'', where ''n'' is the relation's degree, and each value in the tuple corresponds to a unique attribute.<ref>{{Cite web |date=2023-02-12 |title=Tuple in DBMS |url=https://www.geeksforgeeks.org/tuple-in-dbms/ |access-date=2024-08-03 |website=GeeksforGeeks |language=en-US}}</ref>  The number of tuples in this set is the relation's ''[[cardinality]]''.<ref name="professionals"/>{{rp|17–22}}

Relations are represented by ''relational [[Variable (computer science)|variables]]'' or ''relvars'', which can be reassigned.<ref name="professionals"/>{{rp|22–24}} A ''[[database]]'' is a collection of relvars.<ref name="professionals"/>{{rp|112–113}}

In this model, databases follow the ''Information Principle'': At any given time, all [[Information theory|information]] in the database is represented solely by values within tuples, corresponding to attributes, in relations identified by relvars.<ref name="professionals"/>{{rp|111}}

=== Constraints ===

A database may define arbitrary [[boolean expressions]] as [[constraint (database)|constraints]]. If all constraints evaluate as ''true'', the database is ''consistent''; otherwise, it is ''inconsistent''. If a change to a database's relvars would leave the database in an inconsistent state, that change is illegal and must not succeed.<ref name="professionals"/>{{rp|91}}

In general, constraints are expressed using relational comparison operators, of which just one, "is subset of" (⊆), is theoretically sufficient.{{Citation needed|reason=This should be explained, because it is interesting enough to read more about it|date=August 2010}}

Two special cases of constraints are expressed as ''keys'' and ''foreign keys'':

==== Keys ====

A ''candidate key'', or simply a ''key'', is the smallest [[subset]] of attributes guaranteed to uniquely differentiate each tuple in a relation. Since each tuple in a relation must be unique, every relation necessarily has a key, which may be its complete set of attributes. A relation may have multiple keys, as there may be multiple ways to uniquely differentiate each tuple.<ref name="professionals"/>{{rp|31–33}}

An attribute may be unique across tuples without being a key. For example, a relation describing a company's employees may have two attributes: ID and Name. Even if no employees currently share a name, if it is possible to eventually hire a new employee with the same name as a current employee, the attribute subset {Name} is not a key. Conversely, if the subset {ID} is a key, this means not only that no employees ''currently'' share an ID, but that no employees ''will ever'' share an ID.<ref name="professionals"/>{{rp|31–33}}

==== Foreign keys ====
{{main|Foreign key}}

A ''foreign key'' is a subset of attributes ''A'' in a relation ''R<sub>1</sub>'' that corresponds with a key of another relation ''R<sub>2</sub>'', with the property that the [[Projection (relational algebra)|projection]] of ''R<sub>1</sub>'' on ''A'' is a subset of the projection of ''R<sub>2</sub>'' on ''A''. In other words, if a tuple in ''R<sub>1</sub>'' contains values for a foreign key, there must be a corresponding tuple in ''R<sub>2</sub>'' containing the same values for the corresponding key.<ref name="professionals"/>{{rp|34}}

=== Relational operations ===
Users (or programs) request data from a relational database by sending it a [[database query|query]]. In response to a query, the database returns a result set.

Often, data from multiple tables are combined into one, by doing a [[Join (SQL)|join]]. Conceptually, this is done by taking all possible combinations of rows (the [[Cartesian product]]), and then filtering out everything except the answer.

There are a number of relational operations in addition to join. These include project (the process of eliminating some of the columns), restrict (the process of eliminating some of the rows), union (a way of combining two tables with similar structures), difference (that lists the rows in one table that are not found in the other), intersect (that lists the rows found in both tables), and product (mentioned above, which combines each row of one table with each row of the other). Depending on which other sources you consult, there are a number of other operators&nbsp;– many of which can be defined in terms of those listed above. These include semi-join, outer operators such as outer join and outer union, and various forms of division. Then there are operators to rename columns, and summarizing or aggregating operators, and if you permit [[relation (database)|relation]] values as attributes (relation-valued attribute), then operators such as group and ungroup.

The flexibility of relational databases allows programmers to write queries that were not anticipated by the database designers. As a result, relational databases can be used by multiple applications in ways the original designers did not foresee, which is especially important for databases that might be used for a long time (perhaps several decades). This has made the idea and implementation of relational databases very popular with businesses.

=== Database normalization ===
{{Main|Database normalization}}
[[Relation (database)|Relation]]s are classified based upon the types of anomalies to which they're vulnerable. A database that is in the [[first normal form]] is vulnerable to all types of anomalies, while a database that is in the domain/key normal form has no modification anomalies. Normal forms are hierarchical in nature. That is, the lowest level is the first normal form, and the database cannot meet the requirements for higher level normal forms without first having met all the requirements of the lesser normal forms.<ref name="Normalization">David M. Kroenke, ''Database Processing: Fundamentals, Design, and Implementation'' (1997), Prentice-Hall, Inc., pages 130–144</ref>