Relational model: a pot full of tables
About this app
The practice database prodat as a pot full of tables: relation, attribute and tuple on the table tools, a relation as a subset of name × tag after Codd, keys as joints (products.tool → tools.id, NULL), plus 100 million rows: a full scan against a B-tree index and an SQL sentence next to a loop.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
What the relational model is
A relational database consists of tables, which the model calls relations. Each column is an attribute with a domain, such as TEXT or INTEGER, each row is a tuple. The name comes from mathematics: in 1970 Edgar F. Codd described a relation as a subset of the Cartesian product of the domains. Tables are connected through keys: a column such as products.tool holds the id of a row in tools. A join uses this connection, and NULL stands for an unknown value.
What the app shows
The pot holds the practice database prodat with customers, orders, products, deliveries and tools; joints connect the key columns. You can pull the table tools closer, columns and rows are highlighted, and the grid name × tag shows that 5 of 15 pairs are in the table. The workshop connects products with their tools. The scene Speed compares a full scan with a B-tree index on up to 100 million rows.
Try it yourself
Move the number of rows from 10 to 100 million and start the search: the index needs 3 levels, the full scan every row. With a branching factor of 2 the B-tree becomes binary search with 27 comparisons. Raise the number of queries to 10,000 and compare 27.8 hours with 3 seconds (model). In the scene Query you switch the index off and see how the database changes its plan.
Frequently asked questions
- Why are tables called relations?
- Because mathematically a table is a relation: a subset of the Cartesian product of the domains of its columns. The name comes from Codd's article of 1970 and does not refer to relationships between tables.
- What is the difference between an attribute and a tuple?
- An attribute is a column with a name and a domain, such as price of type INTEGER. A tuple is a row, that is one value per attribute, such as T-1000, 76, 540, A.
- Why is an index so fast?
- Without an index the database has to check every row. A B-tree branches into many parts at each node; with 500, 3 levels are enough for 100 million rows, so 3 page reads.
- What can I try in the app?
- Pull tables closer, highlight columns and rows, look at the grid name × tag, draw joints between products and tools, search up to 100 million rows and compare the plan of an SQL query with and without an index.
Subject: Computer science
More from Computer science
- Ackermann function
- Elevator simulator
- Binary workshop
- Binary versus linear search
- The Collatz problem
- Inside a CPU
- Swap the inside
- One frame in 16 milliseconds — how a game engine renders an image (3D)
A VisuApp by heyprof: interactive, free in your browser, no sign-up. What is a VisuApp? · All apps