First, second and third normal forms

The first three normal forms in order: atomic attributes, no partial dependency, no transitive dependency; diagnosing a relation and decomposing it into 3NF.

24 cards
5 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
Scenarios
Understand

A few cards from the deck

First normal form (1NF): what is it?
Show the answer
A relation in which every attribute is atomic: a single value per row and column, with no list and no group of repeated columns.
A column matieres holding "Maths, Réseaux" breaks 1NF; so do matiere1, matiere2, matiere3, because it is the same list in disguise.
Ligne(n_cmd, n_produit, quantite, libelle) has key (n_cmd, n_produit), with (n_cmd, n_produit) → quantite and n_produit → libelle. What is its highest normal form?
- 1FN
- 2FN
- 3FN
- It is not in 1NF
Show the answer
1FN
The values are atomic, so 1NF. But libelle depends on n_produit alone, part of the key: 2NF is broken, and so is 3NF.
Mnemonic: every non-key attribute depends on the key (1NF), the … (2NF) and … (3NF).
Show the answer
Mnemonic: every non-key attribute depends on the key (1NF), the whole key (2NF) and nothing but the key (3NF).
"The whole key" rules out partial dependency; "nothing but the key" rules out transitive dependency.
Partial dependency: what is it?
Show the answer
The dependency of a non-key attribute on only part of the key, when the key is composite.
With the key (n_cmd, n_produit), n_produit → libelle is partial: the label repeats on every order of the product.
Facture(num, date, client, adresse_client) has key num. A customer's address is repeated on each of their invoices. Which normal form is broken, and what should be done?
- 3NF: adresse_client depends on client, not on num; move it to a Client table
- 1NF: the address has several words, so it is not atomic
- 2NF: the address depends on only part of the key num
- None: num is a key, so the relation is in 3NF
Show the answer
3NF: adresse_client depends on client, not on num; move it to a Client table
The key num has a single attribute: no partial dependency is possible. The chain num → client → adresse_client is transitive, which is exactly a 3NF violation.

Study this deck