Relational and Set Theory

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

Relations

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 | +----------+----------+----------+

What is a TYPE?

"a named set of values", every value has a "type", even a relation is a type.

eg:    all possible integers - type INTEGER

         all possible character strings - type CHAR

        etc...

 

Every type has an assignable set or operators

eg:     Integer: =: (assignment), =, >, +, - etc

          char: =: (assignment), = , || , substring      

Relations cont.

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;

Coercions between types:

Coercion in relational databases (SQL) is the automatic conversion of a data type into another data type by the database management system. This process happens implicitly during queries to prevent errors when comparing or operating on different data types.

eg:  The column value '1002' is implicitly coerced into the integer 1002 for the comparison.  

SELECT * FROM orders WHERE customer_id_str = 1002;

To be Continued

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