2

This phrase from Wikipedia left me confused:

The relational model (RM) is an approach to managing data using a structure and language consistent with first-order predicate logic.

The concept of structure is new to me, as I don’t have a formal math background. According to Wikipedia:

In universal algebra and in model theory, a structure consists of a set along with a collection of finitary operations and relations that are defined on it.

When I try to apply this definition to the relational model, I'm having trouble mapping the elements I know (tables, operations, constraints) to these mathematical categories:

  1. Set: I understand that the set in the structure could be made up of tables (or relations, but not conventional set theory relation, but Codd relations) in the relational model. Each table, then, would be an element of this set.

  2. Finitary Operations: The finitary operations seem to correspond to relational algebra operations (like selection, projection, union), which act on these tables to produce new ones.

  3. Relations within the Structure: This is where I’m uncertain. First of all, I have not mapped Codd's relations (i.e., tables) to the concept of the conventional relation in math because as I stated in point 1 I understand they are not exactly the same. As a consequence, I've tried to associate the concept of relation within a structure with that of restrictions in the relational model. Although I'm afraid this doesn't make sense...

Note I don’t have the time to build a stronger math foundation, though I hope to do so in the near future. If my question reveals a high level of misunderstanding due to my limited background, I’d welcome any advice on whether it’s more practical to focus on using the relational model rather than fully understanding its theoretical basis until I've actually learnt the math concepts upon it is built.

Does this mapping correctly apply the concept of "structure" to the relational model, or am I missing something fundamental here?

Smylic
  • 8,098
toniozr
  • 21
  • I'm not sure I follow you though, but a structure according to the definition you provided, is, in notation, something like $(A,f,g,h,R,S,T)$ where $f,g,h$ are operations on $A$, i.e., $f:A^n\to A$, $g:A^m\to A$ and $h:A^k\to A$ are maps, and $R \subseteq A^u$, $S\subseteq A^v$ and $T\subseteq A^w$, where $n,m,k,u,v,w$ are natural numbers. But there can be any number of operations and/or relations. In particular, with no relations you have an algebra; with no operations you have a relational structure. – amrsa Nov 03 '24 at 20:33
  • I don't think the word is intended to be understood in a technical sense there. – Zhen Lin Nov 03 '24 at 23:21

1 Answers1

1

The link to the Wikipedia page on structures is arguably misleading for two reasons; overall I recommend you ignore this and just directly work with examples of relational databases to get a sense of how they work. The Wikipedia article on the relational model also strikes me as poorly written and overly technical and formal for no good reason; you'd be better off working from other sources.

In any case, the connection is that relational databases are many-sorted relational structures. This makes them different from the typical structures encountered in mathematics (such as groups) in two ways:

  1. Typical structures are "one-sorted." This means that there is a single set $X$, for example the underlying set of a group. A relational database involves multiple sets $X, Y, Z, \dots$, one for each type of column (not the tables).

  2. Typical structures mostly use functions (operations), for example the multiplication $m : G \times G \to G$ on a group, which is a binary operation. A relational database, as its name suggests, involves solely relations; these are the tables.

Also confusingly, the relational algebra operations are not the operations in the structure; they are defined at a different level of abstraction and are ways to combine relations together, which can be done in any relational structure.

All of this is way too annoying to talk about abstractly so let's work through a specific example. Say you are a library. You'd probably like to keep track of your members and of your books. So let's say you have a members table which looks like this:

Member name Library card number Book ID
Arya 17 13
Raj 23 89
Javier 37 7

So you already have three types of column, Member name, Library card number, and Book ID (for books that a member has currently checked out). These are your sets; let's call them $M, L, B$. Your members table is a subset

$$T_1 \subseteq M \times L \times B$$

which is a ternary relation between names, library card numbers, and book IDs, which answers questions like "what is the name of the member that has this book currently checked out," etc.

You might also have a books table which looks like this:

Book name Book ID Genre
The Joy of Sets 89 Set theory
Flatland 13 Satire
Gödel, Escher, Bach 7 ???

The books table has one column type in common with the members table, namely Book ID, together with two new column types, Book name and Genre, so you have two new sets $N, G$, for a total of five sets. The books table itself is a subset

$$T_2 \subseteq N \times B \times G$$

which is a second ternary relation, and which answers questions like "what books have the same genre as this book," etc.

Relations can be combined into other relations in many ways; let's look at relational composition. In database terms I believe this is called the natural join; here we can compose / join the members table $T_1$ and the books table $T_2$ along their common column $B$, the book IDs, to get a new table $T_1 \circ T_2$ or $T_1 \bowtie T_2$, which in our example looks like this:

Member name Library card number Book ID Book name Genre
Arya 17 13 Flatland Satire
Raj 23 89 The Joy of Sets Set theory
Javier 37 7 Gödel, Escher, Bach ???

You could continue in this way translating between the language used in mathematics and the language used in database management, but you're probably better off sticking to the databases if that's what you ultimately care about.

Qiaochu Yuan
  • 468,795