AI Glossary / Data engineering and architecture / Intermediate
Derived Relation
Definition
A derived relation is a relation obtained by applying an expression or query to other relations rather than treating its contents as independently supplied base data. Filtering, projection, joins, and other relational operations can produce derived relations. In SQL, views and derived tables illustrate related practical mechanisms. The theoretical relational meaning and SQL behavior are not identical in every respect, particularly around duplicates, nulls, and ordering.
What it means
A database may store individual orders but let users work with a derived set of large orders or a summary by customer. The derived information has a definition that connects it to its inputs. This can make reusable logic clearer and reduce repeated calculations in application code. It does not necessarily mean the result is physically stored, automatically refreshed, or directly editable; those are separate design and implementation choices.
How it works
Define an expression over available relations and evaluate it against the relevant database state. A filter chooses tuples meeting a condition; a projection selects attributes; a join combines matching information. A named view can retain the definition for reuse. A materialized result stores computed data and needs an appropriate maintenance policy. SQL implementations may optimize or rearrange evaluation while preserving the required query semantics. Consumers should understand the result's grain, keys, and dependence on its source data.
Three practical examples
- Reporting: a team derives a customer-level order summary from individual order records, defining exactly which statuses and dates contribute.
- Application interfaces: a view exposes a useful subset of columns and rows, subject to correctly designed database permissions and view behavior.
- AI preparation: a pipeline derives eligible training examples from source records through explicit filters, preserving the definition so the selection can be reproduced.
Think of it this way
Think of a recipe that turns ingredients into a particular dish. The recipe explains where the result comes from, but the dish may be prepared on demand or kept ready for later use. The analogy is limited because relational derivation follows precise data operations, and the result's freshness depends on evaluation or refresh timing. Naming the recipe does not itself keep a prepared dish current.
Worked example
A toy Orders relation contains (O1, C1, 40), (O2, C1, 70), and (O3, C2, 90), with attributes OrderID, CustomerID, and Amount. Filtering for Amount at least 60 yields O2 and O3. Projecting CustomerID from that filtered relation yields C1 and C2 under relational set semantics. A different derivation groups all orders by customer and sums amounts, producing C1 = 110 and C2 = 90. These results answer different questions. If another qualifying order for C1 is added, a mathematical projection still contains C1 once, while an SQL SELECT without DISTINCT can contain repeated C1 values. The expression and language semantics must therefore be explicit.
Illustrative case study
Fictional example: Cedar Metrics maintains several reports that each implement “eligible order” differently. Analysts define one reviewed derived view and compare it with known examples, including canceled and late-arriving orders. They document the view's meaning and which reports use it. A later policy change updates the definition through review rather than silently editing one dashboard. This invented case illustrates reusable derivation; it does not claim that a view guarantees correct business definitions or improves performance automatically.
Common misconceptions and mistakes
Do not assume a derived relation is a permanent copied table. Do not confuse a view's definition with a materialized view's stored contents. Avoid treating a summary row as though it were one original event. Updating derived data can be ambiguous, especially after joins or aggregation, and databases impose specific rules. Also remember that SQL result order is not guaranteed without an appropriate ORDER BY in the final query; a relational result is not inherently a displayed sequence.
Business use
Derived relations help make transformations reviewable and reusable. Define the intended grain and validate counts and measures against small examples before using them widely. Track source dependencies so schema changes do not unexpectedly alter or break consumers. For AI datasets, retain both the derivation logic and the source snapshot or version needed to reproduce it. A saved query alone may produce different examples later if its underlying data changes.
People also ask
Is a derived relation always a view?
No. A query can produce an unnamed derived result, while a view is one way to name a derivation.
Is it always stored physically?
No. Some results are computed when requested; materialized forms store results under a refresh policy.
Can it always be updated?
No. Update behavior depends on the derivation and database rules, and some results have no unambiguous mapping back to base rows.
Check your understanding
A customer summary shows a total of 110 from two orders. Can an application change that total to 100 without deciding which base order values should change?
Answer: Not unambiguously. Several underlying changes could produce the new total. A derived summary does not by itself define the intended update to source data.
Related terms
- Base relation
- View
- Materialized view
- derived table
- relational algebra
- projection
- query result
Learn next
use three small source rows to derive a filtered relation, a projection, and an aggregate. State the grain of each result. Then change one source row and predict which derived outputs change, distinguishing current evaluation from a stored result awaiting refresh.
More in Data engineering and architecture
- Degree of parallelism - Degree of parallelism is the number of execution participants used, or permitted, to work on an operation concurrently.
- Deletion Anomaly - A deletion anomaly occurs when deleting a row to remove one fact unintentionally removes another independent fact that should remain represented in the...
- Delta rule - The delta rule is an error-driven learning rule that adjusts a model's weights in the direction that reduces a squared-error objective.
- Digital curation - Digital curation is the active management of digital material throughout its lifecycle so that it remains understandable, trustworthy and usable for its...
- Digital obsolescence - Digital obsolescence is the loss or weakening of practical access to digital material because the technologies needed to read, interpret or use it are no...
- Dimensional fact model - The Dimensional Fact Model, or DFM, is a graphical conceptual model used to design data warehouses.
Sources
Primary sources consulted: PostgreSQL 18, Table Expressions, https://www.postgresql.org/docs/18/queries-table-expressions.html — opening explanation and derived-table discussion read. CREATE VIEW, https://www.postgresql.org/docs/18/sql-createview.html — documentation excerpts consulted. SQL examples are distinguished from mathematical set semantics. Worked records and practical guidance are original; full editorial review remains pending.
Previous: Derivative-free optimization Next: Description logic