Relational and Set Theory

Interesting fundamentals I have learnt about Databases and relations and sets.

A table is a relation set

A view is a relation set

A query result is a relation set

Every relation has a:

• Heading (attribute names and type names pairs)
• Body (set of tuples)

+----------+----------+----------+ | ID (int) | Name(str)| age(int) | +----------+----------+----------+ | 1 | Bob | 23 | | 2 | Trent | 43 | | 3 | Jane | 53 | | 4 | Sherlene | 12 | | 5 | Holly | 50 | +----------+----------+----------+

Relations are defined over types (each attribute (or column) has a type)

The number of columns in a relation is the "degree"

The number of rows/tuples is the "cardinality"

Relations NEVER contain duplicate tuples. SQL and relational databases FAIL this rule. But mathematically a set never contains duplicates.

The set of tuples in a relation are un-ordered. You might think they are, but they are not!

Two tuples are equal if and only if:

1. They have the same attributes (eg attribute names and type names pairs)
2. The attribute values are the same

Two tuples are duplicates is and only if they are equal.

A relation and a database table are different. A table is a picture of a mathematical relation.

Base Relations vs Derived relations

In Relational databases, base relations are tables. A derived relation is a result set from base table(s). Think of Oracles x$ tables and v$views. Or Postgres system catalogs and system views

The following SQL creates a base relation:

create table people (id int, name str, age int);

The following SQL creates a derived relation:

create view people_names as select name from people where age > 30;

To be Continued

****************************************** keywords: ****************************************** rdbms version: ******************************************