Data are commonly stored in databases and accessed via a database management system (DBMS) such as PostgreSQL. Together, these make up a database system that can be defined, at least in part, by its data model. The relational data model is the most common model for representing data relationships in databases.<\/p>\n
This model, having been around 50 years, is based on set theory and provides a robust framework by which simple and complex relationships can be modeled within a database system.<\/p>\n
The relational data model (RM)<\/strong> is the most widely-used modeling system for database data. It was first described by Edgar F. Codd in his 1969 work A Relational Model of Data for Large Shared Data Banks<\/em> [1].<\/p>\n Codd’s relational model replaced the hierarchical data model\u2014which had many performance drawbacks. The Structured Query Language (SQL)<\/a> was developed to support this new data model and, in 1987, was adopted as the official ANSI-approved language for relational database models [2].<\/p>\n Relational data models are characterized by the following traits:<\/p>\n In relationship to predicate logic, relational data languages such as SQL correspond in the following way:<\/p>\n For example, the predicate statement there exists data such that data is a Person and the data is a Student<\/em>\u00a0roughly translates to the following SQL query statement:<\/p>\n SQL is the most common language used to query relational databases. However,\u00a0 modern SQL-driven applications may implement certain features that do not conform to Codd’s original outline of the relational model. Some of these language features include the following:<\/p>\n SQL provides a robust means to access, manipulate, and update databases. Queries can be written by users and programmers to access databases in varying degrees of complexity. The power of relational database design comes from the ability to specify interrelatedness<\/strong> among data.<\/p>\n For example, the most basic SQL query might select all rows from all tables. While having the benefit of being syntactical simple, this doesn’t provide much functional<\/em> or benefit given that it returns all\u00a0<\/em>the data. This would be achieved with the following statement:<\/p>\n More often, users desire a filtered view of the data. An example might be a university registrar employee querying the student database for a single student, all students in a particular class, or all students with a GPA of 3.5 or higher.<\/p>\n These filtered views can execute logically across single tables or multiple tables using a join<\/em> operation. In addition to the join<\/em> operation, there are several other useful relational operations that can be leveraged to result in logically filtered data:<\/p>\n SQL operations such as these draw their theoretical basis from set theory<\/a>. By applying such theory, a wide range of operations can be defined and engineered for optimization in dealing with relational databases.<\/p>\n Another mathematical basis for the relational data model is that of domain relational calculus (DRC). This was used as early as 1977 by Michael Lacroix and Alain Pirotte in their\u00a0Domain-Oriented<\/em>\u00a0relational Languages<\/em> work [3]. In the words of those authors:<\/p>\n “DRC is a many-sorted calculus, where the structural role of domains is emphasized by defining types for variables and type-checking rules”<\/em><\/p>\n The context of this statement within the domain of the relational data model isn’t immediately clear. However, the abstract of their paper continues to provide a slightly clearer clarification:<\/p>\n “Domain-languages” are contrasted with “tuple languages”, which manipulate as basic objects the relation n-tuples. Directly manipulating domains and domain values interacts more directly with the semantics expressed by the relations and produces simpler and more English-like languages”<\/em><\/p>\n A simplified interpretation is DRC allows the querying of data by broader relationships as opposed to specific values. For example, the following statement is considered a domain relational query:<\/p>\n As opposed to a tuple-relational model that would require something along these lines (pseudocode):<\/p>\n Consider the database design for a stock-trading application. At its very core, it would need to keep track of which company an investor owns securities in, which exchange those securities are traded on, the price paid, and the date on which that position was taken. Below is a sample design of two tables, where the bold ID attribute is the primary key for each:<\/p>\nDefining the Relational Model<\/h2>\n
\n
Logical Design<\/h2>\n
\n
SELECT type\r\nFROM person\r\nWHERE type = 'student'<\/pre>\n
SQL vs. Relational Model<\/h2>\n
\n
Relational Operations<\/h2>\n
SELECT *\r\nFROM *<\/pre>\n
\n
Mathematical Theory for Relational Design<\/h2>\n
SELECT name\r\nFROM person\r\nWHERE name = \"bob\"<\/pre>\n
select person.name = 'bob' and person.company = 'acme'<\/code><\/p>\nExamples of Relational Design<\/h2>\n