# DataBases

For the SQL syntax, go to Usage

# In a Nutshell

• Relational database: database that conforms to the relational model
• Relational model: database model based on first-order logic
• Relational Database Management System: database software (e.g., MySQL, PostgresSQL, Oracle,...); not to be confused with the actual relational database (the container, usually a file, storing the data)

A database is comprised of

tables: technically relations, a structured list of data (of a specific type)


columns: a single field (or attribute) in the table with its associated datatype (double, text,...)


where the data is stored in

rows: records (or tuples) in the table


Note that

• views are tables where the data is computed at query time
• it is good practice to always define a primary key per table (they can be defined over multiple columns)

Example:

• R is a relational schema with attributes A, B, C
• r is an instance of R, i.e., the table of tuples $t_1, t_2, \cdots$
• each tuple is comprised of specific values for each attribute: ti = (ai,bi,ci)
• t1.A refers to attribute A of the tuple t1, i.e., a1

A functional relation $A \to B$ is given, if

$t_i.A = t_j.A \Rightarrow t_i.B = t_j.B , i \neq j.$


I.e., if ai = aj then bi = bj. A, B can also refer to sets of attributes.

A key is a functional relation, that identify tuples of a relation uniquely, i.e., a minimal set of attributes that uniquely determines all other attributes.

# Design

Databases should always be normalized or brought into normal form (NF).

This ensures the

• elimination of redundancy
• efficient organize of the data efficiently
• reduces the potential for anomalies during data operations
• improvement of data consistency
• simplification of future extensions of the logical data model

Mnemonic trick for the first three normal forms: the key, the whole key and nothing but the key, meaning:

• all values uniquely identified by a key (atomicity)
• for composite keys the values depend on the whole key
• all values depend only on a key

Usually, most databases are in the 3rd normal form (common sense). An extension of the 3rd normal form is the Boyce-Codd normal form. The fourth and fifth normal forms deal specifically with the representation of many-many and one-many relationships.

### 1st NF

Every field contains atomic values, i.e., can't be decomposed further.


### 2nd NF

Has turned out to be of no practical use

### Boyce-Codd NF

Each set of attributes depends on the key, or formally

every functional relation $X \Rightarrow A$ is either trivial or X must be a (super)key


### 3rd NF

Every functional relation $X \Rightarrow A$ is either trivial
or X must be a (super)key or A is part of some key for the relation