A database is a collection of data that is organised structurally according to a specific well-defined set of rules.
Database Structure
A database is made up of relations, which we informally refer to as tables. What we informally call columns are its attribute, which contain specific values. On the other hand, its tuples are what we refer to when we say records or rows.
Keys and Dependency
The concept of keys is important in databases:
- A superkey is a set of attributes that identifies each tuple in a relation.
- A candidate key is the minimal of the superkeys (whether the only one or one among many).
- A primary key is the candidate key we choose to identify a tuple.
- A foreign key is a primary key of a relation included within another relation.
Given the nature of keys, the concept of dependency arises:
- A functional dependency is an attribute value dependency that behaves just like a function.
- A partial dependency is a functional dependency where only a part of the candidate key determines the values in another attribute.
- A transitive dependency is a functional dependency where an attribute is dependant on a key by means of another attribute (indirectly, not directly).
Database Normalisation
To avoid errors and anomalies, it is desirable to normalise a database. This normalisation is carried out by making all the relations in a database be compliant with the so-called normal forms:
- The first normal form requires all the attribute values to be atomic.
- The second normal form requires to have no partial dependencies.
- The third normal form requires to have no transitive dependencies.