Relational Database

A RelationalDatabase is a database constituting a set of relations (usually erroneously referred to as "tables"[1]). A relation is a set of tuples (a.k.a. "records"), a tuple being a set of attribute values, each attribute being identified by its name (and not by any ordinal position).

If this sounds pretty mathematical, that's because it's based on the mathematics of relations, which makes a lot of nice properties about relational databases provable.

More accurately, the relational model is based on predicate logic and set theory. You have sets of statements of fact, and the underlying system can determine new sets of facts from those. For efficiency, you have to organize those facts into groups that share the same structure (names and associated type domains), and in practice your ability to draw conclusions is limited by the system's command language (SQL poorly implements predicate logic and set theory; and the database doesn't do much of your thinking for you). The real power comes from your complete control over determining new facts. All relationships between facts are explicit in the database, and the command language can use and manipulate them. The mathematics behind the model make this manipulation feasible.

Relational Databases have basically replaced RollYourOwnDatabases for most non-trivial applications (see NavigationalDatabase). Using an ObjectRelationalMapping can introduce conceptual and performance problems; people have developed ObjectOrientedDatabase systems to address these problems. Alternatively, CrossingChasms is a large pattern language that describes how to connect an object system to a RelationalDatabase.

Relational requires that have a way to uniquely identify tuples by a set of one or more columns. These are the candidate keys, one of which is commonly called the primary key. Relations can be stored or derived, as in SQL VIEWs (note SQL ain't relational).

Relational operators transform a set of one or more relations in a result table, perhaps with a set of expressions as input (such as "salesAmount > 35.00").

RelationalLanguages are generally based on FunctionalProgramming and LogicProgramming concepts in that you ask for what you want instead of spelling out how to obtain it.

Not quite. Physical vs virtual relations is an implementation detail, as it's possible for for all tables to be physical (persisting and remaining updated until explicitly deleted), or any particular mixture (my own implementation keeps all tables up to date unless explicitly copied, and allows any table reference to be dropped at any time; if it's a root table, it won't disappear, if it is, it will). RelationalCalculus is the 'spelling out what you want' variety, RelationalAlgebra still requires you figure out how to derive the table you want from the tables you have.

-- WilliamUnderwood

(Perhaps this should be moved to RelationalModel. Hmmm.)

Relational database theory may well be the first example of a refactorization technique taken to merciless extremes: in a relational database, in each type of data, the items are identified by the key, the whole key, and nothing but the key. Splitting up data to enforce these rules adds data types, reduces certain nasty anomalies, and makes the logic obvious. Doing this normalization at least makes you ask the right questions! The process can convert a user interface into dozens of relational tables in next to no time.

Since relational data is normalized (factorized?) using "know what" responsibilities (only), it does not handle differences in "know how" responsibilities, behaviors, or dynamics. The process also has no generalization/inheritance/derivation/polymorphism constructs. This has (anecdotally) caused conflict between object-oriented programmers and data-base-oriented designers.

-- DickBotting

The process also has no generalization/inheritance/derivation/polymorphism constructs.

Because that's out of scope. On the other hand, TheThirdManifesto includes a proposal for type inheritance which covers that nicely and is orthogonal to RDBMSs.

That's type inheritance for domains (each named field in a relation has an associated domain that defines legal values and legal operations). For aggregating and composing data, you're supposed to have a powerful language (Tutorial D {TutorialDee} or any other valid D -- D being any faithfully RelationalLanguage such as Alphora Dataphor D4, *not* SQL) to do the slice-and-dicing. Having a system that supports writable Views is supposed to make life a lot easier.

Moved from WhyRelationalDatabase?

Explain why Prometheus stole fire and gave it to Mankind. Compare and contrast with the development of RDBMS systems. Include topics on race conditions, integrity, the ACID property, and so forth.

Bonus points for describing strategies for avoiding the creation of external race conditions from the database. For example, don't try to do the constraint's job for it. You won't get it right. Let the database call the shots about what data is invalid.

A corruption of Relational Databases, namely SQL databases, have been in use on a widespread basis since at least the mid 1980's. They have been and continue to be valuable, useful and easy to modify and maintain, processing large volumes of "related" information in an organized, accessible form and require little to no programming experience to begin to use productively. About race conditions and the like, I haven't run into them in the 20 years I have used Relational Databases in an Engineering Environment. My question is Why not Relational Databases?

Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational systems is that a single database can be spread across several relations.

[1] My understanding is that "relation" is the mathematical term for "table", and is NOT about "links". And it is the formal definition of "table". Thus, I don't know why you say it's an "error" to use in interchangeably with "table". I read that "table" is not a mathematical term, and so DrCodd used "relational" instead. Any math vocab whizzes here? --top

Tables are often used to implement relations (finite relations, in particular, which are generally sufficient for data), but don't have the properties of relations. Relations, being mathematical sets, have certain properties: they are unordered, they don't admit to duplication (an element is either in the set or not), and they may be infinite. Tables, however, are ordered (and queries often support 'order by'), allow duplication (in the general case), and are finite.

It is an error to use "relation" interchangeably with "table". It is also an error to say that "relation" is "the formal definition of 'table'". Saying that "relation" is the mathematical term for "table" is analogous to saying "set" is the mathematical term for "linked list".

As far as "links" go, a relation is (appropriately) about a 'relationship' that holds between values in different domains. Each relationship is expressed as a tuple, and a relation is a set of such tuples. The word "link" is ambiguous enough that one could get away with calling a relationship a "link", but, as with most ambiguous words, ItDepends on the context in which the word "link" is used.

See also: RelationalModel, LetsUseAnObjectOrientedDatabase, ObjectsVsRdbmsPerformance, NoMoreDatabases, SqlMyopia, RefactoringWithRelationalDatabases, VeryLargeDatabaseFoundation, ObjectRelationalImpedanceMismatch, SparseColumns, SqlFlaws, RelationalLanguage, HistoryOfRelational.

A list of implementations:

CategoryDatabase CategoryRelationalDatabase

EditText of this page (last edited March 8, 2012) or FindPage with title or text search