Therelational model (RM) is an approach to managingdata using astructure and language consistent withfirst-order predicate logic, first described in 1969 by English computer scientistEdgar F. Codd,[1][2] where all data are represented in terms oftuples, grouped intorelations. A database organized in terms of the relational model is arelational database.
The purpose of the relational model is to provide adeclarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and let thedatabase management system software take care of describing data structures for storing the data and retrieval procedures for answering queries.
Most relational databases use theSQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. Atable in a SQLdatabase schema corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates. However, SQL databasesdeviate from the relational model in many details, and Codd fiercely argued against deviations that compromise the original principles.[3]
The relational model was developed byEdgar F. Codd as a general model of data, and subsequently promoted byChris Date andHugh Darwen among others. In their 1995The Third Manifesto, Date and Darwen try to demonstrate how the relational model can accommodate certain "desired"object-oriented features.[4]
Some years after publication of his 1970 model, Codd proposed athree-valued logic (True, False, Missing/NULL) version of it to deal with missing information, and in hisThe Relational Model for Database Management Version 2 (1990) he went a step further with a four-valued logic (True, False, Missing but Applicable, Missing but Inapplicable) version.[5]
Arelation consists of aheading and abody. The heading defines aset ofattributes, each with aname anddata type (sometimes called adomain). The number of attributes in this set is the relation'sdegree orarity. The body is a set oftuples. A tuple is a collection ofnvalues, wheren is the relation's degree, and each value in the tuple corresponds to a unique attribute.[6] The number of tuples in this set is the relation'scardinality.[7]: 17–22
Relations are represented byrelationalvariables orrelvars, which can be reassigned.[7]: 22–24 Adatabase is a collection of relvars.[7]: 112–113
In this model, databases follow theInformation Principle: At any given time, allinformation in the database is represented solely by values within tuples, corresponding to attributes, in relations identified by relvars.[7]: 111
A database may define arbitraryboolean expressions asconstraints. If all constraints evaluate astrue, the database isconsistent; otherwise, it isinconsistent. If a change to a database's relvars would leave the database in an inconsistent state, that change is illegal and must not succeed.[7]: 91
In general, constraints are expressed using relational comparison operators, of which just one, "is subset of" (⊆), is theoretically sufficient.[8]
Two special cases of constraints are expressed askeys andforeign keys:
Acandidate key, or simply akey, is the smallestsubset 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.[7]: 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 employeescurrently share an ID, but that no employeeswill ever share an ID.[7]: 31–33
Aforeign key is a subset of attributesA in a relationR1 that corresponds with a key of another relationR2, with the property that theprojection ofR1 onA is a subset of the projection ofR2 onA. In other words, if a tuple inR1 contains values for a foreign key, there must be a corresponding tuple inR2 containing the same values for the corresponding key.[7]: 34
Users (or programs) request data from a relational database by sending it aquery. In response to a query, the database returns a result set.
Often, data from multiple tables are combined into one, by doing ajoin. Conceptually, this is done by taking all possible combinations of rows (theCartesian 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 – 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 permitrelation 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.
Relations are classified based upon the types of anomalies to which they're vulnerable. A database that is in thefirst 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.[9]
The relational model is aformal system. A relation's attributes define a set oflogicalpropositions. Each proposition can be expressed as a tuple. The body of a relation is a subset of these tuples, representing which propositions are true. Constraints represent additional propositions which must also be true.Relational algebra is a set of logical rules that canvalidlyinfer conclusions from these propositions.[7]: 95–101
The definition of atuple allows for a unique empty tuple with no values, corresponding to theempty set of attributes. If a relation has a degree of 0 (i.e. its heading contains no attributes), it may have either a cardinality of 0 (a body containing no tuples) or a cardinality of 1 (a body containing the single empty tuple). These relations representBooleantruth values. The relation with degree 0 and cardinality 0 isFalse, while the relation with degree 0 and cardinality 1 isTrue.[7]: 221–223
If a relation of Employees contains the attributes{Name, ID}, then the tuple{Alice, 1} represents the proposition: "There exists an employee namedAlice with ID1". This proposition may be true or false. If this tuple exists in the relation's body, the proposition is true (there is such an employee). If this tuple is not in the relation's body, the proposition is false (there is no such employee).[7]: 96–97
Furthermore, if{ID} is a key, then a relation containing the tuples{Alice, 1} and{Bob, 1} would represent the followingcontradiction:
Under theprinciple of explosion, this contradiction would allow the system to prove that any arbitrary proposition is true. The database must enforce the key constraint to prevent this.[7]: 104
An idealized, very simple example of a description of somerelvars (relation variables) and their attributes:
In thisdesign we have three relvars: Customer, Order, and Invoice. The bold, underlined attributes arecandidate keys. The non-bold, underlined attributes areforeign keys.
Usually onecandidate key is chosen to be called theprimary key and used inpreference over the other candidate keys, which are then calledalternate keys.
Acandidate key is a uniqueidentifier enforcing that notuple will be duplicated; this would make therelation into something else, namely abag, by violating the basic definition of aset. Both foreign keys and superkeys (that includes candidate keys) can be composite, that is, can be composed of several attributes. Below is a tabular depiction of a relation of our example Customer relvar; a relation can be thought of as a value that can be attributed to a relvar.
| Customer ID | Name |
|---|---|
| 123 | Alice |
| 456 | Bob |
| 789 | Carol |
If we attempted toinsert a new customer with the ID123, this would violate the design of the relvar sinceCustomer ID is aprimary key and we already have a customer123. TheDBMS must reject atransaction such as this that would render thedatabase inconsistent by a violation of anintegrity constraint. However, it is possible to insert another customer namedAlice, as long as this new customer has a unique ID, since the Name field is not part of the primary key.
Foreign keys areintegrity constraints enforcing that thevalue of theattribute set is drawn from acandidate key in anotherrelation. For example, in the Order relation the attributeCustomer ID is a foreign key. Ajoin is theoperation that draws oninformation from several relations at once. By joining relvars from the example above we couldquery the database for all of the Customers, Orders, and Invoices. If we only wanted the tuples for a specific customer, we would specify this using arestriction condition. If we wanted to retrieve all of the Orders for Customer123, we couldquery the database to return every row in the Order table withCustomer ID123 .
There is a flaw in ourdatabase design above. The Invoice relvar contains an Order ID attribute. So, each tuple in the Invoice relvar will have one Order ID, which implies that there is precisely one Order for each Invoice. But in reality an invoice can be created against many orders, or indeed for no particular order. Additionally the Order relvar contains an Invoice ID attribute, implying that each Order has a corresponding Invoice. But again this is not always true in the real world. An order is sometimes paid through several invoices, and sometimes paid without an invoice. In other words, there can be many Invoices per Order and many Orders per Invoice. This is amany-to-many relationship between Order and Invoice (also called anon-specific relationship). To represent this relationship in the database a new relvar should be introduced whoserole is to specify the correspondence between Orders and Invoices:
OrderInvoice (Order ID,Invoice ID)
Now, the Order relvar has aone-to-many relationship to the OrderInvoice table, as does the Invoice relvar. If we want to retrieve every Invoice for a particular Order, we can query for all orders whereOrder ID in the Order relation equals theOrder ID in OrderInvoice, and whereInvoice ID in OrderInvoice equals theInvoice ID in Invoice.
Adata type in a relational database might be the set of integers, the set of character strings, the set of dates, etc. The relational model does not dictate what types are to be supported.
Attributes are commonly represented ascolumns,tuples asrows, andrelations astables. A table is specified as a list of column definitions, each of which specifies a unique column name and the type of the values that are permitted for that column. Anattributevalue is the entry in a specific column and row.
A databaserelvar (relation variable) is commonly known as abase table. The heading of its assigned value at any time is as specified in the table declaration and its body is that most recently assigned to it by anupdate operator (typically, INSERT, UPDATE, or DELETE). The heading and body of the table resulting from evaluating a query are determined by the definitions of the operators used in that query.
SQL, initially pushed as thestandard language forrelational databases, deviates from the relational model in several places. The currentISO SQL standard doesn't mention the relational model or use relational terms or concepts.[citation needed]
According to the relational model, a Relation's attributes and tuples aremathematical sets, meaning they are unordered and unique. In a SQL table, neither rows nor columns are proper sets. A table may contain both duplicate rows and duplicate columns, and a table's columns are explicitly ordered. SQL uses aNull value to indicate missing data, which has no analog in the relational model. Because a row can represent unknown information, SQL does not adhere to the relational model'sInformation Principle.[7]: 153–155, 162
 | This sectiondoes notcite anysources. Please helpimprove this section byadding citations to reliable sources. Unsourced material may be challenged andremoved.(December 2023) (Learn how and when to remove this message) |
Basic notions in the relational model arerelation names andattribute names. We will represent these as strings such as "Person" and "name" and we will usually use the variables and to range over them. Another basic notion is the set ofatomic values that contains values such as numbers and strings.
Our first definition concerns the notion oftuple, which formalizes the notion of row or record in a table:
The next definition definesrelation that formalizes the contents of a table as it is defined in the relational model.
Such a relation closely corresponds to what is usually called the extension of a predicate infirst-order logic except that here we identify the places in the predicate with attribute names. Usually in the relational model adatabase schema is said to consist of a set of relation names, the headers that are associated with these names and theconstraints that should hold for every instance of the database schema.
One of the simplest and most important types of relationconstraints is thekey constraint. It tells us that in every instance of a certain relational schema the tuples can be identified by their values for certain attributes.
A superkey is a set of column headers for which the values of those columns concatenated are unique across all rows. Formally:
A candidate key is a superkey that cannot be further subdivided to form another superkey.
Functional dependency is the property that a value in a tuple may be derived from another value in that tuple.
algorithm derive candidate keys from functional dependenciesisinput: a setS of FDs that contain only subsets of a headerHoutput: the setC of superkeys that hold as candidate keys in all relation universes overH in which all FDs inS holdC := ∅ // found candidate keysQ := {H } // superkeys that contain candidate keyswhileQ <> ∅do letK be some element fromQQ :=Q – {K }minimal :=truefor eachX->YinSdoK':= (K –Y) ∪X // derive new superkeyifK'⊂Kthenminimal :=falseQ :=Q ∪ {K'}end ifend forifminimaland there is not a subset ofK inCthen remove all supersets ofK fromCC :=C ∪ {K }end ifend whileOthermodels include thehierarchical model andnetwork model. Somesystems using these older architectures are still in use today indata centers with high data volume needs, or where existing systems are so complex and abstract that it would be cost-prohibitive to migrate to systems employing the relational model. Also of note are newerobject-oriented databases.[10] andDatalog.[11]
Datalog is a database definition language, which combines a relational view of data, as in the relational model, with a logical view, as inlogic programming. Whereas relational databases use a relational calculus or relational algebra, withrelational operations, such asunion,intersection,set difference andcartesian product to specify queries, Datalog uses logical connectives, such asif,or,and andnot to define relations as part of the database itself.
In contrast with the relational model, which cannot express recursive queries without introducing a least-fixed-point operator,[12] recursive relations can be defined in Datalog, without introducing any new logical connectives or operators.
| Types | |
|---|---|
| Concepts | |
| Objects | |
| Components | |
| Functions | |
| Related topics | |
