What is the term for a “wide”, non-normalized view/query?

Question

A well-designed relational DB schema tends to have a relatively large count of relatively small, normalized tables — a cluster of snowflakes, I think, describes the situation.

In order to access the relational data, queries are necessary that join these separate tables into relatively large, non-normalized but very views (or virtual tables)

What is the accepted term for such a “wide” view/query -- a practical, "virtual table" resulting from multiple, useful joins of small, normalized tables?

I have given considerable thought over the years to database design; I just don't have much recent hands on experience so am not necessarily au courant with the terminology.

Answer 1

I am quite sure that the term you are looking for is derived relation.

In relational jargon, a single and normalized table is called a base relation (or base table). In a SQL database management system (DBMS), relations (tables) of this category are the ones declared with the aid of the data definition language (DDL).

The fact of applying certain relational operations (such as JOIN, for instance) on these base relations (or base tables), yields a different type of relation, which is known as derived relation (or derived table). It may be useful to think of this kind of relations as “virtual” tables (as you properly stated) that arise as a result of “playing” with the aforementioned base ones. Tables of this class are declared with the aid of the data manipulation language (DML) in a SQL DBMS.

In order to get practical advantages, a derived relation (table) can be set up as a VIEW in a DBMS. Of course, some of them can be wide and some of them can be narrow, and this aspect is determined by the number of base relations involved and the number of attributes (columns) defined in a particular operation.

To support the above, and because you want an accepted term, it is necessary to refer to an authoritative source. The best one is, naturally, the inventor of the Relational Model, Dr. Edgar Frank Codd. Regarding to these type of relations (tables), here is a small excerpt¹ from his paper Extending the Database Relational Model to Capture More Meaning:

Base relations are those which are defined independently of other relations in the database in the sense that no base relation is completely derivable (independently of time) from any other base relation(s). Derived relations are those which can be completely derived from the base relations. It is this kind of relation which is normally employed to provide users or application programs with their own views of the database.

And here is an extract² from his book The Relational Model for Database Management: Version 2:

…[R]elations, or R-tables, that are internally represented by stored data in some implementation-defined way are called the base relations or base R-tables. All R-tables other than base R-tables are called derived relations or, synonymously, derived R-tables. An example of a derived relation is a view. A view is a virtual R-table defined in terms of other R-tables, and is represented by its defining expression only.

References

_{¹ Codd, E. F. (Dec. 1979). Extending the database relational model to capture more meaning, ACM Transactions on Database Systems, Volume 4 Issue 4 (pp. 397-434). New York, NY, USA.}

_{² Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 17-18). Boston, MA, USA: Addison-Wesley}

Answer 2

I've heard the term "flattened" view of the data...meaning the hierarchy of the relationships have been removed.

Answer 3

I guess the term you are looking for is universal relation although mostly the term is used as a hypothetical relation that is the basis of normalization rather than a view-representation of normalized tables made afterwards. From Fundamentals of Database Systems (Elmasri & Navathe), 6th ed. p. 544:

Section 16.3 is devoted to the development of relational design algorithms that start off with one giant relation schema called the universal relation, which is a hypothetical relation containing all the attributes. This relation is decomposed (or in other words, the given functional dependencies are synthesized) into relations that satisfy a certain normal form like 3NF or BCNF and also meet one or both of the desirable properties.

Also take a look at these articles:

• Wikipedia - Universal relation assumption
• Who won the Universal Relation wars? (some entertaining academic history)

Note that in normal business use it would rarely make sense to construct such a view except in the databases with a very small number of relations.