DataBases

From TechWiki

For the SQL syntax, go to Usage

Contents

[edit]

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)

made up of 

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.

[edit]

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.

[edit]

1st NF

Every field contains atomic values, i.e., can#t be decomposed further.
[edit]

2nd NF

Has turned out to be of no practical use

[edit]

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
[edit]

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
Retrieved from "http://j-node.homeip.net/tech_wiki/index.php/DataBases"