PostgreSQL Language

So how do you pronounce SQL? According to ISO/IEC 9075-1 part 1 Framework, SQL is pronounced "ess cue ell" SQL is a database language and more precisely a data sublanguage used for access to pseudo-relational databases that managed by pseudo-relational database management systmes (RDMBS). And, get this, SQL is not a accronym for Structured Query Language, actually they do not stand for anything at all.

SQL is based on, but is not a strict implementation of, the relational model of data Relational Model, making SQL “pseudo-relational” instead of truly relational.

But what is a Relation

Definition: Relation

Given sets $S_1$, $S_2$, ... $S_n$ where the sets don have to be distinct. A relation $R$ is a relation on the $n$ sets if is a subset of the Cartesian Project $S1\times S2\times \dots Sn$. In mathematical notation this should look familar.

$$R\subset S1\times S2\times \dots Sn$$

The collection of sets $Sn$ are defined as the domain.

Math vs Practice

A relation $R$ in the relational model is very similar to the definition above. A database table I will also call $R$ has the following properties.

• Each row represents a tuple of $R$.
• The ordering of rows does not matter
• All rows are distinct from one another in content.

A Table Is Equal to a Relation

You can think of a table as a persistent representation of a logical relation that is, a relation whose contents can be permanently saved for future use. As far as the table’s user is concerned, a table contains a group of related entity occurrences—that is, an entity set.

#	Characteristic
1	A table is perceived as a two-dimensional structure composed of rows and columns.
2	Each table row (tuple) represents a single entity occurrence within the entity set.
3	Each table column represents an attribute, and each column has a distinct name.
4	Each intersection of a row and column represents a single data value.
5	All values in a column must conform to the same data format.
6	Each column has a specific range of values known as the attribute domain.
7	The order of the rows and columns is immaterial to the DBMS.
8	Each table must have an attribute or combination of attributes that uniquely identifies each row.

Keys

In the relational model, keys are important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data.

The role of a key is based on the concept of determination. Determination is the state in which knowing the value of one attribute makes it possible to determine the value of another.

Relational Database Keys

Key Type	Definition
Superkey	An attribute or combination of attributes that uniquely identifies each row in a table.
Candidate key	A minimal (irreducible) superkey; a superkey that does not contain a subset of attributes that is itself a superkey.
Primary key	A candidate key selected to uniquely identify all other attribute values in any given row; cannot contain null entries.
Foreign key	An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null.
Secondary key	An attribute or combination of attributes used strictly for data retrieval purposes.

Integrity Rules

Entity Integrity

Aspect	Description
Requirement	All primary key entries are unique, and no part of a primary key may be null.
Purpose	Each row will have a unique identity, and foreign key values can properly reference primary key values.
Example	No invoice can have a duplicate number, nor can it be null; all invoices are uniquely identified by their invoice number.

Referential Integrity

Aspect	Description
Requirement	A foreign key may have either a null entry (as long as it is not part of its table’s primary key) or an entry that matches the primary key value in the related table. Every non-null foreign key value must reference an existing primary key value.
Purpose	An attribute may legitimately lack a value, but invalid entries are prevented. Enforcement of referential integrity makes it impossible to delete a row whose primary key has mandatory matching foreign key values in another table.
Example	A customer might not yet have an assigned sales representative number, but it is impossible to have an invalid sales representative number.