💡Recall

Functional Dependencies. Normal Forms

in the relational model there are multiple ways to model a data collection (using tables)
database design should assure that
- no additional tests are required when changing data
- operations can be performed through SQL statements alone
- the above operations can be easily carried out when relations satisfy certain conditions (i.e. relations are in a certain normal form)
common normal forms: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF [defined by Codd (the first 3), Boyce and Codd (BCNF), and Fagin (4NF, 5NF)]

generates a series of problems
ideally, only redundancy-free schemas should be allowed ore one should at least be able to identify schemas with redundancy, even if such schemas are allowed (e.g. for performance reasons)
can be tackled by replacing a relation with a collection of smaller relations - each of the latter contains a (strict) subset of attributes from the original relation but such decompositions can generate new problems (e.g. some data could be lost)
- if a relation is not in normal form X, it can be decomposed into multiple relations that are in normal form X
- to decompose a relation we use:
  - the projection operator
  or
  - the natural join operator
a relation $R$ can be decomposed into multiple new relations $R1,R2,...Rm$ and the decomposition is good if $R=R1R2...*Rm$ , i.e. the data can be obtained from the data stored in relations $R1,R2,...Rm$ (no data is added/lost through decomposition/composition)

simple attribute
composite attribute = a set of attributes in a relation (with at least two attributes)
repeating attributes
- in some applications, attributes (simple or composite) can take on multiple values for a record in the relation
- cannot be used in the relational model, hence they are to be avoided, without losing data
  
  Let $R[A]$ be a relation, $A$ the set of attributes. Let $α$ be a repeating attribute in $R$ (simple or composite):
  
  $R$ can be decomposed into 2 relations, such that $α$ is not a repeating attribute anymore If $K$ is a key in $R$, the two relations into which $R$ is decomposed are:
  
  $R'[K\cupα] = \prod_{K\cupα}^{}(R)$
  
  $R''[A-α] = \prod_{A-α}^{}(R)$
attribute values must be scalar, atomic (they cannot be further decomposed) when defining a relation (in the relational model)

the following 3 relational normal forms use a very important notion: the functional dependency among subsets of attributes
the database administrator is responsible with determining the functional dependencies
these depend on the nature, the semantics of the data stored in the relation

Let $R[A1,A2,...,An]$ be a relation and $α, β$ two subsets of attributes of $R$.

$α$ → $β$ , the (simple or composite) attribute $α$ functionally determines attribute $β$ (simple or composite)
- if and only if every value of $α$ in $R$ is associated with a precise, unique value for $β$ (this association holds throughout the entire existence of relation $R$ )
If an $α$ value appears in multiple rows, each of these rows will contain the same value for $β$:

$\prod_{α}^{}(r) = \prod_{α}^{}(r')$ $implies$ $\prod_{β}^{}(r) = \prod_{β}^{}(r')$
- in the dependency $α$ → $β$, $α$ is the determinant, and $β$ is the dependent
- the functional dependency can be regarded as a property (restriction) that must be satisfied by the database throughout its existence: values can be added/changed in the relation only if the functional dependency is satisfied
- if a relation contains a functional dependency, associations among values can be stored multiple times (data redundancy)

to describe some of the problems that can arise in this context, we'll consider the EXAM relation, storing students' final exam results:

1st Normal Form
database normalization rule
states that a relation must contain only atomic values, meaning that the values in each field must be single, indivisible values, and not repeating groups of values
in other words, a relation is in 1NF if it contains no repeating groups of data
the goal of 1NF is to ensure that the data in a relation is atomic and that there is no data redundancy
a relation that is in 1NF is also free of duplicate rows and has a unique primary key that identifies each row

2nd Normal Form (2NF)
database normalization rule
states that a relation is in 2NF if it is in 1st Normal Form (1NF) and every non-key attribute is functionally dependent on the entire primary key
in other words, a relation is in 2NF if it contains no partial dependencies
a partial dependency is when a non-key attribute is functionally dependent on only a part of the primary key, rather than the whole primary key
the goal of 2NF is to ensure that the data in a relation is atomic and that there is no data redundancy

3rd Normal Form
database normalization rule that states that a relation is in 3NF if it is in 2nd Normal Form (2NF) and all non-key attributes are non-transitively dependent on the primary key
in other words, a relation is in 3NF if it contains no transitive dependencies
a transitive dependency is when a non-key attribute is functionally dependent on another non-key attribute, rather than the primary key
the goal of 3NF is to ensure that the data in a relation is non-redundant and that the primary key fully determines all non-key attributes
a relation that is in 3NF is also free of duplicate data and has a unique primary key that identifies each row