Database Normalisation Flashcards
What is the main goal of database normalisation, and why?
The main goal of database normalisation is to eliminate data redundancy.
Doing so reduces the size of the database and avoids problems with database operations.
What is data redundancy?
The same data occuring multiple times in different parts of the database
What is functional dependency?
Functional dependency describes the relation between attributes in a relation - if A and B are attributes of a relation, B is functionally dependent on A if each value of A is associated with exactly one value of B
- A->B means B is functionally dependent on A
What is full functional dependency?
A full functional dependency describes a functional dependency where, if A functionally determines B, B does not functionally depend on any proper subset of A.
What is normalisation?
A technique for producing a set of relations with desirable properties, given the data requirements of an enterprise.
When is a database in first normal form?
(AKA 1NF)
- Each row must contain only one value for each column
- Relations obtained by our translation from conceptual to logical design are automatically in first normal form
- This means there should be no multi-valued attributes in columns, they should be stored in a separate table.
When is a database in second normal form?
(AKA 2NF)
- Each attribute individually functionally depends on the whole primary key, not just a single part of the primary key.
- Decompose the relational schema along the functional dependencies that is only full on a partial key (with this partial key as primary key of the new schema)
What is meant by transitive dependency?
If A->B and B->C, then C is transitively dependent on A via B.
When is a database in third normal form?
(AKA 3NF)
- A relation that is in 2NF and in which no non-primary-key attribute is transitively dependent on the primary key is in third normal form.
- Each attribute functionally depends on the primary key only, and no other attributes.
